如何通过SQL窗口函数计算设备传感器数据的滑动最大值？-SQL-PHP中文网

滑动最大值必须用max() over(partition by device_id order by ts rows between n preceding and current row)，不能用group by；无order by则rows无效，range易因时间重复导致窗口失控，rows更稳定可靠。

如何通过sql窗口函数计算设备传感器数据的滑动最大值？

滑动最大值必须用 `MAX()` 配合 `OVER()`，不能用聚合函数直接套子查询

直接写 SELECT MAX(value) FROM sensor_data GROUP BY device_id 得到的是全局最大值，不是滑动窗口。窗口函数的核心是保留原始行数，同时按指定范围计算统计值。关键在于 OVER() 里的 ORDER BY 和 ROWS BETWEEN —— 没有 ORDER BY，ROWS 就无效，数据库会报错或返回不可靠结果。

常见错误现象：MAX(value) OVER (PARTITION BY device_id) 看起来分组了，但没 ORDER BY，实际等价于该设备所有历史数据的最大值，不是“最近 N 条”的滑动最大值。

必须显式指定排序字段（通常是时间戳 ts 或自增序号 seq）
PARTITION BY device_id 是可选的，但多设备场景下不加会导致跨设备混算
窗口帧定义推荐用 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，比 RANGE 更稳定（避免时间重复导致行数失控）

`ROWS BETWEEN` 和 `RANGE BETWEEN` 的行为差异直接影响结果可靠性

传感器数据常有高频写入、毫秒级时间戳重复的问题。RANGE BETWEEN INTERVAL '5 seconds' PRECEDING AND CURRENT ROW 表面看是“过去 5 秒内最大值”，但一旦某秒内有 100 条记录，窗口就可能突然膨胀，拖慢查询；更糟的是，若时间戳全相同（比如批量导入未打点），RANGE 会把整批数据都拉进窗口，完全失去滑动意义。

而 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 严格取当前行及前 4 行（共 5 行），无论时间是否重复、是否缺失，行为确定。性能也更好——数据库只需定位物理行偏移，不需时间范围扫描。

用 ROWS：适合固定条数滑动（如“最近 5 次读数”）
用 RANGE：仅当时间精度高、无重复、且业务真需要“时间区间”语义时才考虑
PostgreSQL 支持 RANGE 时间类型，MySQL 8.0+ 仅支持数值和日期字面量，不支持 INTERVAL 直接写在 RANGE 里

MySQL 8.0 和 PostgreSQL 的语法微差要手动对齐

两者都支持标准窗口语法，但 MySQL 对 ORDER BY 子句要求更严：如果 OVER() 里写了 ORDER BY ts，那 ts 字段必须在 SELECT 列表中出现（否则报错 Window '<name>' requires order by</name>），PostgreSQL 则无此限制。

Cutout.Pro老照片上色

Cutout.Pro老照片上色是一款AI图片处理工具，Cutout.Pro 推出的黑白图片上色功能。

下载

另外，MySQL 不允许在同一个 SELECT 中混用带 ORDER BY 和不带 ORDER BY 的窗口函数（会提示 Window function is missing required ORDER BY），而 PostgreSQL 允许。

MySQL 安全写法：SELECT ts, device_id, value, MAX(value) OVER (PARTITION BY device_id ORDER BY ts ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS max_5
PostgreSQL 可省略 ts 在 SELECT 列表，但建议保留，避免后续加 WHERE 时出问题
SQLite 3.25+ 支持窗口函数，但不支持 ROWS BETWEEN 中的 CURRENT ROW 以外的偏移写法（需用 UNBOUNDED PRECEDING 替代）

实时性要求高时，滑动最大值不宜直接查原始表

如果传感器每秒写入千条数据，每次查 MAX() OVER (ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) 都扫最近 30 行，看似轻量，但并发一高，I/O 和排序开销会指数上升。更麻烦的是，窗口函数无法利用普通 B-tree 索引加速“前 N 行”定位——它依赖内存排序缓冲区，容易触发磁盘临时表。

生产环境更稳妥的做法是预计算：用物化视图（PostgreSQL）或定时任务（MySQL）维护每个设备的最近 N 条缓存表，再在这个小表上跑窗口函数；或者用流处理引擎（如 Flink）做实时滑动聚合，SQL 层只查结果表。