PostgreSQL Question posted in
The official documentation can be found here.

How iteratively reduce the number of rows in a group according to a given condition PostgreSQL

I have the table like:

start_dt end_dt ID Index
2023-01-01 2023-03-08 A i
2023-05-08 2023-06-18 A i+1
2023-12-09 2024-02-02 A i+2
2024-12-01 2025-01-05 A i+3
2023-02-01 2023-03-07 B j
2023-06-08 2023-07-18 B j+1
2024-12-09 2024-02-02 B j+2

I need to create new one which satisfies the conditions (for each ID separately):
if start_dt[i+1] – end_dt[i] < 9 months, then the rows i and i+1 collapse, start_dt_new = start_dt[i], end_dt_new = end_dt[i+1].

so the resulted table will look like

start_dt end_dt ID
2023-01-01 2024-02-02 A
2024-12-01 2025-01-05 A
2023-02-01 2023-07-18 B
2024-12-09 2024-02-02 B

how can I do it with PostgreSQL?

What I tried:

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

But may be there is more correct and natural way? my solution does not cover such example:

start_dt end_dt ID Index
2023-01-01 2023-03-08 A i
2023-05-08 2023-06-18 A i+1
2023-12-09 2024-02-02 A i+2
2024-12-01 2025-01-05 A i+3
2025-11-01 2025-12-07 A i+4
2026-01-08 2026-07-18 A i+5
Tags:

2

Answers


  1. 0

    Example

    with a as (
  select a.*
    ,case when age(start_dt,lag(end_dt,1,start_dt)over(partition by id order by start_dt))
            < interval'9 months' 
     then 0 
     else 1 -- start new group
     end monotony_f
  from test a
  order by id, start_dt
),
b as (
  select a.*
      -- group number
     ,sum(monotony_f)over(partition by id order by start_dt) monotony_grn
  from a
)
-- collapse group to one row
select min(start_dt) start_dt,max(end_dt) end_dt,id
from b
group by id,monotony_grn
order by id,min(start_dt);

    before grouping

    start_dt end_dt id age monotony_f monotony_grn
    2023-01-01 2023-03-08 A 00:00:00 0 0
    2023-05-08 2023-06-18 A 2 mons 0 0
    2023-12-09 2024-02-02 A 5 mons 21 days 0 0
    2024-12-01 2025-01-05 A 9 mons 28 days 1 1
    2023-02-01 2023-03-07 B 00:00:00 0 0
    2023-06-08 2023-07-18 B 3 mons 1 day 0 0
    2024-12-09 2024-02-02 B 1 year 4 mons 22 days 1 1
    2023-01-01 2023-03-08 C 00:00:00 0 0
    2023-05-08 2023-06-18 C 2 mons 0 0
    2023-12-09 2024-02-02 C 5 mons 21 days 0 0
    2024-12-01 2025-01-05 C 9 mons 28 days 1 1
    2025-11-01 2025-12-07 C 9 mons 27 days 1 2
    2026-01-08 2026-07-18 C 1 mon 1 day 0 2
    Login or Signup to reply.
  2. 0

    with recursive x as (
    select
    sk,
    id,
    start_dt ,
    end_dt,
    sk || ” sk_path
    from
    test
    –where sk=1
    union all
    select
    t2.sk sk,
    t2.id,
    x.start_dt ,
    t2.end_dt,
    x.sk_path || t2.sk
    from
    test t2
    join x on
    x.id = t2.id
    and x.sk + 1 = t2.sk
    and age(t2.start_dt ,
    x.end_dt) < interval ‘9 months’

              )

    select
    sk,
    id,
    start_dt ,
    end_dt,
    sk_path
    from
    x
    where
    not exists (
    select
    1
    from
    x x2
    where
    x.id = x2.id
    and (( x.start_dt > x2.start_dt
    and x.start_dt < x2.end_dt )
    or
    ( x.end_dt > x2.start_dt
    and x.end_dt < x2.end_dt ) ))

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search