[[341108]]

作者：xmxiong，PCG 運(yùn)營開發(fā)工程師

數(shù)據(jù)倉庫的數(shù)據(jù)統(tǒng)計(jì)，可以歸納為三類：增量類、累計(jì)類、留存類。而累計(jì)類又分為歷史至今的累計(jì)與最近一段時間內(nèi)的累計(jì)(比如滾動月活躍天，滾動周活躍天，最近 N 天消費(fèi)情況等)，借助 bitmap 思想統(tǒng)計(jì)的模型表可以快速統(tǒng)計(jì)最近一段時間內(nèi)的累計(jì)類與留存類。

一、背景

數(shù)據(jù)倉庫的數(shù)據(jù)統(tǒng)計(jì)，可以歸納為三類：增量類、累計(jì)類、留存類。而累計(jì)類又分為歷史至今的累計(jì)與最近一段時間內(nèi)的累計(jì)(比如滾動月活躍天，滾動周活躍天，最近 N 天消費(fèi)情況等)，借助 bitmap 思想統(tǒng)計(jì)的模型表可以快速統(tǒng)計(jì)最近一段時間內(nèi)的累計(jì)類與留存類。

二、業(yè)務(wù)場景

我們先來看幾個最近一段時間內(nèi)的累計(jì)類與留存類的具體業(yè)務(wù)問題，作為做大數(shù)據(jù)的你建議先不要急著往下閱讀，認(rèn)真思考一下你的實(shí)現(xiàn)方案：

1.統(tǒng)計(jì)最近 30 天用戶的累計(jì)活躍天(每個用戶在 30 天里有 N 天使用微視 app，N 為 1-30，然后將月活躍用戶的 N 天加總)?

2.統(tǒng)計(jì)最近 7 天的用戶累計(jì)使用時長?

3.統(tǒng)計(jì)最近 30 天有播放的累計(jì)用戶數(shù)?

4.統(tǒng)計(jì)最近 30 天活躍用戶有多少在最近 30 天里有連續(xù) 3 天及以上活躍?

5.統(tǒng)計(jì) 28 天前活躍用戶的 1、3、7、14、28 天留存率?

三、傳統(tǒng)解決方案

在進(jìn)入本文真正主題之前，我們先來看看常規(guī)的解決思路：1.統(tǒng)計(jì)最近 30 天用戶的累計(jì)活躍天?

--用dau表(用戶ID唯一)，取最近30天分區(qū)，sum(活躍日期)。 
select 
    sum(imp_date) active_date 
  from 
    weishi_dau_active_table 
  where 
    imp_date>=20200701 
    and imp_date<=20200730 

2.統(tǒng)計(jì)最近 7 天的用戶累計(jì)使用時長?

--用dau表(用戶ID唯一)，取最近7天分區(qū)，sum(使用時長)。 
select 
    sum(log_time) log_time 
  from 
    weishi_dau_active_table 
  where 
    imp_date>=20200701 
    and imp_date<=20200707 

3.統(tǒng)計(jì)最近 30 天有播放的累計(jì)用戶數(shù)?

--用用戶播放表(用戶ID唯一)，取最近30天分區(qū)，count(distinct if(播放次數(shù)>0,用戶ID,null))。 
select 
    count(distinct if(play_vv_begin>0,qimei,null)) play_user 
  from 
    weishi_play_active_table 
  where 
    imp_date>=20200701 
    and imp_date<=20200730 

4.統(tǒng)計(jì)最近 30 天活躍用戶有多少在最近 30 天里有連續(xù) 3 天及以上活躍?

