题目解答

题目来源

牛客网： 分组过滤练习题
参考文章：
Mysql中having和where的区别
MySQL聚合函数
MySQL count()函数

题目解析

输入：

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);

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

解答

根据以上分析，是要把分组后的值作为条件，不能直接使用where，需要使用having

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 ;

扩展学习

having和where的区别

having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合
在查询过程中的执行顺序为：from>where>group（含聚合）>having>order>select
having就是来弥补where在分组数据判断时的不足。因为where执行优先级别要快于聚合语句。

聚合函数

如AVG()，COUNT()，SUM()，MAX()和MIN()等，作用在分组上的函数，除了count函数外，其他聚合函数在执行时会忽略NULL值。

count

COUNT函数有几种形式：COUNT(*)，COUNT(expression)和COUNT(DISTINCT expression)。

COUNT()函数返回由SELECT语句返回的结果集中的行数。COUNT()函数计算包含NULL和非NULL值的行，即：所有行。

COUNT(expression)返回不包含NULL值的行数。

COUNT(expression)返回不包含NULL值的行数。

示例1：
有一个表department，数据如下：

select * from `department`;

返回：

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
|    1 |    8000 | Jan   |
|    2 |    9000 | Jan   |
|    3 |   10000 | Feb   |
|    1 |    7000 | Feb   |
|    1 |    6000 | Mar   |
| NULL |    8000 | Jan   |
| NULL |    8000 | Feb   |
| NULL |    9000 | Jan   |
| NULL |    9000 | Feb   |
+------+---------+-------+

此时执行select count(*) from department;,返回数据为9; 示例2：
当执行：

select count(`id`) from `department`;

返回：

+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+

示例3：
当执行：

select count(DISTINCT `id`) from `department`;

返回：

+--------------------+
| count(DISTINCT id) |
+--------------------+
|                  3 |
+--------------------+

COUNT IF

还是前一个示例的表，使用IF来进行流程判断，那么语句为：

select count(IF(`revenue`=9000, 1, NULL)) from department;

SUM

依然是不计算NULL的值，可用FORMAT()进行格式化，例如：

select FORMAT(SUM(`revenue`), 2) total from `department`;

输出

+-------------------------+
| total  |
+-------------------------+
| 74,000.00               |
+-------------------------+