本文實踐了對于千萬級別的用戶，操作總數(shù)達萬級別，每日幾十億操作流水的留存分析工具秒級別查詢的數(shù)據(jù)構建方案。同時，除了留存分析，對于用戶群分析，事件分析等也可以嘗試用此方案來解決。

背景

你可能聽說過Growingio、神策等數(shù)據(jù)分析平臺，本文主要介紹實現(xiàn)留存分析工具相關的內(nèi)容。

留存分析是一種用來分析用戶參與情況/活躍程度的分析模型，可考查進行初始行為后的用戶中，有多少人會進行后續(xù)行為，這是衡量產(chǎn)品對用戶價值高低的重要指標。如，為評估產(chǎn)品更新效果或渠道推廣效果，我們常常需要對同期進入產(chǎn)品或同期使用了產(chǎn)品某個功能的用戶的后續(xù)行為表現(xiàn)進行評估 [1]。大部分數(shù)據(jù)分析平臺主要包括如圖的幾個功能(以神策為例)：

本文主要介紹留存分析工具的優(yōu)化方案(只涉及數(shù)據(jù)存儲和查詢的方案設計，不涉及平臺)。

我想每個數(shù)據(jù)/產(chǎn)品同學在以往的取數(shù)分析過程中，都曾有一個痛點，就是每次查詢留存相關的數(shù)據(jù)時，都要等到天荒地老，慢!而最近采用優(yōu)化方案的目的也是為了提高查詢的效率和減少數(shù)據(jù)的存儲，可以幫助產(chǎn)品快速地查詢/分析留存相關的數(shù)據(jù)。

優(yōu)化方案的核心是在Clickhouse中使用Roaringbitmap對用戶進行壓縮，將留存率的計算交給高效率的位圖函數(shù)，這樣既省空間又可以提高查詢速度。

希望本實踐方案可以給你帶來一些幫助和啟示。下面主要分3個部分詳細介紹：Roaringbitmap簡介、思路與實現(xiàn)、總結(jié)與思考。

Roaringbitmap簡介

下面先簡單介紹一下高效的位圖壓縮方法Roaringbitmap。先來看一個問題:

• 給定含有40億個不重復的位于[0,2^32-1]區(qū)間內(nèi)的整數(shù)集合，如何快速判定某個數(shù)是否在該集合內(nèi)?

顯然，如果我們將這40億個數(shù)原樣存儲下來，需要耗費高達14.9GB的內(nèi)存，這是難以接受的。所以我們可以用位圖(bitmap)來存儲，即第0個比特表示數(shù)字0，第1個比特表示數(shù)字1，以此類推。如果某個數(shù)位于原集合內(nèi)，就將它對應的位圖內(nèi)的比特置為1，否則保持為0，這樣就能很方便地查詢得出結(jié)果了，僅僅需要占用512MB的內(nèi)存，不到原來的3.4% [3]。但是這種方式也有缺點：比如我需要將1~5000w這5000w個連續(xù)的整數(shù)存儲起來，用普通的bitmap同樣需要消耗512M的存儲，顯然，對于這種情況其實有很大的優(yōu)化空間。

2016年由S. Chambi、D. Lemire、O. Kaser等人在論文《Better bitmap performance with Roaring bitmaps》與《Consistently faster and smaller compressed bitmaps with Roaring》中提出了roaringbitmap，主要特點就是可以極大程度地節(jié)約存儲及提供了快速的位圖計算，因此考慮用它來做優(yōu)化。對于前文提及的存儲連續(xù)的5000w個整數(shù)，只需要幾十KB。

它的主要思路是：將32位無符號整數(shù)按照高16位分桶，即最多可能有2^16 =65536個桶，論文內(nèi)稱為container。存儲數(shù)據(jù)時，按照數(shù)據(jù)的高16位找到container(找不到就會新建一個)，再將低16位放入container中。也就是說，一個roaringbitmap就是很多container的集合 [3]，具體細節(jié)可以自行查看文末的參考文章 。

思路與實現(xiàn)

我們的原始數(shù)據(jù)主要分為：

1. 用戶操作行為數(shù)據(jù)table_oper_raw 包括時間分區(qū)(ds)、用戶標識id(user_id)和用戶操作行為名稱(oper_name)，如：20200701|6053002|點擊首頁banner 表示用戶6053002在20200701這天點擊了首頁banner(同一天中同一個用戶多次操作了同一個行為只保留一條)。實踐過程中，此表每日記錄數(shù)達幾十億行。
2. 用戶屬性數(shù)據(jù)table_attribute_raw 表示用戶在產(chǎn)品/畫像中的屬性，包括時間分區(qū)(ds)、用戶標識(user_id)及各種用戶屬性字段(可能是用戶的新進渠道、所在省份等)，如20200701|6053002|小米商店|廣東省。實踐過程中，此表每日有千萬級的用戶數(shù)，測試屬性在20+個。

