应先用窗口函数识别连续相同状态段，再对每段用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_sec
FROM (
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
) t
GROUP 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 处理、重复事件排序这三处，任一疏忽都会让总时长少算几十小时。动手前先用单台设备抽几条典型数据手算验证分段是否合理。
