SPL 实践：漏斗分析
问题描述
漏斗分析定义
电商业务中漏斗分析是常见的统计需求。用户使用智能设备购物时，系统会建立连接形成会话 session。每个会话又包含很多个操作事件 event，比如：访问（visit）类事件，浏览（view）类事件，详情页（detail）类事件等等。
每个用户的操作事件，按照发生时间（简称事件时间）有一定的先后顺序。事件顺序越靠后，完成该事件的用户数量越少，就像是一个漏斗。漏斗转化分析先要统计各步骤操作事件的去重用户数量，在此基础上再进一步计算转化率等等。
以三步漏斗为例，三个事件类型是访问，浏览，详情页。日期范围是从 2023 年 2 月 28 日到 2023 年 3 月 27 日。
第一步，要过滤出这个日期范围内，一个用户的访问事件。由于同时发生的访问事件可能不止一个，所以还要从事件时间相同的记录中找出会话编号最小的记录，作为这个用户在这个时间点的访问事件对应的会话，称为访问事件会话。
第二步，在第一步得到的访问事件会话中，过滤出这个用户所有事件时间在访问事件发生之后一天内的浏览事件。每个用户仅保留最小的会话号、浏览事件时间和访问事件时间。
第三步，在第二步得到的访问浏览事件会话中，过滤出这个用户，发生在浏览事件时间之后，到访问事件一天之内的详情页事件。每个用户仅保留最小的会话号、详情页事件时间和访问事件时间。
每个用户都要查出是否有上述三种事件，并统计访问、浏览和详情页事件的用户总数，并用详情页事件用户数除以访问事件用户数，求三步漏斗转化率。
数据结构与规模
事件类型 eventtype：
字段 |
字段类型 |
字段注释 |
示例数据 |
id |
string |
类型号，主键 |
ed3de1d0e3f475c9bf3e62dc984d4b79 |
name |
string |
名称 |
confirm |
id 是由 16 位十六进制数转换成的长度为 32 位的字符串。用户采用的是 Snowflake，可能对这样的数据类型有特殊的优化措施，但是导出文本数据就是普通字符串了。其它表的主键也是这种类型。
设备类型 devicetype：
字段 |
字段类型 |
字段注释 |
示例数据 |
id |
string |
设备类型号，主键 |
dcb2f062f1436724259deab2bb93a7d5 |
name |
string |
名称 |
pad |
会话表 sessions
字段 |
字段类型 |
字段注释 |
示例数据 |
id |
string |
session 号，主键 |
bb0018615f7163de67b0d5d00593349d |
userid |
string |
用户号 |
cde148d0c8296f1fcab3bf9fdab23d30 |
devicetype |
string |
设备类型号 |
eb58ad0ef88d405b3d2d43c1f55b2e82 |
events：
字段 |
字段类型 |
字段注释 |
示例数据 |
id |
string |
event 号，主键 |
cb9112d01256d51ef7068cdf7c222ea5 |
sessionid |
string |
session 号 |
bb0018615f7163de67b0d5d00593349d |
userid |
string |
用户号 |
cde148d0c8296f1fcab3bf9fdab23d30 |
eventtime |
timestamp |
时间（毫秒） |
2023-03-07 12:03:33.034 |
eventtype |
string |
事件类型 |
ed3de1d0e3f475c9bf3e62dc984d4b79 |
表间关系说明：
sessions 表（简称 s）和 events 表（简称 e）是一对多关系，通过 s 的 userid、id 和 e 的 userid、sessionid 关联。
不同的 userid 对应 session 的 id 不会重复，不同的 session 对应 event 的 id 不会重复。
events 表和 eventtype 表（简称 et）是多对一关系，通过 e 的 eventtype 和 et 的 id 关联。
sessions 表和 devicetype 表（简称 dt）是多对一关系，通过 s 的 devicetype 和 dt 的 id 关联。
全部数据的日期范围是 2023-03-01 到 03-31 日，各表数据量是：
sessions：300 万行
events：3 亿行
eventtype：700 行
devicetype：8 行
环境与期望
现在要在 2023-03-21 前 30 天内任选一个时间跨度，计算 N 步（一般 3-7 步）漏斗转化率，期望在 Snowflake 的 Medium 级集群（8CPU 的 EC2 共 4 节点）上 30 秒内跑出结果。写出 SQL 后实测，计算 14 天跨度的 3 步漏斗，3 分钟内没有跑出结果。
这是因为写成 SQL 非常复杂，即使勉强写出来，漏斗的每个步骤都要写一个子查询，代码很难看懂，执行效率低下。
问题分析
有序计算
漏斗分析参与计算的总数据量很大，而每个用户的数据量却并不是很大，只有几条到几千条。同时，事件的计算是以用户为单位进行的，不同用户之间的事件无关。
对于一个用户来说，多个操作事件是要按照时间顺序计算的，属于比较典型的时序计算。
事件的时序计算逻辑往往特别复杂，需要写多步骤代码才能实现。
SPL 支持数据的物理有序存储。如果 event 表按 userid、sessionid、eventtime 有序存放，计算时，依次将每个 userid 的数据读入内存就更容易完成时序计算。比如先找到页面浏览的事件，再找后边有没有加购物车的事件。如果有，而且和页面浏览的时间间隔小于 7 天，这个用户就是完成了两个步骤的用户。
这样，只需要对数据遍历一次就可以完成漏斗转化分析，计算速度快且占用的内存空间很小。而且，这样的计算逻辑，完全符合我们的自然思维习惯，可以降低写代码的难度。
虽然预先对事件表做排序比较慢，不过排序工作是一次性的，且事件表只要按照用户、会话和时间有序存储一份即可，不会出现冗余。
快速去重
漏斗统计涉及多步有次序的事件，每一步都对应有一个 COUNT(DISTINCT)，以便和上一步的 COUNT(DISTINCT) 一起计算这一步的客户流失率；下一步的 COUNT(DISTINCT) 要在上一步的基础上筛选。并且要考虑事件发生的次序。
COUNT(DISTINCT) 一直是数据库计算的难题，通常都会非常慢，在数据量大（帐号数多）的情况下，还有可能导致数据库崩掉。
利用上面说的 SPL 有序计算，每次读入一个用户的数据进内存进行复杂计算的过程中不会涉及其它用户。后续计算其他用户时，也不会再出现这个用户的数据。所以，计算的结果本身就是不重复的，不需要再做去重。性能提升明显，且内存消耗很小。
数据类型转换
事件类型表和设备类型表是两个小表，如果真的采用 32 位字符串作为 id，那么，events 的 eventtype 字段和 sessions 的 devicetype 字段也必须保存这样的 id。由于 events 和 sessions 表行数非常多，所以需要存储大量的 32 位字符串，占用空间大、计算性能差。
将事件类型表和设备类型表有序存储后，每条记录对应的位置序号是固定的，events 和 sessions 可以存储对应记录的序号，取代原来的 32 位字符串。计算时，将两个小表读入内存中，需要的时候按照位置从内存中获得对应记录就可以了。这种优化方法称为序号化。序号是小整数，占用空间小且计算速度快。
特别是用 eventtype 对 events 表过滤时，还可以利用布尔维序列，将“集合值比较”转换为“序号引用”，进一步提升性能。
类似的，timestamp 也可以转换为长整型，完成整数化，性能也会得到提升。
还有，events 和 sessions 中的 userid、sessionid 如果用 32 位字符串效率会很低，也需要完成整数化。但 32 位字符串超过了 long（通常的最大整数）的取值范围。我们用 SPL 的排号键数据类型，其由两个 long 型构成，正好是 16 位十六进制数。
实践过程
准备实验数据
A |
B |
|
1 |
=8.(rands("bcded",2)/rands("abcdedf0123456789",30)) |
/devicetypes |
2 |
[laptop,PC,gamebox,Spider,TV,phone,pad,unkown] |
|
3 |
=A2.new(A1(#):id,~:name) |
=file("devicetype.txt").export(A3) |
4 |
[visit,view,detail,login,cart,confirm,pay] |
/eventtypes |
5 |
[50,150,200,300,400,500,550] |
=700.(rands("bcded",2)/rands("abcdedf0123456789",30)) |
6 |
=B5.new(~:id,"eventtype"/#:name) |
=A5.(A6(~).name=A4(#)) |
7 |
=file("sessions.txt") |
/sessions |
8 |
=1501000.(rands("bcded",2)/rands("abcdedf0123456789",30)) |
=3001000.(rands("bcded",2)/rands("abcdedf0123456789",30)) |
9 |
=A8.id().to(15000) |
=B8.id().to(3000000) |
10 |
=B9.new(~:id,A9(int((B9.#+1)/2)):userid,int(rand(1000)+1):sessiontime,A3(int(rand(8)+1)).id:devicetype) |
|
11 |
=A7.export(A10) |
|
12 |
=file("events1.btx") |
/events |
13 |
for 50 |
=3010000.(rands("bcded",2)/rands("abcdedf0123456789",30)) |
14 |
=A12.export@ab(B13) |
|
15 |
=A12.cursor@b().sortx(_1) |
=A15.group@1(_1) |
16 |
=file("events2.btx") |
=A16.export@b(B15) |
17 |
=movefile(A12) |
|
18 |
[3812123,7180321,10090667,18100965,22000324,43250567,82788111] |
=A5.new(~:id,A4(#):name,A18(#):eventtime) |
19 |
=A16.cursor@b().new(_1:id) |
=A7.cursor().new(_1:id,_2:userid).rename(id:sessionid) |
20 |
=file("events3.btx") |
=to(700)\A5 |
21 |
for A19,100000 |
=B19.fetch(2000) |
22 |
=B21.derive(A21.to((B21.#-1)*50+1,(B21.#)*50):events,B18.to(rand(7)+1):eventtype,elapse@ms(datetime(2023,3,1,9+rand(8),0,0)+rand(31),rand(1000)):eventtime1) |
|
23 |
=B22.run((events|events).derive(B22.eventtype.m(#).id:eventtype,int(B22.eventtype.m(#).eventtime):et1):events) |
|
24 |
=B23.run(events.derive(elapse@ms(B23.eventtime1,ifn(events.et1,0)):eventtime):events) |
|
25 |
=B24.news(events;sessionid,userid,id,eventtype,eventtime) |
|
26 |
=B25.run(if(eventtype==null,(eventtype=B20(rand(693)+1),eventtime=elapse@ms(datetime(2023,3,1,9+rand(8),0,0)+rand(31),rand(1000))))) |
|
27 |
=A20.export@ab(B26) |
|
28 |
=A20.cursor@b().new(id,userid,sessionid,A6(eventtype).id:eventtype,eventtime) |
|
29 |
=A28.sortx(eventtime) |
|
30 |
=file("events.txt").export(A29) |
=movefile(A20) |
A1-B3 准备 devicetypes 数据。
A4-B6 准备 eventtypes 数据，共 700 个，其中有 7 个计算要用到的，在 A5 中固定位置。
A7 到 B11 准备 sessions 数据，这个表比较大，随机生成的 32 字符串有可能重复。因此 userid 和 sessionid 都多生成 10000 条，然后在内存去掉重复。
A12 开始准备 events 数据。到 A15 为止准备 1.5 亿的 32 位字符串写入 events1.btx，再利用外存排序去重，存入 events2.btx。删掉 events1.btx。
A18、B18 准备计算用到的 7 个步骤，预置一些时间间隔，让生成的数据较多的满足漏斗规则。
A19 开始，用 events2.btx 和 sessions.txt 循环生成 3 亿条 events.btx 数据，到 B28 为止写入 events3.btx。
A28 到 A30 将 events3.btx 按照时间排序后，写入 events.txt。
B30 删除 events3.btx。
数据预处理
devicetype、eventtype 转 btx 代码：
A |
B |
|
1 |
=file("devicetype.txt").import().new(_1:id,_2:name) |
|
2 |
=file("devicetype.btx") |
=A2.export@b(A1) |
A |
B |
|
1 |
=file("eventtype.txt").import().new(_1:id,_2:name) |
|
2 |
=file("eventtype.btx") |
=A2.export@b(A1) |
sessions 预处理代码：
A |
|
1 |
=file("sessions.txt").cursor().new(_1:id,_2:userid,_3:sessiontime,_4:devicetype) |
2 |
=file("devicetype.btx").import@b().keys@i(id) |
3 |
=A1.new(k(bits@h(mid(userid,1,8)):4,bits@h(mid(userid,9,8)):4,bits@h(mid(userid,17,8)):4,bits@h(mid(userid,25,8)):4):userid, k(bits@h(mid(id,1,8)):4,bits@h(mid(id,9,8)):4,bits@h(mid(id,17,8)):4,bits@h(mid(id,25,8)):4):id, sessiontime, A2.pfind(devicetype):devicetype) |
4 |
=A3.sortx(userid,id) |
5 |
=file("sessions.ctx").create@py(#userid,#id,sessiontime,devicetype) |
6 |
=A5.append@i(A4) |
A3 中用 k()和 bits() 函数将 userid、id 转换为排号键，注意 userid 和 id 是由 0-9 和 abcdef 组成的字符串，所以可以用 bits@h() 函数按照 16 进制数位规则转换为整型。
A3 还用 pfind 函数将 devicetype 的 32 位字符串编号，转换为位置序号，完成序号化。
events 预处理代码：
A |
|
1 |
=file("events.txt").cursor().new(_1:id,_2:userid,_3:sessionid,_4:eventtype,_5:eventtime) |
2 |
=file("eventtype.btx").import@b().keys@i(id) |
3 |
=A1.new(k(bits@h(mid(userid,1,8)):4,bits@h(mid(userid,9,8)):4,bits@h(mid(userid,17,8)):4,bits@h(mid(userid,25,8)):4):userid, k(bits@h(mid(sessionid,1,8)):4,bits@h(mid(sessionid,9,8)):4,bits@h(mid(sessionid,17,8)):4,bits@h(mid(sessionid,25,8)):4):sessionid, long(eventtime):eventtime, k(bits@h(mid(id,1,8)):4,bits@h(mid(id,9,8)):4,bits@h(mid(id,17,8)):4,bits@h(mid(id,25,8)):4):id, A2.pfind(eventtype):eventtype) |
4 |
=file("events.ctx").create@py(#userid,#sessionid,#eventtime,#id,eventtype) |
5 |
=A3.sortx(userid,sessionid,eventtime,id) |
6 |
=A4.append@i(A5) |
A3 将 userid、sessionid、id 转换为排号键，将 eventtime 转成 long，并完成 eventtype序号化。
漏斗分析计算
传入参数 arg_date 是日期，例如 2023-03-21。
arg_days 是时间跨度，例如 14 天、30 天。
arg_steps 是要计算的漏斗分析步骤，例如三步漏斗："visit,view,detail"。
A |
|
1 |
=eventtypes=file("eventtype.btx").import@b() |
2 |
=devicetypes=file("devicetype.btx").import@b() |
3 |
=long(elapse(arg_date,-arg_days)) |
4 |
=long(arg_date) |
5 |
=long(arg_date+1) |
6 |
=arg_steps.split@c() |
7 |
=A1.(A6.pos(name)) |
8 |
=if(A6.len()>=3,to(3,A6.len()),[]) |
9 |
=file("events.ctx").open() |
10 |
=sessions=file("sessions.ctx").open().cursor@mv(userid,id,devicetype) |
11 |
=file("events.ctx").open().cursor@v(userid,sessionid,eventtime,eventtype;eventtime>=A3 && eventtime<A5,eventtype:A7:#;A9) |
12 |
=A10.pjoin(userid:sessionid,userid,sessionid,eventtype,eventtime;A9,userid:id,userid,devicetype) |
13 |
=A11.group(userid) |
14 |
=A12.new(~.align@a(7,eventtype):e,e(1).select(eventtime<A4).group@u1(eventtime):e1,e(2).group@o(sessionid):e2${A8.(",e("/~/"):e"/~).concat()}) |
15 |
=A13.derive(join@m(e1:sub_e1,sessionid;e2:sub_e2,sessionid).derive@o(sub_e2.select(eventtime>sub_e1.eventtime && eventtime<sub_e1.eventtime+86400000).min(eventtime):sub_e2_min_time).select(sub_e2_min_time) :e1_join_e2 ) |
16 |
=A14.new(e1.id(devicetype):e1_id_devicetypeno,e1_join_e2.min(sub_e1.eventtime):e1_min_time,e1_join_e2.min(sub_e2_min_time):e2_min_time,e1_join_e2.min(sub_e1.sessionid):e1_min_sessionid${A8.(",e"/~/".select(sessionid==e1_min_sessionid && eventtime>e"/(~-1)/"_min_time && eventtime<e1_min_time+86400000):e"/~/"_1,e"/~/"_1.min(eventtime):e"/~/"_min_time").concat()}) |
17 |
=A15.news(e1_id_devicetypeno;~:devicetype,e2_min_time${A8.(",e"/~/"_min_time").concat()}) |
18 |
=A16.groups(devicetype;count(1):STEP1_COUNT,count(e2_min_time):STEP2_COUNT${A8.(",count(e"/~/"_min_time):STEP"/~/"_COUNT").concat()}) |
A3-A5：对参数 arg_date 求前 arg_days 天和 1 天后的日期，三者都转为长整数 long。
A6：用输入参数计算出需要计算的 3 步漏斗序列。
A7：把序表 eventtypes 中，计算用到的种类型依次设置为 1、2、3，其他都是 null。A7 就是布尔维序列。
A8：计算出 2 步以上漏斗的序号 3。
A9：sessions 建立多路列式游标。
A10：events 建立多路的列式游标，且与 A9 同步分段。游标用布尔维序列A7 过滤出 3 种类型的事件，日期要多取出一天。
A11-A12：event 和 session 两个列式游标用 pjoin 函数有序归并，按照 userid 有序分组。
A13：每个 userid 分组内，eventtype 按照 1,2,3 对齐分组，分别依次对应 3 个事件类型。e1 到 en 中的过滤条件中都有包含字符子串的判断。
A14：e1 和 e2 按照 sessionid 有序归并，结果计算出满足条件的 eventtime 最小值。过滤出最小值不为空的。
A15：e1 对 devicetype 做内存去重，且将 e1 和 e2 归并的结果求 eventtime 最小值和 sessionid 最小值。从 e3 开始过滤出符合 sessionid 和时间条件的记录，也就是 e3 中符合条件的 event。
A16：在 A15 游标基础上计算去重后的 devicetype，求 e2 到 e3 的时间最小值，合并到原游标。
A17：对 A16 做小结果集分组汇总，分组字段是 devicetype，每组求各个步骤的计数值。
A18：将 A17 序表中的 devicetype 从序号转化为名称。
这里给出的是 3 步漏斗，如果要计算其他步骤只要修改参数 arg_steps 就可以了，比如 7 步漏斗改为 "visit,view,detail,login,cart,confirm,pay"，代码不需要改动。
实践效果
8C32G 虚拟机，SPL 计算 14 天的 3 步漏斗分析用时 17 秒，7 步骤漏斗 19 秒。计算 30 天 3 步漏斗分析用时 28 秒，7 步骤漏斗 30 秒。
这个测试用的 CPU 主频只有 1.7G，性能较差。在 GCP 的 16C128G 的虚拟机上计算 14 天跨度 3 步漏斗，可以 10 秒计算完成。
后记
漏斗分析问题涉及的用户数量众多，事件表非常大一般都需要外存存储。但每个用户的数据量却并不是很大，只有几条到几千条。
而且操作事件的计算虽然复杂，却都是以用户为单位进行的。不同用户之间的事件无关，对一个用户的计算一般不涉及其他用户的事件。
我们可以利用 SPL有序存储机制将一个个用户的数据分别加载到内存中进行计算，这样可以降低计算的复杂度，有效提高性能。
这样做，只需要对数据遍历一次就可以完成漏斗转化分析，计算速度快且占用的内存空间很小。而且，这样的计算逻辑，完全符合我们的自然思维习惯，可以降低写代码的难度。
关系数据库和大数据技术普遍采用的 SQL 语言是基于无序集合理论的，不能严格保证每个用户数据连续存放，所以不能直接应用上面说的用户和时间有序算法。如果我们用 Java 等高级语言实现上述计算方法，那么代码量会非常巨大，而且很难通用。
英文版