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

会飞的一十六 2024-11-11

196

点击上方【蓝色】字体关注我们

日常饮食应该注意哪些细节？如何提高睡眠质量？哪些坏习惯要尽量避免……随着生活质量的提高，越来越多人开始关注健康的生活方式。今天就和大家分享一些有利于健康的生活好习惯。

01 场景描述

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

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

02 数据准备

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')

03 问题分析

方法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) diff
      from meeting) t
         lateral view posexplode(split(space(cast(diff as int)), space(1))) tmp as pos, val

第二步：针对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 hour
      from (select id
                 , substr(starttime, 1, 2)                         starttime
                 , substr(endtime, 1, 2)                           endtime
                 , substr(endtime, 1, 2) - substr(starttime, 1, 2) diff
            from meeting) t
               lateral view posexplode(split(space(cast(diff as int)), space(1))) tmp as pos, val) t

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

select id
from (select id
           , hour
           , count(1) over (partition by hour) flg
      from (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) diff
                  from meeting) t
                     lateral view posexplode(split(space(cast(diff as int)), space(1))) tmp as pos, val) t) t
where flg >= 2
group by id

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

详情具体参考文章：

SQL进阶技巧：如何按任意时段分析时间区间问题？

具体讨论方法如下图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.starttime
  and a.starttime <= b.endtime
  and a.id <> b.id

04 小结

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

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

对应图1关系为：

ct <= et and cet >= st

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

会飞的一十六

扫描右侧二维码关注我们

点个【在看】你最好看

sql sql语言

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。