石家庄信息门户网站定制,深入浅出php,电子商务网站设计与网络营销实验,app制作开发公司目录
0 场景描述
1 数据准备
2 问题分析
方法1#xff1a;利用 lateral view posexplode()函数将表展开成时间明细表
方法2#xff1a;利用数学区间讨论思想求解
3 小结
如果觉得本文对你有帮助#xff0c;想进一步学习SQL语言这门艺术的#xff0c;那么不妨也可以选…目录
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 12过滤出大于等于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按照阶梯式增长直到恢复原价。
专栏主要内容 1SQL进阶实战技巧 可以参考如下教程具体链接如下
SQL很简单可你却写不好也许这才是SQL最好的教程
上面链接中的文章及技巧会不定期更新。
2数仓建模实战技巧和个人心得 1新人入职新公司后应如何快速了解业务 2以业务视角看宽表化建设 3) 维度建模 or 关系型建模 4业务模型与数据模型有什么区别业务阶段的模型该如何建设 5业务指标体系该如何建设指标体系该如何维护指标平台应如何建设指标体系 该由谁来搭建 6如何优雅设计DWS层DWS层模型好坏该如何评价 7指标发生异常该如何排查应从哪些方面入手寻找问题点 8 数据架构的选择mpp or hadoop? 9数仓团队应如何体现自己的业务价值讲好数据故事 10BI与大数据有什么关系BI与信息化、数字化之间有什么关系BI与报表之间的关 系 11数据部门如何与业务部门沟通并规划指引业务需求
文章不限于以上内容有新的想法也会及时更新到该专栏。
具体专栏链接如下 数字化建设通关指南_莫叫石榴姐的博客-CSDN博客
