首页 > 试题广场 >

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

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

热度指数：358075 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写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

解释：

第一行：北京大学有设备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

第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

.....

示例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
);
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');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
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');

输出

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

算法知识视频讲解

Sanli_Sang

select university , difficult_level , count(qpd.question_id)/count(distinct qpd.device_id) avg_answer_cnt
from user_profile up , question_practice_detail qpd , question_detail qd
where up.device_id = qpd.device_id and qpd.question_id = qd.question_id
group by university , difficult_level;

首先对不同学校进行分组，然后再根据不同难度进行分组，这样得到的每一组都是同一个学校以及同一个难度的学生。接下来就需要如何考虑组织显示结果，每个学校，每个难度，这个直接写在select即可，主要在于如何计算这个学校这个难度的用户平均刷提数？可不可以直接用刷题总数除以参加刷题的人数？不可以，因为一个人有可能会刷多个同一个难度的题，要精准查询参加刷题人数，首先需要对参加人数进行去重然后再进行求平均数

发表于 2021-10-30 19:23:54 回复(12)

Mysql

RandallGao

请问一下大家，这里是不是inner join和left join都可以呀，为什么呢？这两个用的时候如何区分用哪一个呢？

求大佬们指教！！！

发表于 2022-01-06 09:35:21 回复(14)

Mysql

yub4by

SELECT 
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id)/COUNT(distinct(t2.device_id)) as avg_answer_cnt
FROM 
    user_profile t1,
    question_practice_detail t2,
    question_detail t3
WHERE
    t1.device_id = t2.device_id
    and 
    t2.question_id = t3.question_id
GROUP BY t1.university,t3.difficult_level;

发表于 2021-09-20 20:39:59 回复(15)

不成大牛誓不罢休

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

第一，明确需要按照学校和难度进行分组；

第二，用户平均答题量 = 答题总数 / 用户数，要明确答题总数需要用question_id的行数来表示，也就是需对question_id列进行计数，用户数是device_id的行数，其中答题的题目可以重复，用户则不能重复计数

第三，注意三个表的联结问题，在两个表的基础上直接增加即可）

发表于 2021-10-02 20:29:43 回复(3)

Mysql

Sushine

select university,difficult_level,(count(q.question_id)/count(distinct(q.device_id)))
from user_profile u join question_practice_detail q 
on u.device_id = q.device_id
join question_detail qd on qd.question_id = q.question_id
group by university,qd.difficult_level

发表于 2021-08-26 21:20:18 回复(9)

柒鳴飞

SELECT
    university,
    difficult_level,
    count(q.question_id )/count(
    distinct(q.device_id)) avg_answer_cnt
FROM
    user_profile u
    JOIN question_practice_detail q ON u.device_id = q.device_id
    JOIN question_detail qd ON q.question_id = qd.question_id
GROUP BY
    university,
    difficult_level;

SELECT
    university,
    difficult_level,
    count( q.question_id )/ count(
    DISTINCT ( q.device_id )) avg_answer_cnt
FROM
    user_profile u,
    question_practice_detail q,
    question_detail qd
WHERE
    u.device_id = q.device_id
    AND q.question_id = qd.question_id
GROUP BY
    university,
    difficult_level;

发表于 2021-10-14 15:50:55 回复(1)

Demain210

求各位大神告知：为什么必须用inner join呢？可不可以用where筛选条件来写呢？

SELECT university, qd.difficult_level, COUNT(qpd.device_id) / COUNT(DISTINCT(qpd.device_id))
FROM user_profile as u, question_practice_detail as qpd, question_detail as qd
WHERE u.device_id = qpd.device_id and qpd.question_id = qd.question_id
GROUP BY university, difficult_level

发表于 2021-09-10 00:20:58 回复(13)

执久

select a.university,c.difficult_level,
      count(b.question_id)/count(distinct b.device_id) as avg_answer_cnt
from user_profile a
join question_practice_detail  b
on a.device_id=b.device_id
join question_detail c
on b.question_id=c.question_id
group by a.university,c.difficult_level;

发表于 2022-01-18 09:11:41 回复(4)

Mysql

fuyuAme233

select up.university,qd.difficult_level,(count(1)/count(distinct(qpd.device_id))) from user_profile up
join question_practice_detail qpd on up.device_id = qpd.device_id
join question_detail qd on qd.question_id =qpd.question_id

GROUP by up.university,qd.difficult_level

又臭又长和上一题一样注意一下device_id 去重问题就好了

发表于 2021-08-27 22:20:21 回复(7)

Mysql

牛客825913454号

为什么一二表要用device_id等值连接，而二三表用question_id连接呀

发表于 2022-06-09 19:44:24 回复(7)

Mysql

牛客222133712号

最简洁的办法

SELECT u.university,qd.difficult_level,ROUND(COUNT(qp.question_id)/COUNT(DISTINCT qp.device_id),4) avg_answer_cnt

FROM question_practice_detail qp,user_profile u,question_detail qd
WHERE qp.device_id=u.device_id AND qp.question_id=qd.question_id
GROUP BY university,difficult_level;

