Hive實戰(zhàn)—時間滑動窗口計算
關(guān)注公眾號：
大數(shù)據(jù)技術(shù)派，回復(fù)資料，領(lǐng)取1024G資料。
時間滑動計算
外部調(diào)用實現(xiàn)時間循環(huán)
自關(guān)聯(lián)實現(xiàn)滑動時間窗口
擴展基于自然周的的滾動時間窗口計算
總結(jié)
時間滑動計算
今天遇到一個需求大致是這樣的，我們有一個業(yè)務(wù)涉及到用戶打卡，用戶可以一天多次打卡，我們希望計算出7天內(nèi)打卡8次以上，且打卡時間分布在4天以上的時間，當(dāng)然這只是個例子，我們具體解釋一下這個需求
用戶一天可以打卡多次，所以要求打卡必須分布在4天以上； 7天不是一個自然周，而是某一天和接下來的6天，也就是說時間是是滑動的，窗口大小是7步長是1，說白了就是窗口計算；
其實說到這里你就想到了窗口函數(shù)，雖然這是一個窗口；但是hive卻沒有相應(yīng)的窗口函數(shù)可以計算，接下來我們看一下怎么實現(xiàn)這個邏輯。
外部調(diào)用實現(xiàn)時間循環(huán)
我們可以先寫這樣的一個SQL,就計算每個人在特定時間內(nèi)是否滿足我們的條件，我們先計算出每個人每天的打卡次數(shù)，例如這里我們的時間限制是'20210701' 到'20210707'
??select
?????b.union_id,to_date(ds,'yyyymmdd')?as?dt,count(1)?as?cnt
??from
????ods_la_daily_record_di?b
??where
????--?驅(qū)動表的時間限制
????b.ds>='${bizdate}'
????and?b.ds<=${bizdate2}'
??group?by
????b.union_id,ds
然后我們再判斷這個時間端內(nèi)，用戶的打卡情況是否滿足我們的條件
select
??union_id,count(1)?as?打卡天數(shù),?sum(cnt)?as?打卡次數(shù)
from
(
??select
?????b.union_id,ds,count(1)?as?cnt
??from
????ods_la_daily_record_di?b
??where
????--?驅(qū)動表的時間限制
????b.ds>='${bizdate}'
????and?b.ds<='${bizdate2}'
??group?by
????b.union_id,ds
)
group?by
??union_id
having
??--?時間分布在4天以上
??count(1)>=4
??--?打卡次數(shù)在8次以上
??and?sum(cnt)>=8
;
這樣我們就算出來我們需要的數(shù)據(jù)，接下來我們只需要用其他語言調(diào)用這個SQL ,傳入不同的時間參數(shù)就可以了，利用編程語言實現(xiàn)時間的滑動，例如第一次傳入'20210701-20210707' 第二次傳入'20210702-20210708' 以此傳入即可。
雖然可以實現(xiàn)，但是不好，因為我們還需要其他語言的調(diào)用，其實我們知道在SQL里面的關(guān)聯(lián)其實就是通過循環(huán)實現(xiàn)的，那我們即然能通過循環(huán)實現(xiàn)這個需求，我們能不能通過關(guān)聯(lián)實現(xiàn)這個需求呢
自關(guān)聯(lián)實現(xiàn)滑動時間窗口
其實我們只要讓用戶某一天的數(shù)據(jù)和他接下來的6天的數(shù)據(jù)關(guān)聯(lián)，然后按照這一天的數(shù)據(jù)進行匯總?cè)缓笈袛鄷r候滿足我們的條件即可，如果滿足了條件，那么用戶這一天的數(shù)據(jù)就是滿足我們的需求的，也就是說這個用戶是滿足我們的需求的。
with?tmp?as(
???--?每個人每天打卡的次數(shù)
??select
?????b.union_id,to_date(ds,'yyyymmdd')?as?dt,count(1)?as?cnt
??from
????ods_la_daily_record_di?b
??where
????--?驅(qū)動表的時間限制
????b.ds>='${bizdate}'
??group?by
????b.union_id,ds
)
select
??union_id
from?(
??--?滿足條件的(用戶-天)
??select
????a.union_id,a.dt,sum(b.cnt)?as?打卡次數(shù),count(1)?as?打卡天數(shù)
??from
????tmp?a
??inner?join
???tmp?b
??on
????a.union_id=b.union_id
????and?DATEDIFF(b.dt,a.dt)>=0
????and?DATEDIFF(b.dt,a.dt)<=6
??group?by
????a.union_id,a.dt
??having
????--?次數(shù)限制
????sum(b.cnt)>=8
????--?天數(shù)限制
????and?count(1)>=4
)group?by
??--?對用戶去重
??union_id
;
這里有一個問題需要注意一下，那就是我們滿足條件sum(b.cnt)>=8 and count(1)>=4 的是用戶某一天的數(shù)據(jù)，也就是說我們的維度是union_id-天，所以我們需要對這個數(shù)據(jù)按照用戶為度進行去重。
擴展基于自然周的的滾動時間窗口計算
我們這里思考一個問題，那就是我們知道很多時候我們的計算其實是圍繞著自然周的，雖然我們上面的計算不是自然周，那假設(shè)我們?nèi)绻笪覀兊挠嬎闶亲匀恢苣?，那這個時候我們應(yīng)該怎么計算呢，其實我們數(shù)倉里有一種很表叫做時間維表，我們利用時間維表可以很方便的計算時間相關(guān)的東西，如果你沒有的話建議去網(wǎng)上找一份，或者自己生成一份，因為使用起來很方便。
因為這個表的字段很多，這里我們截取了一部分放到這里了，下面我們看一下怎么使用時間維表進行計算。
select
???UNION_ID,time_weeknum,count(1)?as?打卡天數(shù),?sum(cnt)?as?打卡次數(shù)
from(
??select
?????b.union_id,ds,count(1)?as?cnt
??from
????ods_la_daily_record_di?b
??where
????--?驅(qū)動表的時間限制
????b.ds>='${bizdate}'
????and?b.ds<='${bizdate2}'
??group?by
????b.union_id,ds
)?a
left?join
dim_date_time?b
on
??a.ds=b.time_date
group?by
??--??周的標(biāo)識
??UNION_ID,time_weeknum
HAVING
??--?時間分布在4天以上
??count(1)>=4
??--?打卡次數(shù)在8次以上
??and?sum(cnt)>=8
;
這里我們就基于每個自然周算出了滿足條件的人，當(dāng)然我們還是要針對用戶去重
總結(jié)
我們看到自關(guān)聯(lián)其實可以達到滑動的效果，當(dāng)然不僅僅體現(xiàn)在時間上，就像窗口除了時間窗口還是有基于個數(shù)的窗口，我們要在遇到類似問題的時候就可以選擇這樣的解決方案。
時間維表很重要，可以簡化我們的計算，如果沒有的話，需要創(chuàng)建一個。
交流群
加我微信：ddxygq，回復(fù)加群，我拉你進技術(shù)交流群。
猜你喜歡
數(shù)倉建模—指標(biāo)體系
數(shù)倉建?！獙挶淼脑O(shè)計
Spark SQL知識點與實戰(zhàn)
Hive計算最大連續(xù)登陸天數(shù)
Flink計算pv和uv的通用方法