基于日期区间与上一行关联性的SQL分组实现问询

测试数据 #

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT, start_date DATE,  end_date DATE)
INSERT INTO
    #test
VALUES 
    (1, '2023-01-01', '2024-01-01'),
    (1, '2023-05-01', '2024-07-01'),
    (1, '2025-01-01', '2026-01-01');

SELECT * 
FROM #test;

需求说明 #

为每条记录生成整数分组编号：判断当前行日期区间是否与上一行关联，若不关联则分组编号递增，标记为独立分组。关联规则为当前行的start_date早于或等于上一行的end_date（区间重叠或衔接），满足则归为同一分组。

尝试的SQL #

WITH connects AS (
    SELECT
        id,
        CASE
            WHEN LAG(id, 1) OVER (PARTITION BY id ORDER BY start_date) = id AND (
                start_date <= LAG(end_date, 1) OVER (PARTITION BY id ORDER BY start_date)
                OR
                end_date <= LAG(end_date, 1) OVER (PARTITION BY id ORDER BY start_date)
            ) THEN 1
            WHEN LAG(id, 1) OVER (PARTITION BY id ORDER BY start_date) IS NULL THEN 1
            ELSE 0
        END AS connects_flag
    FROM
        #test
)
SELECT
    *
FROM
    connects;

期望结果 #

正确解法 #

你当前的SQL仅生成了关联标记，还需基于标记计算累计和得到分组编号。同时可简化关联条件（已按id分区、start_date排序，无需重复判断id），修正后的SQL如下：

WITH ranked_data AS (
    SELECT
        id,
        start_date,
        end_date,
        -- 标记当前行是否属于新分组：无前置行/与前置行关联则为0，否则为1
        CASE 
            WHEN LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) IS NULL THEN 0
            WHEN start_date <= LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) THEN 0
            ELSE 1
        END AS new_grp_flag
    FROM #test
),
grouped_data AS (
    SELECT
        *,
        -- 累计求和生成分组编号，+1保证从1开始计数
        SUM(new_grp_flag) OVER (PARTITION BY id ORDER BY start_date) + 1 AS grp
    FROM ranked_data
)
SELECT id, start_date, end_date, grp
FROM grouped_data;

逻辑说明 #

1. ranked_data CTE：为每条记录标记是否开启新分组。第一条记录无前置行，标记为0；当前行与前置行区间关联，标记为0；不关联则标记为1。
2. grouped_data CTE：对new_grp_flag做分区累计求和，再加1得到最终分组编号，确保分组从1开始连续递增。

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