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?.
set datestyle='ISO,MDY';
create table test(ID,Start_date_time,End_date_time,class)
as values
(131, '5/26/2021 11:42'::timestamp, '5/26/2021 12:42'::timestamp, 'AAA')
,(132, '5/26/2021 12:42', '5/26/2021 13:18', 'AAA')
,(113, '5/26/2021 12:44', '5/26/2021 13:19', 'AAA')
,(114, '5/26/2021 13:19', '5/26/2021 13:34', 'AAA')
,(115, '5/26/2021 13:34', '5/26/2021 13:44', 'AAA')
,(111, '5/26/2021 16:09', '5/26/2021 17:24', 'AAA')
,(112, '5/26/2021 17:24', '5/26/2021 18:09', 'AAA')
,(123, '5/26/2021 8:08', '5/26/2021 10:08', 'BBB')
,(124, '5/26/2021 10:08', '5/26/2021 11:08', 'BBB')
,(116, '5/26/2021 11:06', '5/26/2021 11:30', 'BBB')
,(117, '5/26/2021 11:30', '5/26/2021 12:18', 'BBB')
,(118, '5/26/2021 12:18', '5/26/2021 13:06', 'BBB')
,(128, '5/26/2021 9:03', '5/26/2021 9:48', 'CCC')
,(129, '5/26/2021 9:48', '5/26/2021 10:03', 'CCC')
,(130, '5/26/2021 10:03', '5/26/2021 10:13', 'CCC')
,(119, '5/26/2021 10:22', '5/26/2021 10:34', 'CCC')
,(120, '5/26/2021 10:34', '5/26/2021 10:58', 'CCC')
,(121, '5/26/2021 10:58', '5/26/2021 11:10', 'CCC')
,(122, '5/26/2021 11:10', '5/26/2021 11:22', 'CCC')
,(125, '5/26/2021 14:47', '5/26/2021 15:47', 'DDD')
,(126, '5/26/2021 15:47', '5/26/2021 16:35', 'DDD')
,(127, '5/26/2021 16:35', '5/26/2021 17:35', 'DDD')
;
table test;
SET
SELECT 22
| id | start_date_time | end_date_time | class |
|---|---|---|---|
| 131 | 2021-05-26 11:42:00 | 2021-05-26 12:42:00 | AAA |
| 132 | 2021-05-26 12:42:00 | 2021-05-26 13:18:00 | AAA |
| 113 | 2021-05-26 12:44:00 | 2021-05-26 13:19:00 | AAA |
| 114 | 2021-05-26 13:19:00 | 2021-05-26 13:34:00 | AAA |
| 115 | 2021-05-26 13:34:00 | 2021-05-26 13:44:00 | AAA |
| 111 | 2021-05-26 16:09:00 | 2021-05-26 17:24:00 | AAA |
| 112 | 2021-05-26 17:24:00 | 2021-05-26 18:09:00 | AAA |
| 123 | 2021-05-26 08:08:00 | 2021-05-26 10:08:00 | BBB |
| 124 | 2021-05-26 10:08:00 | 2021-05-26 11:08:00 | BBB |
| 116 | 2021-05-26 11:06:00 | 2021-05-26 11:30:00 | BBB |
| 117 | 2021-05-26 11:30:00 | 2021-05-26 12:18:00 | BBB |
| 118 | 2021-05-26 12:18:00 | 2021-05-26 13:06:00 | BBB |
| 128 | 2021-05-26 09:03:00 | 2021-05-26 09:48:00 | CCC |
| 129 | 2021-05-26 09:48:00 | 2021-05-26 10:03:00 | CCC |
| 130 | 2021-05-26 10:03:00 | 2021-05-26 10:13:00 | CCC |
| 119 | 2021-05-26 10:22:00 | 2021-05-26 10:34:00 | CCC |
| 120 | 2021-05-26 10:34:00 | 2021-05-26 10:58:00 | CCC |
| 121 | 2021-05-26 10:58:00 | 2021-05-26 11:10:00 | CCC |
| 122 | 2021-05-26 11:10:00 | 2021-05-26 11:22:00 | CCC |
| 125 | 2021-05-26 14:47:00 | 2021-05-26 15:47:00 | DDD |
| 126 | 2021-05-26 15:47:00 | 2021-05-26 16:35:00 | DDD |
| 127 | 2021-05-26 16:35:00 | 2021-05-26 17:35:00 | DDD |
SELECT 22
with gaps as(
select*,Start_date_time<>lag(End_date_time,1,Start_date_time)over w1 as "gap"
from test
window w1 as(partition by class order by Start_date_time))
,islands as(
select*,count(*)filter(where "gap")over w2 as "island"
from gaps
window w2 as(partition by class order by Start_date_time))
select ID
, Start_date_time
, End_date_time
, class
, first_value(id)over w3 as Updated_ID
from islands
window w3 as(partition by class,"island" order by Start_date_time);
| id | start_date_time | end_date_time | class | updated_id |
|---|---|---|---|---|
| 131 | 2021-05-26 11:42:00 | 2021-05-26 12:42:00 | AAA | 131 |
| 132 | 2021-05-26 12:42:00 | 2021-05-26 13:18:00 | AAA | 131 |
| 113 | 2021-05-26 12:44:00 | 2021-05-26 13:19:00 | AAA | 113 |
| 114 | 2021-05-26 13:19:00 | 2021-05-26 13:34:00 | AAA | 113 |
| 115 | 2021-05-26 13:34:00 | 2021-05-26 13:44:00 | AAA | 113 |
| 111 | 2021-05-26 16:09:00 | 2021-05-26 17:24:00 | AAA | 111 |
| 112 | 2021-05-26 17:24:00 | 2021-05-26 18:09:00 | AAA | 111 |
| 123 | 2021-05-26 08:08:00 | 2021-05-26 10:08:00 | BBB | 123 |
| 124 | 2021-05-26 10:08:00 | 2021-05-26 11:08:00 | BBB | 123 |
| 116 | 2021-05-26 11:06:00 | 2021-05-26 11:30:00 | BBB | 116 |
| 117 | 2021-05-26 11:30:00 | 2021-05-26 12:18:00 | BBB | 116 |
| 118 | 2021-05-26 12:18:00 | 2021-05-26 13:06:00 | BBB | 116 |
| 128 | 2021-05-26 09:03:00 | 2021-05-26 09:48:00 | CCC | 128 |
| 129 | 2021-05-26 09:48:00 | 2021-05-26 10:03:00 | CCC | 128 |
| 130 | 2021-05-26 10:03:00 | 2021-05-26 10:13:00 | CCC | 128 |
| 119 | 2021-05-26 10:22:00 | 2021-05-26 10:34:00 | CCC | 119 |
| 120 | 2021-05-26 10:34:00 | 2021-05-26 10:58:00 | CCC | 119 |
| 121 | 2021-05-26 10:58:00 | 2021-05-26 11:10:00 | CCC | 119 |
| 122 | 2021-05-26 11:10:00 | 2021-05-26 11:22:00 | CCC | 119 |
| 125 | 2021-05-26 14:47:00 | 2021-05-26 15:47:00 | DDD | 125 |
| 126 | 2021-05-26 15:47:00 | 2021-05-26 16:35:00 | DDD | 125 |
| 127 | 2021-05-26 16:35:00 | 2021-05-26 17:35:00 | DDD | 125 |
SELECT 22
create table example_result(ID,Start_date_time,End_date_time,class,Updated_ID)
as values
(131, '5/26/2021 11:42'::timestamp, '5/26/2021 12:42'::timestamp, 'AAA', 131)
,(132, '5/26/2021 12:42', '5/26/2021 13:18', 'AAA', 131)
,(113, '5/26/2021 12:44', '5/26/2021 13:19', 'AAA', 113)
,(114, '5/26/2021 13:19', '5/26/2021 13:34', 'AAA', 113)
,(115, '5/26/2021 13:34', '5/26/2021 13:44', 'AAA', 113)
,(111, '5/26/2021 16:09', '5/26/2021 17:24', 'AAA', 111)
,(112, '5/26/2021 17:24', '5/26/2021 18:09', 'AAA', 111)
,(123, '5/26/2021 8:08', '5/26/2021 10:08', 'BBB', 123)
,(124, '5/26/2021 10:08', '5/26/2021 11:08', 'BBB', 123)
,(116, '5/26/2021 11:06', '5/26/2021 11:30', 'BBB', 116)
,(117, '5/26/2021 11:30', '5/26/2021 12:18', 'BBB', 116)
,(118, '5/26/2021 12:18', '5/26/2021 13:06', 'BBB', 116)
,(128, '5/26/2021 9:03', '5/26/2021 9:48', 'CCC', 128)
,(129, '5/26/2021 9:48', '5/26/2021 10:03', 'CCC', 128)
,(130, '5/26/2021 10:03', '5/26/2021 10:13', 'CCC', 128)
,(119, '5/26/2021 10:22', '5/26/2021 10:34', 'CCC', 119)
,(120, '5/26/2021 10:34', '5/26/2021 10:58', 'CCC', 119)
,(121, '5/26/2021 10:58', '5/26/2021 11:10', 'CCC', 119)
,(122, '5/26/2021 11:10', '5/26/2021 11:22', 'CCC', 119)
,(125, '5/26/2021 14:47', '5/26/2021 15:47', 'DDD', 125)
,(126, '5/26/2021 15:47', '5/26/2021 16:35', 'DDD', 125)
,(127, '5/26/2021 16:35', '5/26/2021 17:35', 'DDD', 125)
;
table example_result;
SELECT 22
| id | start_date_time | end_date_time | class | updated_id |
|---|---|---|---|---|
| 131 | 2021-05-26 11:42:00 | 2021-05-26 12:42:00 | AAA | 131 |
| 132 | 2021-05-26 12:42:00 | 2021-05-26 13:18:00 | AAA | 131 |
| 113 | 2021-05-26 12:44:00 | 2021-05-26 13:19:00 | AAA | 113 |
| 114 | 2021-05-26 13:19:00 | 2021-05-26 13:34:00 | AAA | 113 |
| 115 | 2021-05-26 13:34:00 | 2021-05-26 13:44:00 | AAA | 113 |
| 111 | 2021-05-26 16:09:00 | 2021-05-26 17:24:00 | AAA | 111 |
| 112 | 2021-05-26 17:24:00 | 2021-05-26 18:09:00 | AAA | 111 |
| 123 | 2021-05-26 08:08:00 | 2021-05-26 10:08:00 | BBB | 123 |
| 124 | 2021-05-26 10:08:00 | 2021-05-26 11:08:00 | BBB | 123 |
| 116 | 2021-05-26 11:06:00 | 2021-05-26 11:30:00 | BBB | 116 |
| 117 | 2021-05-26 11:30:00 | 2021-05-26 12:18:00 | BBB | 116 |
| 118 | 2021-05-26 12:18:00 | 2021-05-26 13:06:00 | BBB | 116 |
| 128 | 2021-05-26 09:03:00 | 2021-05-26 09:48:00 | CCC | 128 |
| 129 | 2021-05-26 09:48:00 | 2021-05-26 10:03:00 | CCC | 128 |
| 130 | 2021-05-26 10:03:00 | 2021-05-26 10:13:00 | CCC | 128 |
| 119 | 2021-05-26 10:22:00 | 2021-05-26 10:34:00 | CCC | 119 |
| 120 | 2021-05-26 10:34:00 | 2021-05-26 10:58:00 | CCC | 119 |
| 121 | 2021-05-26 10:58:00 | 2021-05-26 11:10:00 | CCC | 119 |
| 122 | 2021-05-26 11:10:00 | 2021-05-26 11:22:00 | CCC | 119 |
| 125 | 2021-05-26 14:47:00 | 2021-05-26 15:47:00 | DDD | 125 |
| 126 | 2021-05-26 15:47:00 | 2021-05-26 16:35:00 | DDD | 125 |
| 127 | 2021-05-26 16:35:00 | 2021-05-26 17:35:00 | DDD | 125 |
SELECT 22