怎样在SQL中按时间滑动窗口实现滚动分组求平均值-SQL-PHP中文网

avg() over(order by ts rows between n preceding and current row)是标准写法，必须显式指定order by和rows between，缺一不可；仅order by会触发累积平均或报错，rows between才能精确定义滑动窗口边界。

怎样在sql中按时间滑动窗口实现滚动分组求平均值

SQL里没有直接的 `ROLLING AVG` 函数，得靠窗口函数手动构造滑动窗口

标准 SQL（包括 PostgreSQL、SQL Server、BigQuery）不提供原生的“按时间范围滑动”聚合函数，AVG() OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 这类语法只认行数，不认时间间隔。想按“过去7天”“最近1小时”滚动，必须把时间差转换成可排序、可比较的序列，再用 RANGE 或自连接/子查询逼近。

`AVG() OVER (ORDER BY ts RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)` 在哪些数据库能用

这个写法看似理想，但实际支持极有限：

PostgreSQL：不支持 RANGE 配合 INTERVAL，会报错 ERROR: RANGE frame with OFFSET not supported for type timestamp without time zone
BigQuery：支持，语法是 RANGE BETWEEN 7 * 24 * 60 * 60 PRECEDING AND CURRENT ROW（单位为秒），且要求 ORDER BY 列是 INT64 时间戳（如 UNIX_SECONDS(ts)）
MySQL 8.0+：不支持 RANGE 用于时间类型，仅支持数字列
Spark SQL / Trino：部分支持，但需确认版本；Trino 377+ 支持 RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW，前提是 ts 是 TIMESTAMP 类型且无时区

兼容性最强的做法：用自连接 + 时间条件模拟滑动窗口

适用于所有 SQL 引擎，逻辑清晰，但要注意性能和去重边界：

假设表叫 events，含 ts（TIMESTAMP）和 value（NUMERIC）
对每条记录 e1，找所有满足 e2.ts >= e1.ts - INTERVAL '7 days' AND e2.ts 的 <code>e2 行
用 GROUP BY e1.ts, e1.value 聚合，但注意：若 ts 不唯一，需加 ROW_NUMBER() 或主键去重，否则会重复计数

示例片段（PostgreSQL）：

SELECT
  e1.ts,
  AVG(e2.value) AS rolling_7d_avg
FROM events e1
JOIN events e2 ON e2.ts BETWEEN e1.ts - INTERVAL '7 days' AND e1.ts
GROUP BY e1.ts
ORDER BY e1.ts;

性能关键点：给 ts 建索引；数据量大时，考虑加 WHERE e1.ts >= '2024-01-01' 限定主扫描范围

用 `LAG()`/`LEAD()` 手动展开固定行数窗口，再算平均——仅适合等频数据

如果数据是严格按分钟/小时采集（比如 IoT 设备每5分钟一条），且无缺失，可以用行偏移近似时间窗口：

AVG(value) OVER (ORDER BY ts ROWS BETWEEN 139 PRECEDING AND CURRENT ROW) ≈ 过去12小时（140×5min）
优点：快，纯窗口函数，无 JOIN
风险：一旦某条记录丢失，窗口就偏移；时区切换、夏令时、批量补录都会导致结果漂移
别用 RANGE 想绕过这个问题——多数引擎对时间列的 RANGE 实现不可靠，容易漏掉边界记录或重复计算

真正按时间滑动的核心难点不在语法，而在“时间边界是否包含端点”和“空值/重复时间戳如何处理”。生产环境建议先用小范围数据验证边界行为，尤其关注第一条和最后一条记录的输出是否符合业务预期。