怎么用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)

dailidong 1楼•3 个月前

请百度olap大赛，去年大赛场景就是该场景

guzhenping 2楼•2 个月前

第一步建表：

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`

获得的结果：