retention

计算指定时间段内的用户留存率。此函数接受 1 到 31 个条件，并评估每个条件是否为真。 如果条件评估为真，则返回 1。 否则，返回 0。 最终返回一个 0 和 1 的数组。 您可以根据此数据计算用户留存率。

语法

ARRAY retention(ARRAY input)

参数

input：条件数组。 最多可以传入 31 个条件。 用逗号分隔多个条件。

返回值

返回一个 0 和 1 的数组。 0 和 1 的数量与输入条件的数量相同。

评估从第一个条件开始。

• 如果条件评估为真，则返回 1。 否则，返回 0。
• 如果第一个条件不为真，则当前位置及其后面的位置都设置为 0。

示例

1. 创建一个名为 test 的表并插入数据。

   CREATE TABLE test(
       id TINYINT,
       action STRING,
       time DATETIME
   )
   ENGINE=olap
   DUPLICATE KEY(id)
   DISTRIBUTED BY HASH(id);
   
   INSERT INTO test VALUES 
   (1,'pv','2022-01-01 08:00:05'),
   (2,'pv','2022-01-01 10:20:08'),
   (1,'buy','2022-01-02 15:30:10'),
   (2,'pv','2022-01-02 17:30:05'),
   (3,'buy','2022-01-01 05:30:09'),
   (3,'buy','22022-01-02 08:10:15'),
   (4,'pv','2022-01-02 21:09:15'),
   (5,'pv','2022-01-01 22:10:53'),
   (5,'pv','2022-01-02 19:10:52'),
   (5,'buy','2022-01-02 20:00:50');

2. 从 test 查询数据。

   MySQL > select * from test order by id;
   +------+--------+---------------------+
   | id   | action | time                |
   +------+--------+---------------------+
   |    1 | pv     | 2022-01-01 08:00:05 |
   |    1 | buy    | 2022-01-02 15:30:10 |
   |    2 | pv     | 2022-01-01 10:20:08 |
   |    2 | pv     | 2022-01-02 17:30:05 |
   |    3 | buy    | 2022-01-01 05:30:09 |
   |    3 | buy    | 2022-01-02 08:10:15 |
   |    4 | pv     | 2022-01-02 21:09:15 |
   |    5 | pv     | 2022-01-01 22:10:53 |
   |    5 | pv     | 2022-01-02 19:10:52 |
   |    5 | buy    | 2022-01-02 20:00:50 |
   +------+--------+---------------------+
   10 rows in set (0.01 sec)

3. 使用 retention 计算用户留存率。

   示例 1：针对以下条件评估用户行为：在 2022-01-01 浏览商品页面 (action='pv') 并在 2022-01-02 下订单 (action='buy')。

   MySQL > select id, retention([action='pv' and to_date(time)='2022-01-01',
                                 action='buy' and to_date(time)='2022-01-02']) as retention 
   from test 
   group by id
   order by id;
   
   +------+-----------+
   | id   | retention |
   +------+-----------+
   |    1 | [1,1]     |
   |    2 | [1,0]     |
   |    3 | [0,0]     |
   |    4 | [0,0]     |
   |    5 | [1,1]     |
   +------+-----------+
   5 rows in set (0.01 sec)

   在结果中

   • 用户 1 和 5 满足两个条件，返回 [1,1]。

   • 用户 2 不满足第二个条件，返回 [1,0]。

   • 用户 3 满足第二个条件，但不满足第一个条件。 返回 [0,0]。

   • 用户 4 不满足任何条件，返回 [0,0]。

   示例 2：计算在 2022-01-01 浏览商品页面 (action='pv') 并在 2022-01-02 下订单 (action='buy') 的用户百分比。

   MySQL > select sum(r[1]),sum(r[2])/sum(r[1])
   from (select id, retention([action='pv' and to_date(time)='2022-01-01',
                               action='buy' and to_date(time)='2022-01-02']) as r 
   from test 
   group by id 
   order by id) t;
   
   +-----------+---------------------------+
   | sum(r[1]) | (sum(r[2])) / (sum(r[1])) |
   +-----------+---------------------------+
   |         3 |        0.6666666666666666 |
   +-----------+---------------------------+
   1 row in set (0.02 sec)

   返回值是 2022-01-02 的用户留存率。

关键词

retention, retention rate, RETENTION