分组函数
是对表中一组记录进行操作，每组只返回一个结果。即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果。分组时可能是整个表分为一组，也可能根据条件分成多组。
oracle实验17：分组统计函数
分组函数常用到以下的五个函数：
–MIN 最小值 MIN([DISTINCT|ALL]表达式)
–MAX 最大值 MAX([DISTINCT|ALL]表达式)
–SUM 求和 SUM([DISTINCT|ALL]n)
–AVG 平均 AVG([DISTINCT|ALL]n)
–COUNT 计数 COUNT({*|[DISTINCT|ALL]表达式})
求工资总和，最低工资，最高工资，平均工资，工资计数。
SQL> select sum(sal),min(sal),max(sal),avg(sal),count(sal) from emp;
SUM(SAL) MIN(SAL) MAX(SAL) AVG(SAL) COUNT(SAL)
---------- ---------- ---------- ---------- ----------
24930 801 5000 2077.5 12
日期的最小和最大
SQL> select min(hiredate),max(hiredate) from emp;
MIN(HIREDATE) MAX(HIREDATE)
-------------- --------------
17-12月-80 23-1月 -82
日期的小为早，大为晚。
count(*)查有多少行包含空行，count(列名)查非空的行。
SQL> select count(*),count(comm) from emp;
COUNT(*) COUNT(COMM)
---------- -----------
12 4
组函数中空值处理
所有组函数，除了count(*)，都忽略空值。
求平均奖金
SQL> select avg(comm),avg(nvl(comm,0)) from emp;
AVG(COMM) AVG(NVL(COMM,0))
---------- ----------------
550 183.333333
AVG(COMM)奖金非空的人的平均值；
AVG(NVL(COMM,0))所有人奖金的平均值，如果奖金为空，按0计算。
SQL> select sum(comm),count(comm),count(*) from emp;
SUM(COMM) COUNT(COMM) COUNT(*)
---------- ----------- ----------
2200 4 12
SUM和AVG函数都是只能够对数字类型的列或表达式操作。
SQL> select sum(ename) from emp;
select sum(ename) from emp
*
第 1 行出现错误:
ORA-01722: 无效数字
DISTINCT会消除重复记录后再使用组函数
SQL> select count(distinct deptno) from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3
计算有多少不同部门代码数。
GROUP BY子句
语法：
SELECT列名, 组函数(列名)
FROM表名
[WHERE条件]
[GROUP BY分组列]
[ORDER BY列名];
组函数忽略空值,可以使用NVL,NVL2,COALESCE 函数处理空值
结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句
SQL> select deptno ,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9402
20 6778
10 8750
在GROUP BY子句使用中，有两点需要注意：
–GROUP BY子句后的列可以不在SELECT语句中出现，有利于子查询。
–SELECT子句中出现的非分组函数列必须在GROUP BY子句中出现。
SQL> select sum(sal) from emp group by deptno;
SUM(SAL)
----------
9402
6778
8750
SQL> select deptno ,sum(sal) from emp;
select deptno ,sum(sal) from emp
*
第 1 行出现错误:
ORA-00937: 不是单组分组函数
deptno要求每行都显示，而sum要求多行统计后再显示，违反了原则。
多列分组，每列都一样才放到一组。
SQL> select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 801
30 SALESMAN 5602
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 3002
已选择9行。
30号部门中有4个销售，合并成一行。
SQL> select job,avg(sal) from emp group by job;
JOB AVG(SAL)
--------- ----------
CLERK 1017
SALESMAN 1400.5
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3002
SQL> select job,avg(sal) from emp
where avg(sal)>2000
group by job;
where avg(sal)>2000
*
第 2 行出现错误:
ORA-00934: 此处不允许使用分组函数
不能执行，原因是where是条件，avg(sal)是结果。条件中使用了结果，违反了因果关系。
Oracle查询语句的执行顺序是：–FROM WHERE GROUP BY SELECT ORDER BY
HAVING子句
语法：
SELECT列名, 组函数
FROM表名
[WHERE条件]
[GROUP BY分组列]
[HAVING组函数表达式]
[ORDER BY列名];
HAVING是在结果中再次筛选，HAVING一定要出现在group by子句的后面，不能独立存在。
SQL> select job,avg(sal) from emp group by job having avg(sal)>2000;
JOB AVG(SAL)
--------- ----------
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3002
SQL> select deptno,JOB,sal from emp where job='SALESMAN' or job='CLERK' ;
DEPTNO JOB SAL
---------- --------- ----------
20 CLERK 801
30 SALESMAN 1601
30 SALESMAN 1250
30 SALESMAN 1250
30 SALESMAN 1501
30 CLERK 950
10 CLERK 1300
已选择7行。
SQL> select deptno,avg(sal) from emp
where job='SALESMAN' or job='CLERK'
group by deptno
having avg(sal)>1000;
DEPTNO AVG(SAL)
---------- ----------
30 1310.4
10 1300
where和having可以同时出现在一句话中，起作用的时间不同。
总结SELECT语句执行过程：
–通过FROM子句中找到需要查询的表；
–通过WHERE子句进行非分组函数筛选判断；
–通过GROUP BY子句完成分组操作；
–通过HAVING子句完成组函数筛选判断；
–通过SELECT子句选择显示的列或表达式及组函数；
–通过ORDER BY子句进行排序操作。
组函数的嵌套
组函数可以实现嵌套操作，组函数的嵌套要使用group by子句，嵌套级数是2级。
SQL> select max(avg(sal)) from emp group by deptno;
MAX(AVG(SAL))
-------------
2916.66667
求各个部门平均工资的最大值。
巧用decode函数，改变排版方式。
SQL> select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987",
count(ename) "总人数"
from emp;
1980 1981 1982 1987 总人数
---------- ---------- ---------- ---------- ----------
1 10 1 0 12
知识点：
- 组函数
- 分组统计
- null值在组函数中的作用
- having的过滤作用
- 组函数的嵌套