clickhouse - Clickhouse：漏斗分析

Question

我试图在 Clickhouse 中做一个漏斗，但遇到了只有第一个事件记录在步骤中的问题。假设：客户端应该经历 3 个事件 - '001_event'、'002_event' 和 '003_event'。问题是一个客户可以无限次地以这种方式进行，我想考虑每个客户通过这三个事件的漏斗的所有尝试。

例如我有数据集：

appmetica_device_id	事件日期时间	事件名称
168	2021-03-01 12:01:00	001_事件
168	2021-03-01 12:02:00	002_事件
168	2021-03-01 12:03:00	003_事件
168	2021-03-01 15:00:00	001_事件
168	2021-03-01 18:00:00	001_事件
168	2021-03-01 18:02:00	002_事件
200	2021-03-01 12:01:00	001_事件
200	2021-03-01 12:02:00	002_事件
200	2021-03-15 12:03:00	001_事件

最后，我想获得一个包含渠道通过次数的表格，而不是通过渠道的用户数量：

001_事件	002_事件	003_事件
5	3	1

我的代码：

select
sum(step1_time != 0) as step1_achieve,
sum(step2_time != 0) as step2_achieve,
sum(step3_time != 0) as step3_achieve,
sum(step4_time != 0) as step4_achieve
from (
    select groupArray(event_name) as event, groupArray(event_datetime) as event_time,
    arrayFilter(time, name -> (name = '001_event'), event_time, event)[1] as step1_time,
    arrayFilter(time, name -> (name = '002_event') and (time >= step1_time) and (step1_time != 0), event_time, event)[1] as step2_time,
    arrayFilter(time, name -> (name = '003_event') and (time >= step2_time) and (step2_time != 0), event_time, event)[1] as step3_time,
    from app_metrica.events_all
    prewhere event_date >= '2021-03-01'
    where event_name in ('001_event', '002_event', '003_event')
    group by appmetrica_device_id
)

但是我明白了：

001_事件	002_事件	003_事件
2	2	1

标签： clickhouse