如何用SQL统计工单的分组、子组及指定组间的变更次数？

需求说明 #

• 按Group统计变更次数：工单1共3次（A→B、B→C、C→A），工单2共3次；
• 仅统计从Group A到Group B的变更次数：工单1为1次，工单2为2次；
• 按Subgroup统计变更次数：工单1为5次。

现有数据 #

尝试过的SQL（未得到预期结果） #

WITH  Groups_Lag AS
(
    SELECT [Number], [Group], LAG([Group], 1) OVER(ORDER BY Id) AS lag_kpi
from [test].[dbo].[test2]
)
,
final as (
SELECT [Number], [Group],lag_kpi, (Case when [Group] = lag_kpi then 0 else 1 end) as check_col
FROM Groups_Lag
GROUP by [Number], [Group], lag_kpi)
 
select sum(check_col) -1
from final

正确SQL写法 #

问题分析 #

原查询的核心问题：

1. LAG函数未按Number（工单编号）分区，会把不同工单的记录混在一起计算；
2. 不必要的GROUP BY打乱了变更顺序的判断逻辑；
3. 虽用Id排序，但未明确分区，无法保证单工单内的变更顺序正确。

以下是针对三个需求的分别实现：

1. 按Group统计各工单的变更次数

WITH GroupChanges AS (
    SELECT 
        Number,
        [Group],
        LAG([Group]) OVER (PARTITION BY Number ORDER BY Id) AS PreviousGroup
    FROM [test].[dbo].[test2]
)
SELECT 
    Number,
    SUM(CASE WHEN [Group] != PreviousGroup THEN 1 ELSE 0 END) AS GroupChangeCount
FROM GroupChanges
GROUP BY Number;

按工单编号分区，用LAG获取同工单内上一条记录的Group值，统计前后Group不同的次数，即为该工单的Group变更次数。

2. 统计各工单从Group A到Group B的变更次数

WITH ABChanges AS (
    SELECT 
        Number,
        [Group],
        LAG([Group]) OVER (PARTITION BY Number ORDER BY Id) AS PreviousGroup
    FROM [test].[dbo].[test2]
)
SELECT 
    Number,
    SUM(CASE WHEN PreviousGroup = 'A' AND [Group] = 'B' THEN 1 ELSE 0 END) AS AToBChangeCount
FROM ABChanges
GROUP BY Number;

在分组变更的基础上，仅筛选上一个Group是A、当前Group是B的记录，统计这类情况的出现次数。

3. 按Subgroup统计各工单的变更次数

WITH SubgroupChanges AS (
    SELECT 
        Number,
        Subgroup,
        LAG(Subgroup) OVER (PARTITION BY Number ORDER BY Id) AS PreviousSubgroup
    FROM [test].[dbo].[test2]
)
SELECT 
    Number,
    SUM(CASE WHEN Subgroup != PreviousSubgroup THEN 1 ELSE 0 END) AS SubgroupChangeCount
FROM SubgroupChanges
GROUP BY Number;

逻辑与Group统计一致，将判断对象替换为Subgroup，统计同工单内前后Subgroup不同的次数。

内容的提问来源于stack exchange，提问作者A.B.9