【摘要】
从原理上分析 SQL 语句慢的原因，用代码示例给出提速办法。点击了解SQL 提速：漏斗转化分析

问题描述

漏斗转化分析属于分组时序计算，其应用场景很多，电商购买转化分析就是其中之一。在用户登录电商网站后，还会发生页面浏览、搜索、加购物车、下单、付款等多个操作事件。这些事件按照时间有序，每个事件之后都会有用户流失。漏斗转化分析通常先要统计各个操作事件的用户数量，在此基础上再做转换率等复杂的计算。

假设表 T 包含的字段为：id（事件编号），gid（分组字段），etime（时间），eventtype（事件类型），分别对应电商购买分析中的用户操作事件编号，用户号，操作时间，操作事件类型。表 T 参与计算的数据总量通常都很大，需要外存；每组（用户）数据量不大，可以装入内存。

这里，我们讲解 3 步漏斗转化的提速方法，更多步骤的计算可以类推。以 Oracle 为例，SQL 样例如下：

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 分组结果集会很大，而大分组需要外存缓存，所以数据库计算的性能会比较差。

提速方案

一、预先排序，有序计算

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

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

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

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

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：实际执行前面定义的计算，并对各组结果序列的三个成员做小结果集汇总计数。

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

二，数据按照分组、时间字段都有序时，计算漏斗转化。

如果预先按照分组、时间字段都有序，上述代码中的 A3 可以省去 (~.sort(etime))，代码更简单。

三，数据预处理，有序存储。

A1：转换前的原始组表 T-original.ctx。

A2：打开组表 T-original.ctx 建立游标，按照字段 gid，etime 排序，结果游标将字段 gid，etime 放在前面。如果仅按 gid 排序，sortx 写作 sortx(gid) 即可。

A3：新建组表 T.ctx，字段名前面加了 #，即表明该组表对 gid，etime 字段有序。如果仅按照 gid 排序，create 写作 create(#gid,etime,…) 即可。

A4：将排好序的数据输出到组表 T.ctx 中。