PostgreSQL窗口函数计算滚动平均如何实现类似pandas的min_period效果

你可以通过CASE WHEN搭配窗口计数函数实现满252天数据才返回平均值的需求，效果和pandas中df.groupby('symbol')['close'].rolling(252, min_periods=252).mean()完全一致。

调整后的查询语句 #

通用写法（兼容所有支持窗口函数的SQL引擎） #

SELECT 
    datestamp, 
    symbol,
    CASE 
        WHEN COUNT(close) OVER (PARTITION BY symbol ORDER BY datestamp ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) = 252
        THEN AVG(close) OVER (PARTITION BY symbol ORDER BY datestamp ROWS BETWEEN 251 PRECEDING AND CURRENT ROW)
        ELSE NULL 
    END AS rolling_avg_252d
FROM daily_prices

简化写法（支持WINDOW子句的引擎可用，比如PostgreSQL、MySQL 8.0+、BigQuery、Spark SQL） #

把重复的窗口定义抽离出来，代码更易维护：

SELECT 
    datestamp, 
    symbol,
    CASE 
        WHEN COUNT(close) OVER w = 252
        THEN AVG(close) OVER w
        ELSE NULL 
    END AS rolling_avg_252d
FROM daily_prices
WINDOW w AS (PARTITION BY symbol ORDER BY datestamp ROWS BETWEEN 251 PRECEDING AND CURRENT ROW)

实现逻辑说明 #

• 保持原本的窗口范围不变：按标的分组、按日期排序，取当前行+前251行共最多252行数据计算
• 先通过COUNT窗口函数统计当前窗口内的有效收盘价数量，如果刚好等于252，说明数据满周期，返回平均值，否则返回NULL
• 如果你表中的close字段是非空约束，也可以把COUNT(close)替换成COUNT(*)，执行效率更高

内容的提问来源于stack exchange，提问作者Anup S.