如何在MySQL中基于变更日志计算工单的开单与闭单时长？

要精确统计工单在Open和Closed状态的总时长，核心是捕捉每次状态的时间区间——即状态的开始时间和结束时间。利用MySQL的窗口函数LEAD()可以轻松获取每条状态变更记录的下一次变更时间，从而计算单段状态的持续时长，再汇总得到总时长。

核心思路 #

1. 用LEAD()按工单分组、时间排序，获取每条状态变更的下一次时间，作为当前状态的结束时间
2. 映射状态：将Reopened视为Open（因为重开后工单回到开单状态）
3. 计算单段状态时长，再按工单汇总各状态总时长

示例SQL代码 #

假设变更日志表名为ticket_change_log，字段对应：id（序号）、create_time（变更时间）、field（变更字段）、old_status（原状态）、new_status（新状态）、ticket_id（工单ID）。

第一步：获取单状态时间区间及时长 #

SELECT
    ticket_id,
    -- 状态映射：Reopened等价于Open
    CASE new_status
        WHEN 'Reopened' THEN 'Open'
        ELSE new_status
    END AS current_status,
    create_time AS status_start_time,
    -- 下一次变更时间，无后续变更则用当前时间（统计到此刻）
    LEAD(create_time, 1, NOW()) OVER (PARTITION BY ticket_id ORDER BY create_time) AS status_end_time,
    -- 计算该状态持续时长（精确到秒）
    TIMESTAMPDIFF(SECOND, create_time, LEAD(create_time, 1, NOW()) OVER (PARTITION BY ticket_id ORDER BY create_time)) AS duration_seconds
FROM ticket_change_log
WHERE field = 'status' -- 仅筛选状态变更记录

第二步：汇总工单各状态总时长 #

基于上述子查询，汇总得到每个工单的Open/Closed总时长：

SELECT
    ticket_id,
    -- 转换为时分秒格式，便于阅读
    SEC_TO_TIME(SUM(CASE WHEN current_status = 'Open' THEN duration_seconds ELSE 0 END)) AS total_open_duration,
    SEC_TO_TIME(SUM(CASE WHEN current_status = 'Closed' THEN duration_seconds ELSE 0 END)) AS total_closed_duration,
    -- 保留秒数，方便后续计算
    SUM(CASE WHEN current_status = 'Open' THEN duration_seconds ELSE 0 END) AS total_open_seconds,
    SUM(CASE WHEN current_status = 'Closed' THEN duration_seconds ELSE 0 END) AS total_closed_seconds
FROM (
    SELECT
        ticket_id,
        CASE new_status
            WHEN 'Reopened' THEN 'Open'
            ELSE new_status
        END AS current_status,
        create_time AS status_start_time,
        LEAD(create_time, 1, NOW()) OVER (PARTITION BY ticket_id ORDER BY create_time) AS status_end_time,
        TIMESTAMPDIFF(SECOND, create_time, LEAD(create_time, 1, NOW()) OVER (PARTITION BY ticket_id ORDER BY create_time)) AS duration_seconds
    FROM ticket_change_log
    WHERE field = 'status'
) AS status_durations
GROUP BY ticket_id
ORDER BY ticket_id;

关键细节说明 #

• LEAD()函数：解决了行间时间获取的问题，相比MAX()/MIN()能精准定位每次状态变更的结束时间，适配多次状态切换的场景
• 状态映射：Reopened是Closed到Open的过渡，将其映射为Open可保证状态统计的一致性
• 最后一条记录处理：如果工单最后一次变更后未再更新，LEAD()的第三个参数NOW()会自动用当前时间作为结束时间，确保时长统计到当前时刻
• 边界场景适配：若工单无任何状态变更（仅创建），可结合工单主表的创建时间，通过UNION ALL将初始状态加入统计，示例如下：

SELECT
    ticket_id,
    current_status,
    status_start_time,
    LEAD(status_start_time, 1, NOW()) OVER (PARTITION BY ticket_id ORDER BY status_start_time) AS status_end_time,
    TIMESTAMPDIFF(SECOND, status_start_time, LEAD(status_start_time, 1, NOW()) OVER (PARTITION BY ticket_id ORDER BY status_start_time)) AS duration_seconds
FROM (
    -- 工单初始状态（假设主表为tickets，含initial_status字段）
    SELECT
        ticket_id,
        initial_status AS current_status,
        create_time AS status_start_time,
        0 AS sort_order
    FROM tickets
    UNION ALL
    -- 变更日志的状态记录
    SELECT
        ticket_id,
        CASE new_status WHEN 'Reopened' THEN 'Open' ELSE new_status END AS current_status,
        create_time AS status_start_time,
        1 AS sort_order
    FROM ticket_change_log
    WHERE field = 'status'
) AS combined_data
ORDER BY ticket_id, sort_order, status_start_time;

内容的提问来源于stack exchange，提问作者Bartosz Połatyński