現(xiàn)在我們需要根據(jù)這兩類數(shù)據(jù)，求出某天操作了某個行為的用戶在后續(xù)的某一天操作了另一個行為的留存率，比如，在20200701這天操作了“點擊banner”的用戶有100個，這部分用戶在20200702這天操作了“點擊app簽到”的有20個，那么對于分析時間是20200701，且“點擊banner”的用戶在次日“點擊app簽到”的留存率是20%。同時，還需要考慮利用用戶屬性對留存比例進行區(qū)分，例如只考慮廣東省的用戶的留存率，或者只考慮小米商店用戶的留存率，或者在廣東的小米商店的用戶的留存率等等。

一般來說，求留存率的做法就是兩天的用戶求交集，例如前文說到的情況，就是先獲取出20200701的所有操作了“點擊banner”的用戶標識id集合假設為S1，然后獲取20200702的所有操作了“點擊app簽到”的用戶標識id集合假設為S2，最后求解S1和S2的交集：

可以看到，當s1和s2的集合中用戶數(shù)都比較大的時候，join的速度會比較慢。

在此我們考慮前文說到的bitmap，假若每一個用戶都可以表示成一個32位的無符號整型，用bitmap的形式去存儲，S1和S2的求交過程就是直接的一個位比較過程，這樣速度會得到巨大的提升。而Roaringbitmap對數(shù)據(jù)進行了壓縮，其求交的速度在絕大部分情況下比bitmap還要快，因此這里我們考慮使用Roaringbitmap的方法來對計算留存的過程進行優(yōu)化。

1.數(shù)據(jù)構建

整個過程主要是：首先對初始的兩張表——用戶操作數(shù)據(jù)表table_oper_raw和用戶篩選維度數(shù)據(jù)表table_attribute_raw中的user_id字段進行編碼，將每個用戶映射成唯一的id(32位的無符號整型)，分別得到兩個新表table_oper_middle和table_attribute_middle。再將他們導入clickhouse，使用roaringbitmap的方法對用戶進行壓縮存儲，最后得到壓縮后的兩張表table_oper_bit和table_attribute_bit，即為最終的查詢表。流程圖如下：

(1).生成用戶id映射表 首先，需要構建一個映射表table_user_map，包含時間分區(qū)(ds)、用戶標識id(user_d)及映射后的id(id)，它將每個用戶(String類型)映射成一個32位的無符號整型。這里我們從1開始編碼，這樣每個用戶的標識就轉(zhuǎn)化成了指定的一個數(shù)字。

(2).初始數(shù)據(jù)轉(zhuǎn)化 分別將用戶操作數(shù)據(jù)表和用戶篩選維度數(shù)據(jù)中的imei字段替換成對應的數(shù)值，生成編碼后的用戶操作數(shù)據(jù):和用戶篩選維度數(shù)據(jù):

(3).導入clickhouse 首先在clickhouse中創(chuàng)建相同結(jié)構的表，如table_oper_middle_ch。

同 樣的，在clickhouse中 創(chuàng)建表table_attribute_middle_ch。 然后用spark將這兩份數(shù)據(jù)分別導入這 兩張表。 這一步導入很快，幾十億的數(shù)據(jù)大概10分多鐘 就可以完成。

(4).Roaringbitmap壓縮 對于用戶操作流水數(shù)據(jù)，我們先建一個可以存放bitmap的表table_oper_bit，建表語句如下：用戶屬性數(shù)據(jù)table_attribute_bit也類似：這里索引粒度可設置小值，接著用聚合函數(shù)groupBitmapState對用戶id進行壓縮：這樣，對于用戶操作數(shù)據(jù)表，原本幾十億的數(shù)據(jù)就壓縮成了幾萬行的數(shù)據(jù)，每行包括操作名稱和對應的用戶id形成的bitmap：同樣的，用戶屬性的數(shù)據(jù)也可以這樣處理，得到table_attribute_bit表，每行包括某個屬性的某個屬性值對應的用戶的id形成的bitmap：至此，數(shù)據(jù)壓縮的過程就這樣完成了。

2. 查詢過程

