clickhouse数据模型之用户路径分析

大数据群英萃 2020-12-16

577

本文经作者授权，独家转载：

作者主页：https://www.jianshu.com/u/8f36a5e63d18

为什么要有路径分析，举个最简单的例子，你的领导想要知道用户在完成下单前的一个小时都做了什么？绝大多数人拿到这个需求的做法就是进行数据抽样观察以及进行一些简单的问卷调参工作，这种方式不但费时费力还不具有代表性，那么这个时候你就需要一套用户行为路径分析的模型作为支撑，才能快速帮组你找到最佳答案

前言

clickhouse是我见过最完美的OLAP数据库，它不仅将性能发挥到了极致，还在数据分析层面做了大量改进和支撑，为用户提供了大量的高级聚合函数和基于数组的高阶lambda函数。

企业中常用的路径分析模型一般有两种：

已经明确了要分析的路径，需要看下这些访问路径上的用户数据：关键路径分析
不确定有哪些路径，但是清楚目标路径是什么，需要知道用户在指定时间范围内都是通过哪些途径触达目标路径的：智能路径分析

关键路径分析

因为我们接下来要通过sequenceCount完成模型的开发，所以需要先来了解一下该函数的使用：

sequenceCount(pattern)(timestamp, cond1, cond2, ...)

该函数通过pattern指定事件链，当用户行为完全满足事件链的定义是会+1；其中time时间类型或时间戳，单位是秒，如果两个事件发生在同一秒时，是无法准确区分事件的发生先后关系的，所以会存在一定的误差。

pattern支持3中匹配模式：

(?N)：表示时间序列中的第N个事件，从1开始，最长支持32个条件输入；如，(?1)对应的是cond1
(?t op secs)：插入两个事件之间，表示它们发生时需要满足的时间条件（单位为秒）,支持 >=, >, <, <= 。例如上述SQL中，(?1)(?t<=15)(?2)即表示事件1和2发生的时间间隔在15秒以内，期间可能会发生若干次非指定事件。
.*：表示任意的非指定事件。

例如，boos要看在会员购买页超过10分钟才下单的用户数据那么就可以这么写：

SELECT 
    count(1) AS c1,
    sum(cn) AS c2
FROM 
(
    SELECT 
        u_i,
        sequenceCount('(?1)(?t>600)(?2)')(toDateTime(time), act = '会员购买页', act = '会员支付成功') AS cn
    FROM app.scene_tracker
    WHERE day = '2020-09-07'
    GROUP BY u_i
)
WHERE cn >= 1


┌──c1─┬──c2─┐
│ 102 │ 109 │
└─────┴─────┘


## c1是满足条件的用户数，c2是满足条件的用户行为总数

根据上面数据可以看出完成支付之前在会员购买页停留超过10分钟的用户有100多个，那么是什么原因导致用户迟迟不肯下单，接下来我们就可以使用智能路径针对这100个用户展开分析，看看他们在此期间都做了什么。

智能路径分析

智能路径分析模型比较复杂，但同时支持的分析需求也会更加复杂，如分析给定期望的路径终点、途经点和最大事件时间间隔，统计出每条路径的用户数，并按照用户数对路径进行倒序排列
虽然clickhouse没有提供现成的分析函数支持到该场景，但是可以通过clickhouse提供的高阶数组函数进行曲线救国，大致SQL如下：

方案一

 SELECT
  result_chain,
  uniqCombined(user_id) AS user_count
