SQL进阶技巧：如何找出开会时间有重叠的会议室？| 时间区间重叠问题

目录

0 场景描述

1 数据准备

2 问题分析

方法1：利用 lateral view posexplode()函数将表展开成时间明细表

方法2：利用数学区间讨论思想求解

3 小结

如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南

专栏 原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。

---

0 场景描述

有7个会议室，每个会议室每天都有人开会，某一天的开会时间如下：

查询出开会时间有重叠的是哪几个会议室？上面预期结果是 ID 2 3 4 5 6 

1 数据准备

create table  meeting as(
select 1 id,'08:00' starttime,'09:15' endtime
union all
select 2,'13:20','15:20'
union all
select 3,'10:00','14:00'
union all
select 4,'13:55','16:25'
union all
select 5,'14:00','17:45'
union all
select 6,'14:05','17:45'
union all
select 7,'18:05','19:45')

 

2 问题分析

方法1：利用 lateral view posexplode()函数将表展开成时间明细表

具体SQL如下：

select id, starttime + pos  as hour
from (select id, substr(starttime, 1, 2)                         starttime, substr(endtime, 1, 2)                           endtime, substr(endtime, 1, 2) - substr(starttime, 1, 2) difffrom meeting) tlateral view posexplode(split(space(cast(diff as int)), space(1))) tmp as pos, val

ID  HOUR
1	8
1	9
2	13
2	14
2	15
3	10
3	11
3	12
3	13
3	14
4	13
4	14
4	15
4	16
5	14
5	15
5	16
5	17
6	14
6	15
6	16
6	17
7	18
7	19

第二步：针对hour分组，求出count(*)大于等于2时的id即为重叠的会议室

 （1）先利用count(1) over(partition by hour) 进行辅助标记

select id,count(1) over(partition by hour) flg
from (select id, starttime + pos as hourfrom (select id, substr(starttime, 1, 2)                         starttime, substr(endtime, 1, 2)                           endtime, substr(endtime, 1, 2) - substr(starttime, 1, 2) difffrom meeting) tlateral view posexplode(split(space(cast(diff as int)), space(1))) tmp as pos, val) t

1	8	1
1	9	1
3	10	1
3	11	1
3	12	1
4	13	3
2	13	3
3	13	3
4	14	5
6	14	5
5	14	5
3	14	5
2	14	5
5	15	4
6	15	4
2	15	4
4	15	4
5	16	3
6	16	3
4	16	3
5	17	2
6	17	2
7	18	1
7	19	1

（2）过滤出大于等于2的id，并去重获取最终结果

select id
from (select id, hour, count(1) over (partition by hour) flgfrom (select id, starttime + pos as hourfrom (select id, substr(starttime, 1, 2)                         starttime, substr(endtime, 1, 2)                           endtime, substr(endtime, 1, 2) - substr(starttime, 1, 2) difffrom meeting) tlateral view posexplode(split(space(cast(diff as int)), space(1))) tmp as pos, val) t) t
where flg >= 2
group by id

 

方法2：利用数学区间讨论思想求解

详情具体参考文章：

SQL进阶技巧：如何按任意时段分析时间区间问题？ | 分区间讨论【左、中、右】_sql按某时段日期区间聚合-CSDN博客

 具体讨论方法如下图1所示：

情况1：区间在右

判断条件 cet >= et and ct <= et          重叠区间为【ct,et]】

情况2：区间在内

判断条件为 ct>= st  and cet <= et      重叠区间为 【ct,cet】

情况3：区间在左

判断条件 ct <= st  and cet >= st        重叠区间为【st,cet】

第一步：先自关联，生成全量行行比较的数据集

select
from meeting a,meeting b

