概述--ByteHouse 企业版-火山引擎

文档中心

立即注册

导航

概述

最近更新时间：2024.06.28 11:59:38首次发布时间：2024.05.14 15:43:29

ByteHouse 根据用户行为分析使用场景，定制了部分函数，主要包括：

留存分析函数
漏斗分析函数
路径分析函数

相比拼装 SQL或者使用 ClickHouse 原生函数，使用 ByteHouse 自研的专用函数更为高效。本文档详述了这些函数的使用方式。

示例模型

下面是一张示例用表，是由用户行为事实表 Log 和用户维度表 User 组成常用的星型模型。
后文将基于此表为大家演示函数的具体用法。

CREATE TABLE -- 事件日志表
  default.LOG (
    `Visitor` UInt32,
    `EventName` String,
    `EventTime` DateTime,
    `Province` String,
    `City` String
  ) ENGINE = Distributed('your_cluster', 'default', 'LOG_Example_local')
  
  CREATE TABLE -- 用户表
  default.User (
    `Visitor` UInt32,
    `Fvisit_Time` DateTime,
    `Fvisit_Province` String,
    `Fvisit_City` String
  ) ENGINE = Distributed('your_cluster', 'default', 'User_Example_local')
  
  -- 仅展示 Distributed 表 Schema，local 表略

留存分析函数

业务场景

选定一段时间范围，观察此时间范围内每一个时间单位的符合某维度的用户在一段时间范围之后的留存。

留存计算逻辑简介：

先使用 genArrayIf 函数将初访（first_events）及回访（return_events）行为数据预聚合到指定的时间槽位中，得到 Array(UInt8)格式的访问情况位集 bitset。这一步将时间范围切分成离散的时间槽位，并且用位集表示每个槽位上事件发生与否。
再使用 **retention2 **聚合用户初访（first_events）、回访（return_events）位集得到每个时间槽对应的留存数据。

基础概念

时间槽位（Step）：即步长。比如观察 7 天中每 1 天的留存数据，则时间槽位数量（number_step）为 7，时间槽位时长（step_time）为 86400 （单位：秒，86400 秒即一天）。
事件位集：计算留存的中间数据，返回为 [0,1,0,1.....] 代表了在每个时间槽位中，事件是否发生。
初访事件（first_events），即用户在每个时间槽位中，是否发发生了初访事件。举例，用户注册。
回访事件（return_events），即用户在每个时间槽位中，是否发发生了回访事件。举例，用户登录。
- 在下面的例子中，first_events（初访事件）为 [13,2] ，第一个十进制数 13 对应 8 位 2 进制数 0000 1101，第二个十进制数 2 对应 8 位二进制数 0000 0010，这个数组对应的 16 位二进制表示为 0000 0010 0000 1101，一共16位，可以表示16天的事件发生与否，二进制表示的最低位对应第1天，最高位对应第16天，则在第1,3,4,9 天发生了目标事件。同理，return_events（回访事件）数组的含义是在第1,3,9,10 天发生了目标事件。

first_events数组：[13,2]
对应的bit 表示：0000 0010     0000 1101
对应的10进制数     2              13
含义：在第1,3,4,10 天发生了目标事件

return_events 数组：[5,3]
对应的bit 表示：0000 0011     0000 0101
对应的10进制数     3              5
含义：在第1,3,9,10 天发生了目标事件

留存数组：留存即为以用户发生了初访事件的视角看，未来是否在部分槽位发生了回访事件。

--示例：
SELECT retention2(10)([13, 2], [5, 3])