FROM (
  WITH
    toDateTime(maxIf(time, act = '会员支付成功')) AS end_event_maxt,  #以终点事件时间作为路径查找结束时间
    arrayCompact(arraySort(  #对事件按照时间维度排序后进行相邻去重
      x -> x.1,
      arrayFilter(  #根据end_event_maxt筛选出所有满足条件的事件 并按照<时间, <事件名, 页面名>>结构返回
        x -> x.1 <= end_event_maxt,
        groupArray((toDateTime(time), (act, page_name)))
      )
    )) AS sorted_events,
    arrayEnumerate(sorted_events) AS event_idxs,  #或取事件链的下标掩码序列，后面在对事件切割时会用到
    arrayFilter(  #将目标事件或当前事件与上一个事件间隔10分钟的数据为切割点
      (x, y, z) -> z.1 <= end_event_maxt AND (z.2.1 = '会员支付成功' OR y > 600),
      event_idxs,
      arrayDifference(sorted_events.1),
      sorted_events
    ) AS gap_idxs,
    arrayMap(x -> x + 1, gap_idxs) AS gap_idxs_,  #如果不加1的话上一个事件链的结尾事件会成为下个事件链的开始事件
    arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,  #标记切割点
    arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events  #把用户的访问数据切割成多个事件链
  SELECT
    user_id,
    arrayJoin(split_events) AS event_chain_,
    arrayCompact(event_chain_.2) AS event_chain,  #相邻去重
    hasAll(event_chain, [('pay_button_click', '会员购买页')]) AS has_midway_hit,
    arrayStringConcat(arrayMap(
      x -> concat(x.1, '#', x.2),
      event_chain
    ), ' -> ') AS result_chain  #用户访问路径字符串
  FROM (
    SELECT time,act,page_name,u_i as user_id
    FROM app.scene_tracker
    WHERE toDate(time) >= '2020-09-30' AND toDate(time) <= '2020-10-02'
    AND user_id IN (10266,10022,10339,10030)  #指定要分析的用户群
  )
  GROUP BY user_id
  HAVING length(event_chain) > 1
)
WHERE event_chain[length(event_chain)].1 = '会员支付成功'  #事件链最后一个事件必须是目标事件
AND has_midway_hit = 1   #必须包含途经点
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;

实现思路：

将用户的行为用groupArray函数整理成<时间, <事件名, 页面名>>的元组，并用arraySort函数按时间升序排序；
利用arrayEnumerate函数获取原始行为链的下标数组；
利用arrayFilter和arrayDifference函数，过滤出原始行为链中的分界点下标。分界点的条件是路径终点或者时间差大于最大间隔；
利用arrayMap和has函数获取下标数组的掩码（由0和1组成的序列），用于最终切分，1表示分界点；
调用arraySplit函数将原始行为链按分界点切分成单次访问的行为链。注意该函数会将分界点作为新链的起始点，所以前面要将分界点的下标加1；
调用arrayJoin和arrayCompact函数将事件链的数组打平成多行单列，并去除相邻重复项。
调用hasAll函数确定是否全部存在指定的途经点。如果要求有任意一个途经点存在即可，就换用hasAny函数。当然，也可以修改WHERE谓词来排除指定的途经点。
将最终结果整理成可读的字符串，按行为链统计用户基数，完成。

方案二

不设置途经点，且仅以用户最后一次到达目标事件作为参考

 SELECT
  result_chain,
  uniqCombined(user_id) AS user_count 
  FROM (
        select
          u_i as user_id,
          arrayStringConcat(  #获取访问路径字符串
           arrayCompact(   #相邻事件去重
            arrayMap(
              b - > tupleElement(b, 1),
              arraySort(   #对用户事件进行排序得到用户日志的先后顺序
                y - > tupleElement(y, 2),
                arrayFilter(
                  (x, y) - > y - x.2 > 3600  #找到目标节点前1小时内的所有事件   
                  arrayMap(
                    (x, y) - > (x, y),
                    groupArray(e_t),
                    groupArray(time)
                  ), 
                  arrayWithConstant(
                    length(groupArray(time)),
                    maxIf(time, e_t = '会员支付成功')  #设置目标节点
                  )
                )
              )
            )
           ),
          '->'
         ) result_chain
        from
          bw.scene_tracker
        where
          toDate(time) >= '2020-09-30' AND toDate(time) <= '2020-10-02' AND user_id IN (10266,10022,10339,10030)
        group by
          u_i
  ) tab 
  GROUP BY result_chain
  ORDER BY user_count DESC LIMIT 20;

简单说一下上面用到的几个高阶函数：

1.arrayJoin

可以理解为行转列操作

SELECT arrayJoin([1, 2, 3, 4]) AS data


