合并跨周期重复状态的任务生命周期数据问题求助

我有一组跟踪任务生命周期各阶段的数据，基础状态为Open、Worked on、Closed。但系统会多日重复标记工单相同状态，比如原本应该是“周一Open、周五Worked on”，实际可能出现“周一Open、周三Open”，甚至“周一Open、周二Worked on、周三Open”的情况，导致无法直接按Status_Name分组并按BEG_DT排序。

我需要合并连续重复的状态记录：比如示例数据中Status_Name为Approved的2022-02-17和2022-02-18两条重叠记录，应合并为BEG_DT=2022-02-17、END_DT=2022-06-30的单条记录。最终目标是计算各状态的平均时长，所以需要合并所有连续重复的Status_Name记录。

我尝试用递归CTE实现但没成功，相关代码如下：

DROP TABLE IF EXISTS #TEMP 
CREATE TABLE #TEMP (PRODUCT VARCHAR(30), STATUS_NAME VARCHAR(30), BEG_DT DATE,END_DT DATE)
INSERT INTO #TEMP VALUES
('Chevy1','Scoping','2021-08-23','2021-11-18')
,('Chevy1','Qualified','2021-11-25','2022-01-25')
,('Chevy1','Approved','2022-01-25','2022-02-17')
,('Chevy1','Completed','2022-02-17','2022-02-17')
,('Chevy1','Approved','2022-02-17','2022-02-18')
,('Chevy1','Approved','2022-02-18','2022-06-30')
,('Chevy1','Deferred','2022-06-30','2023-03-16')
,('Chevy1','Approved','2023-03-16',null)    
;
WITH CALC AS (
SELECT Product
        ,Status_Name
        ,BEG_DT
        ,END_DT
        ,ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY BEG_DT) RNB
FROM #TEMP
)
,Rec_CALC as (
SELECT Product
        ,Status_Name
        ,BEG_DT
        ,END_DT
        ,CAST(RNB AS INT) AS RNB
FROM CALC

UNION ALL 

SELECT t2.Product
        ,t2.Status_Name
        ,t2.BEG_DT
        ,t2.END_DT
        ,CAST(0 AS INT)
FROM CALC c
JOIN Rec_CALC t2
on c.PRODUCT = t2.PRODUCT
and c.STATUS_NAME = t2.STATUS_NAME
and CAST(c.RNB AS INT) = CAST(t2.RNB-1 AS INT)
)

不用递归CTE，用窗口函数LAG()判断状态是否发生变化，给连续相同状态的记录分配同一个分组ID，再按分组聚合即可，逻辑更简洁高效。

完整实现代码 #

DROP TABLE IF EXISTS #TEMP 
CREATE TABLE #TEMP (PRODUCT VARCHAR(30), STATUS_NAME VARCHAR(30), BEG_DT DATE,END_DT DATE)
INSERT INTO #TEMP VALUES
('Chevy1','Scoping','2021-08-23','2021-11-18')
,('Chevy1','Qualified','2021-11-25','2022-01-25')
,('Chevy1','Approved','2022-01-25','2022-02-17')
,('Chevy1','Completed','2022-02-17','2022-02-17')
,('Chevy1','Approved','2022-02-17','2022-02-18')
,('Chevy1','Approved','2022-02-18','2022-06-30')
,('Chevy1','Deferred','2022-06-30','2023-03-16')
,('Chevy1','Approved','2023-03-16',null)    
;

-- 生成连续状态的分组标识
WITH StatusGroups AS (
    SELECT 
        PRODUCT,
        STATUS_NAME,
        BEG_DT,
        END_DT,
        -- 当当前状态与上一条不同时，分组ID加1，否则保持一致
        SUM(CASE WHEN LAG(STATUS_NAME) OVER (PARTITION BY PRODUCT ORDER BY BEG_DT) = STATUS_NAME THEN 0 ELSE 1 END) 
            OVER (PARTITION BY PRODUCT ORDER BY BEG_DT) AS GroupID
    FROM #TEMP
),
-- 合并连续相同状态的记录
MergedStatus AS (
    SELECT 
        PRODUCT,
        STATUS_NAME,
        MIN(BEG_DT) AS BEG_DT,
        MAX(END_DT) AS END_DT -- MAX会保留null值（未结束的状态）
    FROM StatusGroups
    GROUP BY PRODUCT, STATUS_NAME, GroupID
)
-- 计算各状态平均时长，处理未结束的状态（用当前日期替代null的END_DT）
SELECT 
    STATUS_NAME,
    AVG(DATEDIFF(day, BEG_DT, ISNULL(END_DT, GETDATE()))) AS AvgDurationDays
FROM MergedStatus
GROUP BY STATUS_NAME;

关键逻辑说明 #

1. 分组标识生成：用LAG()函数获取同产品的上一条状态，对比当前状态，状态变化时分组ID递增，这样连续相同状态的记录会被分到同一组；
2. 合并记录：按产品、状态和分组ID聚合，取每组最早的开始日期和最晚的结束日期；
3. 时长计算：用DATEDIFF()计算单条记录的时长，用ISNULL()处理未结束的状态（END_DT为null），最后取各状态的平均值。

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