SQL连续日期区间合并：从空结束日期行向上合并

我来帮你搞定这个连续区间合并的问题！你遇到的核心难点是从END_DTE为NULL的行向上回溯合并连续区间，普通的ROW_NUMBER或者CTE分组没抓住反向回溯的逻辑，我给你一步步拆解解决方案：

你需要处理的是带有时间区间的数据集，其中部分行的START_DTE和上一行的END_DTE完全相等（无间隔连续），要求从END_DTE为NULL的行开始，向上合并所有连续的区间，直到遇到START_DTE和上一行END_DTE不相等的行，最终将连续区间合并为单个区间。

先定义一个符合你场景的示例表和数据：

CREATE TABLE intervals (
    id INT,
    START_DTE DATE,
    END_DTE DATE
);

INSERT INTO intervals VALUES
(1, '2023-01-01', '2023-01-10'),
(2, '2023-01-10', '2023-01-20'),
(3, '2023-01-20', NULL),
(4, '2023-02-01', '2023-02-10'),
(5, '2023-02-10', NULL),
(6, '2023-03-01', '2023-03-15');

合并后应该得到以下结果，连续的区间被合并为单个，END_DTE为NULL的行作为合并区间的终点：

你之前尝试的ROW_NUMBER()或普通CTE没成功，加HAVING COUNT(*)>1没结果，大概率是因为分组逻辑没正确把“从NULL行向上的连续行”归为同一组，导致每个组只有1行，过滤后自然没有数据。

我们需要用窗口函数标记分组边界，再反向累积分组，最终聚合得到合并后的区间：

WITH marked_intervals AS (
    SELECT 
        *,
        -- 标记新分组的起点：当前行和上一行不连续，或者是第一行
        CASE 
            WHEN LAG(END_DTE) OVER (ORDER BY id) IS NULL THEN 1
            WHEN START_DTE != LAG(END_DTE) OVER (ORDER BY id) THEN 1
            ELSE 0
        END AS is_new_group,
        -- 标记终止行（END_DTE为NULL的行）
        CASE WHEN END_DTE IS NULL THEN 1 ELSE 0 END AS is_terminator
    FROM intervals
),
grouped_intervals AS (
    SELECT 
        *,
        -- 反向累积分组：从后往前数，遇到新起点就生成新组号
        SUM(is_new_group) OVER (ORDER BY id DESC) AS group_id
    FROM marked_intervals
)
SELECT 
    MIN(id) AS original_start_id,
    MIN(START_DTE) AS merged_START_DTE,
    MAX(END_DTE) AS merged_END_DTE -- 终止行是NULL，MAX会保留NULL
FROM grouped_intervals
-- 保留两类数据：包含终止行的组，以及单独的非连续行
WHERE group_id IN (SELECT DISTINCT group_id FROM grouped_intervals WHERE is_terminator = 1)
   OR (is_terminator = 0 AND is_new_group = 1)
GROUP BY group_id
ORDER BY merged_START_DTE;

逻辑拆解 #

1. 标记阶段（marked_intervals）：
   • 用LAG()函数获取上一行的END_DTE，和当前行的START_DTE对比，标记出不连续的新分组起点。
   • 同时标记所有END_DTE为NULL的终止行，这是我们回溯合并的起点。
2. 分组阶段（grouped_intervals）：
   • 按id倒序累积is_new_group的和，这样从终止行向上的所有连续行都会被分到同一个group_id里。
3. 聚合阶段：
   • 对每个group_id聚合，取最小的START_DTE作为合并区间的起点，最大的END_DTE作为终点（因为终止行是NULL，MAX函数会保留这个NULL值）。
   • 过滤条件确保我们只保留需要合并的连续组，以及那些没有后续NULL行的单独区间。

• 如果你的数据集是按日期排序而非id，只需要把ORDER BY id改成ORDER BY START_DTE即可。
• 如果有多个相同的group_id（比如多个不连续的终止行），这个逻辑都会正确分组合并。

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