问题描述

在 A 电商公司，漏斗转化率分析是常用且重要的统计需求。

用户使用智能设备购物时，系统会建立连接形成会话 session。每个会话又包含很多个操作事件 event，比如：访问网站，浏览产品页，下单购买等等。每个用户的操作事件有一定的先后顺序，事件顺序越靠后，完成该事件的用户数量越少，就像是一个漏斗。漏斗转化分析先要统计各步骤操作事件的去重用户数量，在此基础上再做转化率等进一步的计算。

问题分析

漏斗分析写成 SQL 非常复杂，即使勉强写出来，漏斗的每个步骤都要写一个子查询，还要反复 JOIN，代码很难看懂，执行效率低下。

A电商公司的表结构脱敏后简化成一张表 T，包括字段事件编号 id，用户号 gid，操作时间 etime，事件类型 eventtype。

三步漏斗的 SQL 语句经过简单修改，写成 Oracle 的语法，大致是下面这样的：

with e1 as (
       select gid,1 as step1,min(etime) as t1
       from T
       where etime>= to_date('2021-01-10', 'yyyy-MM-dd') and etime<to_date('2021-01-25', 'yyyy-MM-dd')     and eventtype='eventtype1' and …
       group by 1
),
e2 as (
      select gid,1 as step2,min(e1.t1) as t1,min(e2.etime) as t2
      from T as e2
      inner join e1 on e2.gid = e1.gid
      where e2.etime>= to_date('2021-01-10', 'yyyy-MM-dd') and e2.etime<to_date('2021-01-25', 'yyyy-MM-dd') and e2.etime > t1 and e2.etime < t1 + 7  and eventtype='eventtype2' and …
      group by 1
),
e3 as (
      select gid,1 as step3,min(e2.t1) as t1,min(e3.etime) as t3
      from T as e3
      inner join e2 on e3.gid = e2.gid
      where e3.etime>= to_date('2021-01-10', 'yyyy-MM-dd') and e3.etime<to_date('2021-01-25', 'yyyy-MM-dd') and e3.etime > t2 and e3.etime < t1 + 7 and eventtype='eventtype3' and …
      group by 1
)
select
  sum(step1) as step1,
  sum(step2) as step2,
  sum(step3) as step3
from
  e1
  left join e2 on e1.gid = e2.gid
  left join e3 on e2.gid = e3.gid

子查询 e1 先从 T 表中过滤出指定时间段内事件类型为 eventtype1 的数据。指定时间段的起止时间通常是传入参数，会动态变化。“and …”表示可能还要满足其他条件，子查询 e2、e3 也要满足同样的条件。然后按照 gid 分组，每组取 etime 最小值作为漏斗转化第一步的时间 t1，定义新字段 step1 值为 1。

子查询 e2 用 T 表（别名 e2）和 e1 做内连接，关联字段是 gid。过滤出指定时间段内 e2.etime 在 t1 后 7 天以内，且事件类型为 eventtype2 的数据，这里的 7 天被称为漏斗窗口期，也可以是传入参数。按照 gid 分组，每组取 e2.etime 最小值作为漏斗转化第二步的时间 t2，定义新字段 step2 值为 1。

子查询 e3 用 T 表（别名 e3）和 e2 做内连接，关联字段 gid。过滤出指定时间段内 e3.etime 大于 t2，且在 t1 后 7 天内（漏斗窗口期），同时事件类型为 eventtype3 的数据。也按照 gid 分组，每组取出 e3.etime 最小值作为漏斗转化第三步的时间 t3，定义新字段 step3 值为 1。

最后，用 e1 左连接 e2、e3，关联字段是 gid，对 step1、step2、step3 汇总求和。

这里给出的是 3 步漏斗转化分析，如果要实现更多步骤的漏斗分析，需要参照 e3，写出子查询 e4、e5…，主查询也要加上对应的关联和汇总代码。