--用dau表(用戶ID唯一)，取最近30天分區(qū)，關(guān)聯(lián)兩次最近30天分區(qū)，關(guān)聯(lián)條件右表分別為imp_date-1，imp_date-2。 
select 
    count(distinct a.qimei) active_num 
  from 
  ( select 
        imp_date 
        ,qimei 
      from 
        weishi_dau_active_table 
      where 
        imp_date>=20200701 
        and imp_date<=20200730 
   )a 
  join --第一次join，先取出連續(xù)2天的用戶，因?yàn)?月1日用戶與7月2號-1天關(guān)聯(lián)得上，表示一個用戶在1號和2號都活躍 
  ( select 
        date_sub(imp_date,1) imp_date 
        ,qimei 
      from 
        weishi_dau_active_table 
      where 
        imp_date>=20200701 
        and imp_date<=20200730 
   )b 
   on 
    a.imp_date=b.imp_date 
    and a.qimei=b.qimei 
  join --第二次join，取出連續(xù)3天的用戶，因?yàn)榈谝淮蝚oin已經(jīng)取出連續(xù)兩天活躍的用戶了，再拿這些7月1日用戶關(guān)聯(lián)7月3日-2天關(guān)聯(lián)得上，表示一個用戶在1號和3號都活躍，結(jié)合第一步j(luò)oin得出用戶至少3天連續(xù)活躍了 
  ( select 
        date_sub(imp_date,2) imp_date 
        ,qimei 
      from 
        weishi_dau_active_table 
      where 
        imp_date>=20200701 
        and imp_date<=20200730 
   )c 
   on 
    a.imp_date=c.imp_date 
    and a.qimei=c.qimei 

當(dāng)然這里也可以用窗口函數(shù) lead 來實(shí)現(xiàn)，通過求每個用戶后 1 條日期與后 2 條日期，再拿這兩個日期分布 datediff 當(dāng)前日期是否為日期相差 1 且相差 2 來判斷是否 3 天以上活躍，但是這個方法也還是避免不了拿 30 天分區(qū)統(tǒng)計(jì)，統(tǒng)計(jì)更多天連續(xù)活躍時的擴(kuò)展性不好的情況 5.統(tǒng)計(jì) 28 天前活躍用戶的 1、3、7、14、28 天留存率?

--用dau表(用戶ID唯一)，取統(tǒng)計(jì)天的活躍用戶 left join 1、3、7、14、28天后的活躍用戶，關(guān)聯(lián)得上則說明對應(yīng)天有留存。 
select 
    '20200701' imp_date 
    ,count(distinct if(date_sub=1,b.qimei,null))/count(distinct a.qimei) 1d_retain_rate 
    ,count(distinct if(date_sub=3,b.qimei,null))/count(distinct a.qimei) 3d_retain_rate 
    ,count(distinct if(date_sub=7,b.qimei,null))/count(distinct a.qimei) 7d_retain_rate 
    ,count(distinct if(date_sub=14,b.qimei,null))/count(distinct a.qimei) 14d_retain_rate 
    ,count(distinct if(date_sub=28,b.qimei,null))/count(distinct a.qimei) 28d_retain_rate 
  from 
    weishi_dau_active_table partition (p_20200701)a 
  left join 
  ( select 
        datediff(imp_date,'20200701') date_sub 
        ,qimei 
      from 
        weishi_dau_active_table 
      where 
        datediff(imp_date,'20200701') in (1,3,7,14,28) 
   )b 
   on 
    a.qimeib=b.qimei 

四、傳統(tǒng)解決方案存在的問題

1.每天大量中間數(shù)據(jù)重復(fù)計(jì)算，比如昨天最近 30 天是 8 月 1 日～ 8 月 30 日，今天最近 30 天為 8 月 2 日～ 8 月 31 日，中間 8 月 2 日～ 8 月 30 日就重復(fù)計(jì)算了。

2.統(tǒng)計(jì)邏輯復(fù)雜，類似業(yè)務(wù)場景 4，困難點(diǎn)在于統(tǒng)計(jì)每一天活躍的用戶第二天是否還繼續(xù)活躍。

