Hive實戰—時間滑動視窗計算

大資料技術派發表於2021-12-23
Hive

關注公眾號：大資料技術派，回覆: 資料，領取1024G資料。

目錄

時間滑動計算

今天遇到一個需求大致是這樣的，我們有一個業務涉及到使用者打卡，使用者可以一天多次打卡，我們希望計算出7天內打卡8次以上，且打卡時間分佈在4天以上的時間，當然這只是個例子，我們具體解釋一下這個需求

  1. 使用者一天可以打卡多次，所以要求打卡必須分佈在4天以上；
  2. 7天不是一個自然周，而是某一天和接下來的6天，也就是說時間是是滑動的，視窗大小是7步長是1，說白了就是視窗計算；

其實說到這裡你就想到了視窗函式，雖然這是一個視窗；但是hive卻沒有相應的視窗函式可以計算，接下來我們看一下怎麼實現這個邏輯

外部呼叫實現時間迴圈

我們可以先寫這樣的一個SQL,就計算每個人在特定時間內是否滿足我們的條件，我們先計算出每個人每天的打卡次數，例如這裡我們的時間限制是'20210701' 到'20210707'

  select
     b.union_id,to_date(ds,'yyyymmdd') as dt,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驅動表的時間限制
    b.ds>='${bizdate}'
    and b.ds<=${bizdate2}'
  group by
    b.union_id,ds

然後我們再判斷這個時間端內，使用者的打卡情況是否滿足我們的條件

select
  union_id,count(1) as 打卡天數, sum(cnt) as 打卡次數
from
(
  select
     b.union_id,ds,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驅動表的時間限制
    b.ds>='${bizdate}'
    and b.ds<='${bizdate2}'
  group by
    b.union_id,ds
)
group by
  union_id
having
  -- 時間分佈在4天以上
  count(1)>=4
  -- 打卡次數在8次以上
  and sum(cnt)>=8
;

這樣我們就算出來我們需要的資料，接下來我們只需要用其他語言呼叫這個SQL ,傳入不同的時間引數就可以了，利用程式語言實現時間的滑動，例如第一次傳入'20210701-20210707' 第二次傳入'20210702-20210708' 以此傳入即可。

雖然可以實現，但是不好，因為我們還需要其他語言的呼叫，其實我們知道在SQL裡面的關聯其實就是通過迴圈實現的，那我們即然能通過迴圈實現這個需求，我們能不能通過關聯實現這個需求呢

自關聯實現滑動時間視窗

其實我們只要讓使用者某一天的資料和他接下來的6天的資料關聯，然後按照這一天的資料進行彙總然後判斷時候滿足我們的條件即可，如果滿足了條件，那麼使用者這一天的資料就是滿足我們的需求的，也就是說這個使用者是滿足我們的需求的。

with tmp as(
   -- 每個人每天打卡的次數
  select
     b.union_id,to_date(ds,'yyyymmdd') as dt,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驅動表的時間限制
    b.ds>='${bizdate}'
  group by
    b.union_id,ds
)
select
  union_id
from (
  -- 滿足條件的(使用者-天)
  select
    a.union_id,a.dt,sum(b.cnt) as 打卡次數,count(1) as 打卡天數
  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
    -- 次數限制
    sum(b.cnt)>=8
    -- 天數限制
    and count(1)>=4
)group by
  -- 對使用者去重
  union_id
;

這裡有一個問題需要注意一下，那就是我們滿足條件sum(b.cnt)>=8 and count(1)>=4 的是使用者某一天的資料，也就是說我們的維度是union_id-天，所以我們需要對這個資料按照使用者為度進行去重。

擴充套件基於自然周的的滾動時間視窗計算

我們這裡思考一個問題，那就是我們知道很多時候我們的計算其實是圍繞著自然周的，雖然我們上面的計算不是自然周，那假設我們如果要求我們的計算是自然周呢，那這個時候我們應該怎麼計算呢，其實我們數倉裡有一種很表叫做時間維表，我們利用時間維表可以很方便的計算時間相關的東西，如果你沒有的話建議去網上找一份，或者自己生成一份，因為使用起來很方便。

因為這個表的欄位很多，這裡我們擷取了一部分放到這裡了，下面我們看一下怎麼使用時間維表進行計算。

select
   UNION_ID,time_weeknum,count(1) as 打卡天數, sum(cnt) as 打卡次數
from(
  select
     b.union_id,ds,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驅動表的時間限制
    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
  --  周的標識
  UNION_ID,time_weeknum
HAVING
  -- 時間分佈在4天以上
  count(1)>=4
  -- 打卡次數在8次以上
  and sum(cnt)>=8
;

這裡我們就基於每個自然周算出了滿足條件的人，當然我們還是要針對使用者去重

總結

我們看到自關聯其實可以達到滑動的效果，當然不僅僅體現在時間上，就像視窗除了時間視窗還是有基於個數的視窗，我們要在遇到類似問題的時候就可以選擇這樣的解決方案。

時間維表很重要，可以簡化我們的計算，如果沒有的話，需要建立一個。

交流群

加我微信：ddxygq，回覆加群，我拉你進技術交流群。

猜你喜歡
數倉建模—指標體系
數倉建模—寬表的設計
Spark SQL知識點與實戰
Hive計算最大連續登陸天數
Flink計算pv和uv的通用方法

相關文章