如何在分组中筛选无日期重叠的首条及符合条件记录？

你的需求核心是动态跟踪最近一条保留记录的日期范围，普通的LAG()函数只能获取固定偏移的上一行数据，无法动态回溯到最近的保留行，所以需要用递归CTE（公共表表达式）来处理。

具体实现步骤 #

假设你的表名为your_table，以下是适用于多数SQL数据库（如SQL Server、PostgreSQL）的解决方案：

1. 给数据添加行号：确保每个Id分组内的行按First_Date顺序排列，方便递归逐行处理。
2. 递归CTE分两部分：
   • 起始成员：取每个Id的第一行，标记为Keep，并记录其Second_Date作为初始判断基准。
   • 递归成员：逐行处理后续数据，判断当前行的First_Date是否不在上一条保留记录的日期开区间(First_Date, Second_Date)内——由于数据已按First_Date排序，只需判断是否**大于等于上一条保留行的Second_Date**即可。如果符合条件则标记为Keep并更新基准，否则标记为Exclude并沿用原基准。

完整SQL代码 #

WITH numbered_data AS (
    SELECT 
        Id, First_Date, Second_Date,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY First_Date) AS rn
    FROM your_table
),
recursive_filter AS (
    -- 起始：保留首行
    SELECT 
        Id, First_Date, Second_Date, rn,
        'Keep' AS Flag,
        Second_Date AS current_base_second
    FROM numbered_data
    WHERE rn = 1

    UNION ALL

    -- 递归：逐行判断并更新基准
    SELECT 
        nd.Id, nd.First_Date, nd.Second_Date, nd.rn,
        CASE 
            WHEN nd.First_Date >= r.current_base_second THEN 'Keep'
            ELSE 'Exclude'
        END AS Flag,
        CASE 
            WHEN nd.First_Date >= r.current_base_second THEN nd.Second_Date
            ELSE r.current_base_second
        END AS current_base_second
    FROM numbered_data nd
    INNER JOIN recursive_filter r 
        ON nd.Id = r.Id AND nd.rn = r.rn + 1
)
-- 输出最终结果
SELECT Id, First_Date, Second_Date, Flag
FROM recursive_filter
ORDER BY rn;

逻辑验证 #

针对你的示例数据，执行后会得到完全符合预期的结果：

• 首行直接保留，基准设为2022-08-16
• 第2-4行的First_Date都在(2022-05-18, 2022-08-16)区间内，标记为Exclude，基准不变
• 第5行的First_Date等于基准日期2022-08-16，标记为Keep，基准更新为2022-11-23
• 第6行的First_Date在(2022-08-16, 2022-11-23)区间内，标记为Exclude

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