如何用SQL计算工单特定状态转换的平均耗时（含状态循环场景）

你的当前SQL存在两个核心问题：一是用MAX()聚合会丢失工单多次状态转换的记录（比如工单反复在B和C间切换的情况），二是没有关联状态转换的前后时间点，根本无法计算耗时。下面是针对需求的可行方案：

核心思路 #

利用LAG()函数按工单分组、按状态变更时间排序，获取每条状态记录的上一个状态和上一次状态变更时间，然后筛选出目标转换对，计算单次转换耗时后再求平均值。

完整SQL示例 #

-- 第一步：获取每个状态变更的前序状态及时间
WITH status_transitions AS (
    SELECT
        ticket_no,
        current_status,
        created_at AS current_time,
        -- 获取上一个状态
        LAG(current_status) OVER (PARTITION BY ticket_no ORDER BY created_at) AS previous_status,
        -- 获取上一个状态的变更时间
        LAG(created_at) OVER (PARTITION BY ticket_no ORDER BY created_at) AS previous_time
    FROM statuses
),
-- 第二步：筛选出目标转换对并计算单次耗时
target_transitions AS (
    SELECT
        ticket_no,
        CONCAT(previous_status, '->', current_status) AS transition,
        -- 计算耗时（根据数据库类型调整函数，比如PostgreSQL用AGE，MySQL用TIMESTAMPDIFF）
        TIMESTAMPDIFF(SECOND, previous_time, current_time) AS transition_seconds
    FROM status_transitions
    -- 筛选需要统计的转换对
    WHERE (previous_status = 'A' AND current_status = 'C')
       OR (previous_status = 'C' AND current_status = 'E')
    -- 排除没有前序状态的第一条记录
    AND previous_status IS NOT NULL
)
-- 第三步：计算各转换对的平均耗时
SELECT
    transition,
    COUNT(*) AS total_transitions,
    AVG(transition_seconds) AS avg_seconds,
    -- 可选：转换为更易读的格式
    SEC_TO_TIME(AVG(transition_seconds)) AS avg_time
FROM target_transitions
GROUP BY transition;

关键说明 #

• 处理循环转换：PARTITION BY ticket_no ORDER BY created_at确保每个工单的状态按时间顺序关联，即使同一状态对反复出现（比如B->C->B->C），每一次转换都会被单独统计。
• 数据库适配：耗时计算函数需根据你的数据库调整：
  • MySQL：用TIMESTAMPDIFF(单位, 开始时间, 结束时间)，单位可选SECOND、MINUTE、HOUR等
  • PostgreSQL：用AGE(current_time, previous_time)获取时间间隔，再提取秒数EXTRACT(SECOND FROM AGE(...))
  • SQL Server：用DATEDIFF(second, previous_time, current_time)
• 扩展转换对：只需在WHERE条件中添加更多OR (previous_status = 'X' AND current_status = 'Y')即可统计其他转换。

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