在SQL中如何用窗口函数计算滚动标准差？

SQL窗口函数计算滚动标准差必须显式定义窗口帧（如ROWS BETWEEN N PRECEDING AND CURRENT ROW），仅用PARTITION BY和ORDER BY会导致累积计算；PostgreSQL、SQL Server、MySQL 8.0+支持该语法，但需确保窗口内至少2行有效数据且正确处理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+ 支持相同语法，但需注意：若窗口内行数 STDDEV_SAMP 返回 NULL（符合 SQL 标准），不是 0
• BigQuery 使用 STDDEV(<code>col) OVER (...) 默认为总体标准差；要滚动样本标准差，必须写 STDDEV_SAMP(<code>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，或需要跨多列/复杂条件，就得绕行：

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

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