3.耗費(fèi)集群資源大，場景 4 和場景 5 都用到了 join 操作，場景 4 還不止一個 join，join 操作涉及 shuffle 操作，shuffle 操作需要大量的網(wǎng)絡(luò) IO 操作，因此在集群中是比較耗性能的，我們應(yīng)該盡量避免執(zhí)行這樣的操作。

4.以上統(tǒng)計(jì)邏輯可擴(kuò)展性差，由于數(shù)據(jù)分析經(jīng)常進(jìn)行探索性分析，上面?zhèn)鹘y(tǒng)方案能解決上面幾個問題，但是數(shù)據(jù)分析稍微改變一下需求，就得重新開發(fā)，例如增加一個 15 天留存，或者統(tǒng)計(jì)最近 2 周的活躍天等。

五、bitmap 原理

上面的業(yè)務(wù)場景能否在一個模型表很簡單就能統(tǒng)計(jì)出，且不需要數(shù)據(jù)重復(fù)計(jì)算，也不需要 join 操作，還能滿足數(shù)據(jù)分析更多指標(biāo)探索分析呢?答案是肯定的，可以借助 bitmap 思想。

何為 bitmap?bitmap 就是用一個 bit 位來標(biāo)記某個元素，而數(shù)組下標(biāo)是該元素，該元素是否存在時用 bit 位的 1,0 表示。比如 10 億個 int 類型的數(shù)，如果用 int 數(shù)組存儲的話，那么需要大約 4G 內(nèi)存，當(dāng)我們用 int 類型來模擬 bitmap 時，一個 int 4 個字節(jié)共 4*8 = 32 位，可以表示 32 個數(shù)，原來 10 億個 int 類型的數(shù)用 bitmap 只需要 4GB / 32 = 128 MB 的內(nèi)存。

六、具體實(shí)現(xiàn)過程

大數(shù)據(jù)開發(fā)參考 bitmap 思想，就是參考其通過數(shù)組下標(biāo)表示該元素的思想，將最近 31 天活躍用戶是否活躍用逗號分隔的 0 1 串存儲下來，將最近 31 天的播放 vv、贊轉(zhuǎn)評等消費(fèi)數(shù)也用逗號分隔的具體數(shù)值存儲下來，形成一個字符數(shù)組，數(shù)組每一個下標(biāo)表示距離最新一天數(shù)據(jù)的天數(shù)差值，第一位下標(biāo)為 0，表示距離今天最新一天數(shù)據(jù)間隔為 0 天，如下所示：

active_date_set 表示 31 天活躍集，0 表示對應(yīng)下標(biāo)(距離今天的 N 天前)不活躍，1 表示活躍;這個數(shù)據(jù)是 8 月 23 日統(tǒng)計(jì)的，1,0,0,1,…… 即用戶在 8 月 23 日，8 月 20 日有活躍，8 月 22 日，8 月 21 日并沒有活躍。play_vv_begin_set 表示 31 天播放 vv 集，0 表示對應(yīng)下標(biāo)(距離今天的 N 天前)沒有播放視頻，正整數(shù)表示當(dāng)天的播放視頻次數(shù);這里用戶雖然在 8 月 23 日，8 月 20 日有活躍，但是該用戶一天只播放了一次視頻就離開微視了。這樣做的好處一方面也是大大壓縮了存儲，極端狀態(tài)下用戶 31 天都來，那么就可以將 31 行記錄壓縮在一行存儲。

假如 1 天活躍用戶 1 億，且這些用戶 31 天都活躍，那么就可以將 31 億行記錄壓縮在 1 億行里，當(dāng)然實(shí)際不會出現(xiàn)這樣的情況，因?yàn)闀幸徊糠掷嫌脩袅魇?，一部分新用戶加入，按照目前微視的統(tǒng)計(jì)可以節(jié)省 80%多的存儲;另一方面可以更簡單快捷地統(tǒng)計(jì)每個用戶最近一個月在微視的活躍與播放、消費(fèi)(贊轉(zhuǎn)評)等情況。

