题目描述
现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
问题联系记录表 question_practice_detail:
id | device_id | quest_id | result | date |
---|---|---|---|---|
1 | 2138 | 111 | wrong | 2021-05-03 |
2 | 3214 | 112 | wrong | 2021-06-10 |
3 | 6543 | 113 | right | 2021-06-15 |
4 | 2316 | 116 | right | 2021-05-13 |
5 | 217 | 118 | right | 2021-08-13 |
最终输出样式:
avg_ret |
---|
0.3000 |
题目分析
所谓次日留存,指的是同一用户(在本题中则为同一设备,即device_id)在当天和第二天都进行刷题。注意,在这题我们不关心同一用户(设备)在这天答了什么题、答题结果如何,只关心他是否答题,因此对于这题来说存在重复的数据(如下图红框所示),需要使用 DISTINCT 去重。
而次日留存率可以这样表示:
|
具体而言,使用两个子查询,查询出两个去重的数据表,并使用条件(q2.date应该是q1.date的后一天)进行筛选,如下所示(数据未显示完全,从左至右顺序,列表名为 q1.device_id, q1.date, q2.device_id, q2.date)
因为使用的是q1左联q2,所以q1的所有信息是显示的;而q2中只显示留存的信息,否则为null。
最后,分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数,即可算出次日留存率.
SQL实现
SELECT
COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
(SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
(SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)
Tip:MySQL中 COUNT在对列进行计数时不统计值为 null的条目
Knowledge Point 知识点
1.MySQL DATE_ADD() 函数
定义:DATE_ADD() 函数向日期添加指定的时间间隔
语法:DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
type 参数是时间单位。
type 参数可以是以下值:
Type 值 |
---|
MICROSECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR |
MINUTE_MICROSECOND |
MINUTE_SECOND |
HOUR_MICROSECOND |
HOUR_SECOND |
HOUR_MINUTE |
DAY_SECOND |
DAY_MINUTE |
DAY_HOUR |
YEAR_MONTH |
2.MySQL COUNT 函数
COUNT(*) 函数返回在给定的选择中被选的行数。
COUNT(column_name) 函数返回指定列的值的数目(NULL 空值不计入)