发表于 2022-03-03 11:31:04 回复(4)

纪念ナルト

我想问下山东大学在的ID在第二张表有吗？怎么关联出来的、？？

发表于 2021-10-28 14:00:46 回复(4)

冯吉诃德说

像我一样题目直接看不懂的有几个？？

发表于 2023-04-24 22:03:21 回复(2)

11111111__

SELECT u.university,d.difficult_level,
COUNT(q.question_id)/COUNT(DISTINCT(q.device_id)) as avg_answer_cnt
FROM user_profile AS u,question_practice_detail AS q,question_detail AS d
WHERE u.device_id = q.device_id and d.question_id = q.question_id
GROUP BY university,difficult_level;

发表于 2021-09-18 22:44:52 回复(3)

下买菜

1.计算参加了答题的不同学校、不同难度的用户平均答题量显然按照学校和难度进行分组

2.还是接上一题,因为不能直接进行avg(问题号)因为有用户多次答题,有用户没答题, 确定一定答题的是在问题练习表中

3.另外处理一个用户多次答题就对练习表中的设备去重 ,设备来自练习表,而不能直接来自没有设备重复的用户表,因为那个表中可能有用户没有参与答题

4.因为确定是答题的,可以直接来自练习表,那如果不确定呢,如果把没答题的用户也统计上呢?这样的话用户不为空,答题可为空,可用用户表尾

主表进行左连接查询

select

u.university,d.difficult_level, count(q.question_id)/count(distinct q.device_id)

avg_answer_cnt

from question_practice_detail q

join user_profile u

on q.device_id=u.device_id

join question_detail d

on d.question_id=q.question_id

group by u.university,d.difficult_level

发表于 2023-02-28 09:28:33 回复(0)

牛客50895393号

select
c.university,
b.difficult_level,
count(a.question_id)/count(distinct a.device_id)as avg_answer_cnt
from
((select
device_id,
question_id
from
question_practice_detail) as a
left join
(select
question_id,
difficult_level
from
question_detail) as b on a.question_id=b.question_id
left join
(select
device_id,
university
from
user_profile)as c on a.device_id=c.device_id)
group by
c.university,
b.difficult_level

发表于 2022-04-08 11:45:32 回复(1)

牛客李哈哈哈

SELECT university,difficult_level,(COUNT(u.question_cnt))/(COUNT(DISTINCT u.device_id))
FROM user_profile u,question_practice_detail qpd,question_detail qd
WHERE u.device_id = qpd.device_id and qpd.question_id=qd.question_id
GROUP BY university,difficult_level;

发表于 2021-09-02 17:41:06 回复(0)

牛客103162232号

# #第一步：求出不同题库的对应难度，建立一个子表

# select qpd.id,qpd.device_id,qpd.question_id,qpd.result,qd.difficult_level

# from question_practice_detail qpd join question_detail qd on qpd.question_id=qd.question_id

#第二步：根据不同学校、不同难度进行分组计算出平均答题量

# select

# u.university,

# a.difficult_level,

# round(count(a.question_id)/count(distinct(u.device_id)),4) avg_answer_cnt

# from

# user_profile u join

# (select qpd.id,qpd.device_id,

# qpd.question_id,

# qpd.result,

# qd.difficult_level

# from

# question_practice_detail qpd join

# question_detail qd

# on qpd.question_id=qd.question_id) as a

# on u.device_id = a.device_id

# group by u.university,a.difficult_level

发表于 2023-06-13 14:48:14 回复(0)

Mysql

好的昵称被占用了

题目答案：

select u.university, q2.difficult_level, 
count(q1.device_id)/count(distinct(q1.device_id)) as avg_answer_cnt
from user_profile u
inner join question_practice_detail q1
on u.device_id=q1.device_id
inner join question_detail q2
on q1.question_id=q2.question_id
group by u.university, q2.difficult_level

1.疑惑人的点可能就在平均刷题数了，答题的总数/答题的总人数，

在表question_practice_detail中，每个device_id对应一个人，device_id可能重复出现因为有的人会答了好几题，device_id的总数就是答题总数，不同的device_id就对应答题人数，分别为：

count(q1.device_id)

count(distinct(q1.device_id))

所以平均刷题数为

count(q1.device_id)/count(distinct(q1.device_id))

2.不使用left join，比如复旦大学的device_id为4321的用户没有答题，如果用left join会返回一行 “复旦大学 None None”，

可以使用right join，因为表question_detail中的question_id在表question_practice_detail中都出现过，表question_practice_detail中的device_id在表user_profile中都出现过，

但是这类型的题目建议使用inner join，因为当表很大时，很难看清楚各表各列是否都出现过，使用inner join返回三个表都匹配的内容。

发表于 2022-09-04 10:38:12 回复(0)

Mysql

美安克华

select 
    u.university,
    qd.difficult_level,
    round(count(qpd.question_id)/count(distinct qpd.device_id),4) as difficult_level
from question_practice_detail qpd
left join user_profile u using (device_id)
left join question_detail qd using (question_id)
group by university,difficult_level

发表于 2022-02-28 14:23:01 回复(0)