如何根据日期范围对值求和并按 MAX 日期分组?

Question

我有一个如下数据集 - 按周和产品汇总的数量和销售额

Week    Product     Quantity    Sales
----    -------     --------    -----
1       12a         6           600
2       12a         4           400
3       12a         3           300
4       12a         1           100
5       12a         3           300 
6       12a         1           100
7       12a         4           400 
8       12a         6           600
9       12a         2           200

对于每周，我需要将当周以及前 3 周的数量和销售额相加

期望的结果将是：

Week    Product     Quantity    Sales
----    -------     --------    -----
1       12a         14          1400  --> Week 1 + Week 2 + Week 3 + Week 4 but row labeled Week 1
2       12a         11          1100

我觉得我每周都需要一个循环来评估

Answer 1

使用窗口函数：

select t.*,
sum(quantity) over (partition by product
order by week
rows between current row and 3 following
) as quantity,
sum(sales) over (partition by product
order by week
rows between current row and 3 following
) as sales
from t;