agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: M. D. <lists@turnkey.bz>
To: pgsql-sql@postgresql.org
Subject: Re: Re: sum of until (running balance) and sum of over date range in the same query
Date: Tue, 29 Oct 2013 07:19:22 -0600
Message-ID: <526FB5DA.1050306@turnkey.bz> (raw)
In-Reply-To: <1383020238958-5776213.post@n5.nabble.com>
References: <526F2EC0.2030300@turnkey.bz>
<1383020238958-5776213.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 10/28/2013 10:17 PM, David Johnston wrote:
> M. D. wrote
>> What I want is a result set grouped by year/quarter/month/week by item,
>> showing on hand at end of that time period and the sum of the amount
>> sold during that time. Is it possible to get this data in one query?
>> The complication is that the sold qty is over the group, while On Hand
>> is a running balance.
> So my eyes glazed over scanning your post but I notice you are not using
> Window Functions.
>
> http://www.postgresql.org/docs/9.3/interactive/tutorial-window.html
> http://www.postgresql.org/docs/9.3/interactive/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
>
> You need to learn about this concept as it likely will readily solve your
> problem.
>
> SELECT day, month, year, sum(sold_qty) AS qty_sold_day_groupall
> , sum(sum(sold_qty)) OVER (PARTITION BY day) AS qty_sold_day
> , sum(sold_qty) OVER (PARTITION BY month) AS qty_sold_month
> , sum(sold_qty) OVER (PARTITION BY year) qty_sold_year
> , sum(sold_qty) OVER (PARTITION BY year ORDER BY day) AS qty_sold_ytd
> FROM ... GROUP BY day, month, year ORDER BY day
>
> Note the double-sum { sum(sum(...)) OVER () } is needed due to the GROUP BY.
> If you want to use the original data you can omit the GROUP BY and the inner
> sum() invocation.
>
> qty_sold_day_groupall == qty_sold_day
> qty_sold_month & qty_sold_year will repeat (the same same exact value for
> every day in the corresponding month/year).
>
> qty_sold_ytd: this is special because of the ORDER BY. Only the rows prior
> to and including the current day are considered (for the other columns,
> lacking the ORDER BY, every row in the partition is considered) so it
> effectively becomes a running total of all prior days plus the current day.
>
> These are well documented and many window-specific functions exists as well
> as being able to use any normal aggregate function in a window context.
> They take a while to learn but are extremely powerful/useful. Performance
> can become a factor because unlike normal GROUP BY aggregation every
> original row in the source table is output. In the above example we didn't
> want all items to be output so we performed a GROUP BY to aggregate the
> items THEN we used windows to perform the separate aggregates in a window
> fashion.
>
> An alternative method (or can be used in conjunction) would be to separate
> these into multiple sub-queries using CTEs (WITH)
>
> WITH group_items AS ( SELECT day, month, year, sum(sold_qty) AS daily_sale
> FROM items ... )
> , group_aggs AS ( SELECT day, month, year, daily_sale, sum(daily_sale) OVER
> (PARTITION BY month) FROM group_item )
>
> or instead of WINDOW functions you can write additional GROUP BY CTE queries
> for the different time-frames
>
> ..., month_total AS ( SELECT month, year, sum(daily_sale) FROM group_items
> GROUP BY month, year )
>
> and then combine these different CTE queries as you deem appropriate.
>
> http://www.postgresql.org/docs/9.3/interactive/sql-select.html (the
> section for "WITH [RECURSIVE])
>
> David J.
>
>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/sum-of-until-running-balance-and-sum-of-over-date-range-in-t...
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
Thank you. This will take a while to digest. I have used window functions
billable_days;
-- if a subscription is ceased same day it's started,
-- that day is still chargable, so bump it
IF billable_days < 1
(for running balance), and knew this would require window functions, but
seems like I did not know how to use them properly. I did not know you
could mix them the way you did here.
Greatly appreciate it.
Mark
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (3+ messages)
Message-ID: <526FB5DA.1050306@turnkey.bz>
Permalink: ../526FB5DA.1050306@turnkey.bz/
Also on: postgresql.org/message-id/526FB5DA.1050306@turnkey.bz
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@turnkey.bz
Subject: Re: Re: sum of until (running balance) and sum of over date range in the same query
In-Reply-To: <526FB5DA.1050306@turnkey.bz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox