先抛一个核心结论：要想正确统计每台设备在不同状态下的持续时长，关键是把连续相同的状态记录合并成一段，再计算每段的时间差。举个简单的例子——设备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处理、重复事件排序这三个地方，任何一个疏忽都可能让总时长少算几十个小时。动手之前，建议先用单台设备抽几条典型数据，手算验证一下分段是否合理。
