首页 > 试题广场 >

统计每个用户的平均刷题数

[编程题]统计每个用户的平均刷题数

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

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

用户信息表：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为432，性别为男，年龄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
山东大学	easy	4.5000
山东大学	medium	3.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`;
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');

输出

山东大学|easy|4.5000
山东大学|medium|3.0000

算法知识视频讲解

Mysql

忆_恒心_YD

SELECT u.university,
d.difficult_level,
(COUNT(d.difficult_level)/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 q.question_id = d.question_id
AND u.university = '山东大学'
GROUP BY  d.difficult_level;

这个类别的题目基本上连个表加个判断条件就结束了

难点其实也就在

计算评论值的地方

(COUNT(d.difficult_level)/count(distinct q.device_id))as avg_answer_cnt

发表于 2021-11-30 15:03:43 回复(0)

更多回答

加油！小菜鸡

总结自己出错原因：

1.没有去重，distinct。
2.没有按照难度分组，group by q2.difficult_level
正确答案如下：

SELECT u.university,q2.difficult_level,
(count(q1.question_id)/count(distinct q1.device_id)) as avg_answer_cnt
from user_profile as u join question_practice_detail as q1
on u.device_id=q1.device_id
join question_detail as q2
on q1.question_id=q2.question_id
where university='山东大学'
group by q2.difficult_level;

发表于 2021-08-28 16:58:51 回复(8)

Mysql

飞翔的塑料袋

# 限制条件：
# 1.参加了答题
# 2.山东大学
# 分组：不同难度 group by difficult_level
# 查询结果：
# 1.difficult_level
# 2.university
# 3.平均答题数
# 总共答题数/总人数
# count(question_id)/count(distinctndevice_id) as avg_answer_cnt
# 连接：
# 1.qpd up device_id
# 2.qpd qd question_id

SELECT
up.university,
qd.difficult_level,
COUNT(qpd.question_id) / COUNT(DISTINCT qpd.device_id) AS avg_answer_cnt
FROM
question_practice_detail AS qpd
LEFT JOIN
user_profile AS up
ON qpd.device_id=up.device_id

LEFT JOIN
question_detail AS qd
ON qd.question_id=qpd.question_id
WHERE
up.university='山东大学'
GROUP BY
qd.difficult_level;

发表于 2021-11-28 15:14:25 回复(3)

你的玄策

SELECT u.university,q2.difficult_level,count(q1.question_id)/count(distinct q1.device_id)
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
having u.university='山东大学'

发表于 2021-08-31 00:01:01 回复(6)

Mysql

0202U

select a.university,c.difficult_level,count(b.question_id)/count(distinct b.device_id) avg_answer_cnt
from user_profile a,question_practice_detail b,question_detail c
where a.device_id=b.device_id and b.question_id=c.question_id and a.university="山东大学"
group by c.difficult_level

发表于 2021-10-17 16:03:23 回复(1)

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 and u.university = '山东大学'
group by university,qd.difficult_level

发表于 2021-08-26 21:23:40 回复(0)

未来就是可期

---连接查询
select a.university,
c.difficult_level,
count(b.question_id)/count(distinct b.device_id)   as avg_answer_cnt
from user_profile a,
     question_practice_detail b,
     question_detail c
where a.device_id=b.device_id 
and b.question_id=c.question_id 
and a.university="山东大学"
group by c.difficult_level

发表于 2022-06-13 16:24:56 回复(5)

Mysql

牛客813293262号

SELECT university,
difficult_level,
count(*)/count(distinct(u.device_id)) as avg_answer_cnt
from user_profile as u
inner join question_practice_detail as qp
on u.device_id=qp.device_id
inner join question_detail as qd
on qp.question_id=qd.question_id
where university="山东大学"
group by difficult_level;

发表于 2021-08-26 16:10:34 回复(0)

冷凡社长

select t.university,t2.difficult_level,
round(count(t1.question_id)/count(distinct t1.device_id),4) as avg_answer_cnt
from user_profile t
    inner join question_practice_detail t1 on t.device_id= t1.device_id
    inner join question_detail t2 on t1.question_id = t2.question_id
where t.university = "山东大学" -- 只比上一题加入了这个条件
group by t.university,t2.difficult_level
order by t.university desc

只比上一题，增加了一个山东大学的筛选条件。

发表于 2022-07-14 17:36:10 回复(4)

牛客655660060号

SELECT
	university,
	d.difficult_level,(
		count( q.question_id )/ count(
		DISTINCT ( q.device_id ))) 
FROM
	user_profile u
	INNER JOIN question_practice_detail q ON u.device_id = q.device_id
	INNER JOIN question_detail d ON d.question_id = q.question_id 
WHERE
	u.university = '山东大学' 
GROUP BY
	d.difficult_level;

发表于 2021-08-26 21:37:40 回复(3)

牛客822042861号

这个表怎么设计的？山大的哪里答题了？出题人？？？

发表于 2021-11-14 16:44:58 回复(4)

Mysql

一只蓝企鹅

这题只需要在上一题的基础上加上一句

WHERE 语句就可以了

WHERE university = '山东大学'

发表于 2022-04-15 08:36:43 回复(1)

Mysql

不知道起什么名的小白

SELECT 
    university, 
    difficult_level,
    ROUND(COUNT(qpd.question_id) / COUNT(DISTINCT qpd.device_id), 4) AS avg_answer_cnt
FROM question_practice_detail qpd
JOIN user_profile up ON qpd.device_id = up.device_id and up.university = '山东大学'
JOIN question_detail qd ON qpd.question_id = qd.question_id
GROUP BY university, difficult_level;

发表于 2022-03-15 11:30:28 回复(0)

XXCC111111

SELECT UP.university, QD.difficult_level, (count(QPD.question_id) / count(distinct QPD.device_id)) as avg_answer_cnt
FROM user_profile AS UP, question_practice_detail as QPD, question_detail AS QD
WHERE UP.device_id = QPD.device_id and QPD.question_id = QD.question_id and UP.university = '山东大学'

GROUP BY QD.difficult_level

错误原因：

没有按照难易水平分组

发表于 2022-03-01 13:57:32 回复(0)

96nnnnie

发表于 2021-08-25 19:06:27 回复(0)

Mysql

酸菜鱼土豆大侠

【分类】：连接查询、多表连接from、join

分析思路

一开始打算使用avg(),但是涉及到不同用户,实现不了,所以只能用除法

分子: 每个学校答题数量
分母: 每个学校不同答题用户

select 查询结果 [学校;题目困难程度;平均答题数]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [山东大学]
group by 分组条件 [题目困难程度]

求解代码

方法一：

表 join 表 using(用列连接)

#统计每个用户的平均刷题数
select
    university,
    difficult_level,
    count(device_id)/count(distinct(device_id)) as avg_answer_cnt
from user_profile
join question_practice_detail using(device_id)
join question_detail using(question_id)
where university = '山东大学' 
group by difficult_level

方法二

from表连接

#统计每个用户的平均刷题数
select
    university,
    difficult_level,
    count(a.device_id)/count(distinct(a.device_id)) as avg_answer_cnt
from user_profile a, question_practice_detail b, question_detail c
where university = '山东大学' 
and a.device_id = b.device_id
and b.question_id = c.question_id
group by difficult_level

发表于 2022-11-13 10:48:43 回复(0)

Mysql

轻飘飘过wind

select up.university,
tmp.difficult_level,
round(sum(tmp.question_cnt) / count(up.device_id), 4) as avg_answer_cnt
from user_profile as up
inner join (
    select 
        qpd.device_id,
        qd.difficult_level,
        count(qpd.question_id) as question_cnt
    from question_practice_detail as qpd
    inner join question_detail as qd
    on qpd.question_id = qd.question_id
    group by qpd.device_id, qd.difficult_level
) as tmp
on up.device_id = tmp.device_id
where up.university = '山东大学'
group by tmp.difficult_level
order by tmp.difficult_level asc

发表于 2022-08-29 19:17:10 回复(0)

Mysql

Stzolo

select a.university,  c.difficult_level, round(count(*)/ count(distinct(a.device_id)),4)
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
where a.university = '山东大学'
group by a.university,  c.difficult_level
order by a.university,  c.difficult_level

发表于 2022-04-23 16:44:51 回复(0)

Mysql

执久

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
where a.university="山东大学"
group by c.difficult_level;
考点还是在distinct去重上，多表链接 join...on...join....on

发表于 2022-01-18 09:21:41 回复(0)

Mysql

伟大的小章鱼在喝茶

SELECT

university,

difficult_level,

COUNT(qpd.question_id)/COUNT(DISTINCT up.device_id)

avg_answer_cnt

FROM

question_practice_detail qpd #表2

JOIN

user_profile up #表1

qpd.device_id=up.device_id

JOIN

question_detail qd #表3

qpd.question_id=qd.question_id

WHERE

university='山东大学'

GROUP BY

difficult_level;

编辑于 2024-02-23 22:02:21 回复(0)

Mysql

牛客977145710号

跟上一题一样啊，加个 where 就行！！！

select
    university,
    difficult_level,
    round(
        count(difficult_level) / count(distinct t1.device_id),
        4
    ) as avg_answer_cnt
from
    question_practice_detail t1
    inner join question_detail t2 on t1.question_id = t2.question_id
    inner join user_profile t3 on t1.device_id = t3.device_id
where
    university = '山东大学'
group by
    university,
    difficult_level

发表于 2023-08-31 12:17:37 回复(0)