ClickHouse实战留存、路径、漏斗、session

什么是留存，比如在20200701这天操作了“点击banner”的用户有100个，这部分用户在20200702这天操作了“点击app签到”的有20个，那么对于分析时间是20200701，且“点击banner”的用户在次日“点击app签到”的留存率是20%。

关于用户留存模型是各大商业数据分析平台必不可少的功能，企业一般用该模型衡量用户的活跃情况，也是能直接反应产品功能价值的直接指标；如，boss想要了解商城改版后，对用户加购以及后续下单情况的影响等。如下图，这就是一个典型的留存分析功能：

问题

通常实现上述需求的传统做法是多表关联，了解clickhouse的攻城狮都清楚，多表关联简直就是clickhouse的天敌；如一张用户行为日志表中至少包含：用户id、行为事件、操作时间、地点属性等，想分析20200909日河南省注册用户次日的下单情况，那么SQL一般会这么写：

select count(distinct t1.uid) r1, count(distinct t2.uid) r2 from

这种方式书写简单、好理解，但是性能会很差，在超大数据集上进行运算是不仅仅影响用户体验，还会因长期占有物理资源而拖垮整个clickhouse上的业务。

解决方法有两种：

使用clickhouse自带的retention函数
Roaringbitmap 通过对数据进行压缩和位运算提高查询性能

Roaringbitmap

通过Roaringbitmap进行用户行为分析是腾讯广告业务中常用的一种实现方案，点击查看，文章中内容较多这里挑选干货进行讲解：

bitmap可以理解为一个长度很长且只存储0/1数字的集合，如某个用户通过特定的哈希算法映射到位图内时，那么该位置就会被置为1，否则为0；通过这种方式对数据进行压缩，空间利用率可提示数十倍，数据可以很容易被系统cache，大大减少了IO操作。

在查询之前需要先对数据进行预处理，这里额外构建两张表，用来存储用户的位图信息。

用户行为日志表：

table_oper_bit

ClickHouse实战留存、路径、漏斗、session

向位图表插入数据，原始数据十几亿，插入后结果只有几万行，而且随着数据范围的再扩大，位图表的数据增量变化也不会很明显

ClickHouse实战留存、路径、漏斗、session

用户基本信息表：table_attribute_bit

ClickHouse实战留存、路径、漏斗、session

同理table_attribute_bit插入后数据也得到了极大的压缩，最终数据如下图：

ClickHouse实战留存、路径、漏斗、session

应用案例

a. 操作了某个行为的用户在后续某一天操作了另一个行为的留存：
如“20200701点击了banner的用户在次日点击app签到的留存人数”，就可以用以下的sql快速求解:

ClickHouse实战留存、路径、漏斗、session

b. 操作了某个行为并且带有某个属性的用户在后续的某一天操作了另一个行为的留存：
如“20200701点击了banner且来自广东/江西/河南的用户在次日点击app签到的留存人数”：

ClickHouse实战留存、路径、漏斗、session

c. 操作了某个行为并且带有某几个属性的用户在后续的某一天操作了另一个行为的留存：
如“20200701点击了banner、来自广东且新进渠道是小米商店的用户在次日点击app签到的留存人数”：

ClickHouse实战留存、路径、漏斗、session

其中bitmapCardinality用来计算位图中不重复数据个数，在大数据量下会有一定的数据误差，bitmapAnd用来计算两个bitmap的与操作，即返回同时出现在两个bitmap中用户数量

查询速度
clickhouse集群现状：

12核125G内存机器10台。

clickhouse版本:20.4.7.67。

查询的表都存放在其中一台机器上。

测试了查询在20200701操作了行为oper_name_1(用户数量级为3000+w)的用户在后续7天内每天操作了另一个行为oper_name_2(用户数量级为2700+w)的留存数据(用户重合度在1000w以上)，耗时0.2秒左右

该方法的确比较灵活，不仅仅能解决留存问题，还有很多关于事件分析的需求等待我们去探索；然而它的缺点是操作复杂，且不支持对实时数据的分析

retention

通过上面的例子不难看出，腾讯的做法虽然提升了查询的性能，但是操作过于复杂，不便于用户理解和后期的维护；关于这些痛点易企秀数仓这边做法是采用retention进行实现
retention function是clickhouse中高级聚合函数，较bitmap的方式实现留存分析会更加简单、高效；语法如下：

retention(cond1, cond2, ..., cond32);

其中满足条件1的数据会置为1，之后的每一个表达式成立的前提都要建立在条件1成立的基础之上，这正好符合我们对留存模型的定义那么我们还以上面的3个场景为例方便对比说明：

20200701点击了banner的用户在次日点击app签到的留存人数

