My sample data looks like:
article | Qty | Date |
A | 3 | 2019-10-11 |
A | 5 | 2019-10-08 |
A | 1 | 2019-10-05 |
A | 2 | 2019-09-15 |
A | 1 | 2019-09-09 |
A | 1 | 2019-09-01 |
B | 3 | 2019-10-11 |
B | 2 | 2019-10-08 |
B | 3 | 2019-10-05 |
B | 1 | 2019-09-15 |
B | 4 | 2019-09-09 |
C | 1 | 2019-10-11 |
C | 2 | 2019-10-08 |
C | 1 | 2019-10-05 |
C | 1 | 2019-09-15 |
C | 0 | 2019-09-09 |
C | 4 | 2019-09-01 |
C | 1 | 2019-08-28 |
and I want following output:
Article | date | Qty | Sum_Qty_28_days |
A | 2019-09-01 | 1 | 1 |
A | 2019-09-09 | 1 | 2 |
A | 2019-09-15 | 2 | 4 |
A | 2019-10-05 | 1 | 4 |
A | 2019-10-08 | 5 | 8 |
A | 2019-10-11 | 3 | 11 |
B | 2019-09-09 | 4 | 4 |
B | 2019-09-15 | 1 | 5 |
B | 2019-10-05 | 3 | 8 |
B | 2019-10-08 | 2 | 6 |
B | 2019-10-11 | 3 | 9 |
C | 2019-08-28 | 1 | 1 |
C | 2019-09-01 | 4 | 5 |
C | 2019-09-09 | 0 | 5 |
C | 2019-09-15 | 1 | 6 |
C | 2019-10-05 | 1 | 2 |
C | 2019-10-08 | 2 | 4 |
C | 2019-10-11 | 1 | 5 |
The logic is: Column “Sum_Qty_28_days “ should sum up the values of “Qty ” column for same article which falls under last 28 days E.g. “Sum_Qty_28_days” value for “article” = ‘A’ and date = ‘’2019-10-8” is 8 because it is summing up the “Qty” values associated with dates (coming within previous 28 days) Which are: 2019-09-15, 2019-10-05, 2019-10-08 for “article” = ‘A’.
Is this possible using SQL window function? I tried myself with following code:
SUM("Qty") OVER (PARTITION BY "article", date_trunc('month',"Date")
ORDER BY "Date")But, it is far from what I need. It is summing up the Qty for dates falling in same month. However, I need to sum of Qty for last 28 days.
Note: I already have solution using non In-database tools, but that it taking huge amount of execution time for whole dataset. I request for a solution with In-database tools only or using window function.
Thanks.
