工单系统中重复部门按归属时段分组的停留时长统计需求

这是个很常见的连续分组统计场景——你需要的是工单在同一部门连续停留的时段，而不是把所有该部门的记录不管时序混在一起计算，对吧？之前用rank类函数没搞定，是因为它们只按部门分组排序，没考虑流转的连续性。

核心思路 #

我们可以通过以下两步实现：

• 第一步：用LAG()窗口函数，给每条记录标记它和上一条记录的部门是否发生变化
• 第二步：基于这个变化标记生成连续分组ID，把连续停留在同一部门的记录归为一组，最后按这个分组聚合统计时长

具体SQL实现 #

先看完整的查询语句，后面再拆解细节：

WITH grouped_history AS (
    SELECT 
        id,
        assigned_group,
        update_time,
        -- 标记当前记录与上一条的部门是否不同
        CASE 
            WHEN LAG(assigned_group) OVER (PARTITION BY id ORDER BY version) = assigned_group THEN 0
            ELSE 1
        END AS group_change_flag,
        -- 生成连续分组ID：累计求和变化标记，每次变化就+1
        SUM(CASE 
            WHEN LAG(assigned_group) OVER (PARTITION BY id ORDER BY version) = assigned_group THEN 0
            ELSE 1
        END) OVER (PARTITION BY id ORDER BY version) AS continuous_group_id
    FROM service_req_history
    WHERE id = 405012 -- 可替换为需要统计的工单ID
)
SELECT 
    MIN(update_time) AS "Min Update Time",
    MAX(update_time) AS "Max Update Time",
    assigned_group,
    DATEDIFF(DAY, MIN(update_time), MAX(update_time)) AS "Days with Department"
FROM grouped_history
GROUP BY id, assigned_group, continuous_group_id
ORDER BY MIN(update_time);

代码拆解 #

1. CTE部分（grouped_history）：

   • LAG(assigned_group) OVER (PARTITION BY id ORDER BY version)：获取当前工单的上一条记录的部门，按version（流转版本）排序保证时序正确
   • group_change_flag：如果当前部门和上一条相同则标记0，不同则标记1
   • continuous_group_id：对group_change_flag做累计求和，这样连续的同一部门会得到同一个ID，部门变化时ID会递增

2. 最终聚合：

   • 按id（工单）、assigned_group（部门）、continuous_group_id（连续分组ID）聚合
   • 计算每个分组的最早/最晚时间，以及停留天数（你之前用的HOUR，这里按期望结果用DAY，可按需调整）

效果验证 #

这个查询会完美匹配你期望的结果：

• 第一次Support的连续时段（2019/07/19-2019/07/22）会被单独分组
• 之后转回的Support（2019/08/26-2019/08/28）会生成另一个分组ID，和第一次的Support分开统计
• 其他部门的连续时段也会各自独立计算

内容的提问来源于stack exchange，提问作者Charl van Tonder