SQL如何统计每台设备在不同状态下的持续时长总和？-SQL-PHP中文网

应先用窗口函数识别连续相同状态段，再对每段用end_time−start_time计算时长；核心是通过sum(case when status!=lag(status) then 1 else 0 end) over(...)生成状态分组id，再按device_id、status、分组id聚合，用lead()补结束时间并coalesce处理null。

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

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

直接用 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)）：

鲜艺AI抠图

鲜艺AI抠图是一款AI图片处理工具，免费 AI 抠图工具，支持离线安装与使用。

下载

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) 上建联合索引