如何用SQL筛选与基准记录间隔≥30天的目标记录？

需求说明 #

需要为每个PersonId筛选记录：

• 以该用户的第一条记录为起始基准
• 依次对比后续记录，找到与当前基准间隔≥30天的记录后，将该记录设为新基准继续对比
• 最终保留所有这些基准记录（包含第一条）

原使用LAG()函数的方案仅能与上一条记录对比，会误删那些与初始基准间隔≥30天，但与上一条记录间隔不足30天的记录，因此需要调整实现逻辑。

测试数据 #

declare @Payment table 
(
    PersonId int
    , fromdate date
    , todate date
)
insert @Payment 
(PersonId, fromdate , todate )
values (1, '07/05/2014' , '07/06/2014' )
,(1, '07/15/2014' , '07/16/2014' )
,(1, '09/23/2014' , '09/24/2014')
,(1, '10/15/2014' , '10/16/2014')
,(1, '10/30/2014' , '10/31/2014')
,(2,  '07/20/2014' ,  '07/25/2014')
,(2, '08/20/2014' , '08/21/2014')
,(2, '09/21/2014' , '09/22/2014')

原错误尝试方案 #

;with cte as
(select PersonId , fromdate , todate
       ,datediff(d
                ,  lag(todate) over (partition by PersonId order by fromdate)
                , fromdate)DaysSinceLastindex
 from @Payment )
select  PersonId , fromdate , todate , DaysSinceLastindex
from cte
where DaysSinceLastindex >= 30 or DaysSinceLastindex is null

该方案会漏掉PersonId=1的10/30/2014记录（它与上一条间隔14天，但与基准09/23/2014间隔37天，符合需求）。

正确实现方案：递归CTE #

利用递归CTE可以动态更新基准，逐步筛选符合条件的记录：

;with RecursiveCTE as (
    -- 锚点成员：取每个PersonId的第一条记录作为初始基准
    select 
        PersonId,
        fromdate,
        todate,
        todate as CurrentBaseDate
    from (
        select *,
               row_number() over (partition by PersonId order by fromdate) as rn
        from @Payment
    ) t
    where rn = 1

    union all

    -- 递归成员：找到比当前基准间隔≥30天的最早记录，更新基准
    select 
        p.PersonId,
        p.fromdate,
        p.todate,
        p.todate as CurrentBaseDate
    from RecursiveCTE r
    join @Payment p on p.PersonId = r.PersonId
    where p.fromdate >= dateadd(day, 30, r.CurrentBaseDate)
    -- 确保取符合条件的最早记录，避免重复或跳过
    and not exists (
        select 1 from @Payment p2
        where p2.PersonId = p.PersonId
        and p2.fromdate >= dateadd(day, 30, r.CurrentBaseDate)
        and p2.fromdate < p.fromdate
    )
)
select PersonId, fromdate, todate
from RecursiveCTE
order by PersonId, fromdate;

逻辑说明 #

1. 锚点成员：通过ROW_NUMBER()为每个PersonId的记录排序，取第一条作为初始基准，同时记录基准日期为该记录的todate。
2. 递归成员：关联原表，筛选出比当前基准日期晚≥30天的记录，并且通过NOT EXISTS确保取到的是符合条件的最早记录，将这条记录作为新的基准继续递归查询。
3. 最终结果按PersonId和fromdate排序，得到所有符合需求的基准记录。

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