SQL查询需求：按规则筛选索赔任务并生成分组行号

需求逻辑 #

• 若索赔同时存在Claim Creation和Claim Creation Followup，仅保留最早的Claim Creation Followup记录，忽略所有Claim Creation；
• 若索赔仅存在Claim Creation，保留最早的Claim Creation记录，并保留其后续所有关联任务；
• 若Claim Creation/Claim Creation Followup后出现其他任务，则下一个Claim Creation/Claim Creation Followup需作为新分组起始（行号从1开始）；若连续多个Claim Creation/Followup任务无其他任务紧随，仅保留第一个。

示例表结构及数据 #

DECLARE @t TABLE
(
    claimid int,
    taskname varchar(100),
    completedate datetime,
    tasknumber int
)

INSERT INTO @t
VALUES (123, 'Claim Creation', '01/01/2024', 455),
       (123, 'Incoming', '01/05/2024', 367),
       (123, 'Claim Creation', '02/01/2024', 455),
       (123, 'Incoming', '02/02/2024', 367),
       (234, 'Claim Creation', '03/24/2024', 455),
       (234, 'Claim Creation Followup', '03/25/2024', 566),
       (234, 'Claim Creation Followup', '03/26/2024', 566),
       (234, 'Incoming', '03/28/2024', 367),
       (224, 'Claim Creation Followup', '02/02/2024', 566),
       (224, 'Claim Creation Followup', '02/25/2024', 566)

期望查询结果 #

当前代码问题 #

当前SQL仅按任务编号和日期排序生成行号，未处理分组和记录过滤逻辑，无法满足需求：

select
    *,
    row_number()
        over(partition by claimid
             order by
                 case
                     when tasknumber = 566 then 1
                     when tasknumber = 455 then 2
                     else 3
                 end,
                 completedate asc) as rownum
from @t

解决方案 #

通过分组标记、优先级过滤、行号生成三步实现需求：

WITH TaskGroups AS (
    -- 标记每个索赔的任务分组，以Creation/Followup作为分组起始
    SELECT 
        *,
        SUM(CASE WHEN taskname IN ('Claim Creation', 'Claim Creation Followup') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY claimid ORDER BY completedate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM @t
),
FilteredTasks AS (
    -- 标记分组内是否存在Followup，并给任务排序确定优先级
    SELECT 
        *,
        MAX(CASE WHEN taskname = 'Claim Creation Followup' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY claimid, group_id) AS has_followup,
        ROW_NUMBER() 
            OVER (PARTITION BY claimid, group_id 
                  ORDER BY CASE taskname 
                              WHEN 'Claim Creation Followup' THEN 1 
                              WHEN 'Claim Creation' THEN 2 
                              ELSE 3 
                          END, completedate ASC) AS task_rank
    FROM TaskGroups
),
FinalTasks AS (
    -- 筛选符合条件的记录
    SELECT 
        claimid AS 索赔ID,
        taskname AS 任务,
        CONVERT(varchar, completedate, 101) AS 日期,
        tasknumber AS 任务ID,
        group_id
    FROM FilteredTasks
    WHERE 
        -- 有Followup的分组：去掉所有Claim Creation，仅保留最早的Followup和后续非起始任务
        (has_followup = 1 AND (taskname != 'Claim Creation' OR task_rank = 1))
        -- 无Followup的分组：仅保留最早的Claim Creation和后续所有任务
        OR (has_followup = 0 AND (taskname != 'Claim Creation' OR task_rank = 1))
        -- 过滤连续起始任务中除第一个以外的记录
        AND NOT (taskname IN ('Claim Creation', 'Claim Creation Followup') AND task_rank > 1)
)
-- 生成每个分组内的行号
SELECT 
    索赔ID,
    任务,
    日期,
    任务ID,
    ROW_NUMBER() OVER (PARTITION BY 索赔ID, group_id ORDER BY 日期 ASC) AS 行号
FROM FinalTasks
ORDER BY 索赔ID, 日期 ASC;

代码说明 #

1. TaskGroups：通过累加Claim Creation/Claim Creation Followup的出现次数，为每个索赔的任务划分独立分组，确保每个新的起始任务都作为分组起点。
2. FilteredTasks：标记分组内是否存在Followup任务，同时为分组内任务排序，明确Followup> Creation>其他任务的优先级。
3. FinalTasks：根据分组是否包含Followup过滤冗余记录，保留符合需求的任务条目。
4. 最后对每个分组生成行号，得到完全匹配期望的结果。

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