如何在SQL查询中实现带滑动窗口的移动平均聚合计算-SQL-PHP中文网

MySQL 8.0+ 中用 AVG() 配合 OVER() 实现滑动窗口平均需指定 ROWS BETWEEN 和 ORDER BY，如 AVG(value) OVER (PARTITION BY user_id ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)，确保按业务时间排序且避免跨组混算，漏写 ORDER BY 会报错。

如何在sql查询中实现带滑动窗口的移动平均聚合计算

MySQL 8.0+ 怎么用 `AVG()` 配合 `OVER()` 做滑动窗口平均

MySQL 8.0 开始原生支持窗口函数，AVG() 在 OVER() 中指定 ROWS BETWEEN 就能实现真正的滑动窗口移动平均。关键不是“排序后取前N行”，而是让每行的计算范围随当前行动态滑动。

常见错误是漏写 ORDER BY —— 窗口函数要求明确的行序，否则 ROWS BETWEEN 无意义，MySQL 会报错 Window 'w' requires an ordering clause。

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：包含当前行及前两行（共3个点）
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：当前行前后各1行（共3个点，对称窗口）
必须搭配 PARTITION BY（如按用户分组）才能避免跨业务实体混算

示例：

SELECT
  dt,
  value,
  AVG(value) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS ma3
FROM metrics;

PostgreSQL 的 `AVG()` 窗口写法和 MySQL 有啥区别

语法几乎一致，但 PostgreSQL 对 NULL 更敏感：若窗口内某行 value 为 NULL，它会被自动排除在 AVG() 计算之外（符合 SQL 标准），而有些用户误以为要手动 COALESCE(value, 0) —— 这反而会扭曲均值。

另一个实际差异是性能：PostgreSQL 在大数据集上对 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（累计平均）优化较好，但对大跨度滑动窗口（如 ROWS BETWEEN 99 PRECEDING AND CURRENT ROW）可能比 MySQL 慢 20%~30%，建议在 dt 列建索引并 CLUSTER 表。

不要用 RANGE 替代 ROWS——时间字段用 RANGE 看似合理，但会导致非等距时间点被错误聚合
如果需要按自然日对齐（比如每天一个值），先用 generate_series() 补全缺失日期，再做窗口计算

SQLite 怎么绕过不支持窗口函数的限制

SQLite 3.25+ 支持窗口函数，但旧版（如 Android 系统内置 SQLite）常卡在 3.19，此时无法用 OVER()。可行替代方案是自连接 + 范围过滤：

SELECT
  a.dt,
  a.value,
  (SELECT AVG(b.value)
   FROM metrics b
   WHERE b.user_id = a.user_id
     AND b.dt BETWEEN datetime(a.dt, '-2 days') AND a.dt) AS ma3
FROM metrics a;

这个写法代价高：外层每行都触发一次子查询，数据量超 1 万行就明显变慢。更稳的解法是导出数据到 Python 用 pandas.Series.rolling() 处理，或升级 SQLite 到 3.25+。