滑动窗口移动平均的正确写法是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 节点和对应排序成本。
