Oracle_分组函数

• 组函数
• GROUP BY子句数据分组
• HAVING子句过滤分组结果集

---

组函数

• （分）组函数作用于一组数据，返回一个值。
• AVG\COUNT\MAX\MIN\SUM

select sum(sal) from emp; //对所有行的sal求和
select count(empno) from emp; //统计；不去重
select sum(sal)/count(empno) from emp; //求平均工资
select avg(sal) from emp; //作用同上句
select count(*), count(comm) from emp; //两个结果不同：count(*)会自动选择没有空值的列

注意：组函数能自动滤空，即自动过滤掉空值。

//屏蔽组函数滤空的方法：借助滤空函数nvl
select count(nvl(comm, 0)) from emp;

去重：

select count(distinct job) from emp;

分组数据

• 根据一些条件将查询到的数据分成若干组：GROUP BY

select deptno, avg(sal) 
from emp 
group by deptno 
order by deptno; //按deptno计算平均薪水，并排序

注意：

1. 在SELECT查询列中，所有没有包含在组函数中的列，都必须在GROUP BY后面出现。
2. 另外，在使用组函数配合GROUP BY时，若缺少GROUP BY子句，会出错。

select deptno, avg(sal) from emp; //出错：缺少GROUP BY分组依据

//统计每年入职的员工个数:
select count(*) total,
	   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"
from emp;
//或者使用group by：
select to_char(hiredate, 'yyyy'), count(*) total
from emp
group by to_char(hiredate, 'yyyy');

Having

• 对分组数据进行过滤：

• 语法：
  select column, group_function
  from table
  [where condition]
  [group by group_by_expression]
  [having group_condition]
  [order by column];

• 由前可知，where也能实现过滤。但，having是对分组后的数据进行过滤（跟在GROUP BY子句后），而where是对整张表的查询结果进行过滤。

//查询平均薪水大于2000的部门（这实际是在分组的基础上过滤分组）
select deptno, avg(sal) 
from emp 
group by deptno
having avg(sal)>2000;

注意：虽然where关键字也能实现过滤，但在该子句中不能使用组函数，而Having子句中可以使用组函数。所以上一个例子中不能使用where子句。

//查询10号部门的平均薪水
select deptno, avg(sal)
from emp
group by deptno
having deptno=10;
//此时，where子句也可
select deptno, avg(sal)
from emp
where deptno=10
group by deptno;

SQL优化建议：
在子句中没有使用组函数的情况下，where和having都能过滤数据，但应尽量使用where。因为where是先过滤再分组，而having是先分组再过滤，当数据量很大时，where优势明显。

GROUP BY的增强

• rollup:

//实现一个报表：按照部门，统计各部门不同工种的工资情况，并计算各部门工资总额：
//分析：
//首先，按照deptno和job分组，select deptno, job, sum(sal) from emp group by deptno, job
//其次，直接按照deptno分组即可，select deptno, sum(sal) from emp group by deptno
//最后，不按照任何条件分组，即 select sum(sal) from emp group by null
//不过，上述语句合起来可写成如下：
select deptno, job, sum(sal) from emp group by rollup(deptno,job);
//等价于：
select deptno, job, sum(sal) from emp group by deptno, job + 
select deptno, sum(sal) from emp group by deptno +
select sum(sal) from emp group by null;
//注意：SQL中没有+连接符，应使用union关键字
//显示格式的设置：
break on deptno skip 2; //相同的部门号只显示一次，不同的部门号之间有2行空行
break on null; //取消设置

• 抽象成表达式：
  group by rollup(a, b) = group by a,b + group by a + group by null
• 另外，可了解：group by rollup(a, b, c)、group by rollup(a, (b, c))、group by a, rollup(b, c)