在Spark中按ID分组合并连续日期区间，识别超1天间隔

问题背景 #

现有一张包含id、startdate、enddate字段的表，日期格式为MM/DD/YYYY，示例数据如下：

+---+-----------+----------+
| id|  startdate|   enddate|
+---+-----------+----------+
|  1| 01/01/2022|01/31/2022|  
|  1| 02/01/2022|02/28/2022|  
|  1| 03/01/2022|03/31/2022|  
|  2| 01/01/2022|03/01/2022|  
|  2| 03/05/2022|03/31/2022| 
|  2| 04/01/2022|04/05/2022|
+---+-----------+----------+

需求 #

按id分组，合并连续的日期区间（当前行startdate与上一行enddate间隔不超过1天则视为连续）；间隔超过1天则保留为独立行。期望输出：

+---+-----------+----------+
| id|  startdate|   enddate|
+---+-----------+----------+
|  1| 01/01/2022|03/31/2022|  
|  2| 01/01/2022|03/01/2022|  
|  2| 03/05/2022|04/05/2022|  
+---+-----------+----------+

注：id=1的所有区间连续，合并为一行；id=2中03/01/2022与03/05/2022间隔超过1天，分为两组。

解决方案（SQL实现） #

这类问题属于区间合并经典场景，可通过窗口函数标记分组后聚合得到结果，以下以Spark SQL为例（其他SQL方言可调整日期函数适配）：

步骤1：转换日期格式并标记新分组 #

先将字符串日期转为日期类型，用LAG()窗口函数获取上一行的enddate，判断是否连续并生成新分组标记：

WITH temp AS (
    SELECT 
        id,
        startdate,
        enddate,
        TO_DATE(startdate, 'MM/dd/yyyy') AS start_dt,
        TO_DATE(enddate, 'MM/dd/yyyy') AS end_dt,
        -- 当前行与上一行间隔超1天则标记为新分组
        CASE 
            WHEN DATEDIFF(TO_DATE(startdate, 'MM/dd/yyyy'), LAG(TO_DATE(enddate, 'MM/dd/yyyy')) OVER (PARTITION BY id ORDER BY start_dt)) > 1
            THEN 1
            ELSE 0
        END AS is_new_group
    FROM your_table_name
),

步骤2：生成分组ID #

通过累加新分组标记，将连续区间归为同一分组：

grouped AS (
    SELECT 
        id,
        startdate,
        enddate,
        SUM(is_new_group) OVER (PARTITION BY id ORDER BY start_dt) AS group_id
    FROM temp
)

步骤3：聚合得到合并结果 #

按id和group_id分组，取每组的最小startdate和最大enddate：

SELECT 
    id,
    MIN(startdate) AS startdate,
    MAX(enddate) AS enddate
FROM grouped
GROUP BY id, group_id
ORDER BY id, startdate;

适配说明 #

• 不同SQL方言的日期转换函数略有差异：MySQL用STR_TO_DATE(startdate, '%m/%d/%Y')，PostgreSQL用CAST(startdate AS DATE)，按需替换即可。
• DATEDIFF()函数用于计算天数差，部分方言可能用DATE_DIFF()，需对应调整。

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