同一列日期对比筛选：保留间隔≥3天的最新记录的SQL问题

执行以下SQL查询：

SELECT title, DATE(end) AS Regresso FROM raddb.AgendaSaidas WHERE title = '763';

返回数据：

# title, Regresso
'763', '2023-01-11'
'763', '2023-01-08'
'763', '2023-01-07'
'763', '2023-01-01'

需求：对比同一列内的日期，仅返回间隔至少3天的记录；若存在相邻间隔不足3天的记录，保留其中最新的那条。预期结果：

# title, Regresso
'763', '2023-01-11'
'763', '2023-01-08'
'763', '2023-01-01'

用户尝试的无效查询：

SELECT DISTINCT title, DATE(end) AS Regresso
FROM raddb.AgendaSaidas AS d1, raddb.AgendaSaidas AS d2 
WHERE d1.title = '763' AND d2.title = '763' AND DATE(d1.end) >= (DATE(d2.end) + INTERVAL 3 DAY)

原自连接查询无法精准筛选相邻短间隔记录并保留最新项，可使用窗口函数LAG()实现需求：

WITH ranked_dates AS (
    SELECT 
        title, 
        DATE(end) AS Regresso,
        LAG(DATE(end)) OVER (PARTITION BY title ORDER BY DATE(end) DESC) AS prev_regresso
    FROM raddb.AgendaSaidas
    WHERE title = '763'
)
SELECT title, Regresso
FROM ranked_dates
WHERE 
    prev_regresso IS NULL 
    OR DATEDIFF(Regresso, prev_regresso) <= -3;

逻辑说明 #

1. PARTITION BY title：限定仅在同一标题的分组内进行日期对比
2. ORDER BY DATE(end) DESC：按日期从新到旧排序，LAG()获取当前记录的上一条（更早的）日期
3. 筛选条件：
   • prev_regresso IS NULL：保留分组内最新的记录
   • DATEDIFF(Regresso, prev_regresso) <= -3：当前日期与上一条日期间隔至少3天（因降序排序，当前日期更早，差值为负），满足则保留；间隔不足3天的旧记录会被排除，只保留最新的那条短间隔记录

若习惯按升序排序，可调整为：

WITH ranked_dates AS (
    SELECT 
        title, 
        DATE(end) AS Regresso,
        LAG(DATE(end)) OVER (PARTITION BY title ORDER BY DATE(end) ASC) AS prev_regresso
    FROM raddb.AgendaSaidas
    WHERE title = '763'
)
SELECT title, Regresso
FROM ranked_dates
WHERE 
    prev_regresso IS NULL 
    OR DATEDIFF(Regresso, prev_regresso) >= 3;

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