sql - 如何根据日期范围对值求和并按 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

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

标签： sqldatesum

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;