By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table dummy (
dummy char(1)
)
insert into dummy values('X')
1 rows affected
with s as (
select '1001' as order_nbr, '1' as step, 'P' as ex_type, timestamp '2021-01-01 00:00:00' as start_ts, timestamp '2021-01-01 09:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '2' as step, 'C' as ex_type, timestamp '2021-01-04 03:00:00' as start_ts, timestamp '2021-01-04 06:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '3' as step, 'C' as ex_type, timestamp '2021-01-03 07:00:00' as start_ts, timestamp '2021-01-03 08:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '4' as step, 'C' as ex_type, timestamp '2021-01-05 10:00:00' as start_ts, timestamp '2021-01-05 15:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '5' as step, 'Z' as ex_type, timestamp '2021-01-06 00:00:00' as start_ts, timestamp '2021-01-06 06:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '6' as step, 'Z' as ex_type, timestamp '2021-01-06 16:00:00' as start_ts, timestamp '2021-01-06 20:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '7' as step, 'C' as ex_type, timestamp '2021-01-07 08:00:00' as start_ts, timestamp '2021-01-07 09:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '8' as step, 'C' as ex_type, timestamp '2021-01-07 10:00:00' as start_ts, timestamp '2021-01-07 12:00:00' as end_ts from dummy union all
select '1002' as order_nbr, '1' as step, 'P' as ex_type, timestamp '2021-01-04 08:00:00' as start_ts, timestamp '2021-01-04 16:00:00' as end_ts from dummy
)
, num as (
select
s.*
/*Find consecutive rows on ex_type field*/
, row_number() over(partition by order_nbr order by start_ts asc) as r1
, row_number() over(partition by order_nbr, ex_type order by start_ts asc) as r2
from s
)
select
order_nbr
, ex_type
, min(start_ts) as start_ts
, max(end_ts) as end_ts
, string_agg(step, ',' order by start_ts asc) as steps
from num
group by
order_nbr
, ex_type
, case
/*For C use group number, for others - use original row number not to collapse them*/
when ex_type = 'C'
then r1 - r2
else r1
end
order by
| order_nbr | ex_type | start_ts | end_ts | steps |
|---|---|---|---|---|
| 1001 | P | 2021-01-01 00:00:00 | 2021-01-01 09:00:00 | 1 |
| 1001 | C | 2021-01-03 07:00:00 | 2021-01-05 15:00:00 | 3,2,4 |
| 1001 | Z | 2021-01-06 00:00:00 | 2021-01-06 06:00:00 | 5 |
| 1001 | Z | 2021-01-06 16:00:00 | 2021-01-06 20:00:00 | 6 |
| 1001 | C | 2021-01-07 08:00:00 | 2021-01-07 12:00:00 | 7,8 |
| 1002 | P | 2021-01-04 08:00:00 | 2021-01-04 16:00:00 | 1 |