SQL中如何基于滚动有效日期筛选间隔超过14天的日期

当然可以搞定这个需求！这种需要动态更新比较基准的场景，递归CTE（公共表表达式）是绝佳的选择——它能一步步迭代处理每个日期，同时跟踪当前的"最后有效日期"，完全匹配你的需求。

先明确核心逻辑：

• 必须严格按照你给定的顺序处理日期（不能按日期本身的升/降序）
• 初始有效日期是第一个日期2020-09-24
• 后续每个日期如果和当前有效日期的间隔超过14天（不管是早14天还是晚14天），就保留它并更新有效日期；否则直接跳过

具体SQL实现（以SQL Server为例） #

-- 第一步：给原始日期分配固定顺序的行号（确保处理顺序和你给定的一致）
WITH numbered_dates AS (
    SELECT 
        date_val = CAST(date_str AS DATE),
        rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  -- 如果有实际的顺序字段（比如导入序号），替换这里的排序逻辑
    FROM (
        VALUES 
            ('2020-09-24'), ('2020-09-22'), ('2020-09-23'), 
            ('2020-09-21'), ('2020-09-17'), ('2020-09-18'), 
            ('2020-09-16'), ('2020-09-28'), ('2020-09-25'), 
            ('2009-05-13'), ('2008-10-24'), ('2009-05-23')
    ) AS t(date_str)
),
-- 第二步：递归迭代，跟踪有效日期
recursive_filter AS (
    -- 锚点：第一个日期作为初始有效日期
    SELECT 
        rn,
        valid_date = date_val,
        current_date = date_val
    FROM numbered_dates
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续每个日期
    SELECT 
        nd.rn,
        -- 计算日期间隔，超过14天则更新有效日期，否则沿用之前的
        valid_date = CASE 
            WHEN ABS(DATEDIFF(DAY, rf.valid_date, nd.date_val)) > 14
            THEN nd.date_val
            ELSE rf.valid_date
        END,
        nd.current_date = nd.date_val
    FROM recursive_filter rf
    JOIN numbered_dates nd ON nd.rn = rf.rn + 1
)
-- 第三步：提取所有被选中的有效日期（去重后就是最终结果）
SELECT DISTINCT valid_date
FROM recursive_filter
ORDER BY valid_date DESC;  -- 若要按原始顺序输出，可改为ORDER BY rn

关键细节说明 #

1. 行号的重要性：SQL中的表本身是无序的，所以必须给每个日期分配行号来固定处理顺序。如果你的数据来自已有表，且有一个能代表输入顺序的字段（比如自增ID、导入时间戳），一定要用那个字段替换ORDER BY (SELECT NULL)，保证顺序完全符合你的要求。
2. 日期间隔计算：用ABS(DATEDIFF(...))确保不管日期是早于还是晚于当前有效日期，只要间隔超过14天就触发更新。不同数据库的日期函数略有差异：
   • MySQL：ABS(DATEDIFF(nd.date_val, rf.valid_date)) > 14
   • PostgreSQL：ABS(DATE_PART('day', rf.valid_date - nd.date_val)) > 14
3. 递归逻辑：每次迭代只处理下一个日期，动态更新有效日期，最后通过DISTINCT提取所有变更过的有效日期，就是你需要的结果。

运行这段代码后，得到的结果正好是你预期的：2020-09-24、2009-05-13、2008-10-24。

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