留存分析函数--ByteHouse云数仓版-火山引擎

文档中心

立即注册

导航

留存分析函数

最近更新时间：2025.06.06 15:23:30首次发布时间：2025.06.06 15:23:30

本文介绍留存分析函数的基础概念和函数的基本用法。

功能概述

业务场景

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

留存计算逻辑简介：

先使用 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 函数将初访（first_events）及回访（return_events）行为数据预聚合到指定的时间槽位中，得到 Array(UInt8)格式的访问情况位集 bitset。这一步将时间范围切分成离散的时间槽位，并且用位集表示每个槽位上事件发生与否。

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。

retention2()

使用 **retention2 **聚合用户初访（first_events）、回访（return_events）位集得到每个时间槽对应的留存数据。

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)格式的留存数组。

使用示例

示例模型

如下示例用表是由用户行为事实表 Log 和用户维度表 User 组成常用的星型模型，后文将基于此表演示留存分析函数的具体用法。

CREATE TABLE -- 事件日志表
  default.LOG (
    `Visitor` UInt32,
    `EventName` String,
    `EventTime` DateTime,
    `Province` String,
    `City` String
  ) ENGINE = CnchMergeTree
  ORDER BY Visitor;
  
  CREATE TABLE -- 用户表
  default.User (
    `Visitor` UInt32,
    `Fvisit_Time` DateTime,
    `Fvisit_Province` String,
    `Fvisit_City` String
  ) ENGINE = CnchMergeTree
  ORDER BY Visitor;

事件分析

在下面的例子中，返回结果表达了 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

留存分析

观察一周 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

该解析结果说明如下：

行维度（i）：表示用户首次访问的第 i 天，（i=1 为第 1 天，依次类推）。
列维度（j）：表示用户首次访问后第 j 天回访。
当 i = j 时，数值表示第 i 天首次访问的总人数。

上述解析结果中，

第一行第一列（1,1）的 109777 代表第一天首次访问的总人数；
第一行第二列（1,2）的 61903 代表第一天首次访问中，在第二天回访的人数;
第二行第四列（2,4）的 62500 代表第二天首次访问中，在第四天回访的人数。

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