SQL统计每台设备各状态持续时长总和的方法

先抛一个核心结论：要想正确统计每台设备在不同状态下的持续时长，关键是把连续相同的状态记录合并成一段，再计算每段的时间差。举个简单的例子——设备A的状态变化是“运行→停机→运行”，如果直接用MIN()和MAX()算首尾时间，就会把第一次运行开始到第二次运行结束全算成运行时长，显然不对。正确的做法是先识别出“连续相同状态段”，然后对每段计算end_time - start_time。

状态持续时间怎么算：先识别状态段，再求差

怎么识别状态段？核心是用窗口函数打一个“状态分组标签”。按device_id和状态排序后，对比当前行和上一行的status，一旦不同就新起一组。常用的技巧是：SUM(CASE WHEN status != LAG(status) OVER (...) THEN 1 ELSE 0 END) OVER (...)，这样就能构造出分组ID。有了分组ID，再按device_id、status、分组ID聚合，用LEAD()补出结束时间，最后用COALESCE处理NULL。

• 时间字段必须用TIMESTAMP或带时区的类型，否则隐式转换会导致精度丢失
• 如果原始数据只有状态变更点（没有明确结束时间），就用LEAD(event_time) OVER (PARTITION BY device_id ORDER BY event_time)补出“下一次变更时间”作为本段结束时间
• 最后一段的LEAD()会返回NULL，建议用COALESCE(LEAD(...), NOW())或业务指定的截止时间

PostgreSQL vs MySQL 8.0+ 的写法差异

PostgreSQL对LAG()、LEAD()和窗口帧定义支持得非常好，写法统一；MySQL 8.0+才开始支持这些函数，5.7及以前只能靠自连接或变量模拟，不仅不可靠，性能也差。

以PostgreSQL为例（假设表为device_events(device_id, status, event_time)）：

SELECT  device_id,  status,  SUM(EXTRACT(EPOCH FROM (end_time - start_time))) AS duration_secFROM (  SELECT    device_id,    status,    event_time AS start_time,    COALESCE(LEAD(event_time) OVER (      PARTITION BY device_id ORDER BY event_time    ), NOW()) AS end_time,    SUM(CASE WHEN status != LAG(status) OVER (      PARTITION BY device_id ORDER BY event_time    ) THEN 1 ELSE 0 END) OVER (      PARTITION BY device_id ORDER BY event_time    ) AS status_group  FROM device_events) tGROUP BY device_id, status, status_group;

• MySQL 8.0+把NOW()换成NOW()或CURRENT_TIMESTAMP即可，但EXTRACT(EPOCH FROM ...)要改成TIMESTAMPDIFF(SECOND, start_time, end_time)
• 如果设备有大量高频状态变更（比如每秒一条），OVER (PARTITION BY device_id ORDER BY event_time)的排序开销很明显，建议在(device_id, event_time)上建联合索引

遇到 NULL 或重复时间戳怎么办

真实日志里经常出现乱序、缺失或同一毫秒多条记录的情况。直接ORDER BY event_time可能让LAG()拿错上一行——比如两条“停机”记录时间相同，但实际发生顺序不同。

• 务必加一个唯一的排序依据：比如ORDER BY event_time, id（id是自增主键或事件唯一ID）
• 用WHERE event_time IS NOT NULL过滤掉脏数据，否则LEAD()和时间差计算会传播NULL
• 重复时间戳本身不致命，但如果状态也相同，可能属于同一逻辑段；如果状态不同，就必须靠额外字段（如日志序列号）确定先后顺序
• 有些系统用字符串存储event_time，记得先CAST(event_time AS TIMESTAMP)，否则排序和计算都会出错

聚合结果里漏了某台设备的某个状态？检查边界条件

常见的漏统计不是语法错误，而是状态段没有闭合。比如设备刚上线只有一条“运行”记录，还没触发下一次变更，LEAD()返回NULL，而你没有用COALESCE()补默认结束时间，整段就被丢弃了。

• 用子查询单独查出所有device_id和status组合，LEFT JOIN到聚合结果，看看哪些组合缺失
• 检查是否用了WHERE status IN ('运行', '停机', '故障')之类的过滤条件，把未知状态（如空字符串、'unknown'）直接剔除了
• 如果设备长时间无上报，最新状态段的结束时间应该设为查询时刻，而不是硬编码某个固定时间点

状态分段逻辑看起来简单，但时间边界、NULL处理、重复事件排序这三个地方，任何一个疏忽都可能让总时长少算几十个小时。动手之前，建议先用单台设备抽几条典型数据，手算验证一下分段是否合理。