1	08:00	09:15	1	08:00	09:15
2	13:20	15:20	1	08:00	09:15
3	10:00	14:00	1	08:00	09:15
4	13:55	16:25	1	08:00	09:15
5	14:00	17:45	1	08:00	09:15
6	14:05	17:45	1	08:00	09:15
7	18:05	19:45	1	08:00	09:15
1	08:00	09:15	2	13:20	15:20
2	13:20	15:20	2	13:20	15:20
3	10:00	14:00	2	13:20	15:20
4	13:55	16:25	2	13:20	15:20
5	14:00	17:45	2	13:20	15:20
6	14:05	17:45	2	13:20	15:20
7	18:05	19:45	2	13:20	15:20
1	08:00	09:15	3	10:00	14:00
2	13:20	15:20	3	10:00	14:00
3	10:00	14:00	3	10:00	14:00
4	13:55	16:25	3	10:00	14:00
5	14:00	17:45	3	10:00	14:00
6	14:05	17:45	3	10:00	14:00
7	18:05	19:45	3	10:00	14:00
1	08:00	09:15	4	13:55	16:25
2	13:20	15:20	4	13:55	16:25
3	10:00	14:00	4	13:55	16:25
4	13:55	16:25	4	13:55	16:25
5	14:00	17:45	4	13:55	16:25
6	14:05	17:45	4	13:55	16:25
7	18:05	19:45	4	13:55	16:25
1	08:00	09:15	5	14:00	17:45
2	13:20	15:20	5	14:00	17:45
3	10:00	14:00	5	14:00	17:45
4	13:55	16:25	5	14:00	17:45
5	14:00	17:45	5	14:00	17:45
6	14:05	17:45	5	14:00	17:45
7	18:05	19:45	5	14:00	17:45
1	08:00	09:15	6	14:05	17:45
2	13:20	15:20	6	14:05	17:45
3	10:00	14:00	6	14:05	17:45
4	13:55	16:25	6	14:05	17:45
5	14:00	17:45	6	14:05	17:45
6	14:05	17:45	6	14:05	17:45
7	18:05	19:45	6	14:05	17:45
1	08:00	09:15	7	18:05	19:45
2	13:20	15:20	7	18:05	19:45
3	10:00	14:00	7	18:05	19:45
4	13:55	16:25	7	18:05	19:45
5	14:00	17:45	7	18:05	19:45
6	14:05	17:45	7	18:05	19:45
7	18:05	19:45	7	18:05	19:45

 第二步：利用图1所描述的关系进行行行比较判断。

最终SQL如下：

select distinct b.id
from meeting a,meeting b
where ((a.starttime >= b.starttime and a.starttime <= b.endtime)or (a.endtime >= b.starttime and a.endtime <= b.endtime))and a.id <> b.id

上述SQL可以进一步简化：图1中的三种情况只要满足如下表达式即都可以满足

三种情况合并为：

a.endtime >= b.starttime and  a.starttime <= b.endtime

最终优化调整后的SQL为：

select distinct b.id
from meeting a,meeting b
where a.endtime >= b.starttimeand a.starttime <= b.endtimeand a.id <> b.id

 

3 小结

本文利用SQL语言，通过两种方式给出了一种时间区间重叠问题的解决方案，并以实际场景为例子进行了详细讲解，其中方法2最为优雅，但需要通过区间讨论得出如下判断表达式，为本题的关键。

a.endtime >= b.starttime and  a.starttime <= b.endtime

对应图1关系为：

ct <= et   and  cet >= st

该表达式包含了图1三种 所有情况。

如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南

专栏 原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。

专栏主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

（2）数仓建模实战技巧和个人心得
       1）新人入职新公司后应如何快速了解业务？

       2）以业务视角看宽表化建设？

       3)  维度建模 or 关系型建模？

       4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

       5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系                           该由谁来搭建？

       6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

       7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

       8） 数据架构的选择，mpp or hadoop?

       9）数仓团队应如何体现自己的业务价值，讲好数据故事？

      10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关                          系？

     11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

具体专栏链接如下： 

 ​​​​​​数字化建设通关指南_莫叫石榴姐的博客-CSDN博客