如何在Snowflake中规避日期缺口的时间序列滚动平均计算偏差？

你这个问题太常见了——当时间序列存在日期缺口（比如周末、节假日无交易数据）时，用ROWS窗口函数就会踩坑：它不管日期隔了多久，只要是前N行就会算进去，就像你例子里2020-07-30的平均居然包含了5天前的2020-07-25数据，这显然不符合「3日移动平均」的业务逻辑。

下面给你两种针对性的解决方案，按需选择：

方案1：用日期范围替代行计数（推荐，最简洁） #

直接把ROWS换成RANGE，指定窗口为当前日期及往前2天的范围，这样Snowflake会自动忽略超出时间范围的数据，只计算真正属于最近3个日历日的价格平均值。

修改后的查询代码：

SELECT 
    TRADE_DATE, 
    SYMBOL, 
    CLOSE_PRICE,
    -- 基于日期范围的3日移动平均：仅包含当前日期及前2天内的数据
    AVG(CLOSE_PRICE) OVER (
        PARTITION BY SYMBOL 
        ORDER BY TRADE_DATE 
        RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
    ) AS MV_AVG_3DAY
FROM STOCK_PRICE;

效果说明： #

• 对于2020-07-30，这个窗口只会包含当天的数据（因为2020-07-28、29是周末，没有交易记录），所以平均值就是当天的收盘价。
• 当遇到连续交易日（比如2020-08-03、04、05），窗口会自动包含这三天的数据，计算出正确的3日平均。

方案2：补全所有日历日期后再计算（适合需要完整时间序列的场景） #

如果你的业务要求必须展示所有日历日期的移动平均（包括无交易的周末/节假日），可以先补全缺失的日期，再填充缺失的价格，最后用ROWS计算平均。

完整代码示例：

WITH DATE_SERIES AS (
    -- 生成覆盖所有交易日期的连续日历序列
    SELECT 
        DATEADD(DAY, SEQ4(), (SELECT MIN(TRADE_DATE) FROM STOCK_PRICE)) AS CAL_DATE
    FROM TABLE(GENERATOR(ROWCOUNT => (
        SELECT DATEDIFF(DAY, MIN(TRADE_DATE), MAX(TRADE_DATE)) + 1 FROM STOCK_PRICE
    )))
),
FILLED_PRICES AS (
    -- 关联连续日期与股票数据，用前一天的价格填充缺失值（可按需改为NULL）
    SELECT 
        ds.CAL_DATE AS TRADE_DATE,
        syms.SYMBOL,
        LAST_VALUE(sp.CLOSE_PRICE) IGNORE NULLS OVER (
            PARTITION BY syms.SYMBOL 
            ORDER BY ds.CAL_DATE
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CLOSE_PRICE
    FROM DATE_SERIES ds
    CROSS JOIN (SELECT DISTINCT SYMBOL FROM STOCK_PRICE) syms
    LEFT JOIN STOCK_PRICE sp 
        ON ds.CAL_DATE = sp.TRADE_DATE AND syms.SYMBOL = sp.SYMBOL
)
-- 计算连续日期的3日移动平均
SELECT 
    TRADE_DATE,
    SYMBOL,
    CLOSE_PRICE,
    AVG(CLOSE_PRICE) OVER (
        PARTITION BY SYMBOL 
        ORDER BY TRADE_DATE 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MV_AVG_3DAY
FROM FILLED_PRICES
ORDER BY SYMBOL, TRADE_DATE;

关键说明： #

• 这个方案会生成所有日历日期的记录，包括无交易的日期，适合需要完整时间序列报表的场景。
• 填充缺失值的逻辑可以灵活调整：如果不需要填充，直接保留NULL，AVG函数会自动忽略NULL值计算平均。

验证结果 #

用你的示例数据测试，方案1中2020-07-30的MV_AVG_3DAY会等于当天的收盘价（AAPL为1010.0），彻底避免了跨多天的错误计算。

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