一般来说，T 表在一定时间段内的数据，按 gid 分组结果集会很大，而大分组需要外存缓存，所以数据库计算的性能会比较差。

在 A 电商的实际环境中，T 表每个月大于 3 亿条数据，这个 SQL 语句在 Snowflake 的 Medium 级集群（4 节点）三分钟没跑出结果。

解决方案

一、预先排序，有序计算

在数据准备阶段，我们要预先将原数据按照分组字段有序存放。做漏斗转化分析时，遍历符合时间段和事件类型等条件的有序数据，依次把各组数据读入内存计算。具体的，第一步将当前分组数据按照时间排序；第二步，在当前组中，找到第一种事件类型的第一条记录，时间记为 t1，赋值给当前分组计算结果数组的第一个成员，如果找不到 t1 则当前组舍弃；第三步，在当前组的第二种事件类型的记录中，找出发生时间在 t1 之后，且早于 t1+7（漏斗窗口期）的第一条记录，其时间 t2 赋值给结果数组的第二个成员（如果找不到 t2 则赋值为空）；第四步，在当前组第三种事件类型的记录中，找出发生时间在 t2 之后，且早于 t1+7 的第一条记录，其时间 t3 赋值给结果数组的第三个成员（如果 t2 为空，或者找不到 t3 则赋值为空）；第五步，用各组结果数组的三个成员做汇总计数，即可得到最终结果。这样做，对数据遍历一次就可以完成计算，不用大分组，性能提升会很明显。

我们还可以预先按照分组字段和时间字段都有序存放，计算时不必每组再排序，对性能提升的作用相对较小，但可以简化代码。

事实上，很多组内时序计算的分组字段都是确定的（比如用户号、帐号），不会是随意选择的字段。只要按照这些确定的字段做一种排序，就能适用于很多组内时序计算了。其他组内时序计算只是组内的具体算法不一样，我们将另外介绍。

预先排序虽然慢，但是一次性的，而且只需要保持一种存储即可，没有冗余。

SQL 基于无序集合，不能严格保证每组数据连续存放，所以不能直接应用有序算法。

二、新增数据

新增数据并不总是按分组字段继续有序，所以不能简单的追加到有序数据的末尾。而直接将有序数据和新增数据一起重新做常规大排序，会非常耗时。

我们可以将新增数据排序后，和原有序数据一起，用低成本的有序归并算法生成新的有序数据。这样整体复杂度相当于把所有数据读写一遍，可以避免常规大排序中产生很多临时外存缓存的现象，从而获得更好的性能。

更进一步，可以另外保持一个小规模的有序数据 (以下称为补数据)。新增数据排序后和补数据归并，原有序数据不变。经过适当的时间后，补数据积累到合适大小时，再和原有序数据归并。做组内时间有序计算时，从有序数据和补数据中分别读取，归并后再计算，性能会比只有一份有序数据时下降一些，但仍能利用有序实现快速计算。

这个适当时间的确定，与新增数据的周期有关。比如每天都有新增数据，则每个月做一次原有序数据和补数据的归并。补数据不会超过一个月的数据量，原有序数据存储一个月之前的所有数据。也就是说补数据可能会比原有序数据小很多，所以每天归并的数据量相对较小，很快就能完成数据追加。每个月才需要完成一次全量有序归并，耗时长一些也可以接受了。

技术选型

关系数据库和大数据技术普遍采用的 SQL 语言是基于无序集合理论的，不能严格保证每组数据连续存放，所以不能直接应用上面说的用户和时间有序算法。

如果我们用 Java 等高级语言实现上述计算方法，那么代码量会非常巨大，而且很难通用。

专业的数据计算引擎 esProc SPL 支持数据物理有序存储，很容易的实现上述计算方法。而且，SPL 封装了大量的数据计算函数，可以用很简捷的代码实现漏斗分析计算。

实际效果

在 GCP 的 16C128G 的虚拟机上计算 14 天跨度 3 步漏斗，可以 10 秒计算完成，达到且超过了用户的期望值。