┌─retention2(10)([13, 2], [5, 3])───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [1,0,1,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,1,0,0,0,1,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

上述例子中，即是时间槽位数量为 10 天，槽位时长为 1 天，first_event 与 return_event 为 [13,2],[5,3] 前提下的留存数组。返回的留存数组 number_stepsnumber_steps 的数组，在上例中，是长度为1010 的数组，因此这个数组应该切分成 10 行去看结果，留存数组的每一行代表以 first_events 中第 i 天为起始事件观察时间，与 return_events 中的事件按位做“与”操作的结果。

1,0,1,0,0,0,0,0,1,1, --以第1天为起始观察点，用户在第1,3,9,10 天进行了回访，所以1,3,9，10列为1
0,0,0,0,0,0,0,0,0,0, --以第2天为起始观察点，第二天没有发生任何事件，所以第二行都是0
0,0,1,0,0,0,0,0,1,1, --以第3天为起始观察点，用户在第3,9,10 天进行了回访，所以3,9,10列为1
0,0,0,1,0,0,0,0,1,1, --以第4天为起始观察点，用户在第9,10 天进行了回访，所以9,10列为1。对第4天本身来说，虽然没有回访事件，但默认也在对应列输出1
0,0,0,0,0,0,0,0,0,0, 
0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,1

将这些留存数组按城市、渠道等聚合，就可以得到留存数据，如：

select city, retention2(3)(..., ...) from ... group by city
--得到某一城市的留存数组为：
22,11,1
0,5,3
0,0,1

函数释义

genArrayIf()

genArrayIf(number_steps, begin_timestamp, step_time)(timestamp, if_expr)

输入参数：

number_steps：时间槽位的数量。
begin_timestamp：分析开始时间戳，如数据筛选的范围从 2022-06-01 开始，所以开始时间戳为 toUInt64(toUnixTimestamp('2022-06-01')) = 1654012800。
step_time：1个时间槽位持续时长。
timestamp: 事件时间戳所在列。需要先转换成时间戳，再转换成Uint64 格式。
if_expr：对留存条件的定义，满足条件的将会被认为行为发生。

输出参数：

输出 Array(UInt8)格式的访问情况位集 bitset。

示例：
在下面的例子中，返回结果表达了7天中，每个单位时间（1天）中是否发生了 EventName = 'E0002' 的事件。

SELECT
    Visitor,
    genArrayIf(7, 1654012800, 86400)(toUInt64(toUnixTimestamp(LOG.EventTime)), EventName = 'E0002') AS return_events
FROM LOG
WHERE (EventTime >= '2022-06-01') AND (EventTime < '2022-06-08') AND (EventName = 'E0002')
GROUP BY Visitor

retention2()

retention2(number_steps)(first_events, return_events)

输入参数：

number_steps：时间槽位的数量。number_steps 取值和genArrayIf 中的number_steps保持一致。

说明

注意：

number_steps取值范围受初访（first_events）、回访（return_events）位集大小的限制的，规则是(events_array.size()-1)*8 < number_steps <= events_array.size()*8。比如下面示例中 first_events 的 UInt8 类型元素个数为 2，则传入的窗口大小应该为 (8, 16] 间某个值。
初访数组，回访数组大小需要保持一致，不一致的情况下最终计算结果会不正确，出现很多0。

first_events：初访事件的位集，需要genArrayIf返回。
return_events：回访事件的位集，需要genArrayIf返回。

输出参数：

输出 Array(UInt64)格式的留存数组。

示例 SQL

观察一周 7 天的窗口内，按城市分组，首访（'E0001'）到回访（'E0002'）留存情况。

SELECT
    City,
    retention2(7)(first_events, return_events)
FROM
(
    SELECT
        City,
        Visitor,
        genArrayIf(7, 1654012800, 86400)(toUInt64(toUnixTimestamp(LOG.EventTime)), EventName = 'E0001') AS first_events
    FROM LOG
    WHERE (EventTime >= '2022-06-01') AND (EventTime < '2022-06-08') AND (EventName = 'E0001')
    GROUP BY
        Visitor,
        City
) AS t1
ANY LEFT JOIN
(
    SELECT
        Visitor,
        genArrayIf(7, 1654012800, 86400)(toUInt64(toUnixTimestamp(LOG.EventTime)), EventName = 'E0002') AS return_events
    FROM LOG
    WHERE (EventTime >= '2022-06-01') AND (EventTime < '2022-06-08') AND (EventName = 'E0002')
    GROUP BY Visitor
) AS t2 ON t1.Visitor = t2.Visitor
GROUP BY City
ORDER BY City ASC
SETTINGS distributed_product_mode = 'local', distributed_group_by_no_merge = 0

得到 7*7 的数组，如上图中 city1 解析为：

109777,61903,62185,62116,62064,62066,61952,
0,109978,61922,62500,61817,62671,62417,
0,0,110033,62193,62177,62230,62287,
0,0,0,109715,61833,62142,62119,
0,0,0,0,110598,62493,62515,
0,0,0,0,0,109508,61909,
0,0,0,0,0,0,110746

可根据需要，计算出从 Day1->Day6 的 1 日转化率。
根据实际需求，可以将时间槽修改为周、月等。

漏斗分析函数

业务场景：选定一段时间范围，观察此时间范围内每一个时间单位（天）内用户按一定时间范围划分的漏斗分层汇总情况。
漏斗计算逻辑简介：

先使用 finderFunnel 计算每个用户在一段时间窗口内的表现；
再使用 funnelRep 聚合 finderFunnel 的结果，形成漏斗每一层所有用户的汇总结果

函数示意

finderFunnel()

finderFunnel(window, start_timestamp, check_granularity, watch_numbers)(server_timestamp, client_timestamp, check_event1, check_event2...)

window：分析观察的窗口总时长时长，单位和client_timestamp 一致，下例中为 86400 *7 秒，也即 7 天。
start_timestamp：分析开始时间戳，如分析从 2022-06-01 开始，所以开始时间戳为 toUInt64(toUnixTimestamp('2022-06-01')) = 1654012800
check_granularity: 一个观察步长持续时间，多大粒度内来计算转化分析，单位和client_timestamp 一致，如 1 天则为 86400（单位：秒）。
watch_numbers: 观察几个步长，如check_granularity是86400（1 天），watch_numbners = 7，指代从start_timestamp开始观察 7 天每天的漏斗情况
server_timestamp：事件发生服务器时间戳列，需转化为UInt64 类型，用于函数运行时，计算事件所属时间槽位/步进。
client_timestamp：事件发生客户端时间戳列，需转化为UInt64 类型，用于函数运行时对数据进行排序。
check_event: 计算转化的事件列表，在window定义的分析时长范围内满足条件的将会被认为事件触发有效。如有3个事件，则输入：EventName = 'E0001', EventName = 'E0002', EventName = 'E0003'

funnelRep()

funnelRep(number_steps, evnet_count)(funnel_res)

number_steps：UInt 型数字，包含的时间槽位数量，通常和 finderFunnel函数中watch_numners保持一致。
evnet_count: UInt 型数字，表示事件转化链上事件总数，通常和finderFunnel函数中check_event的时间数量保持一致，
funnel_res ：转化步骤列表，由 finderFunnel 子查询产生，包含每一个用户产生的所有转化步骤列表。

返回结果：

返回 2 维度数组，输出的二维数组中，总计有 1 个汇总的结果 + 与number_steps 个子数组，第一个子数组是汇总的结果，第二个子数组是第一个时间槽在window定义的分析时长范围内的漏斗计算结果，以此类推。

漏斗分析示例SQL

以下示例指代返回从 2022/6/1-2022/6/8 的 7 天中， 'E0001', 'E0002', 'E0003' 3 个事件的转化漏斗：

SELECT funnelRep(7, 3)(funnel_res)
FROM
(
    SELECT finderFunnel(7 * 86400, 1654012800, 86400, 7)(toUInt64(toUnixTimestamp(LOG.EventTime)), toUInt64(toUnixTimestamp(LOG.EventTime)), LOG.EventName = 'E0001', LOG.EventName = 'E0002', LOG.EventName = 'E0003') AS funnel_res
    FROM LOG
    LEFT JOIN
    (
        SELECT *
        FROM User AS t
    ) AS U ON LOG.Visitor = U.Visitor
    WHERE (EventTime >= '2022-06-01 00:00:00') AND (EventTime <'2022-06-08 00:00:00') AND (EventName IN ('E0001', 'E0002', 'E0003'))
    GROUP BY Visitor
)
SETTINGS distributed_product_mode = 'local', distributed_group_by_no_merge = 1

上面的示例SQL 返回为

┌─funnelRep(7, 3)(funnel_res)─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [[5994501,5944849,56423],[3791527,3780488,39431],[3792876,3767458,33024],[3792031,3733089,26709],[3791519,3656585,20668],[3791859,3480158,14385],[3793389,3077233,8691],[3792344,2143108,3641]] │

即整体漏斗（E0001->E0002->E0003）为：[5994501,5944849,56423]
第 1 天的漏斗为：[3791527,3780488,39431]
...
第 7 天的漏斗：[3792344,2143108,3641]

路径分析函数

pathSplit
pathCount

业务场景

选定一段时间范围，分析此时间范围内用户行为路径，在前端展示效果大致如下
路径分析计算逻辑简介：

先使用 pathSplit 生成每个用户所有的路径组合成的数组。
再使用 array join 将单个用户单行包含所有路径的二维数组展开成多行单个路径。
再使用 pathCount 统计所有用户的路径转化情况。

基础概念

事件（event）：发生的事件，在此语境下，也指在路径分析图中展示的页面访问节点；
属性（prop）：相当于给对应事件打上 tag；
层级（level）：也可理解为图中的路径深度，超过 x 个节点截断路径；
节点（node）：在路径分析的语境下，每个节点即为一个页面。
边（edge）：页面 1 到页面 2 的访问路径。

函数示意

pathSplit()

pathSplit(session_time, level)(timestamp, event, prop)

输入参数：

session_time：路径分析的时间窗口，单位和timestamp 保持一致，下例中为 600 秒，也即只计算首个event 发生后 10 分钟内形成的路径；
level：路径深度，超过 x 个节点截断路径；
timestamp：时间戳 所在列，需要先转换成时间戳，再转换成 Uint64 格式；
event：事件列，仅支持数值类型，需要通过 multiIf 显式指定；

如下例中的：multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0003', 3, 0) AS e

prop：属性列，没有时传 '' 占位；

输出参数（中间结果）：
返回二维数组：[(e1, p1), (e2, p2), (e1, p2)]

(ex,px)：对应事件流，即此条path上发生的事件
e：事件，对应event 列中 multiif定义的事件编号
p：事件对应属性，对应prop

pathCount()

pathCount(y, x)(path, cnt1, cnt2)

pathCount 返回界面展示需要的数据，x 表示横向的路径深度（如上图为 4），
前端展示需要的数据其实就是每个浅蓝色方块代表的node，和浅蓝色方块之间的变edge，都是由pathCount输出

y：纵向单层展示深度，如上图中黄色圈的部分，即 y = 5；
x：横向路径深度，超过 x 个节点截断路径，如上图中 x = 4；
path：pathSplit的返回 path 列（需通过 arrayJoin 展开）；
cnt1：路径权重1，通常直接传 1 就行，用于计算路径 去重后的次数；
cnt2：路径权重2，通常传pathSplit子查询里的 count()求出的 cnt ，用来求路径 不去重的总次数。

返回格式：

返回格式为 3 元组 ([node_index],[nodes], [edges])，通过此结果集组成一张页面路径转化的流图：
node_index：格式为：(event, prop) ，事件节点展示。
- event 为事件的下标编号，若之前 pathSplit 时 event 填写 multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0)，则此时返回 1 对应 'E0001' ，2对应 'E0002'，3对应 'E0004'；
- prop为 pathSplit 传入的 prop。
nodes：二维数组。表示每个节点在各层级（level，下标从 0 开始）出现的去重次数、次数，格式为：(level, node_index, cnt1, cnt2)。
- level：层级，；
- node_index：事件的 index，参考 node_index 数组中和事件的映射关系；
- cnt1：节点访问的去重次数；
- cnt2：节点访问的次数；
edge：二维数组，每条边在各层级出现的去重次数、次数，格式为：

