MySQL按ticket_id分组计算ticket_event表状态流转时间差方案

核心思路 #

先按工单ID分组，把每个工单的各状态对应的更新时间提取为独立字段，再对指定的状态对做时间差计算，不存在的状态对直接返回0即可。

可直接运行的查询语句 #

SELECT
    ticket_id,
    -- OPEN到IN_PROGRESS的时间差
    IF(open_time > 0 AND in_progress_time > open_time, in_progress_time - open_time, 0) AS open_to_inprogress_timediff,
    -- OPEN到DONE的时间差
    IF(open_time > 0 AND done_time > open_time, done_time - open_time, 0) AS open_to_done_timediff,
    -- OPEN到CLOSE的时间差
    IF(open_time > 0 AND close_time > open_time, close_time - open_time, 0) AS open_to_close_timediff,
    -- DONE到CLOSE的时间差
    IF(done_time > 0 AND close_time > done_time, close_time - done_time, 0) AS done_to_close_timediff
FROM (
    -- 子查询：提取每个工单各状态的更新时间
    SELECT
        ticket_id,
        MAX(CASE WHEN status = 'OPEN' THEN gmt_update ELSE 0 END) AS open_time,
        MAX(CASE WHEN status = 'IN_PROGRESS' THEN gmt_update ELSE 0 END) AS in_progress_time,
        MAX(CASE WHEN status = 'DONE' THEN gmt_update ELSE 0 END) AS done_time,
        MAX(CASE WHEN status = 'CLOSE' THEN gmt_update ELSE 0 END) AS close_time
    FROM tiket_event
    GROUP BY ticket_id
) AS ticket_status_time

灵活调整状态对的方法 #

如果需要新增其他状态对的计算，只需要两步：

1. 在内层子查询中新增对应状态的时间提取逻辑，已提取过的状态不需要重复添加
2. 在外层SELECT中新增对应的差值计算逻辑即可，比如要新增IN_PROGRESS到DONE的时间差，只需要加如下代码：

IF(in_progress_time > 0 AND done_time > in_progress_time, done_time - in_progress_time, 0) AS inprogress_to_done_timediff

以上查询用示例数据运行后输出结果和需求完全一致。

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