┌─data─┐
│    1 │
│    2 │
│    3 │
│    4 │
└──────┘

2.uniqCombined

clickhouse中的高性能去重统计函数，类似count(distinct field)，数据量比较小的时候使用数组进行去重，中的数据使用set集合去重，当数据量很大时会使用hyperloglog方式进行j近似去重统计；如果想要精度更改可以使用uniqCombined64支持64位bit

SELECT uniqCombined(data)
FROM 
(
    SELECT arrayJoin([1, 2, 3, 1, 4, 2]) AS data
)


┌─uniqCombined(data)─┐
│                  4 │
└────────────────────┘

3. arrayCompact
对数组中的数据进行相邻去重，用户重复操作的事件只记录一次

SELECT arrayCompact([1, 2, 3, 3, 1, 1, 4, 2]) AS data


┌─data──────────┐
│ [1,2,3,1,4,2] │
└───────────────┘

4. arraySort
对数组中的数据按照指定列进行升序排列；降序排列参考arrayReverseSort

SELECT arraySort(x -> (x.1), [(1, 'a'), (4, 'd'), (2, 'b'), (3, 'c')]) AS data


┌─data──────────────────────────────┐
│ [(1,'a'),(2,'b'),(3,'c'),(4,'d')] │
└───────────────────────────────────┘

5. arrayFilter
只保留数组中满足条件的数据

SELECT arrayFilter(x -> (x > 2), [12, 3, 4, 1, 0]) AS data


┌─data─────┐
│ [12,3,4] │
└──────────┘

6. groupArray
将分组下的数据聚合到一个数组集合中，类似hive中的collect_list函数

SELECT 
    a.2,
    groupArray(a.1)
FROM 
(
    SELECT arrayJoin([(1, 'a'), (4, 'a'), (3, 'a'), (2, 'c')]) AS a
)
GROUP BY a.2


┌─tupleElement(a, 2)─┬─groupArray(tupleElement(a, 1))─┐
│ c                  │ [2]                            │
│ a                  │ [1,4,3]                        │
└────────────────────┴────────────────────────────────┘

7. arrayEnumerate
或取数组的下标掩码序列

SELECT arrayEnumerate([1, 2, 3, 3, 1, 1, 4, 2]) AS data


┌─data──────────────┐
│ [1,2,3,4,5,6,7,8] │
└───────────────────┘

8.arrayDifference
参数必须是数值类型；计算数组中相邻数字的差值，第一个值为0

SELECT arrayDifference([3, 1, 1, 4, 2]) AS data


┌─data──────────┐
│ [0,-2,0,3,-2] │
└───────────────┘

9.arrayMap
对数组中的每一列进行处理，并返回长度相同的新数组

SELECT arrayMap(x -> concat(toString(x.1), ':', x.2), [(1, 'a'), (4, 'a'), (3, 'a'), (2, 'c')]) AS data


┌─data──────────────────────┐
│ ['1:a','4:a','3:a','2:c'] │
└───────────────────────────┘

10.arraySplit

按照规则对数组进行分割

SELECT arraySplit((x, y) -> y, ['a', 'b', 'c', 'd', 'e'], [1, 0, 0, 1, 0]) AS data


┌─data──────────────────────┐
│ [['a','b','c'],['d','e']] │
└───────────────────────────┘


## 遇到下标为1时进行分割，分割点为下一个 数组的起始点；注意，首项为1还是0不影响结果

11.has
判断数组中是否包含某个数据

SELECT has([1, 2, 3, 4], 2) AS data


┌─data─┐
│    1 │
└──────┘

12.arrayStringConcat
将数组转为字符串，需要注意的是，这里的数组项需要是字符串类型

SELECT arrayStringConcat(['a', 'b', 'c'], '->') AS data


┌─data────┐
│ a->b->c │
└─────────┘

13.arrayWithConstant
以某个值进行填充生成数组

SELECT arrayWithConstant(4, 'abc') AS data


┌─data──────────────────────┐
│ ['abc','abc','abc','abc'] │
└───────────────────────────┘

clickhouse数据模型之用户路径分析

前言

关键路径分析

智能路径分析

方案一

评论