mysql刷题笔记

近期，为提升自己的工程能力，在休息时常通过刷题来回顾一下基础性知识。

于是选择了牛客网上的mysql知识题库练手，过程中，主要遇到了几个比较有意思的题，记录下来，方便回顾。

题1：SQL29 计算用户的平均次日留存率

描述

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

示例：question_practice_detail

id	device_id	quest_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

根据示例，你的查询应返回以下结果：

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 作去重后的所有条目数和去重后的次日留存条目数，即可算出次日留存率。

具体实现

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)

题2：SQL34 统计复旦用户8月练题情况

描述

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

示例：用户信息表user_profile

id	device_id	gender	age	university	gpa	active_days_within_30
1	2138	male	21	北京大学	3.4	7
2	3214	male		复旦大学	4.0	15
3	6543	female	20	北京大学	3.2	12
4	2315	female	23	浙江大学	3.6	5
5	5432	male	25	山东大学	3.8	20
6	2131	male	28	山东大学	3.3	15
7	4321	female	26	复旦大学	3.6	9

示例：question_practice_detail

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

 

根据示例，你的查询应返回以下结果：

device_id	university	question_cnt	right_question_cnt
3214	复旦大学	3	0
4321	复旦大学	0	0

问题分解

• 限定条件：需要是复旦大学的（来自表user_profile.university），8月份练习情况（来自表question_practice_detail.date）
• 从date中取month：用month函数即可；
• 总题目：count(question_id)
• 正确的题目数：sum(if(qpd.result='right', 1, 0))
• 按列聚合：需要输出每个用户的统计结果，因此加上group by up.device_id

细节问题

• 8月份没有答题的用户输出形式：题目要求『对于在8月份没有练习过的用户，答题数结果返回0』因此明确使用left join即可，即输出up表中复旦大学的所有用户，如果8月没有练习记录，输出0就好了
• 老样子-表头：as语法重命名后两列就好

完整代码

select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
 
left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8
 
where up.university = '复旦大学'
group by up.device_id

 

题3：SQL35 浙大不同难度题目的正确率

描述

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

示例： user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

示例： question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6543	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong

 

示例： question_detail

question_id	difficult_level
111	hard
112	medium
113	easy
115	easy
116	medium
117	easy

根据示例，你的查询应返回以下结果：

difficult_level	correct_rate
easy	0.5000
medium	1.0000

问题分解

• 限定条件：浙江大学的用户；
• 不同难度：difficult_level（question_detail表中的列），需要分组统计，因此用到group by；
• 正确率：表面理解就是正确数÷总数，正确的是result='right'（question_practice_detail表），数目用函数count，总数是count(question_id)；
• 多张表联合查询：需要用到join，join有多种语法，因为条件限定需要是浙江大学的用户，所以需要是user_profile表的并且能统计出题目难度的记录，因此用user_profile表inner join另外两张表。
  
  

---

细节问题

• 表头重命名：根据输出示例，正确率用as语法重命名
• 升序输出：order by xxx asc
• 正确率的计算方式：判断result是否为right，是的话赋值为1，对于正确的数目，可以用count，也可以用sum，正确率还可以直接用avg计算。
• join方式选择：如果前面inner join改成left join，为了防止结果中有难度为None的结果，需要在order by前加一句 having qd.difficult_level != 'None'

---

完整代码

select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up
 
inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id
 
inner join question_detail as qd
    on qd.question_id = qpd.question_id
 
where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;

几个常用函数的套路：

1.substring_index():分割字符串，连续分割字符串

SELECT SUBSTRING_INDEX('15,151,152,16',',',1); ==>得到结果为： 15
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',2),',',-1);==>得到结果为： 151
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',-2),',',1);==> 得到结果为：152
SELECT SUBSTRING_INDEX('15,151,152,16',',',-1);==>得到结果为： 16

2.case when:分组

格式说明：

简单用法：

　　　　case 列名

　　　　when   条件值1   then  选项1

　　　　when   条件值2    then  选项2.......

　　　　else     默认值      end

搜索用法：
　　　　case  

　　　　when  列名= 条件值1   then  选项1

　　　　when  列名=条件值2    then  选项2.......

　　　　else    默认值 end

例子：

SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;