如何合并多行连续日期范围并计算最长连续天数？

问题背景 #

数据表中存在多行日期范围记录，这些记录实际属于连续的日期区间，需要将它们合并为完整的连续区间，并计算最长的连续天数。尝试使用LAG()和LEAD()函数未成功，因为这两个函数仅能处理单行的前后数据，无法识别多段连续的区间。

示例数据 #

假设数据表结构及数据如下：

期望聚合结果 #

合并连续区间后，得到如下结果：

解决方案 #

核心思路是通过窗口函数生成连续区间的分组标识，再对分组进行聚合，突破LAG()/LEAD()只能处理相邻单行的限制。以下以MySQL为例，提供完整实现：

1. 标记连续区间分组 #

先按ID分组、StartDate排序，用LAG()获取上一条记录的EndDate，判断当前记录是否与上一段连续，通过累计求和生成分组ID：

WITH ranked_data AS (
    SELECT 
        ID,
        StartDate,
        EndDate,
        -- 当前日期段与上一段不连续时，生成新分组
        SUM(CASE WHEN StartDate = DATE_ADD(LAG(EndDate) OVER (PARTITION BY ID ORDER BY StartDate), INTERVAL 1 DAY) THEN 0 ELSE 1 END) 
        OVER (PARTITION BY ID ORDER BY StartDate) AS group_id
    FROM your_table_name
)

2. 聚合分组得到合并结果 #

基于分组ID，对每个ID和group_id聚合，计算合并后的起止日期和连续天数：

SELECT 
    ID,
    MIN(StartDate) AS MergeStart,
    MAX(EndDate) AS MergeEnd,
    DATEDIFF(MAX(EndDate), MIN(StartDate)) + 1 AS ContinuousDays
FROM ranked_data
GROUP BY ID, group_id
ORDER BY ID, MergeStart;

3. 计算每个ID的最长连续天数 #

如果需要单独统计每个ID的最长连续天数，可在上述结果基础上再次聚合：

WITH merged_intervals AS (
    WITH ranked_data AS (
        SELECT 
            ID,
            StartDate,
            EndDate,
            SUM(CASE WHEN StartDate = DATE_ADD(LAG(EndDate) OVER (PARTITION BY ID ORDER BY StartDate), INTERVAL 1 DAY) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY StartDate) AS group_id
        FROM your_table_name
    )
    SELECT 
        ID,
        MIN(StartDate) AS MergeStart,
        MAX(EndDate) AS MergeEnd,
        DATEDIFF(MAX(EndDate), MIN(StartDate)) + 1 AS ContinuousDays
    FROM ranked_data
    GROUP BY ID, group_id
)
SELECT 
    ID,
    MAX(ContinuousDays) AS MaxContinuousDays
FROM merged_intervals
GROUP BY ID;

注意事项 #

• 不同数据库的日期函数语法有差异：
  • PostgreSQL：用LAG(EndDate) + INTERVAL '1 day'替代DATE_ADD
  • SQL Server：用DATEADD(day, 1, LAG(EndDate) OVER(...))替代DATE_ADD
• 若日期区间存在重叠（而非单纯连续），可调整判断条件为StartDate <= DATE_ADD(LAG(EndDate) OVER(...), INTERVAL 1 DAY)，实现重叠区间的合并。

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