如何在Oracle SQL中编写迭代查询实现分组日期间隔筛选

需求回顾 #

• 按party_id分组，对data_date升序排序
• 每组必须保留首个data_date（即该组最小日期）
• 后续保留的data_date需与上一个选中日期间隔至少30天（例如2023-04-04之后，2023-05-04及以后符合要求）

解决方案：递归CTE实现 #

可以通过Oracle的递归公共表表达式（CTE）一步完成这个需求，核心逻辑是迭代筛选符合间隔要求的日期：

WITH date_selection AS (
    -- 锚点成员：获取每个party_id的首个日期（分组后最小的data_date）
    SELECT 
        party_id,
        data_date AS selected_date,
        data_date AS last_selected_date
    FROM (
        SELECT 
            party_id,
            data_date,
            ROW_NUMBER() OVER (PARTITION BY party_id ORDER BY data_date) AS rn
        FROM your_table  -- 替换为你的实际表名
    ) t
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归成员：迭代寻找下一个符合间隔要求的最早日期
    SELECT 
        t.party_id,
        t.data_date AS selected_date,
        t.data_date AS last_selected_date
    FROM your_table t
    JOIN date_selection ds 
        ON t.party_id = ds.party_id
        AND t.data_date > ds.last_selected_date + INTERVAL '30' DAY
    WHERE NOT EXISTS (
        -- 确保当前选中的是符合条件的最早日期，避免跳过更早的合格日期
        SELECT 1
        FROM your_table t2
        WHERE t2.party_id = t.party_id
          AND t2.data_date > ds.last_selected_date + INTERVAL '30' DAY
          AND t2.data_date < t.data_date
    )
)
SELECT party_id, selected_date
FROM date_selection
ORDER BY party_id, selected_date;

代码逻辑说明 #

1. 锚点成员：通过ROW_NUMBER()窗口函数对每个party_id的data_date升序排序，取每组第一条记录（即首个日期）作为初始选中值。
2. 递归成员：
   • 自连接递归CTE，匹配同一party_id且日期晚于上一个选中日期+30天的记录
   • 用NOT EXISTS子查询确保选中的是当前符合条件的最早日期，避免遗漏更早的合格日期
3. 最终查询：输出所有选中的日期，按party_id和selected_date排序

示例验证 #

针对party_id=12345的数据集：2023-01-01、2023-02-04、2023-02-05、2023-03-30、2023-03-31、2023-04-04

• 锚点选中2023-01-01
• 递归第一次筛选：找到2023-01-01+30天（2023-01-31）之后的最早日期2023-02-04
• 递归第二次筛选：找到2023-02-04+30天（2023-03-06）之后的最早日期2023-03-30
• 后续无符合2023-03-30+30天（2023-04-29）的日期，递归终止
  最终结果为2023-01-01、2023-02-04、2023-03-30，完全符合预期

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