PostgreSQL筛选间隔至少14天的连续日期数据

要实现这个需求，核心是对每个用户的日期排序后，只保留与上一个保留日期间隔≥14天的记录。PostgreSQL的递归CTE（公共表表达式）是最适合的方案，因为它能迭代判断每个日期是否符合条件，而不是仅和原始序列的前一个日期对比。

示例场景 #

假设你的表名为user_dates，结构和数据如下：

预期结果需要剔除间隔不足14天的记录，最终得到：

实现SQL代码 #

WITH sorted_dates AS (
    -- 先对每个用户的日期去重并排序，生成行号
    SELECT 
        userid,
        event_date,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY event_date) AS rn
    FROM (
        SELECT DISTINCT userid, event_date FROM user_dates
    ) AS unique_dates
),
recursive_filter AS (
    -- 锚点：取每个用户的第一个日期作为起始点
    SELECT userid, event_date, rn
    FROM sorted_dates
    WHERE rn = 1
    UNION ALL
    -- 递归遍历：只保留与上一个保留日期间隔≥14天的最早后续日期
    SELECT 
        sd.userid,
        sd.event_date,
        sd.rn
    FROM sorted_dates sd
    JOIN recursive_filter rf 
        ON sd.userid = rf.userid 
        AND sd.rn > rf.rn
    WHERE sd.event_date >= rf.event_date + INTERVAL '14 days'
    -- 确保只取符合条件的最早日期，避免重复匹配
    AND NOT EXISTS (
        SELECT 1
        FROM sorted_dates sd2
        WHERE sd2.userid = sd.userid
        AND sd2.rn > rf.rn
        AND sd2.rn < sd.rn
        AND sd2.event_date >= rf.event_date + INTERVAL '14 days'
    )
)
SELECT userid, event_date
FROM recursive_filter
ORDER BY userid, event_date;

代码说明 #

1. sorted_dates：先对原始表去重（避免同一用户有重复日期），再按userid分组、event_date排序，给每条记录生成行号rn，方便后续递归遍历。
2. recursive_filter：
   • 锚点部分：获取每个用户的第一条日期作为初始保留记录。
   • 递归部分：关联上一轮保留的记录，找到后续日期中与上一个保留日期间隔≥14天的记录，同时通过NOT EXISTS确保只取符合条件的最早日期，防止跳过中间可能的有效日期。
3. 最终查询：从递归结果中取出userid和event_date，按用户和日期排序输出。

特殊情况处理 #

• 如果你的表中没有重复日期，可以去掉内层的SELECT DISTINCT，直接从user_dates生成sorted_dates。
• 如果需要按自然日间隔计算（而非包含时间），确保event_date是date类型，若为timestamp类型，可以用sd.event_date::date >= rf.event_date::date + INTERVAL '14 days'转换后再判断。

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