SQL用SUM()OVER()实现滑动窗口移动平均计算

滑动窗口移动平均在SQL里是个常见需求，但写对的人不多。核心问题在于，很多人以为用A VG() OVER()加上ROWS BETWEEN就完事了，其实不然。正确做法是：SUM(value) OVER (ORDER BY ts ROWS BETWEEN N PRECEDING AND CURRENT ROW) / (N + 1)。关键就在于分母，得写死，不能依赖COUNT(*) OVER(...)，因为后者会把NULL值也计入行数，导致分子分母不匹配，结果自然就错了。

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

SQL里用SUM() OVER()算移动平均，本质上是先求和，再手动除以窗口内行数。不能直接写A VG() 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”的场景）
• 用A VG(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节点和对应排序成本。