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

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

前言

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没有提供现成的分析函