如何在PostgreSQL中按条件迭代合并分组内的数据行？

需求说明 #

原始表结构与数据 #

合并规则 #

按ID分组单独处理：若第i+1行的start_dt与第i行的end_dt间隔小于9个月，则合并两行，新行的start_dt为第i行的start_dt，end_dt为第i+1行的end_dt；连续多行符合条件时需全部合并为一行。

目标表结果 #

现有方案的问题 #

你尝试的SQL代码仅能处理两两相邻行的合并，无法覆盖多行连续符合条件的场景。比如以下扩展测试案例：

按规则，i+4和i+5行应合并，且如果后续还有符合条件的行也需连续合并，原有方案无法实现这类场景。

正确实现方案 #

核心思路是通过生成连续合并组的标识，将所有需要合并的行归为同一组，再按组聚合得到结果：

-- 建表与插入测试数据（包含扩展案例）
create table test (
    start_dt date,
    end_dt date,
    id varchar(10)
);

insert into test(start_dt, end_dt, id)
values 
    ('2023-01-01', '2023-03-08', 'A'),
    ('2023-05-08', '2023-06-18', 'A'),
    ('2023-12-09', '2024-02-02', 'A'),
    ('2024-12-01', '2025-01-05', 'A'),
    ('2025-11-01', '2025-12-07', 'A'),
    ('2026-01-08', '2026-07-18', 'A'),
    ('2023-02-01', '2023-03-07', 'B'),
    ('2023-06-08', '2023-07-18', 'B'),
    ('2024-12-09', '2024-02-02', 'B');

-- 合并逻辑实现
with grouped_data as (
    select 
        *,
        -- 标记当前行是否为新组的起始：若与上一行间隔>=9个月，或为组内第一行，则标记为1
        case 
            when lag(end_dt) over(partition by id order by start_dt) is null 
                 or start_dt - lag(end_dt) over(partition by id order by start_dt) >= interval '9 months'
            then 1 
            else 0 
        end as is_new_group
    from test
),
group_ids as (
    select 
        *,
        -- 累加标记生成组ID，连续可合并的行会拥有相同的组ID
        sum(is_new_group) over(partition by id order by start_dt) as group_id
    from grouped_data
)
-- 按ID和组ID聚合，取每组的最小start_dt和最大end_dt
select 
    id,
    min(start_dt) as start_dt,
    max(end_dt) as end_dt
from group_ids
group by id, group_id
order by id, start_dt;

代码说明 #

1. grouped_data CTE：按ID分组、start_dt排序，判断当前行是否需要开启新组——如果是组内第一行，或者当前行的start_dt与上一行的end_dt间隔≥9个月，则标记为新组起始。
2. group_ids CTE：通过累加is_new_group的值生成组ID，这样所有连续可合并的行都会被分配到同一个组ID下。
3. 最终聚合：按ID和组ID分组，取每组的最小start_dt和最大end_dt，得到合并后的结果。

该方案可以完美处理单行、两两相邻、多行连续符合条件的所有场景。

内容的提问来源于stack exchange，提问作者Любовь Пономарева