一道有趣的sql题，统计复旦用户8月练题情况

这是一道非常有趣的sql题，第一部分是题的描述，第二部分是题解（tips：右上角有个小目录）。
不妨看完题目先思考一下，或者实际运行一下。也可直接从题解看起，题解并非直述答案，而是从每一个sql知识点由浅入深，深入浅出。在牛客上这是为数不多的“困难”sql题之一，相信你会有所收获。

统计复旦用户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

示例1

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出：

3214|复旦大学|3|0
4321|复旦大学|0|0

---

题解

题目要求：

1. 复旦大学的每个用户
2. 8月份练习的总题目数和回答正确的题目数情况

拆分需求：
 
​  复旦大学
 
  复旦大学用户
 
  8月份
 
  练习总题目数（统计）
 
  回答正确数（统计）

一、 表连接

这里涉及到了两个表的数据，两个表有一个关联字段为device_id， 那这里一定有一个关联查询，且一定是user_profile表和question_practice_detail表为一对多的关系。写一个基础的结构：

select * from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id;

查询结果如下：

这里我们可以看到，一个device_id对应多条记录，这就是question_practice_detail表中不同的记录，而最后所需要的结果为一个device_id对应的数据，所以我们可以先进行group by

select * from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
group by u.device_id;

二、 数据过滤

然后从最简单的开始，对复旦大学做过滤，对月份做过滤。

这里有一个值得注意的点，如果我们从where中对被连接的表进行过滤时，是先将结果查出，再进行过滤。这里我们可以在连接时，用条件做连接数据限制，如下：

-- 对主表使用where， 对被连接的表在on后跟条件
select * from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id and MONTH(q.date) = '08'
where u.university = '复旦大学'
group by u.device_id;

三、数据统计

拆分需求：
 
​  复旦大学 ✅
 
​  复旦大学用户 ✅
 
​  8月份 ✅
 
​  练习总题目数（统计）
 
  回答正确数（统计）

现在还有剩下的两个需求，首先是统计练习总题目数，我们可以使用count() 进行统计。

select u.device_id as device_id, u.university as university, count(1) as question_cnt from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id and MONTH(q.date) = '08'
where u.university = '复旦大学'
group by u.device_id;

查询结果如下：

就差最后一个条件，就是正确的题目数量，就是统计在上面查询结果基础上result这个字段等于right的字段有多少个

这里我们可以使用sum进行统计，如果result为right就+1, 这里最高效的方法是使用 case when函数，用法如下：

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

-- 在SUM中这样用：
sum(case when result = 'right' then 1 else 0 end)

最后结果如下(格式化了一下)：

SELECT
	u.device_id AS device_id,
	u.university AS university,
	count( 1 ) AS question_cnt,
	sum( CASE WHEN q.result = 'right' THEN 1 ELSE 0 END ) AS right_question_cnt 
FROM
	user_profile u
	LEFT JOIN question_practice_detail q ON u.device_id = q.device_id 
	AND MONTH ( q.date ) = '08' 
WHERE
	u.university = '复旦大学' 
GROUP BY
	u.device_id;

四、 NULL值处理

使用上面的结果我们去运行，

发现我们的结果并不对，这里我们统计出的总条数比正确结果多了，为什么呢？？

我们查询出全部字段看一下：

select * FROM
	user_profile u
	LEFT JOIN question_practice_detail q ON u.device_id = q.device_id 
	AND MONTH ( q.date ) = '08' 
WHERE
	u.university = '复旦大学' 

结果是这样的：

我们注意到这些字段是空值，这代表什么呢？ 这表示存在着复旦大学的某个用户，并没有问题练习的记录！

这才想起题目中的要求：对于在8月份没有练习过的用户，答题数结果返回0.

这时候我们的记录就只有主表信息。所以最后，我们需要做一下NULL值处理。这里我们可以直接照搬 正确题数统计的套路，使用SUM+c CaseWhen， sql如下：

SELECT
	u.device_id AS device_id,
	u.university AS university,
	sum( CASE WHEN q.result IS NOT NULL THEN 1 ELSE 0 END ) AS right_question_cnt,
	sum( CASE WHEN q.result = 'right' THEN 1 ELSE 0 END ) AS right_question_cnt 
FROM
	user_profile u
	LEFT JOIN question_practice_detail q ON u.device_id = q.device_id 
	AND MONTH ( q.date ) = '08' 
WHERE
	u.university = '复旦大学' 
GROUP BY
	u.device_id;

最后运行，通过！prefect！