SQL 提速:漏斗转化分析

【摘要】
从原理上分析 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

),

with 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

),

with 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 基于无序集合,不能严格保证每组数据连续存放,所以不能直接应用有序算法。

二、新增数据

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

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

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

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

代码示例

一、数据按照分组字段有序,时间字段无序时,计算漏斗转化。


A

B

1

=["eventtype1","eventtype2","eventtype3"]

=file("T.ctx").open()

2

=B1.cursor(gid,etime,eventtype;etime>=date("2021-01-10")   && etime<date("2021-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

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

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)),代码更简单。

 

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


A

1

=file("T-original.ctx")

2

=A1.open().cursor().sortx(gid,etime).new(gid,etime,…)

3

=file("T.ctx").create(#gid,#etime,…)

4

=A3.append@i(A2)

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 中。

 

四,新增数据追加。

假设 T 表每天的新增数据存储在 T_new.btx 中,且字段名称、顺序与 T.ctx 相同。


A

B

1

=file("T.ctx").open()


2

=file("T_new.btx").cursor@b().sortx(gid,etime)

3

if   (day(now())==1)

>A1.reset()

4

>A1.append@a(A2)


A1:打开组表 T.ctx。

A2:定义 T_new.btx 的游标并排序,通常每天新增数据量不大,这里虽然用 sortx,但实际上经常会是内存排序,速度很快。如果仅对 gid 有序,去掉 sortx 中的 etime 就可以了。

A3:判断日期是否为 1 号,如果不是则执行 A4,用 append@a 只在补数据上归并。如果是 1 号,那么就执行 B3,用 reset 把原有序数据和补数据有序归并成新的有序数据。

 


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