首先，簡要地介紹下方案中常用的bitmap函數(shù)(詳細見文末的參考資料)：

• bitmapCardinality 返回一個UInt64類型的數(shù)值，表示bitmap對象的基數(shù)。用來計算不同條件下的用戶數(shù)，可以粗略理解為count(distinct)
• bitmapAnd 為兩個bitmap對象進行與操作，返回一個新的bitmap對象?？梢岳斫鉃橛脕頋M足兩個條件之間的and，但是參數(shù)只能是兩個bitmap
• bitmapOr 為兩個bitmap對象進行或操作，返回一個新的bitmap對象?？梢岳斫鉃橛脕頋M足兩個條件之間的or，但是參數(shù)也同樣只能是兩個bitmap。如果是多個的情況，可以嘗試使用groupBitmapMergeState

舉例來說，假設20200701這天只有[1,2,3,5,8]這5個用戶點擊了banner，則有：

# 返回5  
select bitmapCardinality ( user_bit )  
from tddb . table_oper_bit  
where ds = 20200701 AND oper_name =  
'點擊banner'  
又如果20200701從小米商店新進的用戶是[1,3,8,111,2000,100000]，則有：  
# 返回3,因為兩者的重合用戶只有1,3,8這3個用戶  
select bitmapCardinality ( bitmapAnd (  
( SELECT user_bit  
FROM tddb . table_oper_bit  
WHERE ( ds = 20200701 ) AND ( oper_name = '點擊banner' )),  
( SELECT user_bit  
FROM tddb . table_attribute_bit  
WHERE ds = 20200701 and ( attr_id = 'first_channel' ) and ( attr_value IN ( '小米商店'  
))))) 

有了以上的數(shù)據(jù)生成過程和bitmap函數(shù)，我們就可以根據(jù)不同的條件使用不同的位圖函數(shù)來快速查詢，具體來說，主要是以下幾種情況：

a. 操作了某個行為的用戶在后續(xù)某一天操作了另一個行為的留存：

• 如“20200701點擊了banner的用戶在次日點擊app簽到的留存人數(shù)”，就可以用以下的sql快速求解:

b. 操作了某個行為并且?guī)в心硞€屬性的用戶在后續(xù)的某一天操作了另一個行為的留存：

• 如“20200701點擊了banner且來自廣東/江西/河南的用戶在次日點擊app簽到的留存人數(shù)”：

c. 操作了某個行為并且?guī)в心硯讉€屬性的用戶在后續(xù)的某一天操作了另一個行為的留存：

• 如“20200701點擊了banner、來自廣東且新進渠道是小米商店的用戶在次日點擊app簽到的留存人數(shù)”：

3. 實踐效果

根據(jù)這套方案做了實踐，對每日按時間分區(qū)、用戶、操作名稱去重后包括幾十億的操作記錄，其中包含千萬級別的用戶數(shù)，萬級別的操作數(shù)。最后實現(xiàn)了：

• 存儲 原本每日幾十G的操作流水數(shù)據(jù)經(jīng)壓縮后得到的表table_oper_bit為4GB左右/天。而用戶屬性表table_attribute_bit為500MB左右/天
• 查詢速度 clickhouse集群現(xiàn)狀：12核125G內(nèi)存機器10臺。clickhouse版本:20.4.7.67。查詢的表都存放在其中一臺機器上。測試了查詢在20200701操作了行為oper_name_1(用戶數(shù)量級為3000+w)的用戶在后續(xù)7天內(nèi)每天操作了另一個行為oper_name_2(用戶數(shù)量級為2700+w)的留存數(shù)據(jù)(用戶重合度在1000w以上)，耗時0.2秒左右
• 反饋 最后和前端打通，效果也是有了明顯的優(yōu)化，麻麻再也不用擔心我會轉(zhuǎn)暈~

總結(jié)與思考

總的來說，本方案的優(yōu)點是：

• 存儲小，極大地節(jié)約了存儲;
• 查詢快，利用bitmapCardinality、bitmapAnd、bitmapOr等位圖函數(shù)快速計算用戶數(shù)和滿足一些條件的查詢，將緩慢的join操作轉(zhuǎn)化成位圖間的計算;
• 適用于靈活天數(shù)的留存查詢;
• 便于更新，用戶操作數(shù)據(jù)和用戶屬性數(shù)據(jù)分開存儲，便于后續(xù)屬性的增加和數(shù)據(jù)回滾。

另外，根據(jù)本方案的特點，除了留存分析工具，對于用戶群分析，事件分析等工具也可以嘗試用此方案來解決。