ClickHouse留存分析工具十亿数据秒级查询措施

背景

你可能听说过Growingio、神策等数据分析平台，本文主要介绍实现留存分析工具相关的内容。

留存分析是一种用来分析用户参与情况/活跃程度的分析模型，可考查进行初始行为后的用户中，有多少人会进行后续行为，这是衡量产品对用户价值高低的重要指标。如，为评估产品更新效果或渠道推广效果，我们常常需要对同期进入产品或同期使用了产品某个功能的用户的后续行为表现进行评估 [1]。大部分数据分析平台主要包括如图的几个功能(以神策为例)：

ClickHouse留存分析工具十亿数据秒级查询方案

本文主要介绍留存分析工具的优化方案(只涉及数据存储和查询的方案设计，不涉及平台)。

我想每个数据/产品同学在以往的取数分析过程中，都曾有一个痛点，就是每次查询留存相关的数据时，都要等到天荒地老，慢!而最近采用优化方案的目的也是为了提高查询的效率和减少数据的存储，可以帮助产品快速地查询/分析留存相关的数据。

优化方案的核心是在Clickhouse中使用Roaringbitmap对用户进行压缩，将留存率的计算交给高效率的位图函数，这样既省空间又可以提高查询速度。

希望本实践方案可以给你带来一些帮助和启示。下面主要分3个部分详细介绍：Roaringbitmap简介、思路与实现、总结与思考。

Roaringbitmap简介

下面先简单介绍一下高效的位图压缩方法Roaringbitmap。先来看一个问题:

给定含有40亿个不重复的位于[0,2^32-1]区间内的整数集合，如何快速判定某个数是否在该集合内?

显然，如果我们将这40亿个数原样存储下来，需要耗费高达14.9GB的内存，这是难以接受的。所以我们可以用位图(bitmap)来存储，即第0个比特表示数字0，第1个比特表示数字1，以此类推。如果某个数位于原集合内，就将它对应的位图内的比特置为1，否则保持为0，这样就能很方便地查询得出结果了，仅仅需要占用512MB的内存，不到原来的3.4% [3]。但是这种方式也有缺点：比如我需要将1~5000w这5000w个连续的整数存储起来，用普通的bitmap同样需要消耗512M的存储，显然，对于这种情况其实有很大的优化空间。

2016年由S. Chambi、D. Lemire、O. Kaser等人在论文《Better bitmap performance with Roaring bitmaps》与《Consistently faster and smaller compressed bitmaps with Roaring》中提出了roaringbitmap，主要特点就是可以极大程度地节约存储及提供了快速的位图计算，因此考虑用它来做优化。对于前文提及的存储连续的5000w个整数，只需要几十KB。

它的主要思路是：将32位无符号整数按照高16位分桶，即最多可能有2^16 =65536个桶，论文内称为container。存储数据时，按照数据的高16位找到container(找不到就会新建一个)，再将低16位放入container中。也就是说，一个roaringbitmap就是很多container的集合 [3]，具体细节可以自行查看文末的参考文章 。

思路与实现

我们的原始数据主要分为：

用户操作行为数据table_oper_raw 包括时间分区(ds)、用户标识id(user_id)和用户操作行为名称(oper_name)，如：20200701|6053002|点击首页banner 表示用户6053002在20200701这天点击了首页banner(同一天中同一个用户多次操作了同一个行为只保留一条)。实践过程中，此表每日记录数达几十亿行。

用户属性数据table_attribute_raw 表示用户在产品/画像中的属性，包括时间分区(ds)、用户标识(user_id)及各种用户属性字段(可能是用户的新进渠道、所在省份等)，如20200701|6053002|小米商店|广东省。实践过程中，此表每日有千万级的用户数，测试属性在20+个。

现在我们需要根据这两类数据，求出某天操作了某个行为的用户在后续的某一天操作了另一个行为的留存率，比如，在20200701这天操作了“点击banner”的用户有100个，这部分用户在20200702这天操作了“点击app签到”的有20个，那么对于分析时间是20200701，且“点击banner”的用户在次日“点击app签到”的留存率是20%。同时，还需要考虑利用用户属性对留存比例进行区分，例如只考虑广东省的用户的留存率，或者只考虑小米商店用户的留存率，或者在广东的小米商店的用户的留存率等等。

一般来说，求留存率的做法就是两天的用户求交集，例如前文说到的情况，就是先获取出20200701的所有操作了“点击banner”的用户标识id集合假设为S1，然后获取20200702的所有操作了“点击app签到”的用户标识id集合假设为S2，最后求解S1和S2的交集：

ClickHouse留存分析工具十亿数据秒级查询方案

可以看到，当s1和s2的集合中用户数都比较大的时候，join的速度会比较慢。