在SQL中模拟slider的slide_index_*()函数处理非连续滑动窗口

针对非连续周数的供应商销量表，要高效计算每个供应商过去4周的销量总和、无需补全缺失周数，以下是两种适配Snowflake环境的纯SQL方案：

方案一：自关联聚合（通用SQL兼容） #

通过表自关联筛选同供应商下、周数在当前周前3周至当前周的所有记录，再聚合求和，适配所有支持自关联的SQL引擎：

SELECT
    a.SUPPLIER_ID,
    a.WEEK_IN_YEAR,
    a.APPLE_SALES,
    SUM(b.APPLE_SALES) AS PAST_4_WEEKS_SALES
FROM apple_tbl a
LEFT JOIN apple_tbl b
    ON a.SUPPLIER_ID = b.SUPPLIER_ID
    -- 筛选当前周及前3周的记录（共4周）
    AND b.WEEK_IN_YEAR BETWEEN a.WEEK_IN_YEAR - 3 AND a.WEEK_IN_YEAR
GROUP BY a.SUPPLIER_ID, a.WEEK_IN_YEAR, a.APPLE_SALES
ORDER BY a.SUPPLIER_ID, a.WEEK_IN_YEAR;

说明 #

• 缺失周数因无对应记录，不会被关联计入总和，符合业务逻辑。
• 若存在跨年周数（如当前周为年度第2周，需包含上一年的50/51/52周），可将周数转换为YEAR * 100 + WEEK_IN_YEAR的数值形式，再调整关联条件的数值范围。

方案二：RANGE窗口函数（Snowflake最优解） #

Snowflake支持基于数值列的RANGE窗口，无需自关联，直接通过窗口范围筛选符合条件的周数，是大数据场景下的高效方案：

SELECT
    SUPPLIER_ID,
    WEEK_IN_YEAR,
    APPLE_SALES,
    SUM(APPLE_SALES) OVER (
        PARTITION BY SUPPLIER_ID  -- 按供应商分组
        ORDER BY WEEK_IN_YEAR     -- 按周数排序
        -- 范围：当前周及前3周（数值差≤3）
        RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS PAST_4_WEEKS_SALES
FROM apple_tbl
ORDER BY SUPPLIER_ID, WEEK_IN_YEAR;

说明 #

• RANGE窗口基于ORDER BY列的数值范围而非物理行位置，完美适配非连续周数场景，性能远优于自关联。
• 跨年场景下，可将排序字段替换为YEAR * 100 + WEEK_IN_YEAR，确保跨年度周数的范围计算正确：

SELECT
    SUPPLIER_ID,
    YEAR,
    WEEK_IN_YEAR,
    APPLE_SALES,
    SUM(APPLE_SALES) OVER (
        PARTITION BY SUPPLIER_ID
        ORDER BY YEAR * 100 + WEEK_IN_YEAR
        RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS PAST_4_WEEKS_SALES
FROM apple_tbl
ORDER BY SUPPLIER_ID, YEAR, WEEK_IN_YEAR;

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