怎么用ClickHouse做漏斗分析?
请教下如何用ClickHouse做漏斗分析。它的SQL和内置方法不知能否支持。
具体需求如下: 我们有一系列用户访问页面的事件: 时间 用户 页面id 2018-4-24 18:45 A 首页 2018-4-24 18:46 A 购物车 2018-4-24 18:45 B 首页 2018-4-24 18:48 B 商品详情 2018-4-24 18:49 B 购物车 2018-4-24 18:46 C 商品详情
第一步:需要按用户聚合成每个用户的访问路径: 用户 路径 A 首页=>购物车 B 首页=>商品详情=>购物车 C 商品详情
第二步:在路径的每个节点中,增加这个节点相对于整个路径的起始节点的时间偏移量(分钟为单位): 用户 路径 A (首页, 0)=>(购物车, 1) B (首页, 0)=>(商品详情,3)=>(购物车,4) C (商品详情, 0)
第三步:用户输入起始节点、目标节点、有效时间(分钟为单位)三个参数,查询以上数据。 找出从起始节点到目标节点的所有路径(包括间接路径)。并且目标节点相对于起始节点的时间偏移量不超过用户输入的有效时间。
例如,假如用户输入的查询参数是 首页、购物车、3,那么以上表中,只有第一条记录符合查询条件,即 用户 路径 A (首页, 0)=>(购物车, 1)
请百度olap大赛,去年大赛场景就是该场景
第一步建表:
create table test_analysis (created_at DateTime, dt Date, user String, page_id String ) ENGINE=MergeTree(dt, (user, dt), 8192);
插入测试数据:
insert into table test_analysis Format Values
('2018-4-24 18:45','2018-4-24','A','首页'),
('2018-4-24 18:46','2018-4-24','A','购物车'),
('2018-4-24 18:45','2018-4-24,'B','首页'),
('2018-4-24 18:48','2018-4-24','B','商品详情'),
('2018-4-24 18:49','2018-4-24','B','购物车'),
('2018-4-24 18:46','2018-4-24','C','商品详情');
第二步,建立模型:
SELECT `user`,
created_at,
page_id,
gap1/60 AS "与第一个动作的间隔时间",
if(gap1 == 0, 0, runningDifference(gap1)/60) AS "与上一个动作的间隔时间"
FROM
(SELECT `user`,
created_at,
fist_created_at,
page_id,
created_at-fist_created_at AS gap1
FROM test_analysis ANY
LEFT JOIN
(SELECT `user` ,
min(created_at) AS fist_created_at
FROM test_analysis
GROUP BY `user`) using(`user`)) AS t
获得的结果:
大致上可以获取了时间间隔,只是没有按照楼主的要求把路径标注的很清楚。这一点,使用clickhouse的groupArray()很容易实现,我就不搞了。在这个结果上变化形式可以做很多事情。
第三步,应用该SQL,配合Redash 可视化工具做桑基图(热力学中,一种描述能量流动的图)。PS:不一定有实际的业务意义。 sql :
SELECT
user as stage0,
groupArray(stage1)[1] as stage1, groupArray(stage2)[1] as stage2, groupArray(stage3)[1] AS stage3,
count(value) AS value
FROM
(SELECT `user`,
if(page_id=='首页', page_id, NULL) AS stage1 ,
if(page_id =='商品详情', page_id, NULL) AS stage2,
if(page_id == '购物车', page_id, NULL) AS stage3,
count(*) AS value
FROM
(SELECT `user`,
created_at,
page_id,
gap1/60 AS "与第一个动作的间隔时间",
if(gap1 == 0, 0, runningDifference(gap1)/60) AS "与上一个动作的间隔时间"
FROM
(SELECT `user`,
created_at,
fist_created_at,
page_id,
created_at-fist_created_at AS gap1
FROM test_analysis ANY
LEFT JOIN
(SELECT `user` ,
min(created_at) AS fist_created_at
FROM test_analysis
GROUP BY `user`) using(`user`)) AS tt) AS test
GROUP BY `user`, page_id) AS test1
group by `user`
获得的结果:
事情搞完了,说下感受:
- 上述场景在Hive里很容易搞,用开窗函数实现比较easy,但是clickhouse不支持开窗函数,比较蛋疼。
- 建模的SQL用到了join,在需要处理的数据集规模超过128G(标配的物理机内存)时,可能会引起问题,当然是可以通过限制时间范围来解决,个人觉得不够优雅。
- 习惯了Hadoop生态的用户们有学习代价,需要重新再学一些好用的SQL函数(同时,必须放弃最好用的开窗函数。。噩耗)。
@guzhenping 多谢大神,我仔细研究下你的SQL,然后和你讨论。 确实,这样用join比较耗内存。
https://events.yandex.com/lib/talks/247/ 这里有人提出了另一个方案,我大致看懂了。供你参考。
ClickHouse的有序漏斗函数:
但是还没有release,这个就是易观的OLAP大赛,基于CK开发的拓展功能。
作者是韦万和李本旺。
@jackpgao 谢谢
官方文档已经录入了这个函数,然而没有release,他们略业余了。
@kikanjuu 可能不属于强需求吧。再等等吧。不行就去官方提个issue。