Spark SQL中缺失周数据时计算近4周平均销售额的方法

你的核心问题是原窗口函数基于物理行位置筛选，而非实际周数范围，导致缺失周时会错误纳入超出4周的数据。要在不补全缺失周记录的前提下解决，可改用数值范围窗口+总和除以4的方案，具体如下：

解决方案代码 #

假设你的week_num是数值格式（如202408代表2024年第8周），直接使用以下SQL：

SELECT
    item,
    week_num,
    sales,
    -- 计算近4周销售额总和，缺失周按0计入，再除以4得平均
    SUM(sales) OVER (
        PARTITION BY item
        ORDER BY week_num
        RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
    ) / 4 AS Avg_sales_4_weeks
FROM your_table;

如果week_num是字符串格式（如2024-08），先转成数值型再计算：

SELECT
    item,
    week_num,
    sales,
    SUM(sales) OVER (
        PARTITION BY item
        ORDER BY CAST(replace(week_num, '-', '') AS INT)
        RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
    ) / 4 AS Avg_sales_4_weeks
FROM your_table;

方案原理 #

1. 替换ROWS为RANGE窗口：RANGE BETWEEN 3 PRECEDING AND CURRENT ROW会基于week_num的数值范围筛选，只保留当前周及往前推3周的记录（即近4周：current_week-3到current_week），彻底避免物理行位置带来的错误。
2. SUM替代AVG：SUM(sales)会自动忽略缺失周的记录（等价于这些周销售额为0），最后除以4是因为近4周固定为4个时间单位，无论是否有数据缺失，都要按4周计算平均。
3. 无需补全数据：全程基于现有记录计算，不会增加表的行数，满足性能和数据量要求。

对比原方案的优势 #

原方案ROWS BETWEEN current row and 3 following是按行的数量取后续3行，当存在周缺失时，这些行的week_num可能远超出current_week-3的范围（比如商品2的202408周会取到202401周的数据），而新方案通过数值范围严格限定了时间区间，结果完全符合业务需求。

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