SQL如何统计每台设备在不同状态下的持续时长总和

应先用窗口函数识别连续相同状态段，再对每段用end_time−start_time计算时长；核心是通过SUM(CASE WHEN status!=LAG(status) THEN 1 ELSE 0 END) OVER(...)生成状态分组ID，再按device_id、status、分组ID聚合，用LEAD()补结束时间并COALESCE处理NULL。

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

直接用 MIN() 和 MAX() 算每台设备每个状态的首尾时间，会把中间状态跳变忽略掉——比如设备A在「运行」→「停机」→「运行」，你不能把第一次运行开始到第二次运行结束全算成运行时长。必须把连续相同状态的记录合并成一段，再计算每段的 end_time - start_time。

核心是用窗口函数打“状态分组标签”：按 device_id 和状态排序后，对比当前行和上一行的 status，一旦不同就新起一组。常用技巧是 SUM(CASE WHEN status != LAG(status) OVER (...) THEN 1 ELSE 0 END) OVER (...) 构造分组ID。

• 确保时间字段是 TIMESTAMP 或带时区的类型，避免隐式转换导致精度丢失
• 若原始数据只有状态变更点（无明确 end_time），需用 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 处理、重复事件排序这三处，任一疏忽都会让总时长少算几十小时。动手前先用单台设备抽几条典型数据手算验证分段是否合理。