該模型表的詳細(xì)實(shí)現(xiàn)過程如下：

1.該模型表的前 31 天需要初始化一個集合，將第一天的數(shù)據(jù)寫到該表，然后一天一天滾動壘起來，累計(jì) 31 天之后就得到這個可用的集合表了，也就可以例行化跑下去。

2.最新一天需要統(tǒng)計(jì)時，需要拿前一天的集合表，剔除掉相對今天來說第 31 天前的數(shù)據(jù)，然后每個集合字段將最后一位刪除掉 。

3.拿最新一天的增量數(shù)據(jù)(下面用 A 表替代) full join 第 2 步處理后的前一天表(下面用 B 表替代)關(guān)聯(lián)。

這里有三種情況需要處理：

a.既出現(xiàn)在 A 表，也出現(xiàn)在 B 表，這種情況，只需直接拼接 A 表的最新值與 B 表的數(shù)組集即可(在微視里就是最近 30 天用戶有活躍，且在最新一天有留存);

b.只出現(xiàn)在 B 表(在微視里是最近 30 天活躍的用戶在最新一天沒留存)，這時需要拿 “0,” 拼接一個 B 表的數(shù)組集，“0,” 放在第一位;

c.只出現(xiàn)在 A 表(在微視里是新用戶或者 31 天前活躍的回流用戶)，這時需要拿 “1,”拼接一個 30 位長的默認(rèn)數(shù)組集 “0,0,0,…,0,0” ，“1,” 放在第一位。經(jīng)過如此幾步，就可以生成最新一天的集合表了，具體脫敏代碼如下：

select 
    20200823 imp_date 
    ,nvl(a.qimei,b.qimei) qimei 
    ,case 
       when a.qimei=b.qimei then concat(b.active_date_set,',',a.active_date_set) 
       when b.qimei is null then concat('0,',a.active_date_set) 
       when a.qimei is null then concat(b.active_date_set,',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') 
      end active_date_set 
    ,case 
       when a.qimei=b.qimei then concat(b.log_num_set,',',a.log_num_set) 
       when b.qimei is null then concat('0,',a.log_num_set) 
       when a.qimei is null then concat(b.log_num_set,',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') 
      end log_num_set 
    ,case 
       when a.qimei=b.qimei then concat(b.log_time_set,',',a.log_time_set) 
       when b.qimei is null then concat('0,',a.log_time_set) 
       when a.qimei is null then concat(b.log_time_set,',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') 
      end log_time_set 
    ,case 
       when a.qimei=b.qimei then concat(b.play_vv_begin_set,',',a.play_vv_begin_set) 
       when b.qimei is null then concat('0,',a.play_vv_begin_set) 
       when a.qimei is null then concat(b.play_vv_begin_set,',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') 
      end play_vv_begin_set 
  from 
  ( select 
        qimei 
        ,substr(active_date_set,1,instr(active_date_set,',',1,30)-1) active_date_set 
        ,substr(log_num_set,1,instr(log_num_set,',',1,30)-1) log_num_set 
        ,substr(log_time_set,1,instr(log_time_set,',',1,30)-1) log_time_set 
        ,substr(play_vv_begin_set,1,instr(play_vv_begin_set,',',1,30)-1) play_vv_begin_set 
      from 
        weishi_31d_active_set_table partition(p_20200822)a 
      where 
        last_time>=20200723 
   )a 
  full join 
  ( select 
        qimei 
        ,'1' active_date_set 
        ,cast(log_num as string) log_num_set 
        ,cast(log_time as string) log_time_set 
        ,cast(play_vv_begin as string) play_vv_begin_set 
      from 
        weishi_dau_active_table partition(p_20200823)a 
   )b 
   on 
    a.qimei=b.qimei 

初始化集合代碼相對簡單，只需保留第一位為實(shí)際數(shù)值，然后拼接一個 30 位的默認(rèn)值 0 串，初始化脫敏代碼如下：