(level, from_node_index, to_node_index, cnt1, cnt2)

level：层级，即输入的 x；
from_node_index：来源节点，参考 node_index 数组中和事件的映射关系；
to_node_index：目标节点，参考 node_index 数组中和事件的映射关系；to_node_index 用 65535 标记 from_node_index 已经是终点的情况，用户进行到这个事件之后就流失了的情况;
cnt1：节点访问的去重次数；
cnt2：节点访问的次数；

路径分析示例SQL

指定一个用户 Visitor = 964049 来说明。
该示例为 2022/6/1-2022/6/2 中，发生 'E0001', 'E0002', 'E0004' 事件的路径数据，展示发生事件后 10 分钟内的页面信息，展示的页面路径深度为 3 层。
首先先通过 pathSplit 计算中间结果：

SELECT Visitor,pathSplit(600, 3) (toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '') AS path
FROM LOG
WHERE (EventTime >= '2022-06-01 00:00:00')
  AND (EventTime < '2022-06-02 00:00:00')
  AND (EventName IN ('E0001', 'E0002', 'E0004'))
  and Visitor = 964049
GROUP BY Visitor

--返回：
┌─Visitor─┬─path────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  964049 │ [[(1,''),(2,''),(3,'')],[(1,'')],[(1,''),(2,''),(3,'')],[(1,''),(2,''),(2,'')],[(1,'')],[(1,'')],[(1,'')],[(1,''),(3,''),(3,'')],[(1,''),(2,''),(3,'')],[(1,''),(1,''),(3,'')],[(1,'')],[(1,''),(2,''),(3,'')],[(1,'')],[(1,''),(2,''),(2,'')]] │
└─────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

