漏斗分析入门SQL

我们漏斗分析中定义的需求如下

注册-> 点击新闻-> 进入详情页-> 发布评论  
可以记事件为ABCD
A->B->C->D

接下来我们用SQL实现这个需求
我们来查询 20210103到2020109 事件范围内，并且窗口时间是3天的漏斗

with t1 as (
select
id,
ctime,
event
from tmp
where event=A and ctime>='20200103' and ctime<'20200109'
),
t2 as (
select
id,
ctime,
event
from tmp
where event=B and ctime>='20200103' and ctime<'20200109'
),
t3 as (
select
id,
ctime,
event
from tmp
where event=C and ctime>='20200103' and ctime<'20200109'
),
t4 as (
select
id,
ctime,
event
from tmp
where event=D and ctime>='20200103' and ctime<'20200109'
)
select 
count(t1.id) step1,
count(t2.id) step2,
count(t3.id) step3,
count(t4.id) step4
from t1 
left join t2 on t1.id=t2.id and t1.ctime<t2.ctime 
and t2.ctime-t1.ctime<86400*3*1000
left join t3 on t2.id=t3.id and t2.ctime<t3.ctime 
and t3.ctime-t2.ctime<86400*3*1000
left join t4 on t3.id=t4.id and t3.ctime<t4.ctime 
and t4.ctime-t3.ctime<86400*3*1000

总结

• 写漏斗分析需要较多的join,数据量大的时候效率会不好!