SQL需求：按Case Ref统计任务后续关联Success数及代码修复求助

问题 #

现有一张案件任务执行记录表，包含Case Ref（案件编号）、Task Name（任务名称）、Datetime（执行时间）字段，示例数据如下：

需求是生成新表，展示每个属于Task 1/Task 2/Task 3的任务对应的Case Ref、任务名、执行时间，以及该任务之后到同Case下下一个非Success任务前的Success任务数量，期望结果如下：

当前使用的SQL代码存在跨Case统计Success的问题（比如Case A的Task后跟着Case B的Success会被错误统计），原代码如下：

with t1 as (
select distinct top 1000
[Case Ref]
, [Case Ref] [case ref 2]
, [GTH Task Name]
, [GTH Performed Datetime]
from Table
where [gth task name] in ('Task 1', 'Task 2', 'Task 3', 'Success'))

, cte AS (
        SELECT [Case Ref]
                     ,[GTH Task Name]
                     ,[GTH Performed Datetime]
                    ,SUM(case when [GTH Task Name]='Success' and [case ref] = [case ref 2] then 1 else 0 end) OVER (ORDER BY [GTH Performed Datetime] DESC) workings
              FROM t1
              )

SELECT [Case Ref],
        [GTH Task Name], 
       workings - COALESCE(LEAD(workings) OVER (ORDER BY [GTH Performed Datetime]), 0) 'count'
      ,[GTH Performed Datetime]
FROM cte
WHERE [gth task name] in ('Task 1', 'Task 2', 'Task 3')
ORDER BY [GTH Performed Datetime]

原代码中试图用[case ref] = [case ref 2]限制同Case统计，但未生效，需要修复。

修复后的SQL代码 #

WITH task_groups AS (
    SELECT 
        [Case Ref],
        [GTH Task Name],
        [GTH Performed Datetime],
        -- 为每个Case下的非Success任务分组，Success继承前一个非Success的组ID
        SUM(CASE WHEN [GTH Task Name] IN ('Task 1', 'Task 2', 'Task 3') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY [Case Ref] ORDER BY [GTH Performed Datetime]) AS group_id
    FROM [Table]
    WHERE [GTH Task Name] IN ('Task 1', 'Task 2', 'Task 3', 'Success')
),
success_counts AS (
    SELECT 
        [Case Ref],
        group_id,
        COUNT(CASE WHEN [GTH Task Name] = 'Success' THEN 1 END) AS success_num
    FROM task_groups
    GROUP BY [Case Ref], group_id
)
SELECT 
    t.[Case Ref],
    t.[GTH Task Name],
    t.[GTH Performed Datetime],
    COALESCE(s.success_num, 0) AS [Number of Successes]
FROM task_groups t
LEFT JOIN success_counts s 
    ON t.[Case Ref] = s.[Case Ref] 
    AND t.group_id = s.group_id
WHERE t.[GTH Task Name] IN ('Task 1', 'Task 2', 'Task 3')
ORDER BY t.[GTH Performed Datetime];

修复说明 #

1. 原代码核心问题：

   • 窗口函数未按Case Ref分区，导致跨Case统计数据；
   • [case ref] = [case ref 2]完全无效，因为两个字段是同一行的同一个值，无法起到过滤作用。

2. 修复逻辑：

   • task_groupsCTE：按Case Ref分区，为每个非Success任务分配递增的组ID，后续的Success会继承这个组ID，确保同Case下一个任务到下一个任务之间的所有Success被归为同一组；
   • success_countsCTE：按Case Ref和group_id分组，统计每组内的Success数量；
   • 最后关联任务行和统计结果，得到每个任务对应的Success数量，严格限制在同一Case内计算。

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