Snowflake中如何计算基于30天窗口的可变行数移动平均？

核心解决方案 #

Snowflake支持基于时间间隔的滑动窗口，无需固定行数，直接通过日期范围定义窗口即可满足需求。以下是针对示例数据的完整SQL实现：

WITH raw_data AS (
    SELECT 
        TO_DATE(date_str, 'MM月DD日') AS date_col,
        value_col
    FROM (
        VALUES 
            ('1月1日', 100),
            ('1月5日', 200),
            ('1月20日', 100),
            ('2月3日', 0),
            ('2月3日', 500),
            ('2月10日', 400),
            ('3月8日', 600)
    ) AS t(date_str, value_col)
)
SELECT
    TO_CHAR(date_col, 'MM月DD日') AS 日期,
    ROUND(AVG(value_col) OVER (
        ORDER BY date_col
        RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
    ), 0) AS "30天平均值",
    CONCAT('计算方式为 `AVG(', LISTAGG(value_col, ',') OVER (
        ORDER BY date_col
        RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
    ), ')`') AS 说明
FROM raw_data
ORDER BY date_col;

关键逻辑说明 #

• 日期类型转换：用TO_DATE将文本格式日期转为Snowflake可运算的日期类型，确保时间范围判断准确。
• 动态窗口定义：窗口子句RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW是核心，它会自动筛选当前行日期往前30天内的所有记录——不管单日有多少条数据、中间有没有日期缺失，只要在时间范围内就纳入计算。
• 平均值计算：AVG(value_col)在上述动态窗口内计算平均值，ROUND(..., 0)用来和示例中的整数结果对齐。
• 说明生成：用LISTAGG()函数把窗口内的数值拼接成字符串，生成和示例一致的计算说明，方便验证结果。

结果验证 #

执行上述SQL后，输出结果与期望完全一致：

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