SQL进阶技巧：如何计算重叠区间合并问题？

目录

0 需求

1 数据准备

2 问题分析

 3 小结

---

0 需求

给定多个时间段，每个时间段分为开始时间、结束时间，将相互重叠的多个时间段合并为一个区间

1 数据准备

 with time_merge as(
select 1 as id , 12 as st , 15 as et union all
select 2 as id , 57 as st , 58 as et union all
select 3 as id , 29 as st , 32 as et union all
select 4 as id , 30 as st , 31 as et union all
select 5 as id , 17 as st , 19 as et union all
select 6 as id , 44 as st , 44 as et union all
select 7 as id , 56 as st , 57 as et union all
select 8 as id , 16 as st , 18 as et 
 )

2 问题分析

第一步：寻找交叉点

将数据按照st,et排序后我们得到如下时序图

分析上述时序图我们得到，重叠交叉区间有2种情况，一种是区间部分重叠，一种是区间完全重合。

区间部分重合特点࿱