SQL基于当前行值计算产品上周及近4周销售额的实现方案咨询

我正在处理订单表tblOrders，表结构与样例数据如下：

week | product | sale    
1    | ABC     | 2
1    | ABC     | 1
2    | ABC     | 1
3    | ABC     | 5
4    | ABC     | 1
2    | DEF     | 5

需求 #

给每一行新增列ProductPreviousWeekSales，统计当前行对应产品上周的总销售额：例如产品ABC的第2周行，该列值为ABC第1周的销售额总和3，首周对应值为0，预期输出如下：

week | product | sale    | ProductPreviousWeekSales
1    | ABC     | 2       | 0
1    | ABC     | 1       | 0
2    | ABC     | 1       | 3
3    | ABC     | 5       | 1
4    | ABC     | 1       | 5
2    | DEF     | 5       | 0

要求尽量避免使用子查询或多表关联，降低大数据量下的性能开销，同时后续需要扩展计算近4周的同类指标。

可以直接使用支持RANGE帧定义的窗口函数实现，仅需单次全表扫描，性能远高于关联、子查询方案，且扩展近N周统计非常方便：

SELECT
    week,
    product,
    sale,
    COALESCE(SUM(sale) OVER (
        PARTITION BY product
        ORDER BY week
        RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
    ), 0) AS ProductPreviousWeekSales
FROM tblOrders

逻辑说明 #

• PARTITION BY product：按产品分组，所有统计仅限当前产品内
• ORDER BY week：按周升序排序，为窗口帧提供排序依据
• RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING：限定窗口范围为「比当前行周数小1的所有行」，刚好对应上周的所有销售记录
• COALESCE(..., 0)：将首周无上周数据的null值转为0，符合需求

扩展近4周统计 #

如果需要计算当前产品近4周（不含当前周）的总销售额，仅需修改窗口帧范围即可：

-- 近4周（当前周往前数4周，不含当前周）总销售额
SUM(sale) OVER (
    PARTITION BY product
    ORDER BY week
    RANGE BETWEEN 4 PRECEDING AND 1 PRECEDING
)

注意事项 #

• 要求week字段为连续的整数类型，如果业务中存在周跳号（如缺周、周编号不连续）的情况，需要先补全连续周维度后再计算，避免统计偏差。

内容的提问来源于stack exchange，提问作者jjk_charles