SELECT
20200701点击了banner且来自广东/江西/河南的用户在次日点击app签到的留存人数

SELECT
按照上面的方式第三个场景也能很快实现，这里留给大家去尝试...

不过该方式与bitmap比也有缺陷，那就是如果用户日志表中不存储用户属性信息时，就需要与用户属性表进行关联查询，两张大表关联，查询性能会相当慢。

什么是有序漏斗，有序漏斗需要满足所有用户事件链上的操作都是逡巡时间先后关系的，且漏斗事件不能有断层，触达当前事件层的用户也需要经历前面的事件层

接上一章智能路径分析，假设我们已经得到了触达支付购买的路径有 “首页->详情页->购买页->支付“ 和 “搜索页->详情页->购买页->支付“ 两个主要路径，但是我们不清楚哪条路径转化率高，那么这个时候漏斗分析就派上用场了

漏斗模型是一个倒置的金字塔形状，主要用来分析页面与页面功能模块之前的转化情况，下面一层都是基于紧邻的上一层转化而来的，也就是说前一个条件是后一个条件成立的基础；解决此类场景clickhouse提供了一个名叫windowFunnel的函数来实现：

windowFunnel(window)(timestamp, cond1, cond2, ..., condN)

window：

窗口大小，从第一个事件开始，往后推移一个窗口大小来提取事件数据
timestamp：

可以是时间或时间戳类型，用来对时事件进行排序
cond：

每层满足的事件

为了便于大家理解，这里举个简单的栗子：

CREATE TABLE test.action

插入测试数据

insert into action values(1,'浏览','2020-01-02 11:00:00');

已30分钟作为一个时间窗口，看下windowFunnel返回了什么样的数据

SELECT 

这里level只记录了路径中最后一次事件所属的层级，如果直接对level分组统计就会丢失之前的层级数据，导致漏斗不能呈现金字塔状

模型

继续使用上面的测试数据，通过数组的高阶函数对上述结果数据进行二次加工处理以获取完整漏斗展示效果。

案例
分析"2020-01-02"这天路径为“浏览->点击->下单->支付”的转化情况。

SELECT level_index,count(1) FROM

ClickHouse实战留存、路径、漏斗、session

为什么要有路径分析，举个最简单的例子，你的领导想要知道用户在完成下单前的一个小时都做了什么？绝大多数人拿到这个需求的做法就是进行数据抽样观察以及进行一些简单的问卷调参工作，这种方式不但费时费力还不具有代表性，那么这个时候你就需要一套用户行为路径分析的模型作为支撑，才能快速帮组你找到最佳答案

clickhouse是我见过最完美的OLAP数据库，它不仅将性能发挥到了极致，还在数据分析层面做了大量改进和支撑，为用户提供了大量的高级聚合函数和基于数组的高阶lambda函数。

企业中常用的路径分析模型一般有两种：

已经明确了要分析的路径，需要看下这些访问路径上的用户数据：关键路径分析
不确定有哪些路径，但是清楚目标路径是什么，需要知道用户在指定时间范围内都是通过哪些途径触达目标路径的：智能路径分析

关键路径分析

因为我们接下来要通过sequenceCount完成模型的开发，所以需要先来了解一下该函数的使用：

sequenceCount(pattern)(timestamp, cond1, cond2, ...)

该函数通过pattern指定事件链，当用户行为完全满足事件链的定义是会+1；其中time时间类型或时间戳，单位是秒，如果两个事件发生在同一秒时，是无法准确区分事件的发生先后关系的，所以会存在一定的误差。

pattern支持3中匹配模式：

(?N)：表示时间序列中的第N个事件，从1开始，最长支持32个条件输入；如，(?1)对应的是cond1
(?t op secs)：插入两个事件之间，表示它们发生时需要满足的时间条件（单位为秒）,支持 >=, >, <, <= 。例如上述SQL中，(?1)(?t<=15)(?2)即表示事件1和2发生的时间间隔在15秒以内，期间可能会发生若干次非指定事件。
.*：表示任意的非指定事件。

例如，boos要看在会员购买页超过10分钟才下单的用户数据那么就可以这么写

SELECT

根据上面数据可以看出完成支付之前在会员购买页停留超过10分钟的用户有100多个，那么是什么原因导致用户迟迟不肯下单，接下来我们就可以使用智能路径针对这100个用户展开分析，看看他们在此期间都做了什么。

智能路径分析

智能路径分析模型比较复杂，但同时支持的分析需求也会更加复杂，如分析给定期望的路径终点、途经点和最大事件时间间隔，统计出每条路径的用户数，并按照用户数对路径进行倒序排列
虽然clickhouse没有提供现成的分析函数支持到该场景，但是可以通过clickhouse提供的高阶数组函数进行曲线救国，大致SQL如下：

