SQL编程----统计每个学校各难度的用户平均刷题数

描述

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

用户信息表：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	NULL	复旦大学	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	male	28	复旦大学	3.6	9	6	52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了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	6534	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong
8	5432	117	wrong
9	5432	112	wrong
10	2131	113	right
11	5432	113	wrong
12	2315	115	right
13	2315	116	right
14	2315	117	wrong
15	5432	117	wrong
16	5432	112	wrong
17	2131	113	right
18	5432	113	wrong
19	2315	117	wrong
20	5432	117	wrong
21	5432	112	wrong
22	2131	113	right
23	5432	113	wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

......

最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

表：question_detail

id	question_id	difficult_level
1	111	hard
2	112	medium
3	113	easy
4	115	easy
5	116	medium
6	117	easy

第一行表示: 题目id为111的难度为hard

....

第一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

university	difficult_level	avg_answer_cnt
北京大学	hard	1.0000
复旦大学	easy	1.0000
复旦大学	medium	1.0000
山东大学	easy	4.5000
山东大学	medium	3.0000
浙江大学	easy	5.0000
浙江大学	medium	2.0000

具体代码实现：

SELECT ue.university,ql.difficult_level,ROUND(COUNT(qdl.question_id)/COUNT(DISTINCT(qdl.device_id)),4) AS avg_answer_cnt
FROM question_practice_detail AS qdl JOIN user_profile AS ue 
ON qdl.device_id=ue.device_id
JOIN question_detail AS ql
ON qdl.question_id=ql.question_id
GROUP BY ue.university,ql.difficult_level;

解题思路：

1.计算不同学校、不同难度的用户平均答题量：

例如：

第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000

第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000

使用COUNT()函数；注意device_id需要去重求和，不然平均数永远是1。例如：id是123的做了两道题，如果不去重求和，那么id总数是2（两次123），做题总数也是2，平均值2/2=1。

2.结果在小数点位数保留4位，4位之后四舍五入：

使用ROUND()函数，4表示保留四位小数；

3.关联三张表：

JOIN ON 用于将两个或多个表中的数据关联起来，它的具体用法如下：

在数据库中，JOIN ON用于将两个或多个表中的数据关联起来。常见的JOIN操作有以下几种用法：

INNER JOIN（内连接）：返回两个表中匹配的行。语法如下：SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列;
LEFT JOIN（左连接）：返回左表中所有的行以及与右表中匹配的行。如果右表中没有匹配的行，则返回NULL。语法如下：SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 表1.列 = 表2.列;
RIGHT JOIN（右连接）：返回右表中所有的行以及与左表中匹配的行。如果左表中没有匹配的行，则返回NULL。语法如下：SELECT 列名 FROM 表1 RIGHT JOIN 表2 ON 表1.列 = 表2.列;
FULL JOIN（全连接）：返回左表和右表中所有的行，如果没有匹配的行，则返回NULL。语法如下：SELECT 列名 FROM 表1 FULL JOIN 表2 ON 表1.列 = 表2.列;

4.不同难度，不同学校分组：

 使用 GROUP BY ue.university,ql.difficult_level 来实现；