其实前面聊的很多函数,其实单行函数。
现在我们开始聊一些聚合函数,所以本章就是聊一些常见的聚合函数。
常用的聚合函数
函数 | 作用 |
---|---|
AVG() | 求平均值 |
SUM() | 求和 |
COUNT() | 算出有多少个 |
MIN() | 求最小值 |
MAX() | 求最大值 |
上面的方法只是常用的,而不是所有的,比如还有什么中位数等方法。
还是用员工表进行演示,员工表如下:
SELECT MAX(sal),MIN(sal),AVG(sal),COUNT(sal),SUM(sal) FROM test.emp
这个看着和数学的祭祀啊u你一样,但是这个有一个问题,也是数据库中最怕的数据NULL.
正常来说AVG=SUM/COUNT.
所以我们搞以下抽成这个字段:comm
SELECT MAX(comm),MIN(comm),COUNT(comm),SUM(comm),COUNT(1),AVG(comm),SUM(comm)/COUNT(comm),SUM(comm)/15 FROM test.emp
这个值可以看出,无论AVG,SUM,COUNT,三个函数都会不计算该字段的值为NULL。所以在要求中计算所有用户的平均,要看清楚具体的需求。
min和max在遇见NULL的时候其不为最大也不是最小,如果该字段都为NULL,那么其结果为NULL,如果只有一个值非NULL,其它都是NULL,那么最大值最小值都是这个非NULL的值。
上面还有一个COUNT(1)
其的值为一共多少条数据,为什么会这样呢,这个就像是在
SELECT sal,1 FROM emp
-- 1字段下的数据,sal有多少个,1就补充多少个,所以很多时候 求全部的平均值的时候,是sum(某值)/count(1),当然其它字段也行。
补充 COUNT的效率
其实求一共多少条,目前有三种写法:
SELECT COUNT(sal),COUNT(*),COUNT(1) FROM emp
-- 默认使用字段中值都不为空,如果字段中有值为NULL ,那么count(字段)有可能不对。
三者的速度其实速度和引擎有关,后面聊优化的时候,在具体聊引擎,现在记住结果即可。
如果使用的是MyISAM存储引擎,则三者的效率相同,都是O(1).
如果使用的是InnoDB存储引擎,则三者的效率:COUNT(*)=COUNT(1)(只能说约等于)>COUNT(字段)(如果字段中都非空,那几乎和前两者没有什么区别。)
所以无论在那个引擎下,建议用COUNT(*)算多少行。
其实上面的常用的聚合函数,其很重要的是搭配着GROUP BY
而使用的。
GROUP BY
GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。比如前面计算的最大最小或者平均值都是整体,如果按部门求平均工资呢?
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno
可见将不同的部分,分别进行求职。
当然其后面可以有多个字段,比如部门,以及部门下的职位的平均工资。
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job
-- GROUP BY 后面的字段其实前后顺序不一样,不影响结果
这个地方又涉及到一个规范:
比如这样写:
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno
在MYSQL中不会报错,如果在ORACL中就会报错,不过这样写job字段又没什么意义,所以一般SELECT中出现的非使用组函数的字段必须声明在GROUP BY 中,反之GROUP BY 中的字段可以不出现在SELECT 中
前面聊ORDER BY的时候说其位置的放置,那么GROUP BY呢?
GROUP BY 声明在FROM 以及 WHERE 后面,ORDER BY 前面,LIMIT 前面
WITH ROLLUP
这个是在分组后计算的数据后面,计算一个全部的。可能这样说有点绕,还是演示以下比较靠谱
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno WITH ROLLUP
如果对这个数字不熟悉的话,看下:
SELECT AVG(sal) FROM emp
可以看出其下面又进行了一个求总的平均值,当然,还有其它聚合函数也是类似,所以不在演示。
不过这个时候就涉及到一个排序问题,所以在使用WIHT ROLLUP的时候,排序可能会让结果有点模糊,比如下面求各个部分的工资,然后排序。
SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno WITH ROLLUP ORDER BY avg_sal
-- 这样写就会报错,比较整体的平均值和各个部分平均值数据的性质不一样啊。
HAVING
这个是过滤条件语句的。
如果这样说是否感觉和WHERE有重复呢?
既然两个不同的筛选关键字,那其自然有区别。
SELECT sal FROM emp HAVING sal>2000
如果这样写的话,感觉就是WHERE 的用法,说实话在MYSQL函数中都有两个不同的函数得到的结果一样。
现在又有一个区别,比如各个部门平均工资大于2500块的有哪些部门?
SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal) >'2500' GROUP BY deptno
可以看出报错了,那么使用HAVING呢
SELECT deptno,AVG(sal) FROM emp HAVING AVG(sal) >'2500' GROUP BY deptno
同样是报错啊,不过可以调一下位置。
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >'2500'
这样就可以了,WHERE 是不肯能放在GROUP BY 后面的。
现在可以看出:**如果条件语句中使用了聚合函数,则必须使用HAVING替换WHERE,否则报错。 **
如果没有GROUP BY 的时候,HAVING 和WHERE 似乎一样,如果二者只选一个用的话,还是有要求的:一般HAVING 搭配着GROUP BY 搭配着使用,如果没有GROUP BY的时候使用WHERE.
比如下面:
而且WHERE 和HAVING可以一起用。
如果不一起用的话:
-- 第一种
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >'2500' AND deptno=10
前面说了可以一起用,如下:
-- 第二种
SELECT deptno,AVG(sal) FROM emp WHERE deptno=10 GROUP BY deptno HAVING AVG(sal) >'2500'
这个时候就有问题,为什么用一个就行,还需要用两个,或者说两者执行是否效率有区别?
先说结果:第一种执行的效率低于第二种。
整体总结:
当前的过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING中
当前的过滤条件中没有聚合函数时,则过滤条件声明在WHERE和HAVING中都行,但是建议非聚合函条件放在WHERE 语句后面(其实在开发中之不是建议而是必须)
SQL执行顺序
前面说HAVING执行效率低于WHERE是为什么呢?这个就需要聊一下SQL在执行中具体的执行的顺序是什么?
-- SQL92 语法 语句的完整结构
SELECT .,..,...,(聚合函数)
FROM ....,....
WHERE 多表的连接条件 AND 不包含集合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,..(ASC/DESC)
LIMIT ...,..
-- SQL99 语法 语句的完整结构
SELECT .,..,...,(聚合函数)
FROM .... (LEFT/RIGHT) JOIN ... ON 多表连接条件
WHERE 不包含集合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,..(ASC/DESC)
LIMIT ...,..
上面演示了SQL语句的组成部分。这个就有一个疑问了,比较在很多语言中都是从上而下执行的那么SQL执行的顺序也是如此吗?
其实不是具体的执行是:
所以具体分四大部分,在各自的部分中依次从下而下执行。
-
第一部分:
- from 表 :数据的源头必然第一,数据不可能凭空而生。
- ON 告诉数据库,有表要连接起来。
- (LEFT/RIGHT)讲明表的具体关联关系是左还是右。
- WHERE 筛选条件,毕竟如果有运算的话,将不符合条件的语句筛选出来。
- GROUP BY 这样就可以理解为什么搭配着GROUP BY 出现的聚合函数不能放在WHERE 中了,因为其执行顺序在WHERE 后面
-
第二部分
- HAVING : 对有聚合函数的数据再一次进行筛选
-
第三部分
- SELECT 将需要显示的字段选出来,如果使用了DISTINCT,其再SELECT 后面显示。如果有别名的话,也是这个地方生效,所以不能在WHERE 中使用,不过HAVING例外,其可以使用别名(在MYSQL中,如果在SQLserver中也会报错。)。
-
第四部分
- ORDER BY: 根据条件将其进行排序
- LIMIT 显示多少数据
补充: 个人觉得其实上面所说的HAVING 在SELECT前面有点不太对,更好的理解是 SELECT -> HAVING -> SELECT 。HAVING 是对SELECT集合再次进行了一次筛选,然后再SELECT。