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
    user_id,
    count(CASE WHEN event_id='e1' THEN 1 END) as step_1_count,
    count(CASE WHEN event_id='e2' THEN 1 END) as step_2_count,
    count(CASE WHEN event_id='e4' THEN 1 END) as step_3_count,
    count(CASE WHEN event_id='e5' THEN 1 END) as step_4_count,
    window_funnel(3600 * 24, 'default', event_time, event_id='e2', before_event_id='e1') as step_2_conversion,
    window_funnel(3600 * 24, 'default', event_time, event_id='e4', before_event_id='e2') as step_3_conversion,
    window_funnel(3600 * 24, 'default', event_time, event_id='e5', before_event_id='e4') as step_4_conversion
FROM
    event_info_log
GROUP BY
    user_id;