ORACLE--函数2（聚合函数、分组函数）

1 聚合函数

又称多行函数、分组函数
作用：对结果集某些字段的值进行统计的，会忽略null。即拿到某一列多行的数据进行统计运算，但只返回一个结果。

1.1 MAX-MIN 字段最值

分别求出指定字段的最大值和最小值

--查看emp表中公司的最高工资与最低工资是多少
SELECT MAX(sal),MIN(sal) FROM emp;

1.2 AVG-SUM 平均值和总和

查看emp表中员工的平均工资和总工资是多少
SELECT  AVG(sal),SUM(sal) FROM emp; 

–查看公司平均奖金
SELECT AVG(comm),SUM(comm) FROM emp;
这是错误写法，此时AVG计算的平均值是非NULL值的平均值（SUM除以4），并没有除以总人数（14），这样求的就不是整个公司人的平均奖金了。解决方法：

SELECT AVG(NVL(comm,0)),SUM(comm)FROM emp;

1.3 COUNT() 统计字段记录数

返回给定字段不为null有多少条数
SELECT COUNT(comm) FROM emp;	--4
--聚合函数都是忽略了NULL值，若想不忽略NULL就要考虑NVL：
SELECT COUNT(NVL(comm,0)) FROM emp;	--14
--查看emp表中有多少条记录
SELECT COUNT(*) FROM emp;--14	（效率低，不会忽略NULL值）
SELECT COUNT(1) FROM emp;--14	（效果高，不会忽略NULL值）
SELECT COUNT(22) FROM emp;--14
--聚合函数书写汇总
SELECT MAX(sal) 最高工资,MIN(sal) 最低工资,AVG(sal) 平均工资,SUM(sal) 工资总和,COUNT(1) 总人数 FROM emp;

SUM和AVG函数只可以对数字类型进行统计。
MAX和MIN函数可以对数字类型和字符串类型进行统计求最值。

2 分组(配合聚合函数)

为了配合聚合函数，进行再次细分。

2.1 GROUP BY 分组

将查询出的结果集按照指定的字段值相同的记录看成一组，然后配合聚合函数进行细分的统计工作，即对某个细分的每个组里面的数据进行统计。如将部门号分组，可以求出每个部门的平均工资。

--先对部门号进行排序
SELECT * FROM emp ORDER BY depto;
--再查询每个部门的平均工资（单个字段分组）
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;

注意：分组查询SELECT后面只能跟分聚合函数和分组函数。因为单行数据和多行数据不能同时被查询。
GROUP BY可以根据字段进行分组，分组原则把相同的看成一组。多个字段分组，则相同的部门和职位是一组。

--查看同部门同职位的平均工资是多少（多个字段分组）
SELECT AVG(sal),job,deptno FROM emp GROUP BY job,deptno ORDER BY deptno;

–当select字句中含有聚合函数时，如果查询的字段没有使用聚合函数，那么一律都要放到GROUP BY 后面，反过来如果没有聚合函数，不建议分组。

–查看部门的平均工资，前提是该部门的平均工资高于2000
SELECT AVG(sal),deptno FROM emp WHERE AVG(sal)>2000 GROUP BY deptno;–结果报错
以上执行后报错：不允许使用分组函数，原因是过滤时机不对，是因为WHERE是对记录进行查询的，而聚合函数时过滤时是已经知道所有的数据在进行求平均值，这与WHERE功能不符合。
WHERE中不能使用聚合函数作为过滤条件，原因过滤时机不对。
WHERE是在数据库查询表中数据时，对数据逐条过滤来决定是否查询出该数据时使用的，因此where用来确定结果集的数据。
要想使用聚合函数的结果作为过滤条件时，那么数据要先从表中查询完毕（WHERE在查询过程中发挥作用）后得到结果集，并且分组完毕后再进行聚合函数统计结果，此时得到结构后才可以对分组进行过滤。
所以以上，这个过滤时机是在WHERE之后进行，就引入了：

2.2 HAVING 过滤分组

使用聚合函数结果作为查询条件要使用HAVING，HVAING 必须跟在GROUP BY 子句后。

--查看部门的平均工资，前提是该部门的平均工资高于2000
SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;
--查看平均工资高于2000的部门的最低工资和最高工资分别是多少
SELECT MAX(sal),MIN(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

–对上述语句添加一个WHERE，也是可以的。只不过要符合WHERE的语法要求。

SELECT MAX(sal),MIN(sal),deptno FROM emp WHERE deptno =20  GROUP BY deptno HAVING AVG(sal)>2000 ORDER BY deptno;

3 查询语句执行顺序

过滤条件是聚合函数就要用HAVING，HAVING和GROUP BY在一起。
FROM 如果from后跟上多表，执行顺序从右往左，数据量 少的表放右边
WHERE 如果WHERE后面跟上多个条件，从右往左，数据 过滤条数多的放右边
GROUP BY 如果分组跟多个字段分组从左往右
HAVING HAVING要先对数据进行统计在进行聚合函数过 滤，效率低，消耗资源多
SELECT 查询内容 尽量不要用 *，数据少可以写。
ORDER BY 最后
书写顺序 select–from --where–group by–having-- order BY
执行顺序： from–where-group by -having-select-order by