select 
    20200823 imp_date 
    ,qimei 
    ,'1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0' active_date_set 
    ,concat(cast(log_num as string),',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') log_num_set 
    ,concat(cast(log_time as string),',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') log_time_set 
    ,concat(cast(play_vv_begin as string),',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0') play_vv_begin_set 
  from 
    weishi_dau_active_table partition(p_20200823)a 

七、具體使用案例

在 hive 里對這些 0 1 集合串的使用是比較困難的，為了讓這個模型表的可用性更高，因此寫了幾個 UDF 函數(shù)來直接對數(shù)組集合進(jìn)行簡單地運(yùn)算，目前寫了如下幾個：str_sum()、str_count()、str_min()、str_max()，其中 str_sum、str_min、str_max 這幾個函數(shù)的參數(shù)一樣，第一個傳入一個數(shù)組集合字符串，第二位傳入一個整數(shù)，代表要計(jì)算最近 N 天的結(jié)果，第三個參數(shù)是傳入一個分隔符，在本模型里分隔符均為逗號“,”。

這幾個函數(shù)都是返回一個 int 值，str_sum 返回來的是最近 N 天的數(shù)值加總，str_min 返回該數(shù)組集合元素里最小的值，str_max 返回該數(shù)組集合元素里最大的值;str_count 前 3 個參數(shù)與前面三個函數(shù)一樣，第 4 個參數(shù)是傳入要統(tǒng)計(jì)的值，返回來的也是 int 值，返回傳入的統(tǒng)計(jì)值在數(shù)組集合出現(xiàn)的次數(shù)，具體使用方法如下，由于是自定義函數(shù)，在 tdw 集群跑的 sql 前面需加@pyspark：

以上函數(shù)的具體使用案例脫敏代碼如下：

@pysparkselect 
    qimei 
    ,str_sum(active_date_set,30,',') active_date_num  --每個用戶最近30天活躍天數(shù) 
    ,str_sum(play_vv_begin_set,30,',') play_vv_begin  --每個用戶最近30天播放視頻次數(shù) 
    ,30 - str_count(interact_num_set,30,',','0') interact_date_num  --每個用戶最近30天有互動的天數(shù)，通過 30 - 互動天數(shù)為0 統(tǒng)計(jì)得到 
  from 
    weishi_31d_active_set_table partition(p_20200823)a 
  where 
    last_time>20200724 

當(dāng)然除了上面幾種 udf 統(tǒng)計(jì)所需指標(biāo)之外，也可以通過正則表達(dá)式進(jìn)行使用，比如統(tǒng)計(jì)活躍天可以這樣統(tǒng)計(jì)：

--將數(shù)組集合里的'0'和','用正則表達(dá)式匹配去掉再來看剩下1的個數(shù)即可。 
select 
    count(qimei) --月活 
    ,sum(length(regexp_replace(substr(active_date_set,1,60),'0|,',''))) active_date_num  --月活躍天 
  from 
    weishi_31d_active_set_table partition(p_20200823)a 
  where 
    last_time>20200724 

開篇前的幾個業(yè)務(wù)場景，也可以通過該表快速統(tǒng)計(jì)：1.統(tǒng)計(jì)最近 30 天用戶的累計(jì)活躍天?

@pyspark 
select 
    sum(active_date_num) active_date_num  --滾動月活躍天 
    ,count(1) uv  --滾動月活 
  from 
  ( select 
        qimei 
        ,str_sum(active_date_set,30,',') active_date_num 
      from 
        weishi_31d_active_set_table partition(p_20200823)a 
      where 
        last_time>20200724 
   )a 

2.統(tǒng)計(jì)最近 7 天的用戶累計(jì)使用時長?

@pyspark 
select 
    sum(log_time) log_time  --滾動周活躍天 
    ,count(1) uv  --滾動周活 
  from 
  ( select 
        qimei 
        ,str_sum(log_time_set,7,',') log_time 
      from 
        weishi_31d_active_set_table partition(p_20200823)a 
      where 
        last_time>20200817 
   )a 

