LAG函数能算出传感器运行间隔，因其本质是取同设备上一行的时间戳，与当前行相减即可得间隔；必须配合PARTITION BY device_id和ORDER BY timestamp使用，否则会跨设备或乱序计算。
LAG 函数为什么能算出传感器运行间隔
LAG 的本质是“取上一行的值”，对按时间排序的传感器记录来说，它能轻松拿到前一次采集的时间戳。只要把当前行的 timestamp 和 LAG(timestamp) 相减，就得到两次采集之间的间隔。关键前提是：数据必须用 ORDER BY timestamp 显式排序，否则 LAG 返回的“上一行”毫无意义。
正确写法：带 PARTITION BY 的 LAG 防止跨设备混算
一个系统通常有多个设备（device_id），如果直接对全表 ORDER BY timestamp，LAG 可能把设备 A 的最后一条和设备 B 的第一条连起来算间隔——结果完全错误。必须按设备分组：
SELECT device_id, timestamp, EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY device_id ORDER BY timestamp))) AS interval_sec FROM sensor_readings;
注意点：
-
PARTITION BY device_id确保每个设备独立计算，互不干扰 -
EXTRACT(EPOCH FROM ...)把时间差转为秒数（PostgreSQL）；MySQL 用TIMESTAMPDIFF(SECOND, ..., ...)，SQLite 用julianday()换算 - 第一行每台设备的
LAG(timestamp)是NULL，对应interval_sec也是NULL，这是正常行为，不是 bug
常见错误：ORDER BY 写错或漏掉导致间隔乱跳
这些写法会出问题：
- 只写
ORDER BY id（主键）——传感器插入顺序 ≠ 实际采集时间，尤其存在延迟上报或重传时 - 写成
ORDER BY timestamp DESC——LAG取的是“后一行”，结果变成倒序间隔，数值可能为负 - 漏掉
ORDER BY（仅靠PARTITION BY）——窗口函数行为未定义，不同数据库返回结果可能不一致
验证方法：加一列 LAG(timestamp) OVER (PARTITION BY device_id ORDER BY timestamp)，肉眼检查是否真为“本设备前一次时间”。
性能与边界情况提醒
大数据量下，LAG 本身不慢，但排序成本高。确保 (device_id, timestamp) 有联合索引。
还要留意：
- 时间字段类型要是
TIMESTAMP WITH TIME ZONE或统一时区的TIMESTAMP，混用本地时间+UTC 会导致间隔异常 - 传感器偶发失联，间隔可能长达几小时甚至几天——这不是 SQL 错，而是业务事实，需后续用
WHERE interval_sec 这类条件过滤异常值 - 如果需要“上一次正常值”而非“上一次时间”，得改用
LAG(value) FILTER (WHERE status = 'ok')（PostgreSQL 14+）
真正难的从来不是写对那行 LAG，而是确认时间字段干净、时区一致、设备标识唯一——这些不在 SQL 里，但在数据源头。