再通过arrayJoin 把单行的path 返回的二维数组表示的多条path展开成多行单条path，计算每一条path出现的次数

SELECT path    AS path,
             count() AS cnt
      FROM (SELECT arrayJoin(pathSplit(600, 3)(toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '')) AS path
            FROM LOG
            WHERE (EventTime >= '2022-06-01 00:00:00')
              AND (EventTime < '2022-06-02 00:00:00')
              AND (EventName IN ('E0001', 'E0002', 'E0004'))
              and Visitor = 964049
            GROUP BY Visitor)
      GROUP BY path
 --返回
┌─path───────────────────┬─cnt─┐
│ [(1,'')]               │   6 │
│ [(1,''),(1,''),(3,'')] │   1 │
│ [(1,''),(2,''),(3,'')] │   4 │
│ [(1,''),(2,''),(2,'')] │   2 │
│ [(1,''),(3,''),(3,'')] │   1 │
└────────────────────────┴─────┘

再通过 pathCount 计算出最终的 3*3 结果（路径深度为 3，同时每层至多展示的节点为 3 个）：

SELECT pathCount(3, 3) (path, 1, cnt)
FROM (SELECT path    AS path,
             count() AS cnt
      FROM (SELECT arrayJoin(pathSplit(600, 3)(toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '')) AS path
            FROM LOG
            WHERE (EventTime >= '2022-06-01 00:00:00')
              AND (EventTime < '2022-06-02 00:00:00')
              AND (EventName IN ('E0001', 'E0002', 'E0004'))
              and Visitor = 964049
            GROUP BY Visitor)
      GROUP BY path )
