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