T-SQL需求：筛选间隔超30天记录并动态重置基准日期

示例表结构及数据 #

需求逻辑 #

按会员分组，按访问日期排序，从首个日期开始，筛选出与当前基准日期间隔超过30天的记录；每次找到符合条件的记录后，将基准日期重置为该记录的日期，继续后续筛选。以MemberKey=34432为例，符合条件的记录依次为2022-03-22、2022-04-27、2022-08-10、2022-11-22。

原问题代码 #

用户提供的代码无法正确实现需求，原代码如下：

SELECT
    MemberKey
    ,group_id
    ,VisitStartDate
    ,CASE WHEN DATEDIFF(DD,LAG(VisitStartDate,1)OVER(PARTITION BY MemberKey,group_id ORDER BY VisitStartDate),VisitStartDate) >30 THEN VisitStartDate END AS NextVisitDate
FROM
    (
        SELECT 
             MemberKey
            ,group_id
            ,VisitStartDate
            ,FIRST_VALUE(VisitStartDate) OVER (PARTITION BY MemberKey, group_id ORDER BY VisitStartDate DESC) - FIRST_VALUE(VisitStartDate) OVER (PARTITION BY MemberKey, group_id ORDER BY VisitStartDate) AS diff
        FROM
            (
                SELECT
                    MemberKey
                    ,VisitStartDate
                    ,SUM(group_id) OVER (PARTITION BY MemberKey ORDER BY VisitStartDate) AS group_id
                FROM 
                    (
                        SELECT
                            MemberKey
                            ,VisitStartDate
                            ,CASE WHEN DATEDIFF(DD,LAG(VisitStartDate, 1, VisitStartDate) OVER (PARTITION BY MemberKey ORDER BY VisitStartDate),VisitStartDate) > 30 THEN 1 ELSE 0 END AS group_id
                        FROM
                            Table   
                        WHERE
                            MemberKey = 34432
                    ) S
            ) S
    ) A

修正后的代码 #

使用递归CTE可以高效处理10万+会员的批量需求，代码如下：

WITH RecursiveVisits AS (
    -- 锚点成员：获取每个会员的最早访问日期作为初始基准
    SELECT 
        MemberKey,
        VisitStartDate AS BaseDate,
        VisitStartDate AS CurrentVisitDate,
        1 AS Level
    FROM (
        SELECT 
            MemberKey,
            VisitStartDate,
            ROW_NUMBER() OVER (PARTITION BY MemberKey ORDER BY VisitStartDate) AS rn
        FROM TABLE
    ) t
    WHERE rn = 1

    UNION ALL

    -- 递归成员：找到下一个与当前基准日期间隔超过30天的最早访问日期
    SELECT 
        rv.MemberKey,
        v.VisitStartDate AS BaseDate,
        v.VisitStartDate AS CurrentVisitDate,
        rv.Level + 1 AS Level
    FROM RecursiveVisits rv
    CROSS APPLY (
        SELECT TOP 1 VisitStartDate
        FROM TABLE t
        WHERE t.MemberKey = rv.MemberKey
          AND t.VisitStartDate > rv.BaseDate
          AND DATEDIFF(DAY, rv.BaseDate, t.VisitStartDate) > 30
        ORDER BY VisitStartDate
    ) v
)
-- 筛选出除初始基准外的符合条件的记录
SELECT 
    MemberKey,
    CurrentVisitDate AS VisitStartDate
FROM RecursiveVisits
WHERE Level > 1
ORDER BY MemberKey, CurrentVisitDate;

代码说明 #

1. 锚点成员：通过ROW_NUMBER()获取每个会员的最早访问日期，作为筛选的初始基准。
2. 递归成员：每次从上一轮的基准日期出发，使用CROSS APPLY找到下一个间隔超过30天的最早访问日期，更新基准日期后继续递归。
3. 最终筛选：排除初始基准记录（Level=1），得到符合需求的所有记录。

该方案利用递归CTE的特性动态更新基准日期，同时通过TOP 1和排序确保每次找到的是下一个符合条件的最早日期，性能上能够支持10万+会员的批量处理。

内容的提问来源于stack exchange，提问作者Prabhu Shankar Sankaranarayana