--返回
┌─pathCount(5, 3)(path, 1, cnt)─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ([(1,''),(3,''),(2,'')],
[[0,0,5,14],[1,0,1,1],[1,1,1,1],[1,2,2,6],[2,1,3,6],[2,2,1,2]],
[[0,0,0,1,1],[0,0,1,1,1],[0,0,65535,1,6],[0,0,2,2,6],[1,2,2,1,2],[1,1,1,1,1],[1,0,1,1,1],[1,2,1,1,4]]) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

结果为：

node_index：[(1,''),(3,''),(2,'')]，表示事件编号映射为 0->1(E0001),1->3(E0004),2->2(E0002)
nodes：[[0,0,5,14],[1,0,1,1],[1,1,1,1],[1,2,2,6],[2,1,3,6],[2,2,1,2]]
- 第 1 层，[0,0,5,14]
  - 仅一个节点“事件 1”，即 E0001；
  - 该节点的去重访问次数为 5，总访问次数为14
- 第 2 层，[1,0,1,1],[1,1,1,1],[1,2,2,6]
  - 发生了三个事件：E0001，E0004，E0002
  - 3个节点的去重访问次数为 1,1,2；总访问次数为 1,1,6
- ....
edge：[0,0,0,1,1],[0,0,1,1,1],[0,0,65535,1,6],[0,0,2,2,6],[1,2,2,1,2],[1,1,1,1,1],[1,0,1,1,1],[1,2,1,1,4]
- 第一层，存在以下几条边：
  - 0->0：即 'E0001'->'E0001'，1 个访问
  - 0->1：1 个访问
  - 0->65535（即已经是终点），1个去重访问
  - 0->2，2个去重访问
- 第二层，存在以下几条边：
  - 2->2，1 个去重访问
  - 1->1，1 个去重访问
  - 0->1，1 个去重访问
  - 2->1，1 个去重访问
可以将结果信息补充为页面路径分析，如