在Databricks中使用SQL合并连续时间段的技术求助

需求说明 #

当某行的end_date是下一行start_date的前一天时，将这些连续的时间段合并为一行，保留该连续段最早的start_date和最晚的end_date。

演示数据集 #

期望输出 #

解决方案 #

单独使用LAG仅能判断相邻两行的连续性，无法覆盖多行连续的场景。可以通过标记分组边界+分组聚合的方式解决：

完整SQL代码 #

WITH tagged_data AS (
    SELECT
        id,
        start_date,
        end_date,
        -- 标记分组边界：当前行与上一行不连续时记为1，否则0
        CASE
            WHEN start_date = LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) + INTERVAL 1 DAY
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM your_table_name
),
grouped_data AS (
    SELECT
        id,
        start_date,
        end_date,
        -- 累积求和生成分组ID，同一连续时间段的行将获得相同ID
        SUM(is_new_group) OVER (PARTITION BY id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM tagged_data
)
SELECT
    ROW_NUMBER() OVER (ORDER BY group_id) AS row,
    id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM grouped_data
GROUP BY id, group_id
ORDER BY group_id;

逻辑说明 #

1. 标记分组边界：在tagged_data中，通过LAG窗口函数对比当前行与上一行的日期，判断是否为新分组的起点。
2. 生成分组ID：在grouped_data中，对标记的边界值进行累积求和，将同一连续时间段的行归为同一分组。
3. 聚合结果：按id和分组ID聚合，取每个分组的最早开始日期和最晚结束日期，最后生成新的行号。

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