如何计算SQL中一组数值的移动平均值_利用AVG() OVER窗口函数实现趋势分析-SQL-PHP中文网

真正的移动平均需用 rows 显式定义窗口，避免 order by 重复值导致的 range 退化；缺日期需先补全时间序列再计算；多期均线宜用单次扫描多 over 或 cte 优化性能。

如何计算sql中一组数值的移动平均值_利用avg() over窗口函数实现趋势分析

AVG() OVER 可以直接算移动平均，但窗口范围必须显式定义，否则默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW —— 这不是你想要的“固定窗口大小”移动平均。

什么是真正的移动平均：ROWS 而不是 RANGE

很多人写 AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 却发现结果不对，原因常出在 ORDER BY 字段有重复值（比如同一天多条记录），此时数据库可能自动退化为 RANGE 模式，导致窗口实际包含更多行。

务必用 ROWS 显式声明，避免隐式 RANGE
确保 ORDER BY 列唯一，或加二级排序（如 ORDER BY date, id）
窗口大小 = 前N行 + 当前行，例如 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 是5期移动平均

处理缺失日期导致的窗口断裂

原始数据若按日聚合但中间缺天（如周末无数据），ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 会跨过空缺，取到更早的日期，造成时间跨度不一致。这不是函数错，是数据本身稀疏。

先用 GENERATE_SERIES()（PostgreSQL）或递归 CTE（MySQL/SQL Server）补全日期维度
再 LEFT JOIN 原始数据，用 COALESCE(value, 0) 或 NULL 填充
最后在完整序列上跑 AVG() OVER ... ROWS，才能保证每期都是严格向前N天

性能与可读性平衡：别在子查询里套多层窗口

想同时看3期、7期、30期移动平均？别这么写：

SELECT
  AVG(x) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3,
  AVG(x) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
  AVG(x) OVER (ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma30
FROM t

看起来干净，但多数引擎会对同一列重复扫描三次窗口——尤其数据量大时，CPU 和内存开销明显上升。

单次扫描 + 多个 OVER 子句在逻辑上没问题，但 PostgreSQL 14+、SQL Server 2022 已优化；旧版本建议拆成 CTE 预计算排序键
如果还要加条件过滤（如只算 status = 'active' 的移动平均），务必把 WHERE 放在最外层，否则窗口函数会基于过滤前的数据计算
MySQL 8.0 对 AVG() OVER 的优化较弱，大数据量下考虑用变量模拟（但会丢失并行能力）

移动平均真正难的不是语法，而是对齐时间粒度、处理空值、确认窗口是否被 ORDER BY 的重复值悄悄扭曲——这些地方一错，趋势线就偏了，而且很难一眼看出来。