【Hive SQL 每日一题】统计用户留存率_hive sql计算留存

用户留存率

用户留存率（User Retention Rate）是一个衡量用户在特定时间段内继续使用某个产品或服务的比例的指标。

它反映了用户对产品的持续兴趣和满意度，是评估产品成功与否的重要指标之一。用户留存率通常以百分比表示，并且可以按天、周、月等时间单位进行计算。

计算用户留存率的基本公式是：

假设你是一家游戏开发公司，在游戏发布后第一天有 10000 名玩家下载并开始玩游戏。到第七天还有 5000 名玩家在继续玩这个游戏，那么第一周的用户留存率如下：

测试数据

DROP TABLE IF EXISTS user_activity;
CREATE TABLE user_activity (
    user_id INT,
    activity_date DATE
);

INSERT INTO user_activity (user_id, activity_date) VALUES
(1, '2024-05-10'),
(1, '2024-05-11'),
(1, '2024-05-15'),
(2, '2024-05-10'),
(2, '2024-05-11'),
(2, '2024-05-12'),
(2, '2024-05-16'),
(3, '2024-05-10'),
(3, '2024-05-11'),
(3, '2024-05-13'),
(3, '2024-05-15'),
(4, '2024-05-10'),
(4, '2024-05-11'),
(4, '2024-05-12'),
(4, '2024-05-13'),
(4, '2024-05-14'),
(5, '2024-05-10'),
(5, '2024-05-11'),
(5, '2024-05-12'),
(5, '2024-05-13'),
(5, '2024-05-16'),
(6, '2024-05-10'),
(6, '2024-05-12'),
(6, '2024-05-14'),
(6, '2024-05-16');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

需求说明

统计系统上线后每天的用户留存率（假设该系统上线时间为 2024-05-10）。

结果示例：

结果按 activity_date 升序排列

其中：

• activity_date 统计的日期；
• user_retention_rate 当天的用户留存率。

需求实现

select
    activity_date,
    concat(cast(user_count/lag_user_count as decimal(5,2)) * 100,"%") user_retention_rate
from
    (select
        activity_date,
        user_count,
        lag(user_count,1,0) over(order by activity_date) lag_user_count
    from
       (select
          activity_date,
          count(distinct user_id) user_count
        from
          user_activity
        group by
          activity_date)t1 )t2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

实现用户留存率的需求其实很简单，这里要求在系统上线后，统计每天的用户留存率，我们每次只需要算出“今天”的用户数量与“昨天”的用户数量比例即可。

首先，取到每天登录的用户数量：

select
    activity_date,
    count(distinct user_id) user_count
from
    user_activity
group by
    activity_date;
1
2
3
4
5
6
7

这里每个用户可能在当天登录多次，所以需要进行去重统计。

接下来只需要获取到隔日的用户登录人数，然后求两者的比例即可得到用户的留存率：

select
    activity_date,
    user_count,
    lag(user_count,1,0) over(order by activity_date) lag_user_count
from
   (select
      activity_date,
      count(distinct user_id) user_count
    from
      user_activity
    group by
      activity_date)t1;
1
2
3
4
5
6
7
8
9
10
11
12

这里通过 lag 窗口函数来获取上一天的用户登录人数，如果是系统上线第一天，则没有上一天，用 0 表示。

最后，求出当天与上一天的用户比例即可：

select
    activity_date,
    concat(cast(user_count/lag_user_count as decimal(5,2)) * 100,"%") user_retention_rate
from
    (select
        activity_date,
        user_count,
        lag(user_count,1,0) over(order by activity_date) lag_user_count
    from
       (select
          activity_date,
          count(distinct user_id) user_count
        from
          user_activity
        group by
          activity_date)t1 )t2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

输出结果如下：

最终得到的结果是每天的次日留存率趋势，因为我们每次取的数据都是当日与次日的比例。

这里变换一下需求，求系统上线第 n 天的用户留存率，也就是说，后续的留存率，都是基于首日的登录人数来进行计算的，如下所示：

假设第一天登录 6 人，第二天登录人数 3 人，那么 1 日留存率为：3/6=50%；
假设第一天登录 6 人，第三天登录人数 2 人，那么 2 日留存率为：2/6=34%；
假设第一天登录 6 人，第四天登录人数 4 人，那么 3 日留存率为：4/6=67%；
…

select
    activity_date,
    datediff(activity_date,"2024-05-10") date_diff_num,
    concat(cast(user_count/first_user_count as decimal(5,2)) * 100,"%") user_retention_rate
from
   (select
      1 a,
      count(distinct user_id) first_user_count
    from
      user_activity
    where
      activity_date = "2024-05-10")t1
join
   (select
      1 a,
      activity_date,
      count(distinct user_id) user_count
    from
      user_activity
    where
      activity_date != "2024-05-10"
    group by
      activity_date)t2
on
  t1.a = t2.a;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

在 t1 查询中，去重统计出系统上线首日的用户登录人数；

在 t2 查询中，按日期聚合去重统计除首日外的每日用户登录人数；

设立一个为真的连接条件，将数据进行组合，计算第 n 日的用户留存率。

最终结果如下所示：