Snowflake SQL实现动态前置行滚动平均值的技术问题

以下是数据示例，最右侧列是需要计算的目标值：

计算逻辑 #

针对每个品牌下的所有商品，回溯包含当前周在内的3周（每个商品对应3行数据），计算周期时长列的平均值。注意：周期时长列中的加粗值用于计算品牌3周周期时长平均值列中的加粗值，计算需针对每个商品单独进行。

核心难点 #

使用preceding窗口函数时，仅支持固定回溯行数，但实际需要按「周数×品牌下商品数量」的动态行数进行回溯，常规方法无法实现。

尝试过的SQL代码 #

SELECT report_date
,item_cd
,recent_cycle_length
,brand_name
,max(report_date) as latestdate
,avg(recent_cycle_length) over 
    (partition by brand_name 
     order by report_date 
     rows between 11 preceding and current row) AS BRAND_AVG_CYCLE_LENGTH_LAST_12_WEEKS
FROM table
group by recent_cycle_length, brand_name, report_Date,item_cd
;

在Snowflake中，可以通过先统一周维度、再关联时间范围的方式实现动态回溯，具体SQL代码如下：

WITH weekly_data AS (
    -- 将日期转换为标准周（这里用ISO周，确保周范围统一）
    SELECT 
        report_date,
        item_cd,
        recent_cycle_length,
        brand_name,
        DATE_TRUNC('week', report_date) AS report_week
    FROM your_table
),
final_calculation AS (
    SELECT 
        wd.report_date,
        wd.item_cd,
        wd.recent_cycle_length,
        wd.brand_name,
        -- 计算当前品牌下、当前周及前2周的所有周期时长平均值
        AVG(wd_inner.recent_cycle_length) AS brand_3week_avg_cycle_length
    FROM weekly_data wd
    JOIN weekly_data wd_inner 
        ON wd.brand_name = wd_inner.brand_name
        AND wd_inner.report_week BETWEEN DATEADD('week', -2, wd.report_week) AND wd.report_week
    GROUP BY wd.report_date, wd.item_cd, wd.recent_cycle_length, wd.brand_name
)
SELECT * FROM final_calculation
ORDER BY brand_name, report_date, item_cd;

代码说明 #

1. weekly_data：将原始日期截断为标准周，统一时间统计维度，避免日期格式差异导致的范围错误
2. final_calculation：通过自关联筛选出同品牌下、当前周及往前2周的所有记录，直接计算这些记录的周期时长平均值，替代固定行数的窗口函数逻辑，完美适配品牌下商品数量动态变化的场景

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