基于状态变更对案件活动表记录分组的SQL 2014实现问询

嘿，这个需求我之前帮朋友处理过类似的，在SQL Server 2014里完全可以实现，咱们一步步来拆解解决：

第一步：填充NULL状态值 #

原数据里有很多status为NULL的行，需要沿用前一条非NULL的状态值。SQL Server 2014的LAG()函数不支持IGNORE NULLS，所以我们用分组的方式来填充：

WITH FilledStatus AS (
    SELECT 
        caseID,
        datetime,
        action,
        status,
        -- 给每个非NULL状态的行标记分组，NULL行继承前面的分组号
        SUM(CASE WHEN status IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY caseID 
            ORDER BY datetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS status_grp
    FROM YourActivityTable
),
FilledStatusFinal AS (
    SELECT 
        caseID,
        datetime,
        action,
        -- 取分组内的非NULL状态值填充所有行
        MAX(status) OVER (PARTITION BY caseID, status_grp) AS filled_status
    FROM FilledStatus
)

第二步：标记并生成Session编号 #

Session是从OPEN/REOPEN开始，到下一个CLOSED结束，下一个OPEN/REOPEN开启新Session。我们需要先识别每个Session的起始点，再累加生成编号：

, SessionMarked AS (
    SELECT 
        caseID,
        datetime,
        filled_status,
        -- 当状态是OPEN或REOPEN时，标记为新Session的起点
        CASE WHEN filled_status IN ('OPEN', 'REOPEN') THEN 1 ELSE 0 END AS session_start
    FROM FilledStatusFinal
),
SessionNumbered AS (
    SELECT 
        caseID,
        datetime,
        filled_status,
        -- 累加起点标记，生成Session编号
        SUM(session_start) OVER (
            PARTITION BY caseID 
            ORDER BY datetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session
    FROM SessionMarked
)

第三步：生成最终的日期区间结果 #

最后按caseID、session、filled_status分组，获取每个状态的起始日期，并用LEAD()函数获取下一个状态的起始日期作为结束日期：

SELECT 
    caseID AS CaseID,
    filled_status AS status,
    MIN(datetime) AS datefrom,
    -- 取下一个状态的最早日期作为当前状态的结束日期，没有则为NULL
    LEAD(MIN(datetime)) OVER (
        PARTITION BY caseID 
        ORDER BY MIN(datetime)
    ) AS dateto,
    session
FROM SessionNumbered
GROUP BY caseID, session, filled_status
ORDER BY caseID, session, datefrom;

完整代码 #

把上面的CTE组合起来，替换YourActivityTable为你的实际表名即可：

WITH FilledStatus AS (
    SELECT 
        caseID,
        datetime,
        action,
        status,
        SUM(CASE WHEN status IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY caseID 
            ORDER BY datetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS status_grp
    FROM YourActivityTable
),
FilledStatusFinal AS (
    SELECT 
        caseID,
        datetime,
        action,
        MAX(status) OVER (PARTITION BY caseID, status_grp) AS filled_status
    FROM FilledStatus
),
SessionMarked AS (
    SELECT 
        caseID,
        datetime,
        filled_status,
        CASE WHEN filled_status IN ('OPEN', 'REOPEN') THEN 1 ELSE 0 END AS session_start
    FROM FilledStatusFinal
),
SessionNumbered AS (
    SELECT 
        caseID,
        datetime,
        filled_status,
        SUM(session_start) OVER (
            PARTITION BY caseID 
            ORDER BY datetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session
    FROM SessionMarked
)
SELECT 
    caseID AS CaseID,
    filled_status AS status,
    MIN(datetime) AS datefrom,
    LEAD(MIN(datetime)) OVER (
        PARTITION BY caseID 
        ORDER BY MIN(datetime)
    ) AS dateto,
    session
FROM SessionNumbered
GROUP BY caseID, session, filled_status
ORDER BY caseID, session, datefrom;

验证结果 #

用你提供的示例数据测试，这个代码会生成你期望的输出：每个状态的日期区间、对应的Session编号，最后一个状态的dateto为NULL。

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