目录
题目和要求
表：Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
（player_id，event_date）是此表的主键(具有唯一值的列的组合)
这张表显示了某些游戏的玩家的活动情况
每一行表示一个玩家的记录，在某一天使用某个设备注销之前，登录并玩了很多游戏（可能是 0）
玩家的 安装日期 定义为该玩家的第一个登录日。
我们将日期 x 的 第一天留存率 定义为：假定安装日期为 X 的玩家的数量为 N ，其中在 X 之后的一天重新登录的玩家数量为 M，M/N 就是第一天留存率，四舍五入到小数点后两位。
编写解决方案，报告所有安装日期、当天安装游戏的玩家数量和玩家的 第一天留存率。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-01 | 0 |
| 3 | 4 | 2016-07-03 | 5 |
+-----------+-----------+------------+--------------+
输出：
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2 | 0.50 |
| 2017-06-25 | 1 | 0.00 |
+------------+----------+----------------+
解释：
玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天留存率是 1/2=0.50
玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天留存率为 0/1=0.00
1. 题目代码
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-01', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');
2. 解题分析图览
方法1: avg条件 无join 代码最短的方法
方法2: join avg条件 (join on and 效率很高)
方法3: 与方法1一样灵活,但是效率更高
3. 难点分析
本题的难点主要在于：
-
注册时间的确定：需要为每位用户找到最早的
event_date作为注册时间，这通常需要使用聚合函数如MIN。 -
留存率的计算：计算特定天数后的留存率涉及日期的操作和逻辑判断，需要正确地将注册时间与后续活动日期进行比较。
-
优化查询性能：在数据量较大的情况下，如何通过合理的
JOIN和聚合来优化查询性能，是实际工作中需要考虑的关键问题。 -
数据去重：实际操作中，用户可能在同一天有多次活动记录，因此在计算留存率时需要对数据进行去重处理，以确保准确性。
4. 答案代码以及pretty表格解释
-- 方法1 avg条件 无join 代码最短的方法
with a1 as (
select
player_id,
min(event_date) 注册时间
from activity
group by player_id)
-- 以上筛选出注册表
select 注册时间 install_dt,
count(*) installs,
round(avg((player_id, 注册时间 + interval 1 day) in (select distinct player_id,event_date from activity)), 2) Day1_retention
-- 这种方法最段,但是因为是子查询遍历查找,不如join的先筛选在查找,推荐使用方法2 这里不distinct也行,因为题目说
-- 依次类推 + interval 3 day 三日留存; + interval 5 day 五日留存 （player_id，event_date）是此表的主键(具有唯一值的列的组合)
from a1 -- 但是我建议一定要distinct因为实际工作操作时可能同天多次登录
group by 注册时间;
+---------+--------+---------------+
|install_dt|installs|Day1_retention|
+---------+--------+---------------+
|2016-03-01|2 |0.50 |
|2017-06-25|1 |0.00 |
+---------+--------+---------------+
-- 方法2 join avg条件 (join on and 效率很高)
512 ms 击败 100.00%;
with a1 as (
select
player_id,
min(event_date) 注册时间
from activity
group by player_id)
select
注册时间 install_dt,
count(*) installs,
round(avg(a.player_id is not null),2) Day1_retention
from a1
join
(select distinct player_id,event_date from activity) a-- 我建议一定要distinct因为实际工作操作时可能同天多次登录
on a1.注册时间+interval 1 day =a.event_date and a1.player_id=a.player_id
group by 注册时间;
+---------+----------+---------+----------+
|player_id|注册时间 |player_id|event_date|
+---------+----------+---------+----------+
|1 |2016-03-01|1 |2016-03-02|
|3 |2016-03-01|null |null |
---------------------------------------------------
|2 |2017-06-25|null |null |
+---------+----------+---------+----------+
-- 方法3 与方法1一样灵活,但是效率更高
推荐使用本方法 519 ms 击败 95.24%;
with a1 as (
select
player_id,
min(event_date) 注册时间
from activity
group by player_id)
select
注册时间 install_dt,
count(distinct a1.player_id) installs,
round(
sum(datediff(event_date,注册时间)=1)
/
count(distinct a1.player_id,注册时间)
,2) Day1_retention
from a1
join
(select distinct player_id,event_date from activity) a-- 我建议一定要distinct因为实际工作操作时可能同天多次登录
on a1.player_id=a.player_id
group by 注册时间;
+---------+----------+---------+----------+
|player_id|注册时间 |player_id|event_date|
+---------+----------+---------+----------+
|1 |2016-03-01|1 |2016-03-01|
|1 |2016-03-01|1 |2016-03-02|
|3 |2016-03-01|3 |2016-03-01|
|3 |2016-03-01|3 |2018-07-03|
---------------------------------------------------
|2 |2017-06-25|2 |2017-06-25|
+---------+----------+---------+----------+
5. 关键总结
-
公共表表达式 (CTE)：使用
WITH子句来简化复杂查询，提升代码可读性。 -
聚合函数：利用
MIN函数确定用户的注册时间，通过COUNT和AVG计算安装数量和留存率。 -
日期操作：使用日期函数如
DATE_ADD或INTERVAL来计算注册后的特定天数，用于留存率的计算。 -
JOIN 与子查询：理解不同的方法如
JOIN和子查询在性能和可读性上的差异，选择最优方案。 -
数据去重：在处理实际数据时，确保对重复记录进行去重，保证计算的准确性。
-
性能优化：通过合理的查询结构和索引优化，提高SQL查询的执行效率。
-
留存率计算逻辑：掌握如何根据注册时间和后续活动时间计算不同天数的用户留存率，理解其业务意义。