方案一

 SELECT

实现思路：

将用户的行为用groupArray函数整理成<时间, <事件名, 页面名>>的元组，并用arraySort函数按时间升序排序；
利用arrayEnumerate函数获取原始行为链的下标数组；
利用arrayFilter和arrayDifference函数，过滤出原始行为链中的分界点下标。分界点的条件是路径终点或者时间差大于最大间隔；
利用arrayMap和has函数获取下标数组的掩码（由0和1组成的序列），用于最终切分，1表示分界点；
调用arraySplit函数将原始行为链按分界点切分成单次访问的行为链。注意该函数会将分界点作为新链的起始点，所以前面要将分界点的下标加1；
调用arrayJoin和arrayCompact函数将事件链的数组打平成多行单列，并去除相邻重复项。
调用hasAll函数确定是否全部存在指定的途经点。如果要求有任意一个途经点存在即可，就换用hasAny函数。当然，也可以修改WHERE谓词来排除指定的途经点。
将最终结果整理成可读的字符串，按行为链统计用户基数，完成。

ClickHouse实战留存、路径、漏斗、session

方案二

不设置途经点，且仅以用户最后一次到达目标事件作为参考

SELECT

简单说一下上面用到的几个高阶函数：

arrayJoin
可以理解为行转列操作

SELECT arrayJoin([1, 2, 3, 4]) AS data
uniqCombined
clickhouse中的高性能去重统计函数，类似count(distinct field)，数据量比较小的时候使用数组进行去重，中的数据使用set集合去重，当数据量很大时会使用hyperloglog方式进行j近似去重统计；如果想要精度更改可以使用uniqCombined64支持64位bit

SELECT uniqCombined(data)
arrayCompact
对数组中的数据进行相邻去重，用户重复操作的事件只记录一次

SELECT arrayCompact([1, 2, 3, 3, 1, 1, 4, 2]) AS data
arraySort
对数组中的数据按照指定列进行升序排列；降序排列参考arrayReverseSort

SELECT arraySort(x -> (x.1), [(1, 'a'), (4, 'd'), (2, 'b'), (3, 'c')]) AS data
arrayFilter
只保留数组中满足条件的数据

SELECT arrayFilter(x -> (x > 2), [12, 3, 4, 1, 0]) AS data
groupArray
将分组下的数据聚合到一个数组集合中，类似hive中的collect_list函数

SELECT
arrayEnumerate
或取数组的下标掩码序列

SELECT arrayEnumerate([1, 2, 3, 3, 1, 1, 4, 2]) AS data
arrayDifference
参数必须是数值类型；计算数组中相邻数字的差值，第一个值为0

SELECT arrayDifference([3, 1, 1, 4, 2]) AS data
arrayMap
对数组中的每一列进行处理，并返回长度相同的新数组

SELECT arrayMap(x -> concat(toString(x.1), ':', x.2), [(1, 'a'), (4, 'a'), (3, 'a'), (2, 'c')]) AS data
arraySplit
按照规则对数组进行分割

SELECT arraySplit((x, y) -> y, ['a', 'b', 'c', 'd', 'e'], [1, 0, 0, 1, 0]) AS data

遇到下标为1时进行分割，分割点为下一个数组的起始点；注意，首项为1还是0不影响结果
has
判断数组中是否包含某个数据

SELECT has([1, 2, 3, 4], 2) AS data
hasAll
判断数组中是否包含指定子集

SELECT hasAll([1, 2, 3, 4], [4, 2]) AS data
arrayStringConcat
将数组转为字符串，需要注意的是，这里的数组项需要是字符串类型

SELECT arrayStringConcat(['a', 'b', 'c'], '->') AS data
arrayWithConstant
以某个值进行填充生成数组

SELECT arrayWithConstant(4, 'abc') AS data

什么是session，Session即会话，是指在指定的时间段内在您的网站/H5/小程序/APP上发生的一系列用户行为的集合。例如，一次会话可以包含多个页面浏览、交互事件等。

Session 是具备时间属性的，根据不同的切割规则，可以生成不同长度的 Session; 可见，Session统计与上述智能路径检测的场景有相似之处，都需要寻找用户行为链的边界进行处理；session分析主要包含两部分：

session切割；例如，用户访问您的网站，打开了一个网页，有事离开了电脑。几个小时候回来后继续访问，用户的session访问次数应该算作几次? 又比如，用户在pc端添加了购物车，在手机端完成了支付，又应该算作几次。
session指标统计；session分析常见的分析指标有，session访问次数，访问深度、访问时长、跳出率等等。