SQL实现同一ID下连续日期区间的识别与合并

我来帮你搞定这个连续日期区间合并的问题～你之前尝试的自连接分组思路没有考虑到连续区间的逐行判断逻辑，所以没办法准确划分出连续的组。这里我们可以用窗口函数来实现需求，这是处理这类连续区间问题的经典方法。

核心思路 #

我们需要给每个连续的日期区间分配一个唯一的组ID，然后按id1和组ID聚合，取每组的最小起始日期和最大结束日期。具体分三步：

1. 按id1分组、st排序，用LAG窗口函数获取上一条记录的结束日期，判断当前记录的起始日期是否是上一条结束日期的次日，标记是否为新组。
2. 对新组标记做累计求和，生成组ID（连续的区间会得到同一个组ID）。
3. 按id1和组ID分组，聚合得到每组的最小st和最大endt。

完整SQL代码 #

SELECT 
    id1,
    MIN(st) AS st,
    MAX(endt) AS endt
FROM (
    SELECT 
        id1,
        st,
        endt,
        -- 累计求和生成组ID：连续的区间会共享同一个组ID
        SUM(is_new_group) OVER (PARTITION BY id1 ORDER BY st) AS group_id
    FROM (
        SELECT 
            id1,
            st,
            endt,
            -- 判断当前记录是否是新组的开始：如果当前st是上一条endt的次日，则不是新组（标记0），否则是新组（标记1）
            CASE WHEN st = LAG(endt) OVER (PARTITION BY id1 ORDER BY st) + INTERVAL '1 day' 
                 THEN 0 
                 ELSE 1 
            END AS is_new_group
        FROM c
    ) t1
) t2
GROUP BY id1, group_id
ORDER BY id1, st;

代码解释 #

1. 内层子查询t1：给每条记录添加is_new_group标记。LAG(endt) OVER (PARTITION BY id1 ORDER BY st)会获取当前id1下，上一条按st排序的记录的endt。如果当前记录的st正好是上一条endt的次日，说明属于同一个连续区间，标记为0；否则标记为1（新组开始）。
2. 中间子查询t2：对is_new_group做累计求和，生成group_id。因为连续区间的is_new_group都是0，累计求和后组ID不会变化；遇到新组（标记1）时，组ID会递增，这样就把所有连续的区间分到了同一个组。
3. 外层查询：按id1和group_id分组，取每组的最小st（区间起始）和最大endt（区间结束），得到最终合并后的结果。

验证结果 #

执行这段SQL后，输出会完全符合你的期望：

id1          st          endt
1700171048  2020-12-21  2021-01-03
1700171048  2021-01-05  2021-01-19
1700171048  2021-01-22  2021-02-17
1700171049  2020-12-21  2021-02-17

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