wordpress主題安裝說明seo主要做什么
目錄
0 場景描述
1 數(shù)據(jù)準(zhǔn)備
2 問題分析
方法1：利用 lateral view posexplode()函數(shù)將表展開成時間明細(xì)表
方法2：利用數(shù)學(xué)區(qū)間討論思想求解
3 小結(jié)
如果覺得本文對你有幫助，想進(jìn)一步學(xué)習(xí)SQL語言這門藝術(shù)的，那么不妨也可以選擇去看看我的博客專欄 ，部分內(nèi)容如下：
數(shù)字化建設(shè)通關(guān)指南
專欄 原價99，現(xiàn)在活動價59.9，按照階梯式增長，直到恢復(fù)原價。
0 場景描述
有7個會議室，每個會議室每天都有人開會，某一天的開會時間如下：
查詢出開會時間有重疊的是哪幾個會議室？上面預(yù)期結(jié)果是 ID 2 3 4 5 6?
1 數(shù)據(jù)準(zhǔn)備
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()函數(shù)將表展開成時間明細(xì)表
具體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, valID 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) 進(jìn)行輔助標(biāo)記
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) t1 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，并去重獲取最終結(jié)果
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：利用數(shù)學(xué)區(qū)間討論思想求解
詳情具體參考文章：
SQL進(jìn)階技巧：如何按任意時段分析時間區(qū)間問題？ | 分區(qū)間討論【左、中、右】_sql按某時段日期區(qū)間聚合-CSDN博客
?具體討論方法如下圖1所示：
情況1：區(qū)間在右
判斷條件 cet >= et and ct <= et ? ? ? ? ?重疊區(qū)間為【ct,et]】
情況2：區(qū)間在內(nèi)
判斷條件為 ct>= st ?and cet <= et ? ? ?重疊區(qū)間為 【ct,cet】
情況3：區(qū)間在左
判斷條件 ct <= st ?and cet >= st ? ? ? ?重疊區(qū)間為【st,cet】
第一步：先自關(guān)聯(lián)，生成全量行行比較的數(shù)據(jù)集
select
from meeting a,meeting b1 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所描述的關(guān)系進(jìn)行行行比較判斷。
最終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可以進(jìn)一步簡化：圖1中的三種情況只要滿足如下表達(dá)式即都可以滿足
三種情況合并為：
a.endtime >= b.starttime and? a.starttime <= b.endtime
最終優(yōu)化調(diào)整后的SQL為：
select distinct b.id
from meeting a,meeting b
where a.endtime >= b.starttimeand a.starttime <= b.endtimeand a.id <> b.id?
3 小結(jié)
本文利用SQL語言，通過兩種方式給出了一種時間區(qū)間重疊問題的解決方案，并以實際場景為例子進(jìn)行了詳細(xì)講解，其中方法2最為優(yōu)雅，但需要通過區(qū)間討論得出如下判斷表達(dá)式，為本題的關(guān)鍵。
a.endtime >= b.starttime and? a.starttime <= b.endtime
對應(yīng)圖1關(guān)系為：
ct <= et? ?and??cet >= st
該表達(dá)式包含了圖1三種 所有情況。
如果覺得本文對你有幫助，想進(jìn)一步學(xué)習(xí)SQL語言這門藝術(shù)的，那么不妨也可以選擇去看看我的博客專欄 ，部分內(nèi)容如下：
數(shù)字化建設(shè)通關(guān)指南
專欄 原價99，現(xiàn)在活動價59.9，按照階梯式增長，直到恢復(fù)原價。
專欄主要內(nèi)容：
（1）SQL進(jìn)階實戰(zhàn)技巧
可以參考如下教程，具體鏈接如下
SQL很簡單，可你卻寫不好？也許這才是SQL最好的教程
上面鏈接中的文章及技巧會不定期更新。
（2）數(shù)倉建模實戰(zhàn)技巧和個人心得
? ? ? ?1）新人入職新公司后應(yīng)如何快速了解業(yè)務(wù)？
? ? ? ?2）以業(yè)務(wù)視角看寬表化建設(shè)？
? ? ? ?3) ?維度建模 or 關(guān)系型建模？
? ? ? ?4）業(yè)務(wù)模型與數(shù)據(jù)模型有什么區(qū)別？業(yè)務(wù)階段的模型該如何建設(shè)？
? ? ? ?5）業(yè)務(wù)指標(biāo)體系該如何建設(shè)？指標(biāo)體系該如何維護(hù)？指標(biāo)平臺應(yīng)如何建設(shè)？指標(biāo)體系 ? ? ? ? ? ? ? ? ? ? ? ? ? 該由誰來搭建？
? ? ? ?6）如何優(yōu)雅設(shè)計DWS層？DWS層模型好壞該如何評價？
? ? ? ?7）指標(biāo)發(fā)生異常，該如何排查？應(yīng)從哪些方面入手尋找問題點？
? ? ? ?8） 數(shù)據(jù)架構(gòu)的選擇，mpp or hadoop?
? ? ???9）數(shù)倉團(tuán)隊?wèi)?yīng)如何體現(xiàn)自己的業(yè)務(wù)價值，講好數(shù)據(jù)故事？
? ? ? 10）BI與大數(shù)據(jù)有什么關(guān)系？BI與信息化、數(shù)字化之間有什么關(guān)系？BI與報表之間的關(guān) ? ? ? ? ? ? ? ? ? ? ? ? ?系？
? ?? 11）數(shù)據(jù)部門如何與業(yè)務(wù)部門溝通，并規(guī)劃指引業(yè)務(wù)需求？
文章不限于以上內(nèi)容，有新的想法也會及時更新到該專欄。
具體專欄鏈接如下：?
???????數(shù)字化建設(shè)通關(guān)指南_莫叫石榴姐的博客-CSDN博客
?
?