Gaps-and-islands问题：统计设备各状态累计耗时

你的需求核心是通过下一条记录的时间戳作为当前状态的结束时间，累计计算每个状态的总耗时。可以通过LEAD()窗口函数实现，以下是具体步骤和SQL代码：

1. 计算单条记录的耗时 #

先用LEAD()获取同serialno分组内下一条记录的时间戳，再计算当前记录的持续时长：

SELECT
    id,
    serialno,
    state,
    timestamp,
    CASE
        WHEN LEAD(timestamp) OVER (PARTITION BY serialno ORDER BY id) IS NOT NULL
        THEN TIMESTAMPDIFF(SECOND, timestamp, LEAD(timestamp) OVER (PARTITION BY serialno ORDER BY id))
        ELSE NULL -- 对应示例中的(ongoing)，后续汇总时排除
    END AS duration
FROM your_table_name;

执行后会得到你需要的中间结果：

2. 汇总每个状态的总耗时 #

基于上述中间结果，分组求和并拼接计算过程：

SELECT
    serialno,
    state,
    CASE 
        WHEN COUNT(duration) > 1 THEN CONCAT(GROUP_CONCAT(duration SEPARATOR ' + '), ' = ', SUM(duration))
        ELSE CAST(SUM(duration) AS CHAR)
    END AS duration
FROM (
    SELECT
        serialno,
        state,
        TIMESTAMPDIFF(SECOND, timestamp, LEAD(timestamp) OVER (PARTITION BY serialno ORDER BY id)) AS duration
    FROM your_table_name
) t
WHERE duration IS NOT NULL -- 排除未结束的状态记录
GROUP BY serialno, state;

执行后得到最终预期结果：

关键说明 #

• 排序逻辑：如果id的递增顺序和时间戳顺序一致，用ORDER BY id没问题；若时间戳可能乱序，需改为ORDER BY timestamp。
• 时间差函数适配：不同数据库语法略有区别：
  • MySQL：TIMESTAMPDIFF(SECOND, start, end)
  • PostgreSQL：EXTRACT(EPOCH FROM (end_time - start_time))::INT
  • SQL Server：DATEDIFF(SECOND, start_time, end_time)

内容的提问来源于stack exchange，提问作者robert.oh.