首页 > 试题广场 >

分组过滤练习题

[编程题]分组过滤练习题

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

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

示例：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		复旦大学	4.0	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	female	26	复旦大学	3.6	9	6	52

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

根据示例，你的查询应返回以下结果，注意返回的字段名需要保持一致，同时保留3位小数(系统后台也会自动校正)，3位之后四舍五入：

university	avg_question_cnt	avg_answer_cnt
北京大学	2.5000	21.000
浙江大学	1.000	2.000

解释: 平均发贴数低于5的学校或平均回帖数小于20的学校有2个

属于北京大学的用户的平均发帖量为2.500，平均回答数量为21.000

属于浙江大学的用户的平均发帖量为1.000，平均回答数量为2.000

示例1

输入

drop table if exists user_profile;
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` float,
`answer_cnt` float
);
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);

输出

university|avg_question_cnt|avg_answer_cnt
北京大学|2.500|21.000
浙江大学|1.000|2.000

算法知识视频讲解

牛客371347618号

聚合函数结果作为筛选条件时，不能用where，而是用having语法

发表于 2021-09-13 16:52:11 回复(13)

Mysql

__steve_yee

SELECT
    university,
    avg(  question_cnt  )  AS  avg_question_cnt,
    avg(  answer_cnt  )  AS  avg_answer_cnt  
FROM
    user_profile  GROUP BY university  
HAVING
    avg_question_cnt  <  5  OR  avg_answer_cnt  <  20

注意：

WHERE 关键字无法与合计函数一起使用；
SQL语句执行顺序 摘自《MySQL技术内幕:SQL编程》
FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT

(8) SELECT (9) DISTINCT 
(1) FROM 
(3)  JOIN 
(2)    ON 
(4) WHERE 
(5) GROUP BY 
(6) WITH {CUBE|ROLLUP} 
(7) HAVING 
(10) ORDER BY 
(11) LIMIT

发表于 2021-09-06 11:32:18 回复(18)

郑大涛

原因：因为 where 的操作对象是一条记录，比如说当一条记录xxx时，在什么条件下可以用 where

但是涉及到多条记录时，比如说这里的对象是从一组一组中再过滤到"平均问问题书小于 5" 的小组

这种情况下，where 就不适用了
此时，提供了 having 的关键词。

where 从记录中法过滤出某一条记录

having 可以从一组组记录中过滤掉其哪几组

发表于 2021-11-18 22:22:11 回复(2)

Mysql

超越3岁

1、SQL出现having的原因是，where关键字无法与聚合函数一起使用

2、having关键字放在group by关键字后面，针对分组后的数据进行筛选

发表于 2021-11-05 14:59:44 回复(2)

Mysql

Leona622

聚合函数结果作为筛选条件时，不能用where，而是用having语法

发表于 2021-11-02 22:54:52 回复(0)

Mysql

小月可以的！

一、sql执行顺序

from
join
on
where
group by(开始使用select中的别名，后面的语句中都可以使用)
avg,sum....
having
select
distinct
order by
limit

发表于 2021-08-29 20:45:11 回复(4)

Mysql

键盘盘盘盘

SELECT university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg(question_cnt) <5

or avg(answer_cnt) <20

注意分组条件是university，求的是平均值avg，比较的话需要使用Having，而且，不要少写逗号，不要忘了写from user_profile

发表于 2021-11-03 20:20:36 回复(3)

冷凡社长

select university
,round(avg(question_cnt),3) as  avg_question_cnt
,round(avg(answer_cnt),3) as  avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt <20

主要考察点：having筛选，having常用于对分组结果进行筛选。
这里不能使用where的原因是 where的执行顺序早于groupby,所以where 也不可以使用列的别名。而having可以。

发表于 2022-07-14 15:28:17 回复(2)

Mysql

oasis_98

select 
    university,
    avg(question_cnt) avg_question_cnt,
    avg(answer_cnt) avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20;

# select查询字段中有非聚合函数和聚合函数
# 一定是按照非聚合函数字段进行分组

# 按照执行顺序，是from -> where -> group by -> having -> select -> order by -> limit
# 理论上来说，select中的聚合函数字段无法在having中使用，
# 但是MySQL会做自动优化的替换，无论定义在哪，聚合的操作执行一次，having里和select里都可以使用

发表于 2022-01-12 13:01:57 回复(5)

牛客945781675号

select
t.university,
t.avg_question_cnt,
t.avg_answer_cnt
from
(
select
university,
ROUND(AVG(question_cnt), 1) AS avg_question_cnt,
ROUND(AVG(answer_cnt), 1) AS avg_answer_cnt
from
user_profile
GROUP BY
university
) t
where
t.avg_question_cnt<5 or t.avg_answer_cnt<20

不常用 having 都忘了，傻了！！！

发表于 2022-06-20 11:39:53 回复(4)

Mysql

牛肉板面

SELECT university,
ROUND(AVG(question_cnt),1) AS avg_question_cnt,
ROUND(AVG(answer_cnt),1) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg(question_cnt)<5

or avg(answer_cnt)<20;

这段代码为什么不行呀，自测运行可以通过，提交的时候就只输出一条结果了，求大佬帮助

发表于 2022-01-19 01:36:07 回复(6)

我喜欢🌙

SELECT university,
avg(question_cnt) AS avg_question_cnt,
avg(answer_cnt) AS avg_answer_cnt
FROM user_profile
GROUP BY university

HAVING avg_question_cnt<5 OR avg_answer_cnt<20

WHERE分组之前过滤数据，条件中不能有聚合函数

group by在having之前，group by先分组，having再筛选满足条件的组

发表于 2021-11-25 22:15:01 回复(0)

Mysql

OneBetter000

// 1、常规思路
select * from
(select university, AVG(question_cnt) as avg_question_cnt, AVG(answer_cnt) as avg_answer_cnt from user_profile group by university) 
as tmp 
where avg_question_cnt < 5&nbs***bsp;avg_answer_cnt < 20;
 
// 2、合理思路
select university, AVG(question_cnt) as avg_question_cnt, AVG(answer_cnt) as avg_answer_cnt 
from user_profile 
group by university 
having avg_question_cnt < 5&nbs***bsp;avg_answer_cnt < 20;

//3、总结
 话说第一种方案性能要比第二种要高😥

发表于 2022-01-10 16:55:40 回复(5)

Mysql

酸菜鱼土豆大侠

【场景】：每个学校用户的平均

【分类】：分组过滤

分析思路

select 查询结果 [学校,avg(发帖) as 平均发帖数;avg(回帖) as 平均回帖数]
from 从哪张表中查找数据 [user_profile]
group by 分组 [学校]
having 对分组结果指定条件 [平均发帖数小于5或者平均回帖数小于20]

求解代码

方法一：

select
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university 
having avg_question_cnt < 5 or avg_answer_cnt < 20

发表于 2022-11-23 19:14:34 回复(0)

Mysql

牛客383646600号

【记录】

1.使用HAVING HAVING子句用于对分组后的结果再进行过滤，它的功能有点像WHERE子句，但它用于组而不是单个记录。

2.在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。

3.HAVING通常与GROUP BY子句一起使用。

发表于 2021-12-08 13:59:34 回复(0)

Mysql

我的世界01号

分析题目可得

对university进行分组，再分完组里面再根据聚合函数avg平均结果question_cnt< 5或者answer_cnt<20的数据及格筛选即可。代码如下：

SELECT university,avg(question_cnt) avg_question_cnt,avg(answer_cnt) avg_answer_cnt
from user_profile
group by university
having avg(question_cnt)<5 or avg(answer_cnt) <20;

发表于 2021-11-21 14:37:13 回复(0)

Mysql

牛客655660060号

select university,round(avg(question_cnt),4) que,round(avg(answer_cnt),4) ans from  user_profile group by university having que<5&nbs***bsp;ans<20;

发表于 2021-08-26 19:15:52 回复(1)

Mysql

238狗嘉豪

select university,
round(avg(question_cnt),3) as avg_question_cnt,
round(avg(answer_cnt),3) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt <20;

发表于 2022-04-06 21:59:12 回复(1)

牛客17959883号

SELECT university,
AVG(question_cnt) AS avg_question_cnt,
AVG(answer_cnt) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt<5 OR avg_answer_cnt<20

--WHERE分组之前过滤数据，不能过滤聚合函数的结果

--having分组后再筛选满足条件的组，可过滤聚合函数的结果
--WHERE 用在group by前，having在group by之后

发表于 2021-12-08 12:07:40 回复(0)

Mysql

有滋有味小熊鞋

SELECT university, avg(question_cnt) as avg_question ,avg(answer_cnt) as avg_answer_cont
FROM user_profile
group by university
HAVING avg(question_cnt) < 5 OR avg(answer_cnt) < 20;

发表于 2021-10-03 16:43:34 回复(1)