Re: Re: sum of until (running balance) and sum of over date range in the same query

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 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