从开发难度来看，SPL 做了大量封装，提供了丰富的函数，内置了上述优化方案需要的基本算法。计算三步漏斗的 SPL 代码大致是下面这样：

	A	B
1	=["eventtype1","eventtype2","eventtype3"]	=file("T.ctx").open()
2	=B1.cursor(gid,etime,eventtype;etime>=date("2021-01-10") && etime<date("2024-01-25") && A1.contain(eventtype) && …)
3	=A2.group(gid).(~.sort(etime))	=A3.new(~.select@1(eventtype==A1(1)):first,~:all).select(first)
4	=B3.(A1.(t=if(#==1,t1=first.etime,if(t,all.select@1(eventtype==A1.~ && etime>t && etime<t1+7).etime, null))))
5	=A1.("count(~("/#/")):STEP"/#).concat@c()
6	=A4.groups(;${A5})

代码要比前面的 SQL 更短、更灵活，再增加几步漏斗也还是这段代码。

A1：定义三个事件类型，也可以通过参数传入。

B1：打开组表 T.ctx。

A2：建立游标，过滤出满足时间段、事件类型等条件的数据，过滤条件都可以通过参数传入。

A3：定义分组计算，每组按照 etime 排序。

B3：定义新游标，每组中第一种事件类型的第一条数据命名为 first 字段，原分组命名为 all 字段。再定义过滤，去掉 first 字段为空的。

A4：对 B3 定义计算。每条记录都按照 A1 循环计算。第一次循环，将 first 的时间，也就是第一种事件类型最早的发生时间，同时赋值给变量 t1、t 和结果序列的第一个成员。第二次循环，在 all 中找 eventtype 为第二种事件类型，且 etime 大于 t，小于 t1 之后 7 天的第一条记录，其 etime 赋值给 t 和结果序列的第二个成员（找不到则赋值为空）。第三次循环，如果 t 不为空，在 all 中找 eventtype 为第三种事件类型，且 etime 大于 t，小于 t1 之后 7 天的第一条记录，其 etime 同时赋值给 t 和结果序列的第三个成员（t 为空或者找不到则赋值为空）。注意，这里的 7 天（漏斗窗口期）也可以通过参数传入。

A5：根据 A1 的漏斗步数，动态生成 count 语句count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3。

A6：实际执行前面定义的计算，并对各组结果序列的三个成员做小结果集汇总计数。其中利用了 A5 生成的 count 代码，实际执行的是：

=A4.groups(; count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)

这里给出的也是 3 步漏斗转化分析，如果要实现更多步骤计算，只要在 A1 的事件类型序列中增加后续步骤的事件类型即可，例如：["eventtype1","eventtype2","eventtype3","eventtype4","eventtype5"…]。如果通过参数传入事件类型序列，代码就可以不做任何变动。相比 SQL 增加子查询、修改主查询的方法，要简单很多了。

后记

漏斗分析问题涉及的用户数量众多，事件表非常大一般都需要外存存储。但每个用户的数据量却并不是很大，只有几条到几千条。

而且操作事件的计算虽然复杂，却都是以用户为单位进行的。不同用户之间的事件无关，对一个用户的计算一般不涉及其他用户的事件。

我们可以利用 SPL 有序存储机制将一个个用户的数据分别加载到内存中进行计算，这样可以降低计算的复杂度，有效提高性能。

这样做，只需要对数据遍历一次就可以完成漏斗转化分析，计算速度快且占用的内存空间很小。而且，这样的计算逻辑，完全符合我们的自然思维习惯，可以降低写代码的难度。

关系数据库和大数据技术普遍采用的 SQL 语言是基于无序集合理论的，不能严格保证每个用户数据连续存放，所以不能直接应用上面说的用户和时间有序算法。如果我们用 Java 等高级语言实现上述计算方法，那么代码量会非常巨大，而且很难通用。