首页 > 试题广场 >

统计每个学校的答过题的用户的平均答题数

[编程题]统计每个学校的答过题的用户的平均答题数

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

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。

device_id	gender	age	university	gpa	active_days_within_30
2138	male	21	北京大学	3.4	7
3214	male	NULL	复旦大学	4	15
6543	female	20	北京大学	3.2	12
2315	female	23	浙江大学	3.6	5
5432	male	25	山东大学	3.8	20
2131	male	28	山东大学	3.3	15
4321	male	28	复旦大学	3.6	9

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

最后一行表示:用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天

答题情况明细表 question_practice_detail，其中question_id是题目编号，result是答题结果。

device_id	question_id	result
2138	111	wrong
3214	112	wrong
3214	113	wrong
6543	111	right
2315	115	right
2315	116	right
2315	117	wrong
5432	118	wrong
5432	112	wrong
2131	114	right
5432	113	wrong

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

....

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

请你写SQL查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！！！

university	avg_answer_cnt
北京大学	1.0000
复旦大学	2.0000
山东大学	2.0000
浙江大学	3.0000

解释:

第一行：北京大学总共有2个用户，2138和6543，2个用户在question_practice_detail里面答了2题，平均答题数目为2/2=1.0000

....

最后一行:浙江大学总共有1个用户，2315，这个用户在question_practice_detail里面答了3题，平均答题数目为3/1=3.0000

示例1

输入

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

