Apache Doris实现漏斗分析

Question

之前看了这篇播文DORIS----漏斗转化分析案例实现_咚动咚的博客-CSDN博客，大致了解了如何计算漏斗分析。如果在这个基础上增加关联属性功能，应该如何实现呢？关联属性的意思是指在在转化的过程中都是同一个商品。比如：点击秒杀活动–>参加活动—>参与秒杀–>秒杀成功—>成功支付的都是同一个商品
另外，这篇博文写的计算方式应该是按照人数计算转化率，如果按次数计算该如何改造呢？

@咚动咚 您要有时间帮忙解答下

语法:
window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)

漏斗分析函数搜索滑动时间窗口内最大的发生的最大事件序列长度。
-- window ：滑动时间窗口大小，单位为秒。
-- mode  ：保留，目前只支持default。-- 相邻两个事件之间没有时间间隔要求，并且相邻两个事件中可以做其他的事件
-- timestamp_column ：指定时间列，类型为DATETIME, 滑动窗口沿着此列工作。
-- eventN ：表示事件的布尔表达式。

select 
user_id,
window_funnel(3600*24, 'default', event_time, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
from event_info_log 
group by user_id

+---------+------+
| user_id | step |
+---------+------+
| u006    |    4 |
| u007    |    2 |
| u005    |    3 |
| u004    |    3 |
| u010    |    0 |
| u001    |    3 |
| u003    |    2 |
| u002    |    3 |
| u008    |    3 |
| u009    |    2 |
+---------+------+



-- 算每一层级的转换率
select
'购买转化漏斗' as funnel_name,
sum(if(step >= 1 ,1,0)) as step1,
sum(if(step >= 2 ,1,0)) as step2,
sum(if(step >= 3 ,1,0)) as step3,
sum(if(step >= 4 ,1,0)) as step4,
round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as 'step1->step2_radio',
round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as 'step2->step3_radio',
round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as 'step3->step4_radio'
from
(
select 
user_id,
window_funnel(3600*24, 'default', report_date, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
from event_info_log 
where to_date(report_date) = '2022-11-01'
and event_id in('e1','e4','e5','e2')
group by user_id
) as t1 

-- res
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| funnel_name        | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| 购买转化漏斗       |     9 |     9 |     6 |     1 |                  1 |               0.67 |               0.17 |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+

所有回答麻烦请基于此SQL进行回复，不要复制粘贴概念理论

Answer 1

该问题已经自行解决，分享最终答案给大家

select
    d,
    sum(if(step >= 1 , 1, 0)) as 启动,
    sum(if(step >= 1 , 1, 0) * cnt) as 启动次数,
    sum(if(step >= 2 , 1, 0)) as 首页,
    sum(if(step >= 2 , 1, 0) * cnt) as 首页次数,
    sum(if(step >= 3 , 1, 0)) as 详情,
    sum(if(step >= 3 , 1, 0) * cnt) as 详情次数,
    sum(if(step >= 4 , 1, 0)) as 下载,
    sum(if(step >= 4 , 1, 0) * cnt) as 下载次数,
    round(sum(if(step >= 2 , 1, 0))/ sum(if(step >= 1 , 1, 0)), 2) as '启动->首页',
    round(sum(if(step >= 3 , 1, 0))/ sum(if(step >= 2 , 1, 0)), 2) as '首页->详情',
    round(sum(if(step >= 4 , 1, 0))/ sum(if(step >= 3 , 1, 0)), 2) as '详情->下载'
from
    (
    select
        userid,
        count(1) cnt,
        to_date(day) as d,
        window_funnel(86400,
        'default',
        day,
        event = '启动',
        event = '首页',
        event = '详情',
        event = '下载') as step
    from
        funnel_test
    where
        date(day) >= '2023-07-01'
        and date(day) <= '2023-07-30'
        and (  
       (event = '启动' )  
       Or (event = '首页' )  
       Or (event = '详情' )  
        Or (event = '下载' )  
        )  
    group by
        userid, productid, to_date(day)
        order by userid, to_date(day)
) as t1
group by d
order by d;