如何在SQL中通过SUM() OVER()实现滑动窗口的移动平均计算？

滑动窗口移动平均的正确写法是SUM(value) OVER(ORDER BY ts ROWS BETWEEN N PRECEDING AND CURRENT ROW)/(N+1)，需用COALESCE处理NULL，分母不可用COUNT(*)，且ORDER BY列须有索引以保障性能。

滑动窗口移动平均的正确语法结构

SQL 中用 SUM() OVER() 算移动平均，本质是先用窗口函数求和，再手动除以窗口内行数。不能直接写 AVG() OVER() 后加 ROWS BETWEEN 就完事——虽然有些数据库支持，但行为不统一，且无法控制是否包含当前行、是否跳过 NULL。

标准可靠写法是：SUM(value) OVER (ORDER BY ts ROWS BETWEEN N PRECEDING AND CURRENT ROW) / (N + 1)。注意分母必须是明确的整数，不能依赖 COUNT(*) OVER(...)，因为后者会把 NULL 值也计入行数，而 SUM() 自动忽略 NULL，导致分子分母不匹配。

常见错误：NULL 值导致结果为 NULL 或除零

当 value 列含 NULL 时，SUM() 返回 NULL，整个表达式就变成 NULL / (N + 1) → 结果仍是 NULL。这不是 bug，是 SQL 的三值逻辑行为。

解决办法只有提前清洗或转换：

• 用 COALESCE(value, 0) 把 NULL 当 0 处理（适用于业务上“无数据=0”）
• 用 AVG(COALESCE(value, 0)) OVER (...) 配合 ROWS，但注意这算的是“补零后的平均”，语义可能偏离原始需求
• 更严谨的做法是：先用 COUNT(value) OVER (...) 统计非 NULL 行数，再做除法，例如：SUM(value) OVER (...) / NULLIF(COUNT(value) OVER (...), 0)

ROWS BETWEEN 的边界行为差异

不同数据库对 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 的起始位置处理一致，但对开头几行的窗口大小不一致——PostgreSQL 和 SQL Server 会自动缩减窗口（如第 1 行只取 1 行），而 MySQL 8.0 默认也如此；但旧版 Hive 或某些 OLAP 引擎可能报错或返回 NULL。

关键点：

• CURRENT ROW 一定包含自身，无需额外假设
• UNBOUNDED PRECEDING 不适合移动平均，它会累积到表头，不是“滑动”
• 想实现“严格 5 行窗口，不足不计算”，得配合 CASE WHEN COUNT(*) OVER (...) = 5 THEN ... END

性能与索引提示

SUM() OVER() 的执行效率高度依赖 ORDER BY 字段是否有索引。如果按时间戳 ts 排序，但 ts 列没索引，全表扫描+排序开销会陡增，尤其在千万级表上延迟明显。

实操建议：

• 确保 ORDER BY 列有单列索引，或作为联合索引最左前缀
• 避免在 OVER() 子句里用函数，例如 ORDER BY DATE(ts) 会让索引失效
• 若只需近似移动平均（如每小时聚合后计算），先用子查询聚合再套窗口，比直接在明细层跑 OVER() 快一个数量级

窗口函数看着简洁，但底层要维护滑动状态，实际是逐行计算+缓存局部结果。数据量大时，别只盯着语法对不对，先看执行计划里有没有 WindowAgg 节点和对应排序成本。