实时项目-漏斗分析

需求：

我们漏斗分析中定义的需求如下：
注册-> 点击新闻-> 进入详情页-> 发布评论

转换成事件：
SignUp -> AppClick[element_page=‘新闻列表页’] ->
AppClick[element_page=‘内容详情页’]->NewsAction[action_type=‘评论’]

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

注意：我们这里数据就三天，所以窗口期也就是不用判断，但是我们以后可能会拿到N天数据，所以要加窗口期判断

sql实现：

-- 分析sql，首先我们可以先把每一个事件的数据按照条件查询出来，然后在将每一个事件中的时间拿到，进行关联查询，通过时间进行判断该事件是否在窗口期以内，并且还要和上一个事件判断，一定要大于它
-- 拿到三天内每一个事件数据
with t1 as(
    select
    distinct_id,
    ctime,
    event
    from  ods_db_news.event
    where event='SignUp'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
),
t2 as(
    select
    distinct_id,
    ctime,
    event
    from  ods_db_news.event
    where event='AppClick' and element_page='新闻列表页'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
),
t3 as(
    select
    distinct_id,
    ctime,
    event
    from  ods_db_news.event
    where event='AppClick' and element_page='内容详情页'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
),
t4 as(
    select
    distinct_id,
    ctime,
    event
    from  ods_db_news.event
    where event='NewsAction' and action_type='评论'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
    and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
)
select
count(distinct t1.distinct_id) step1,
count(distinct t2.distinct_id) step2,
count(distinct t3.distinct_id) step3,
count(distinct t4.distinct_id) step4
from t1 
left join t2 
on t1.distinct_id=t2.distinct_id 
and t1.ctime<t2.ctime  and t2.ctime-t1.ctime<86400*3*1000
left join t3 
on t2.distinct_id=t3.distinct_id
and t2.ctime<t3.ctime  and t3.ctime-t1.ctime<86400*3*1000
left join t4  
on t3.distinct_id=t4.distinct_id
and t3.ctime<t4.ctime and t4.ctime-t1.ctime<86400*3*1000;

结果：

执行上述查询可以看到如下类似结果
step1 | step2 | step3 | step4
-------±------±------±------
3154 | 79 | 2 | 1
代表着我们的漏斗的每一步的人数