如何标记与已识别记录间隔≥7天的SQL数据行？

测试表创建语句 #

drop table #tmp1
create table #tmp1(
membernum int,
sevrfromdate date
)

insert into #tmp1
values (1000016, '4/13/2022')
insert into #tmp1
values (1000016, '4/15/2022')
insert into #tmp1
values (1000016, '4/20/2022')
insert into #tmp1
values (1000016, '4/22/2022')
insert into #tmp1
values (1000016, '4/23/2022')
insert into #tmp1
values (1000016, '4/28/2022')

问题核心 #

你之前用的lag()函数只能取原始数据里紧邻的上一条记录日期，但需求是和上一条被标记为有效的记录间隔≥7天，普通窗口函数没法追踪这个动态的“上一条有效记录”，得用递归CTE来实现。

正确SQL实现 #

WITH ranked_data AS (
    -- 按会员分组，给日期排序生成行号，方便递归逐行处理
    SELECT 
        membernum,
        sevrfromdate,
        ROW_NUMBER() OVER (PARTITION BY membernum ORDER BY sevrfromdate) AS rn
    FROM #tmp1
),
recursive_selection AS (
    -- 锚点：每个会员的第一条记录直接标记为有效
    SELECT 
        membernum,
        sevrfromdate,
        rn,
        CAST(1 AS BIT) AS is_valid,
        sevrfromdate AS last_valid_date
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：对比当前日期与上一条有效记录的间隔
    SELECT 
        rd.membernum,
        rd.sevrfromdate,
        rd.rn,
        -- 间隔≥7天则标记为有效，否则无效
        CASE WHEN DATEDIFF(day, rs.last_valid_date, rd.sevrfromdate) >= 7 THEN 1 ELSE 0 END AS is_valid,
        -- 如果当前有效，更新上一条有效日期为当前日期，否则沿用之前的
        CASE WHEN DATEDIFF(day, rs.last_valid_date, rd.sevrfromdate) >= 7 THEN rd.sevrfromdate ELSE rs.last_valid_date END AS last_valid_date
    FROM ranked_data rd
    JOIN recursive_selection rs ON rd.membernum = rs.membernum AND rd.rn = rs.rn + 1
)
-- 输出最终结果，保留需要的字段
SELECT 
    membernum,
    sevrfromdate,
    is_valid
FROM recursive_selection
ORDER BY membernum, sevrfromdate;

结果说明 #

运行后得到的结果完全符合需求，有效行和示例一致：

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