问题是计算通道的总开放时长，只要有任意一个终端开放通道就算开放，难点在于各种终端开放时间重叠包含。
三种思路：
1、先取排序后的第一条数据的时间段为基准，然后两两比较，累加时间。
2、把两两时间串起来，有交集的时间段，直接取最小时间至最大时间，没交集的减去中间不连续的部分。
还有一种思路是求出每个时间段的值，然后减去重叠的时间段。这种过于复杂不考虑，因为重叠的时间段肯定不止重复一次。
第一种实现会在一些嵌套包含的情况下变得复杂，所以无奈只能先过滤那些完全被包含的记录。
第二种实现目前来看代码简洁，思路清晰，而且使用存储过程实现，效率较高。
一、分析
二、代码实现
drop table xcp;
create table xcp(terminal varchar2(2),channel varchar2(2),begin_time date,end_time date);
--问题1、计算总时长，以下为测试数据
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 order by begin_time;--计算总时长，方法一
select sum(greatest(y.acc, 0) - greatest(y.feature2, 0)) total_time_hours
--select y.*, greatest(y.acc, 0) - greatest(y.feature2, 0) total_time_hours
from (select x.*,
(x.end_time - x.pre_end_time) * 24 acc, --累加值
(x.end_time - x.pre_end_time) * 24 feature1, --特征1，这个值小于0则为第二类，也就是情形4，可以发现这个特征的计算方式其实和累加值计算方式一样，这里为了演示，真实场景可以不用计算，为了忽略情形4，直接使用 greatest(acc,0)
(x.begin_time - x.pre_end_time) * 24 feature2 --特征2，这个值大于0则为第三类，也就是情形8,为了修正，可以在acc的基础上，减去 greatest(feature2,0)
from (select t.*,
lag(t.end_time, 1, t.begin_time) over(partition by t.channel order by t.begin_time, t.end_time) pre_end_time, --上一条结束时间,注意第一条给的默认值（第一条没有上一条）
lag(t.begin_time, 1, t.begin_time) over(partition by t.channel order by t.begin_time, t.end_time) pre_start_time --上一条开始时间,注意第一条给的默认值（第一条没有上一条）
from xcp t
where not exists (select 1
from xcp s
where s.begin_time < t.begin_time
and s.end_time > t.end_time)) x) y;--计算总时长，优化后的方法二（思路来自基友https://www.cnblogs.com/yongestcat/p/12590154.html）
--第8的特征：下一条记录开始时间 大于 截止当前行的最大结束时间；那么就把这部分时间记下来，最后减掉即可
select (max(end_time) - min(begin_time)) * 24 -
sum(decode(sign(next_begin_time - max_end_time),
1,
(next_begin_time - max_end_time) * 24,
0)) 通道开通时间
from (select a.channel,
a.begin_time,
a.end_time,
max(a.end_time) over(partition by a.channel order by a.begin_time rows between unbounded preceding and current row) max_end_time, --截止当前行的最大结束时间
lead(a.begin_time, 1) over(partition by a.channel order by a.begin_time) next_begin_time --下一条记录的开始时间
from xcp a) tmp;--方法二的plsql实现by小金
/*思路：
第一步：两两合并，两条记录之间的关系只有两种：有交集 和 无交集
1)对于有交集的：两两合并，取min(begin_time),max(end_time)作为新记录，
2)对于无交集的：同样取min(begin_time),max(end_time)作为新记录，不过把中间空白部分计入duration_del
第二步：然后将第一步合并的新纪录和下一条记录再两两合并，以此类推，直至合并完所有记录
第三步：结果就是 最终合并记录的 end_time-begin_time-duration_del*/
declare
duration_del number:=0;--存储无交集的两两记录之间的空白时间
--用于存储合并后的时间
begin_time_merge date; end_time_merge date;
--用于输入要查询的时间段
day1 date:=to_date(20200314,'yyyymmdd');
day2 date:=to_date(20200330,'yyyymmdd');
begin
--初始化时间
begin_time_merge :=day1; end_time_merge:=day1;
for i in (select rownum rnow,aa.* from
(select a.channel,greatest(a.begin_time, day1) begin_time,least(a.end_time,day2) end_time
from xcp a where not (end_time < day1 or begin_time> day2) order by 2)aa
)loop --扫描一次全表
if i.begin_time>end_time_merge then
duration_del:= duration_del+ (i.begin_time-end_time_merge)*24;--空白部分计入duration_del
end if;
end_time_merge := greatest(end_time_merge,i.end_time);
end loop;
dbms_output.put_line((end_time_merge-begin_time_merge)*24-duration_del||'个小时通道开放');
end;
/--问题2、计算某一天（17号）时长，先构造跨天数据
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;
--计算17号开放时长（小时）方法一，方法二就是上面方法二的基础上修改就行了，这里就不写了
with tmp as
(select terminal,
channel,
least(greatest(begin_time, to_date('20200317', 'yyyymmdd')),
to_date('20200317', 'yyyymmdd') + 1) begin_time,
greatest(least(end_time, to_date('20200317', 'yyyymmdd') + 1),
to_date('20200317', 'yyyymmdd')) end_time
from xcp)
select sum(greatest(y.acc, 0) - greatest(y.feature2, 0)) total_time_hours
from (select x.*,
(x.end_time - x.pre_end_time) * 24 acc,
(x.begin_time - x.pre_end_time) * 24 feature2
from (select t.*,
lag(t.end_time, 1, t.begin_time) over(partition by t.channel order by t.begin_time, t.end_time) pre_end_time,
lag(t.begin_time, 1, t.begin_time) over(partition by t.channel order by t.begin_time, t.end_time) pre_start_time
from tmp t
where not exists (select 1
from tmp s
where s.begin_time < t.begin_time
and s.end_time > t.end_time)) x) y;版权声明：本文为Seandba原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。