SQL统计各状态耗时：为每个状态生成小时耗时列

核心思路 #

1. 按案例ID分组，对状态变更记录按时间排序，获取每条记录的下一次变更时间，以此计算单个状态的持续时长。
2. 按ID汇总每个状态的总耗时。
3. 将汇总结果与原表关联，让每个ID的所有记录都带上各状态的总耗时。

SQL 实现（通用版，需根据数据库调整日期转换） #

WITH sorted_changes AS (
    -- 按ID分组排序，获取下一次状态变更时间
    SELECT 
        ID,
        -- 根据数据库调整日期转换函数：
        -- SQL Server: CONVERT(DATETIME, CreatedDate, 103)
        -- MySQL: STR_TO_DATE(CreatedDate, '%d/%m/%Y %H:%i:%s')
        -- PostgreSQL: TO_TIMESTAMP(CreatedDate, 'DD/MM/YYYY HH24:MI:SS')
        CreatedDate,
        OldValue,
        NewValue,
        LEAD(CreatedDate) OVER (PARTITION BY ID ORDER BY CreatedDate) AS NextChangeDate
    FROM your_table_name
),
status_duration AS (
    -- 计算单个状态的持续小时数
    SELECT 
        ID,
        NewValue AS Status,
        -- 最后一条无后续变更的记录（如Closed状态）耗时记为0
        DATEDIFF(HOUR, CreatedDate, COALESCE(NextChangeDate, CreatedDate)) AS HoursSpent
    FROM sorted_changes
),
status_total AS (
    -- 按ID汇总各状态总耗时，生成对应列
    SELECT 
        ID,
        SUM(CASE WHEN Status = 'Escalated' THEN HoursSpent ELSE 0 END) AS TimeSpentEscalatedStatus,
        SUM(CASE WHEN Status = 'Open' THEN HoursSpent ELSE 0 END) AS TimeSpentOpenStatus,
        SUM(CASE WHEN Status = 'In Progress' THEN HoursSpent ELSE 0 END) AS TimeSpentInProgressStatus,
        SUM(CASE WHEN Status = 'With Customer' THEN HoursSpent ELSE 0 END) AS TimeSpentWithCustomerStatus,
        SUM(CASE WHEN Status = 'Closed' THEN HoursSpent ELSE 0 END) AS TimeSpentClosedStatus
    FROM status_duration
    GROUP BY ID
)
-- 关联原表与汇总结果，输出最终数据
SELECT 
    t.ID,
    t.CreatedDate,
    t.OldValue,
    t.NewValue,
    st.TimeSpentEscalatedStatus,
    st.TimeSpentOpenStatus,
    st.TimeSpentInProgressStatus,
    st.TimeSpentWithCustomerStatus,
    st.TimeSpentClosedStatus
FROM your_table_name t
JOIN status_total st ON t.ID = st.ID
ORDER BY t.ID, t.CreatedDate;

数据库适配说明 #

• SQL Server：将所有CreatedDate替换为CONVERT(DATETIME, CreatedDate, 103)（103对应DD/MM/YYYY日期格式）
• MySQL：替换为STR_TO_DATE(CreatedDate, '%d/%m/%Y %H:%i:%s')
• PostgreSQL：替换为TO_TIMESTAMP(CreatedDate, 'DD/MM/YYYY HH24:MI:SS')

补充说明 #

原表中ID=2的记录时间顺序存在逻辑矛盾（20号的记录早于18号），执行时会按实际时间排序计算，若需保证状态流转逻辑正确，需先修正数据的时间顺序。最终结果中每个ID的所有行都会显示该案例在各状态下的总耗时，与示例格式一致。

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