Re: Re: sum of until (running balance) and sum of over date range in the same query - Mailing list pgsql-sql
From | M. D. |
---|---|
Subject | Re: Re: sum of until (running balance) and sum of over date range in the same query |
Date | |
Msg-id | 526FB5DA.1050306@turnkey.bz Whole thread Raw |
In response to | Re: sum of until (running balance) and sum of over date range in the same query (David Johnston <polobo@yahoo.com>) |
List | pgsql-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-the-same-query-tp5776209p5776213.html > 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 itIF 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