使用Lead窗口函数时排序异常导致标记功能失效的问题

需求说明 #

• 为同一日期内状态先为Approved、后变为Terminated的保单记录标记Flag=1，其余记录Flag=0
• 符合条件示例：ClaimID=123、333；不符合示例：ClaimID=234、444

现有数据集 #

期望输出 #

测试表创建代码 #

declare @t table (
    claimid int,
    status varchar(100),
    completedate datetime
);

insert into @t (claimid, status, completedate)
values
(123, 'Terminated', '12/12/2024 01:20'),    
(123, 'Approved', '12/12/2024 01:19' ),
(123, 'Approved', '12/10/2024 02:00'),
(123, 'Cancelled', '12/23/2024 02:01'),    
(234, 'Cancelled', '03/25/2024 03:00'),
(234, 'Approved', '03/24/2024 02:20'),    
(234, 'Terminated', '03/25/2024 02:22'),
(333, 'Approved', '04/01/2024 03:00'),
(333, 'Terminated', '04/01/2024 02:40'),
(444, 'Terminated', '04/02/2024 03:00'),
(444, 'Approved', '04/02/2024 03:02');
  
select *       
from @t;

尝试的错误查询 #

原查询的问题在于分区时用了完整的datetime字段，导致同一日期但不同时间的记录被错误拆分，同时排序逻辑未覆盖所有场景：

select *,
    case when (status = 'Approved' and lead(status, 1) over (partition by claimid, completedate 
    order by completedate ASC) = 'Terminated') then 1 else 0 end as Flag
from #test

解决方案 #

核心逻辑是先按ClaimID和日期部分分组，判断该日期内是否存在先Approved后Terminated的序列，再将标记应用到对应记录上：

with cte_status_check as (
    select 
        *,
        cast(completedate as date) as claim_date,
        -- 检查当前记录之后是否有Terminated状态
        max(case when status = 'Terminated' then 1 else 0 end) over (
            partition by claimid, cast(completedate as date) 
            order by completedate 
            rows between current row and unbounded following
        ) as has_later_terminated,
        -- 检查当前记录之前是否有Approved状态
        max(case when status = 'Approved' then 1 else 0 end) over (
            partition by claimid, cast(completedate as date) 
            order by completedate 
            rows between unbounded preceding and current row
        ) as has_earlier_approved
    from @t
),
cte_date_flag as (
    select 
        claimid,
        claim_date,
        -- 标记该日期是否存在符合条件的状态序列
        max(case 
            when has_earlier_approved = 1 and has_later_terminated = 1 
                 and status in ('Approved', 'Terminated') then 1 
            else 0 
        end) as date_has_valid_sequence
    from cte_status_check
    group by claimid, claim_date
)
select 
    t.claimid,
    t.status,
    cast(t.completedate as date) as completedate,
    case 
        when df.date_has_valid_sequence = 1 and t.status in ('Approved', 'Terminated') then 1
        else 0
    end as flag
from @t t
left join cte_date_flag df 
    on t.claimid = df.claimid 
    and cast(t.completedate as date) = df.claim_date
order by t.claimid, cast(t.completedate as date), t.completedate;

代码说明 #

1. cte_status_check：为每条记录计算所在日期的后续是否有Terminated、前面是否有Approved
2. cte_date_flag：按ClaimID和日期分组，标记该日期是否存在符合条件的状态流转
3. 最终查询：关联日期标记，对符合条件的Approved和Terminated记录设置Flag=1，其余为0

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