LAG()需配合PARTITION BY和ORDER BY才能精准获取上一行时间戳，否则会导致跨设备混算、负数或NULL；实操须确保时间字段为标准类型、排序确定，并用COALESCE等处理边界。
LAG函数怎么拿到上一行的时间戳
核心是用 LAG() 提取同一设备/会话中前一条记录的 event_time，但必须配合 ORDER BY 和 PARTITION BY —— 否则数据错位、时长算成负数甚至跨设备混算。常见错误是只写 ORDER BY event_time 却漏掉 PARTITION BY device_id，结果把设备A的最后一条和设备B的第一条连起来算停留时间。
实操建议：
- 确保排序字段包含业务主键（如
device_id,session_id）+ 时间戳，例如ORDER BY device_id, event_time - 如果原始时间字段是字符串（如
'2024-03-15 10:22:31'），先用TO_TIMESTAMP()或STR_TO_DATE()转成时间类型，否则LAG()拿到的是字典序前一行，不是时间序 - PostgreSQL/Oracle 支持
LAG(col, offset, default)的第三个参数填默认值，MySQL 8.0+ 也支持；但旧版 MySQL 只能靠COALESCE(LAG(...), '1970-01-01')避免 NULL
计算停留时长为什么总出负数或NULL
负数说明 event_time 没严格递增，可能是日志采集乱序、时钟漂移或批量导入未排序；NULL 则大概率是分区首行——LAG() 对每个分组第一行返回 NULL，这是正常行为，不是 bug。
实操建议：
- 用
WHERE LAG(event_time) IS NOT NULL过滤掉首行，或用COALESCE(DATEDIFF(event_time, LAG(event_time)), 0)把首行设为 0 - 若发现大量负数，先加检查：
SELECT *, event_time > LAG(event_time) OVER (PARTITION BY device_id ORDER BY event_time) AS is_ordered，查出 false 就得清洗数据 - 时间单位要对齐：MySQL 用
TIMESTAMPDIFF(SECOND, ..., ...)，PostgreSQL 用EXTRACT(EPOCH FROM (event_time - LAG(event_time)))，别直接减（PostgreSQL 返回 interval，MySQL 返回日期差）
状态机场景下如何关联前后状态判断有效停留
单纯算时间差没意义——比如从 'idle' → 'idle' 不该计停留，而 'idle' → 'running' 才代表状态跃迁。必须把 LAG(status) 和当前 status 一起取出来做条件过滤。
实操建议：
- 在子查询或 CTE 中同时取出
LAG(status)和LAG(event_time)，再在外层WHERE status != LAG(status)筛出真实状态变化点 - 注意初始状态：首行
LAG(status)是 NULL，status != NULL永远为 false，所以要用status != COALESCE(LAG(status), 'unknown')或显式OR LAG(status) IS NULL捕获第一条 - 如果状态有中间态（如
'starting'→'running'），且只想统计稳定态停留，就加AND LAG(status) NOT IN ('starting', 'stopping')
性能和边界情况怎么处理
百万级日志表上直接套两层窗口函数容易慢，尤其 PARTITION BY 基数高（如每条记录都是独立 device_id）时，LAG 的开销接近全表扫描。另外，长时间无事件的设备会漏掉“超时退出”这类隐式状态变更。
实操建议：
- 给
(device_id, event_time)建联合索引，比单列索引对窗口函数更友好 - 避免在
WHERE中直接用LAG()（MySQL 8.0.22+ 支持，但老版本会报错），一律先放子查询里计算好再过滤 - 对“超时”逻辑，不能只依赖日志：需额外补全逻辑，例如用
LEAD(event_time)算下一次事件间隔，若 > 30 分钟且当前状态是'running'，就人工插入一条'timeout'事件再重跑
真正难的不是写对 LAG()，而是确认你的“状态变更”定义是否覆盖了所有业务语义——比如设备断连后重连，日志里可能只有新 'online'，但实际应视为从 'offline' 变更而来。这种得靠业务规则补全，SQL 本身做不到。
