LAG()必须配合ORDER BY使用，否则结果不可靠；传感器数据虽天然有序，但LAG()不自动识别时间顺序，需显式ORDER BY timestamp并处理NULL、首行及性能优化。
LAG() 必须配合 ORDER BY 使用，否则结果不可靠
传感器数据天然有序（按时间戳），但 LAG() 不会自动识别“时间顺序”。如果查询中漏写 ORDER BY，数据库可能按物理存储顺序返回行，导致前一行根本不是上一时刻的数据，状态切换点完全错乱。
实操建议：
- 始终在窗口函数中显式声明排序：用
ORDER BY timestamp（或ORDER BY created_at，取决于字段名） - 若存在同一时间多个读数，需补充二级排序，例如
ORDER BY timestamp, sensor_id，避免 nondeterministic 行序 - 不要依赖表的
PRIMARY KEY或索引顺序代替ORDER BY
用 LAG() 比较当前值与前值，识别状态变化
状态切换的本质是「当前值 ≠ 前一值」。直接用 LAG(value) 取出上一行的 value，再和当前 value 做等值判断即可定位切换点。
常见错误现象：只查出“变化发生”，却无法区分是「开→关」还是「关→开」。
实操建议：
- 用
LAG(value) OVER (ORDER BY timestamp)获取前值，别忘了加别名，比如prev_value - 用
CASE WHEN value != prev_value THEN 1 ELSE 0 END标记切换（注意 NULL 处理） - 若状态为字符串（如
'ON'/'OFF'），比较时要确保大小写一致；数字状态则注意类型是否隐式转换（如tinyintvsvarchar）
处理 NULL 和首行问题：LAG() 的第一行总是 NULL
LAG() 对窗口首行返回 NULL，这会导致第一行的比较表达式（如 value != prev_value）变成 UNKNOWN，最终被过滤掉或误判。传感器首条记录本身很可能就是一次有效切换（比如设备刚上线）。
实操建议：
- 用
COALESCE(LAG(value) OVER (...), value)把首行前值设为自身，让首次比较恒为FALSE，再单独用ROW_NUMBER() = 1补上首行 - 更稳妥做法：把切换判断拆成两部分——
value != COALESCE(prev_value, 'dummy')+OR ROW_NUMBER() OVER (...) = 1 - 避免用
IS NULL直接判断prev_value，因为真实数据里value也可能为NULL，需区分“无前值”和“前值为空”
性能关键：给 timestamp 加索引，且避免在 LAG() 中嵌套复杂表达式
对百万级传感器数据跑 LAG()，若没索引，排序成本极高；若在 OVER 子句里写 ORDER BY DATE(timestamp) 这类函数，索引失效，全表扫描不可避免。
实操建议：
- 确保
timestamp字段有 B-tree 索引（PostgreSQL/MySQL/SQL Server 均适用） - 不要在
ORDER BY里用函数或计算字段，例如ORDER BY HOUR(timestamp)或ORDER BY sensor_id * 100 + EXTRACT(EPOCH FROM timestamp) - 如果需按设备分组找各自的状态切换，必须加
PARTITION BY sensor_id，且索引应为复合索引(sensor_id, timestamp)
真正难的不是写出 LAG()，而是确认每一行的“前一行”在业务上是否真的对应“前一时刻”。时间精度（毫秒？秒？）、时区、设备时钟漂移、数据延迟入库——这些都会让看似正确的 SQL 在生产环境漏掉或误报切换点。
