大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说用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;
代码100分
问题核心是求多条记录之间的并集操作 ，我写的sql如下
代码100分--问题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如下：
代码100分----问题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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/9311.html
