用sql对含有时间段字段(起始时间、结束时间)的记录做并集处理

来自于一个基友的问题：

他的博客同问题链接    sql时间段取并集、合并 https://blog.csdn.net/seandba/article/details/105152412 

问题：计算通道的总开放时长，只要有任意一个终端开放通道就算开放，难点在于各种终端开放时间重叠包含

问题测试数据

--问题一、测试数据--计算总开放时长（小时）
truncate table xcp;
insert into xcp values('1','a1',to_date('20200317 01:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 06:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('2','a1',to_date('20200317 01:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 06:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('2','a1',to_date('20200317 01:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 08:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('2','a1',to_date('20200317 02:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 07:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('2','a1',to_date('20200317 03:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 07:00:00','yyyymmdd hh24:mi:ss'));

insert into xcp values('2','a1',to_date('20200317 05:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 09:00:00','yyyymmdd hh24:mi:ss '));
insert into xcp values('3','a1',to_date('20200317 09:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 11:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('3','a1',to_date('20200317 12:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 13:00:00','yyyymmdd hh24:mi:ss'));

insert into xcp values('2','a1',to_date('20200317 14:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 19:00:00','yyyymmdd hh24:mi:ss '));
insert into xcp values('3','a1',to_date('20200317 16:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 19:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('3','a1',to_date('20200317 18:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 19:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('3','a1',to_date('20200317 18:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 21:00:00','yyyymmdd hh24:mi:ss'));
commit;

select * from xcp;

问题核心是求多条记录之间的并集操作 ，我写的sql如下

--问题1
with tmp1 as (  --取所有时间节点
select channel,begin_time time from xcp
union select channel,end_time from xcp
union select channel,min(begin_time) from xcp group by channel
union select channel,max(end_time) from xcp group by channel),

tmp2 as(--每个时间节点连接到下个节点  形成时间段
select a.channel,a.time,lead(a.time,1) over(partition by a.channel order by a.time) nexttime
from tmp1 a),

tmp3 as(--每个时间段取中值
select b.channel,b.time,b.nexttime,(b.nexttime-b.time)/2+b.time midtime
from tmp2 b
where b.nexttime is not null),

tmp4 as(--若中值处于原始记录中  则该段时间为通道开通时间 否则通道不开通
select c.*,
case when exists (select 1 from xcp o where c.midtime between o.begin_time and o.end_time) then 1 else 0 end *
(c.nexttime-c.time)*24 duration
from tmp3 c)

select nvl(d.channel,'合计时长') 通道,d.time 开始时间,d.nexttime 结束时间,
sum(duration) "通道开通时间(小时)" from tmp4 d
group by rollup((d.channel,d.time,d.nexttime))
order by 2;

看着就很垃圾的sql，执行计划一定垃圾，记录以备后查询吧

原理是吧时间节点拿出来，对没两个时间节点之间的时间段，取中间值到原始记录表查询，如果是，这段时间就是属于并集后的，然后对并集后的记录求和

问题2：求17日的的通道开放时长

--问题2、测试数据--计算27号开放时长（小时）
truncate table xcp;
insert into xcp values('13','a1',to_date('20200314 08:00:00','yyyymmdd hh24:mi:ss'),to_date('20200315 09:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('14','a1',to_date('20200317 08:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 09:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('15','a1',to_date('20200316 03:00:00','yyyymmdd hh24:mi:ss'),to_date('20200317 05:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('16','a1',to_date('20200317 08:00:00','yyyymmdd hh24:mi:ss'),to_date('20200318 10:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('17','a1',to_date('20200316 08:00:00','yyyymmdd hh24:mi:ss'),to_date('20200318 10:00:00','yyyymmdd hh24:mi:ss'));
insert into xcp values('18','a1',to_date('20200320 08:00:00','yyyymmdd hh24:mi:ss'),to_date('20200321 10:00:00','yyyymmdd hh24:mi:ss'));
commit;

select * from xcp order by begin_time

sql如下：

----问题2
with tmp1 as (  --取所有时间节点    取17号就加入17号0点和24点两个时间
select channel,begin_time time from xcp
union select channel,end_time from xcp
union select channel,min(begin_time) from xcp group by channel
union select channel,max(end_time) from xcp group by channel
union select distinct channel,to_date('20200317','yyyymmdd') from xcp
union select distinct channel,to_date('20200318','yyyymmdd') from xcp),

tmp2 as(--每个时间节点连接到下个节点  形成时间段
select a.channel,a.time,lead(a.time,1) over(partition by a.channel order by a.time) nexttime
from tmp1 a),

tmp3 as(--每个时间段取中值
select b.channel,b.time,b.nexttime,(b.nexttime-b.time)/2+b.time midtime
from tmp2 b
where b.nexttime is not null
and to_char(b.time,'yyyymmdd')=20200317),

tmp4 as(--若中值处于原始记录中  则该段时间为通道开通时间 否则通道不开通
select c.*,
case when exists (select 1 from xcp o where c.midtime between o.begin_time and o.end_time) then 1 else 0 end *
(c.nexttime-c.time)*24 duration
from tmp3 c)

select nvl(d.channel,'合计时长') 通道,d.time 开始时间,d.nexttime 结束时间,
sum(duration) "通道开通时间(小时)" from tmp4 d
group by rollup((d.channel,d.time,d.nexttime))
order by 2;

思路是在第一步取时间节点的时候单独加入17日0点24点的时间点即可

优化：

上述代码全表扫描5次，效率垃圾，从小强的第8种情况的反面考虑，结合小强给的思路，即可优化到扫描一次全表即可，代码如下

  --第8的特征：下一条记录开始时间  大于  本条记录的结束时间；那么就把这部分时间记下来，最后减掉即可
with tmp as(
select a.channel,a.begin_time,a.end_time,
(lead(a.begin_time,1) over(partition by a.channel order by begin_time,end_time)  - a.end_time)*24 hoursto_next_begin_time  --距离下一条记录的时间间隔  如果是正数就是第8种情况
from xcp a) 

select (max(end_time)-min(begin_time))*24 - sum(decode(sign(hoursto_next_begin_time),1,hoursto_next_begin_time,0))  通道开通时间
from tmp aa