3.統(tǒng)計(jì)最近 30 天有播放的累計(jì)用戶數(shù)?

@pyspark 
select 
    count(1) uv  --播放次數(shù)>0 
  from 
  ( select 
        qimei 
        ,str_sum(play_vv_begin_set,30,',','0') play_vv_begin 
      from 
        weishi_31d_active_set_table partition(p_20200823)a 
      where 
        last_time>20200724 
   )a 
  where 
    play_vv_begin>0 

4.統(tǒng)計(jì)最近 30 天活躍用戶有多少在最近 30 天里有連續(xù) 3 天及以上活躍?

--只是判斷活躍集合里面有連續(xù)3位 1,1,1, 即可select 
    count(if(substr(active_date_set,1,60) like '%1,1,1,%',qimei,null)) active_date_num 
  from 
    weishi_31d_active_set_table partition(p_20200823)a 
  where 
    last_time>20200724 

5.統(tǒng)計(jì) 28 天前活躍用戶的 1、3、7、14、28 天留存率?

--不需要join操作，只需找到活躍日期集對應(yīng)位是否1即可select 
    '20200723' imp_date 
    ,count(if(split(active_date_set,',')['29']='1',qimei,null))/count(1) 1d_retain_rate 
    ,count(if(split(active_date_set,',')['27']='1',qimei,null))/count(1) 3d_retain_rate 
    ,count(if(split(active_date_set,',')['23']='1',qimei,null))/count(1) 7d_retain_rate 
    ,count(if(split(active_date_set,',')['16']='1',qimei,null))/count(1) 14d_retain_rate 
    ,count(if(split(active_date_set,',')['2']='1',qimei,null))/count(1) 28d_retain_rate 
  from 
    weishi_31d_active_set_table partition(p_20200823)a 
  where 
    last_time>20200723 
    and split(active_date_set,',')['30']='1' 

八、總結(jié)

從上面 5 個業(yè)務(wù)場景可以看出來，只要有這樣一個借助 bitmap 思想統(tǒng)計(jì)的模型表，不管統(tǒng)計(jì)最近一段時間的累計(jì)(月活躍天、月播放用戶等)與統(tǒng)計(jì) 1 個月內(nèi)的留存，都可以一條簡單語句即可統(tǒng)計(jì)，不需要 join 操作，每天例行化跑時不需要重復(fù)跑接近一個月的分區(qū)，1 個月內(nèi)可以支持任意統(tǒng)計(jì)，比如只需最近 2 周的活躍天等，因此這樣的模型相對通用，另外如果業(yè)務(wù)需要用到 2 個月的數(shù)據(jù)，也可以將模型從 31 位擴(kuò)展到 61 位。

當(dāng)然任何事情不可能只有優(yōu)點(diǎn)，而不存在缺點(diǎn)的情況，這里這個優(yōu)化的模型只是參考了 bitmap 思想，并不是 bitmap 方案實(shí)現(xiàn)，雖然可以將 31 天活躍用戶壓縮 80%多存儲，但是每天都存儲 31 天活躍用戶的壓縮數(shù)據(jù)，因此相比之前只保留天增量表來說，還是增加了實(shí)際存儲空間，但是這個以存儲換計(jì)算的方案是符合數(shù)倉設(shè)計(jì)原則的，因?yàn)橛?jì)算是用成本昂貴的 cpu 和內(nèi)存資源，存儲是用成本低廉的磁盤資源，因此有涉及最近 N 天累計(jì)或者留存計(jì)算需求的朋友可以借鑒這樣的思路。

【本文為51CTO專欄作者“騰訊技術(shù)工程”原創(chuàng)稿件，轉(zhuǎn)載請聯(lián)系原作者（微信號：Tencent_TEG）】

戳這里，看該作者更多好文