如何查找与最新日期无间隙（最大允差30天）的最早日期范围

现有表情况 #

DateRanges表的结构和数据如下：

要实现的需求 #

1. 把所有间隔不超过30天的日期范围合并成连续的大区间，最终要得到这样的结果：

2. 同时要找出和最新的那个日期区间（也就是2013-01-01到2014-12-31）属于同一无间隙组的最早起始日期（也就是2005-01-01）

之前遇到的问题 #

之前试过用自连接判断日期重叠来合并，但只要连续的无间隙区间超过3个，这个方法就不管用了，想过用递归，但不确定SQL支不支持。

可行的解决方案 #

方案一：递归CTE（支持的SQL：SQL Server、PostgreSQL、MySQL 8.0+等） #

递归CTE能处理任意数量的连续无间隙区间，具体代码如下：

WITH OrderedRanges AS (
    -- 先按起始日期给每个区间排个号
    SELECT 
        Id, FromDate, ToDate,
        ROW_NUMBER() OVER (ORDER BY FromDate) AS rn
    FROM DateRanges
),
RecursiveGroups AS (
    -- 从第一个区间开始，作为初始组
    SELECT 
        rn,
        FromDate AS GroupFrom,
        ToDate AS GroupTo
    FROM OrderedRanges
    WHERE rn = 1

    UNION ALL

    -- 逐个往后判断：如果当前区间和上一组的结束日间隔≤30天，就合并到上一组；否则新建组
    SELECT 
        o.rn,
        CASE WHEN DATEDIFF(day, rg.GroupTo, o.FromDate) <= 30 THEN rg.GroupFrom ELSE o.FromDate END,
        CASE WHEN DATEDIFF(day, rg.GroupTo, o.FromDate) <= 30 THEN GREATEST(rg.GroupTo, o.ToDate) ELSE o.ToDate END
    FROM OrderedRanges o
    JOIN RecursiveGroups rg ON o.rn = rg.rn + 1
),
FinalGroups AS (
    -- 把每个组的起始日和最终结束日提取出来
    SELECT 
        GroupFrom,
        MAX(GroupTo) AS GroupTo
    FROM RecursiveGroups
    GROUP BY GroupFrom
)
-- 输出合并后的所有区间，同时标记出和最新区间同组的最早起始日
SELECT 
    GroupFrom AS FromDate,
    GroupTo AS ToDate,
    CASE WHEN GroupTo = (SELECT MAX(ToDate) FROM DateRanges) THEN '是' ELSE '否' END AS 是否关联最新日期,
    CASE WHEN GroupTo = (SELECT MAX(ToDate) FROM DateRanges) THEN GroupFrom ELSE NULL END AS 关联最新日期的最早FromDate
FROM FinalGroups;

方案二：非递归窗口函数法（兼容更多SQL方言） #

如果你的SQL不支持递归，用窗口函数标记组的起始点也能实现：

WITH MarkedGroups AS (
    SELECT 
        FromDate, ToDate,
        -- 标记新组：如果当前区间和前一个的结束日间隔超过30天，或者是第一个区间，就算新组起点
        CASE WHEN DATEDIFF(day, LAG(ToDate) OVER (ORDER BY FromDate), FromDate) > 30 OR LAG(ToDate) OVER (ORDER BY FromDate) IS NULL THEN 1 ELSE 0 END AS IsNewGroup
    FROM DateRanges
),
GroupedRanges AS (
    -- 累加新组标记，得到每个区间的组ID
    SELECT 
        FromDate, ToDate,
        SUM(IsNewGroup) OVER (ORDER BY FromDate) AS GroupId
    FROM MarkedGroups
)
-- 按组ID合并区间
SELECT 
    MIN(FromDate) AS FromDate,
    MAX(ToDate) AS ToDate
FROM GroupedRanges
GROUP BY GroupId;

-- 单独提取关联最新日期的最早起始日
SELECT MIN(FromDate) AS 关联最新日期的最早FromDate
FROM GroupedRanges
WHERE GroupId = (
    SELECT GroupId 
    FROM GroupedRanges
    WHERE ToDate = (SELECT MAX(ToDate) FROM DateRanges)
);

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