如何使用SQL计算状态转换场景下各状态的总耗时

该方案适用于包含时间、状态两列的时序表，可直接统计每个状态的累计总耗时（单位：秒），与你给出的示例计算结果匹配。

实现逻辑 #

• 用窗口函数获取每条状态记录对应的下一条记录的时间，作为当前状态的结束时间
• 计算单条状态记录的持续时长，按状态分组累加时长即可得到总耗时

示例表结构 #

假设时序表名为status_log，字段如下：

• log_time：状态记录时间，datetime类型
• status：状态标识，varchar类型

对应你的示例输入数据：

log_time	status
2024-01-01 00:00:00	A
2024-01-01 00:00:06	B
2024-01-01 00:00:21	A
2024-01-01 00:00:25	结束标识

主流数据库实现代码 #

MySQL 8.0+/支持窗口函数的数据库 #

SELECT 
    status,
    SUM(TIMESTAMPDIFF(SECOND, log_time, next_log_time)) AS total_duration_second
FROM (
    SELECT 
        log_time,
        status,
        LEAD(log_time, 1) OVER (ORDER BY log_time) AS next_log_time
    FROM status_log
) t
-- 若需要统计当前还在持续的最后一条状态，可去掉WHERE条件，将next_log_time为空的替换为NOW()
WHERE next_log_time IS NOT NULL
GROUP BY status;

执行后返回结果和你示例一致：A总耗时6秒，B总耗时15秒。

其他数据库适配修改 #

• PostgreSQL：将TIMESTAMPDIFF(SECOND, log_time, next_log_time)替换为EXTRACT(EPOCH FROM (next_log_time - log_time))
• SQL Server：将TIMESTAMPDIFF(SECOND, log_time, next_log_time)替换为DATEDIFF(SECOND, log_time, next_log_time)

不支持窗口函数的低版本数据库适配 #

用自关联实现相同逻辑：

SELECT 
    a.status,
    SUM(TIMESTAMPDIFF(SECOND, a.log_time, MIN(b.log_time))) AS total_duration_second
FROM status_log a
LEFT JOIN status_log b ON a.log_time < b.log_time
GROUP BY a.log_time, a.status
HAVING MIN(b.log_time) IS NOT NULL
GROUP BY a.status;

内容的提问来源于stack exchange，提问作者Stack Overflow