如何根据给定条件PostgreSQL迭代减少组中的行数

我有这样一张桌子:

我需要创建一个满足条件的新ID(分别针对每个ID): 如果Start_dt[i+1]-end_dt[i]&lt；9个月，则行i和i+1折叠，Start_dt_new=Start_dt[i]，end_dt_new=end_dt[i+1].

因此，生成的表将如下所示

我怎样才能用PostgreSQL做到这一点？

我try 了什么:

create table test (
    
    start_dt date,
    end_dt date,
    id varchar(10)
    
);


insert into test(start_dt, end_dt, id)
    values (date'2023-01-01', date'2023-03-08', 'A'),
    (date'2023-05-08', date'2023-06-18', 'A'),
    (date'2023-12-09', date'2024-02-02', 'A'),
    (date'2024-12-01', date'2025-01-05', 'A'),
    (date'2023-02-01', date'2023-03-07', 'B'),
    (date'2023-06-08', date'2023-07-18', 'B'),
    (date'2024-12-09', date'2024-02-02', 'B');
    
    with a as (
      select a.*,
      case when age(lead(start_dt) over(partition by id order by start_dt), end_dt)
      < interval'9 months' then 1 else 0 end monotony_f
      from test a
      order by id, start_dt
    ),
    b as (
      select start_dt, end_dt, id, coalesce(lag(monotony_f) over(partition by id order by start_dt), 1) monotony_f
      from a
      order by id, start_dt
    )
    select start_dt, end_dt, id from b
    where monotony_f = 0
    union
    select min(start_dt) start_dt, max(end_dt) end_dt, id from b
    where monotony_f = 1
    group by id
    order by id, start_dt

但或许还有更正确、更自然的方法呢？我的解决方案不包括这样的例子: