[大数据]一道有趣的sql题，统计复旦用户8月练题情况

一道有趣的sql题

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

描述

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

示例：用户信息表user_profile

示例：question_practice_detail

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

示例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！

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