如何在SQL中利用聚合窗口函数计算连续移动平均值-SQL-PHP中文网

连续移动平均值是对当前行及指定行数范围内的滑动窗口求平均，需配合over()与order by及rows between使用；普通avg()是聚合函数，无窗口语义，不保留原行结构。

如何在sql中利用聚合窗口函数计算连续移动平均值

什么是连续移动平均值，和普通 AVG() 有什么区别

连续移动平均值（rolling average）不是对整列求平均，而是对当前行及往前 N 行（或前后各 N 行）构成的滑动窗口做平均。它必须依赖 ORDER BY 定义行序，且窗口范围需显式声明；而普通 AVG() 是聚合函数，不带 OVER 就会把整个分组压成一行，没法保留原始行结构。

常见错误是写成 AVG(value) OVER () —— 这等于全表平均，不是“移动”的；或者漏掉 ORDER BY，导致数据库报错（如 PostgreSQL 报 window definition requires an ordering clause）或结果不可预测（MySQL 8.0+ 要求显式排序）。

用 ROWS BETWEEN 实现固定长度滑动窗口

这是最常用、语义最清晰的方式，适用于按时间、ID 或其他单调字段排序的场景：

窗口定义必须包含 ORDER BY，例如按 date 或 id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示：当前行 + 前两行，共 3 行
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示：前一行、当前行、后一行（中心对称）

SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_avg_3
FROM sales;

注意：首两行因缺少前置数据，窗口实际只有 1 或 2 行，AVG() 仍会正常计算（不补零），结果行数与原表一致。

处理时间间隔不均等的数据（如非每日记录）

如果原始数据缺失某些日期（比如周末无交易），用 ROWS 会跳过空档，导致“物理行数”和“业务时间跨度”错位。此时应改用 RANGE，但仅限于支持该语法的数据库（PostgreSQL、SQL Server 支持；MySQL 8.0+ 不支持 RANGE 与数值/日期表达式组合）。

可行替代方案：

先用 GENERATE_SERIES（PostgreSQL）或递归 CTE 补全日期维度
或改用子查询 + 时间条件关联（性能较差，但兼容性强）

SELECT
  t1.date,
  t1.value,
  (SELECT AVG(t2.value)
   FROM sales t2
   WHERE t2.date BETWEEN t1.date - INTERVAL '2 days' AND t1.date) AS avg_last_3_days
FROM sales t1;

这种写法可读性低、无法利用窗口优化，仅作兜底；真正上线应优先确保基础数据按时间密排，或接受 ROWS 的物理窗口语义。