合併日期時間範圍

我一直在努力在 oracle SQL/PLSQL（數據庫標準版 11gR2）中合併日期時間範圍。

我正在嘗試合併日期時間範圍，以便以下數據

order_id    start_date_time         end_date_time
3933        04/02/2020 08:00:00     04/02/2020 12:00:00
3933        04/02/2020 13:30:00     04/02/2020 17:00:00
3933        04/02/2020 14:00:00     04/02/2020 19:00:00
3933        05/02/2020 13:40:12     05/02/2020 14:34:48
3933        05/02/2020 14:00:00     05/02/2020 18:55:12
3933        05/02/2020 14:49:48     05/02/2020 15:04:48
3933        06/02/2020 08:00:00     06/02/2020 12:00:00
3933        06/02/2020 13:30:00     06/02/2020 17:00:00
3933        06/02/2020 14:10:12     06/02/2020 18:49:48
3933        07/02/2020 08:00:00     07/02/2020 10:30:00
3933        07/02/2020 08:00:00     07/02/2020 12:00:00
3933        07/02/2020 13:30:00     07/02/2020 17:00:00
11919       14/05/2020 09:00:00     14/05/2020 17:00:00
11919       14/05/2020 09:00:00     14/05/2020 17:00:00
11919       14/05/2020 15:00:00     14/05/2020 16:30:00
11919       15/05/2020 08:40:12     15/05/2020 16:30:00
11919       15/05/2020 09:40:12     15/05/2020 16:30:00
11919       15/05/2020 10:15:00     15/05/2020 12:15:00
11919       15/05/2020 13:19:48     15/05/2020 16:00:00
11919       18/05/2020 08:49:48     18/05/2020 09:45:00
11919       18/05/2020 10:00:00     18/05/2020 17:00:00
11919       18/05/2020 10:00:00     18/05/2020 16:58:12
11919       18/05/2020 15:34:48     18/05/2020 16:10:12
11919       18/05/2020 16:30:00     18/05/2020 16:45:00
...         ...                     ...

將轉換為以下結果集

--after merge (this is the result I am seeking)
order_id    start_date_time         end_date_time
3933        04/02/2020 08:00:00     04/02/2020 12:00:00
3933        04/02/2020 13:30:00     04/02/2020 19:00:00
3933        05/02/2020 13:40:12     05/02/2020 18:55:12
3933        06/02/2020 08:00:00     06/02/2020 12:00:00
3933        06/02/2020 13:30:00     06/02/2020 18:49:48
3933        07/02/2020 08:00:00     07/02/2020 12:00:00
3933        07/02/2020 13:30:00     07/02/2020 17:00:00
11919       14/05/2020 09:00:00     14/05/2020 17:00:00
11919       15/05/2020 08:40:12     15/05/2020 16:30:00
11919       18/05/2020 08:49:48     18/05/2020 17:00:00
...         ...                     ...

start_date_time 和 end_date_time 的格式為 DAY/MONTH/YEAR HH24:MI:SS。

關於如何在 Oracle SQL 或 PL/SQL 中進行合併的任何建議/解決方案？

我認為這是一個微不足道的問題，但是我還沒有在網際網路上找到解決方案。

提前致謝。

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
create table data(order_id number, start_date_time date, end_date_time date);
insert into data values (3933  ,'04/02/2020 08:00:00', '04/02/2020 12:00:00');
insert into data values (3933  ,'04/02/2020 13:30:00', '04/02/2020 17:00:00');
insert into data values (3933  ,'04/02/2020 14:00:00', '04/02/2020 19:00:00');
insert into data values (3933  ,'05/02/2020 13:40:12', '05/02/2020 14:34:48');
insert into data values (3933  ,'05/02/2020 14:00:00', '05/02/2020 18:55:12');
insert into data values (3933  ,'05/02/2020 14:49:48', '05/02/2020 15:04:48');
insert into data values (3933  ,'06/02/2020 08:00:00', '06/02/2020 12:00:00');
insert into data values (3933  ,'06/02/2020 13:30:00', '06/02/2020 17:00:00');
insert into data values (3933  ,'06/02/2020 14:10:12', '06/02/2020 18:49:48');
insert into data values (3933  ,'07/02/2020 08:00:00', '07/02/2020 10:30:00');
insert into data values (3933  ,'07/02/2020 08:00:00', '07/02/2020 12:00:00');
insert into data values (3933  ,'07/02/2020 13:30:00', '07/02/2020 17:00:00');
insert into data values (11919 ,'14/05/2020 09:00:00', '14/05/2020 17:00:00');
insert into data values (11919 ,'14/05/2020 09:00:00', '14/05/2020 17:00:00');
insert into data values (11919 ,'14/05/2020 15:00:00', '14/05/2020 16:30:00');
insert into data values (11919 ,'15/05/2020 08:40:12', '15/05/2020 16:30:00');
insert into data values (11919 ,'15/05/2020 09:40:12', '15/05/2020 16:30:00');
insert into data values (11919 ,'15/05/2020 10:15:00', '15/05/2020 12:15:00');
insert into data values (11919 ,'15/05/2020 13:19:48', '15/05/2020 16:00:00');
insert into data values (11919 ,'18/05/2020 08:49:48', '18/05/2020 09:45:00');
insert into data values (11919 ,'18/05/2020 10:00:00', '18/05/2020 17:00:00');
insert into data values (11919 ,'18/05/2020 10:00:00', '18/05/2020 16:58:12');
insert into data values (11919 ,'18/05/2020 15:34:48', '18/05/2020 16:10:12');
insert into data values (11919 ,'18/05/2020 16:30:00', '18/05/2020 16:45:00');
commit;

然後用一些魔法：

select d1.order_id, d1.start_date_time, max(d2.end_date_time) as end_date_time
from data d1 join data d2
on (d1.order_id = d2.order_id and
(d1.start_date_time <= d2.end_date_time and d2.start_date_time <= d1.end_date_time))
group by d1.order_id, d1.start_date_time
intersect
select d1.order_id, min(d1.start_date_time), d2.end_date_time
from data d1 join data d2
on (d1.order_id = d2.order_id and
(d1.start_date_time <= d2.end_date_time and d2.start_date_time <= d1.end_date_time))
group by d1.order_id, d2.end_date_time
order by 1, 2, 3;

 ORDER_ID START_DATE_TIME     END_DATE_TIME      
---------- ------------------- -------------------
     3933 04/02/2020 08:00:00 04/02/2020 12:00:00
     3933 04/02/2020 13:30:00 04/02/2020 19:00:00
     3933 05/02/2020 13:40:12 05/02/2020 18:55:12
     3933 06/02/2020 08:00:00 06/02/2020 12:00:00
     3933 06/02/2020 13:30:00 06/02/2020 18:49:48
     3933 07/02/2020 08:00:00 07/02/2020 12:00:00
     3933 07/02/2020 13:30:00 07/02/2020 17:00:00
    11919 14/05/2020 09:00:00 14/05/2020 17:00:00
    11919 15/05/2020 08:40:12 15/05/2020 16:30:00
    11919 18/05/2020 08:49:48 18/05/2020 09:45:00
    11919 18/05/2020 10:00:00 18/05/2020 17:00:00

引用自：https://dba.stackexchange.com/questions/268651