SQL Cumulative Sum by Group by time condition

user_14976915 0
Viewed 1163

I have a table with columns dummy_id, date_registered, item_id, quantity, price, like this:

dummy_id date_registered item_id quantity price my_cumulative
1 2013-07-01 100 10 34.5 10
2 2013-07-01 145 8 2.3 8
3 2013-07-11 100 20 34.5 30
4 2013-07-23 100 15 34.5 45
5 2013-07-24 145 10 34.5 18

And if I want to calculate column my_cumulative which contains the cumulative totals of each item_id order by date_registered I use this code:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id order by date_registered) as cumulative
from table t;

And it works well. But what if I now want to my_cumulative column for each row to count only orders from the last month? (calculate the sum of the quantity only for rows where the date_register column is less than the current one, no more than a month)

Is there any way to do this in sql? (prefer postgresql)

cumulative-sum window-functions postgresql sql
1 Answers

If you want cumulative quantities for the current month -- which is what I suspect you want, then change the partition by:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id, date_trunc('month', date_registered) order by date_registered) as cumulative
from table t;

If you really want the last month, then use a range window frame with interval:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id
                           order by date_registered
                           range between interval '1 month' preceding and current row
                          ) as cumulative
from table t;

The first seems much more useful to me.

user_11440350
Related