INSERT INTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(3214,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);
INSERT INTO question_practice_detail VALUES(2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(6543,111,'right');
INSERT INTO question_practice_detail VALUES(2315,115,'right');
INSERT INTO question_practice_detail VALUES(2315,116,'right');
INSERT INTO question_practice_detail VALUES(2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(2131,114,'right');
INSERT INTO question_practice_detail VALUES(5432,113,'wrong');

输出

北京大学|1.0000
复旦大学|2.0000
山东大学|2.0000
浙江大学|3.0000

算法知识视频讲解

Mysql

恶作剧之神阿蒙

此题最重要的就是理解平均回答数是回答数除以回答的人

即question_id/device_id

select university,(count(question_id)/count(distinct(qpd.device_id))) as avg_answer_cnt 
from user_profile u join question_practice_detail qpd on u.device_id=qpd.device_id
group by university

发表于 2021-08-25 10:18:50 回复(24)

一只懒懒的程序媛

/*
分析思路
select 查询结果 [university,'每个人平均答题数量':问题数/设备数(一个设备对应多个题，要去重)]
from 从哪张表中查找数据[两张表联结]
where 查询条件 [无]
group by 分组条件 [university]
*/

-- avg_answer_cnt = count(question_id)/count(device_id)  
select university,(count(question_id)/count(distinct(q.device_id))) 
as avg_answer_cnt 
from user_profile u 
join question_practice_detail q on u.device_id = q.device_id
group by university;

发表于 2021-10-16 15:05:55 回复(6)

Mysql

牛客452118981号

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

这里用inner不用left join 原因在于，在表a中，还存在北京理工学生情况，但北京理工学生并没有参与答题，left join 仍然会保留北京理工数据，但inner join 只会保留两表交集部分

发表于 2022-01-04 22:02:27 回复(14)

11111111__

SELECT university,
COUNT(q.question_id)/COUNT(DISTINCT(q.device_id)) AS avg_answer_cnt
FROM user_profile as u,question_practice_detailAS q
WHERE u.device_id = q.device_id
GROUP BY university;

菜鸟做了半个小时终于搞出来了，这才中等难度哎😐

发表于 2021-09-15 23:14:00 回复(11)

加油！小菜鸡

求指点，看题解才知道，关键是(COUNT(q.question_id)/count(distinct q.device_id))

但是题目问：用户平均答题数量。不是应该用answer_id 吗？(当然表没有这个列，所以是否题目不严谨，还是我理解不对了？)

SELECT u.university,(COUNT(q.question_id)/count(distinct q.device_id))as avg_answer_cnt
FROM user_profile as u join question_practice_detail as q
on q.device_id=u.device_id
group by university;

发表于 2021-08-28 16:09:57 回复(17)

Mysql

vegetabledog🐶

解题：
首先辨识到需要根据大学分表，分表后做什么呢？统计每个大学表中学生的个数，和完成题目次数（注意不是个数），假设有一个学校，只有1个同学，他把两道题一共完成了3次，那么结果应该是：3次/1个同学=3

综上，学生数需要去重，题目次数不需要去重，题目个数不纳入考虑。

SELECT university,COUNT(qt.question_id)/COUNT(distinct(qt.device_id)) avg_answer_cnt
FROM question_practice_detail qt LEFT JOIN user_profile ut
ON qt.device_id=ut.device_id
GROUP BY university

发表于 2021-09-10 10:15:42 回复(10)

Mysql

李玉婷的旁听生

1、题目不难，主要是用户平均答题数的定义，题目信息给的少，导致很含糊，需要猜很久。

SELECT university,
       count(*)/count(DISTINCT q.device_id) avg_answer_cnt
FROM question_practice_detail q
	LEFT JOIN user_profile u ON u.device_id=q.device_id
GROUP by university

发表于 2021-10-06 19:00:49 回复(6)

江南画倦

SELECT    -- 查询
    university,  -- 大学列名
    COUNT(question_id)/count(distinct qpd.device_id) AS avg_answer_cnt
    -- 题目编号总个数（题目数量） / 去掉重复之后的用户编号 总个数（用户数量）  使用别名
FROM question_practice_detail AS qpd  -- 来源表名 别名
JOIN user_profile AS up    -- 连接第二张表  别名
ON qpd.device_id=up.device_id   -- 连接条件 第一表和第二表的用户ID相等
GROUP BY university  -- 按大学名 分组

菜鸡的我只能看题解讨论去打一遍

发表于 2021-11-29 15:41:26 回复(3)

Mysql

牛客798237088号

就想问一下，为什么不能用user_profile表的answer_cnt列用AVG函数来算呢?

发表于 2021-09-17 21:40:35 回复(8)

Mysql

冷凡社长

select t.university,round(avg(num),4) as avg_answer_cnt
from -- 思路先求出每个用户答题数
    (select device_id,count(*) as num 
            from question_practice_detail
            group by device_id) t1
inner join user_profile t
on t.device_id = t1.device_id
group by t.university order by t.university

我首先想到的是，先求出每个用户的答题数，然后表链接，根据每个用户的学校，再继续往下。
由于一开始没有对学校排序，报错了几次，就是顺序不对。

后面看了一下评论区，发现大家都是以下面的代码为主

select t.university,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 
    group by t.university order by t.university

这个写也很好，只要能明白对于每个学校，用答题的数量/答题的人数即可。

这道题的难度，就有点上升了开始。

发表于 2022-07-14 17:00:01 回复(3)

我的世界01号

真题好难哦，题目都给爷整蒙了

发表于 2021-11-26 15:18:45 回复(4)

Mysql

Leona622

--01
  select u.university,
         round((count(q.question_id) / count(distinct u.device_id) ),4) as avg_answer_cnt
    from user_profile u, question_practice_detail q
   where u.device_id = q.device_id 
   group by u.university
   ;
--02
  select u.university,
         round((count(q.question_id) / count(distinct u.device_id) ),4) as avg_answer_cnt
    from user_profile u
   inner join question_practice_detail q
      on u.device_id = q.device_id 
   group by u.university
   ;

发表于 2021-11-02 23:16:02 回复(0)

爱吃芒果的帅哥

在这道题卡我半个小时，

审题： '' 先取出每个学校的用户 '' 再计算 '' 平均答题数量 ''

DISTINCT a.device_id 将用户去重，再计数

count(DISTINCT a.device_id)

''平均答题数量'' 是存在重复答题用户的，得先去除重复值

count(b.question_id)

结果源码：

SELECT
university, count(b.question_id) / count(DISTINCT a.device_id)
from
user_profile as a,
question_practice_detail as b
where
a.device_id = b.device_id
group by
university

发表于 2021-09-26 01:10:11 回复(4)

Mysql

牛客222133712号

方法1借鉴20题讨论区高赞回答
#方法1
SELECT u.university,ROUND(COUNT(q.question_id)/COUNT(DISTINCT q.device_id),4) AS avg_answer_cnt
FROM user_profile u,question_practice_detail q
WHERE u.device_id=q.device_id
GROUP BY university;
#方法2
SELECT u.university,ROUND(COUNT(q.question_id)/COUNT(distinct(q.device_id)),4) avg_answer_cnt
FROM question_practice_detail q LEFT JOIN user_profile u
ON q.device_id=u.device_id
GROUP BY university

发表于 2022-03-03 11:12:36 回复(2)

Mysql

Tina要成为数据分析师呀

题目改成：运营想要了解每个学校答过题的用户人均答题数量情况，把平均改成人均是不是方便理解了

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

发表于 2021-12-23 02:03:56 回复(0)

Mysql

ELIAUK1219

SELECT
    u.university,
    convert(COUNT(q.question_id) / COUNT(DISTINCT q.device_id ),decimal(2, 1)) as avg_answer_cnt
FROM
     user_profile as u JOIN question_practice_detail as q
     on u.device_id = q.device_id
GROUP BY
        u.university;

最终输出结果小数位截取

CONVERT(expr,type)

expr：原始数据/表达式
type：类型
例如：decimal(10, 2)，表示最终得到的结果，整数部分保留位数 + 小数部分保留位数 <= 10，小数部分位数2

发表于 2021-10-19 17:01:17 回复(1)

Mysql

牛客94874598号

我认为本题目应该用user_profile left join question_practice_detail

第一个表里面最后一个人没答题也会影响该学校平均用户答题数量

实际输出

北京大学|1.0000
复旦大学|1.0000
山东大学|2.0000
浙江大学|3.0000

发表于 2021-11-19 14:15:28 回复(5)

Mysql

rickylarte

SELECT c.university,(COUNT(c.question_id)/COUNT(distinct c.device_id)) as avg_question
FROM
(SELECT (b.device_id),a.university,b.question_id
FROM user_profile as a join
question_practice_detail as b
on a.device_id=b.device_id)AS c
group BY c.university

发表于 2021-08-31 10:48:48 回复(1)

奋斗终身_伟大复兴

SELECT u.university,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 #两个表连接的条件就是device_id相等
GROUP BY

university; # 以每个学校为分组条件

# 其中平均答题数量，是由题目跟用户组成，用户由设备id，device_id，设备id相同，需要去重。

# answer_cnt字段不是答题，是回帖字段意思，不是答题的数量！！！

发表于 2021-09-23 14:51:16 回复(1)

Mysql

奶昔嘎嘎

select
  university,
  avg(dtnum) as avg_answer_cnt
from
  user_profile u
  join (
    select
      device_id,
      count(question_id) as dtnum
    from
      question_practice_detail
    group by
      device_id
  ) as q on u.device_id = q.device_id
group by
  university
  order by university

发表于 2022-08-11 11:07:53 回复(0)