一步步教你使用SQL窗口函数计算滚动标准差

SQL窗口函数计算滚动标准差，这事儿还真得手动指定窗口帧——光是写个PARTITION BY和ORDER BY，出来的结果其实是累积计算，而不是真正意义上的固定宽度滚动。PostgreSQL、SQL Server、MySQL 8.0+都支持这个语法，但有个前提：窗口内至少得有两行有效数据，还得把NULL处理妥当。

SQL 窗口函数不直接支持 STDDEV 的滚动计算

平常我们用的标准SQL（PostgreSQL、SQL Server、Oracle这些），窗口函数里的STDDEV、STDDEV_POP和STDDEV_SAMP，虽然能当窗口函数用，但默认行为是全局聚合——如果不加ROWS BETWEEN，它们其实相当于对整个分区求标准差，而不是按滑动窗口算。MySQL 8.0+虽然支持窗口函数，但STDDEV_SAMP()在窗口模式下同样需要显式帧定义，而且部分版本的计算结果可能有点偏差。

要真正实现“滚动标准差”，关键就是手动指定窗口帧，同时确认你的数据库在这个帧下能正确重算样本标准差。

• PostgreSQL 14+ 和 SQL Server 2022 都支持 STDDEV_SAMP() OVER (ORDER BY x ROWS BETWEEN N PRECEDING AND CURRENT ROW)，结果可信
• MySQL 8.0.22+ 也支持相同语法，但需要注意：如果窗口内行数小于2，STDDEV_SAMP 会返回 NULL（符合SQL标准），而不是0
• BigQuery 用 STDDEV(col) OVER (...) 默认是总体标准差；要滚动样本标准差，得写 STDDEV_SAMP(col) OVER (...)

如何写出正确的滚动标准差窗口定义

错误写法是这样的：STDDEV_SAMP(amount) OVER (PARTITION BY category ORDER BY dt)——缺少ROWS或RANGE子句，实际算出来的是“从分区首行到当前行”的累积标准差，不是固定宽度的滚动窗口。

正确写法必须显式声明帧范围：

STDDEV_SAMP(sales) OVER (  PARTITION BY region   ORDER BY date   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

这段代码的意思是：对每个region，按date排序，取当前行以及前6行（总共7行）来计算样本标准差。

• ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 是中心对称滚动（5行），适合平滑时间序列
• 尽量避免用 RANGE（比如 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW），因为日期重复时可能会意外扩大窗口
• 如果排序列有重复值（比如多笔同日的订单），ROWS 比 RANGE 更可控

常见错误现象与排查点

执行完发现结果全是NULL或者数值异常？大概率是下面这些问题：

• 窗口内有效非NULL值不足2个——STDDEV_SAMP强制返回NULL（样本标准差分母是n−1，n=1时没定义）
• 误用了STDDEV_POP：它算的是总体标准差，分母是n，即使只有1行也返回0，但不符合“滚动样本波动”的本意
• ORDER BY列没有去重或者包含NULL——窗口排序不稳定，同一个输入多次运行结果可能不一致（尤其在PostgreSQL中）
• 数据库版本太低：比如PostgreSQL的ROWS帧下，STDDEV_SAMP窗口重计算可能会报错或静默降级

替代方案：当窗口函数不可靠时怎么办

如果所在环境（比如旧版Hive、某些云数仓方言）根本不支持带帧的STDDEV_SAMP，或者需要跨多列、复杂条件，那就得绕个弯：

• 用A VG和A VG(x*x)手动组合：SQRT(A VG(val*val) OVER win - A VG(val) OVER win * A VG(val) OVER win)——这是数学上的等价式，但要注意浮点精度损失和NULL传播问题
• 在应用层拉取原始数据后用Python/Pandas计算：df['rolling_std'] = df['val'].rolling(7).std(ddof=1)，更灵活，结果也容易验证
• 建物化中间表：先用自连接或LATERAL JOIN构造每行对应的滚动窗口行集，再GROUP BY + STDDEV_SAMP——性能差，只适合小数据量

滚动标准差真正难的点，其实不是语法，而是确认你的数据库版本、NULL处理策略、以及帧边界是否真的按预期截取了数据行。跑完结果后，务必拿小样本手算验证前两行——这才是最靠谱的做法。