综合数据和分组函数
分组函数是对一批（一组）数据进行操作（综合）之后返回一个值。这批数据可能是整个表，也可能是按某种条件把该表分成的组。
一：五个常用的分组函数
Oracle常用的分组函数有以下5个：COUNT、AVG、SUM、MAX、MIN。下面分别介绍这5个分组函数。
二：count函数
COUNT的格式如下：COUNT（{*[ DISTINCT|ALL]表达式} 该函数返回查询的行数。
SQL> select count(*) from emp;
COUNT(*)
----------
14
COUNT(*)返回表中所有的行包括空行和重复的行。
SQL> select count(mgr) from emp;
COUNT(MGR)
----------
13
COUNT（表达式）返回表中所有表达式为非空的行，所以COUNT(mgr) 返回表中所有mgr为非空的行。
三：AVG和SUM函数
AVG函数的格式如下：AVG（[DISTINCT|ALL]表达式）该函数返回表达式的平均值。
SUM函数的格式如下：SUM（[DISTINCT|ALL]表达式） 该函数返回表达式的总合。
SQL> SELECT AVG(sal) "Average Salary", SUM(sal) "Summary", COUNT(sal) "Records"
2 FROM emp;
Average Salary Summary Records
-------------- ---------- ----------
2073.21429 29025 14
SQL> select sum(comm),avg(comm),count(comm),count(*)
2 from emp;
SUM(COMM) AVG(COMM) COUNT(COMM) COUNT(*)
---------- ---------- ----------- ----------
2200 550 4 14
由上例可知AVG（COMM）在计算平均值的时候只计算COMM不为NULL的行。即：avg（列名）函数只处理该列不为null的记录。
四：MIN和MAX函数
MAX函数的格式如下：MAX（[DISTINCT|ALL]表达式） 该函数返回表达式的最大值。
MIN函数的格式如下：MIN（[DISTINCT|ALL]表达式） 该函数返回表达式的最小值。
SQL> SELECT MIN(sal) "Lowest Salary", MAX(sal) "Highest Salary"
2 FROM emp;
Lowest Salary Highest Salary
------------- --------------
800 5000
不像AVG和SUM函数只能操作数字型数据，MIN和MAX函数不但可用于数字型数据，而且还可以用于字符型数据和日期型数据。
SQL> select max(job),min(job),max(hiredate),min(hiredate) from emp;
MAX(JOB) MIN(JOB) MAX(HIREDATE) MIN(HIREDATE)
--------- --------- -------------- --------------
SALESMAN ANALYST 23-5月 -87 17-12月-80
五：GROUP BY子句的应用
select 选择列表（列名|表达式|分组函数） from 表名 group by 列名|表达式 [order by 列名|表达式|分组函数]
其中：
1：select的选择列表中的“列”必须是group by使用的列的子集，select的选择列表中的表达式必须和group by使用的表达式一样，select的选择列表中的分组函数可以任意。
2：order by使用的“列”必须是group by使用的列的子集，order by使用的表达式必须和group by使用的表达式一样，order by使用的分组函数可以任意。
在前面几节中，我们都是把一个表看成一个大组来处理。我们可以使用GROUP BY 子句把一个表化分成若干个组，在一个表中建立多组数据,然后使用分组函数对各组数据进行操作后返回一个值，
注意是每组数据返回一个值。如下例：
SQL> select job,avg(sal)
2 from emp
3 group by job;
JOB AVG(SAL)
--------- ----------
ANALYST 3000
CLERK 1037.5
MANAGER 2758.33333
PRESIDENT 5000
SALESMAN 1400
上例使用group by子句把emp表中的数据按照job分组，job相同的记录分为一个组，然后使用avg函数对每组中的sal列求平均值，这样每组就有一个sal的平均值。
这样求出的结果就是各种job的sal的平均值。
如果在使用了group by子句的select语句中不使用分组函数的话，则效果如同在选择列表前加上distinct。如下列：
SQL> select deptno from emp group by deptno;
DEPTNO
----------
10
20
30
如果group by使用的列中包含null值，则所有该列为null的记录分到同一个组，如下例：
SQL> select sum(deptno),count(deptno),avg(deptno),comm from emp group by comm;
SUM(DEPTNO) COUNT(DEPTNO) AVG(DEPTNO) COMM
----------- ------------- ----------- ----------
30 1 30 0
30 1 30 300
30 1 30 500
30 1 30 1400
190 10 19
六：改变GROUP BY子句的排序次序
Oracle 默认的GROUP BY 子句的排序方式是由小到大的顺序排列（升序排序）。我们可以用ORDER BY 子句来改变这一默认排序次序。
SQL> select job,avg(sal)
2 from emp
3 group by job
4 order by 2 desc;
JOB AVG(SAL)
--------- ----------
PRESIDENT 5000
ANALYST 3000
MANAGER 2758.33333
SALESMAN 1400
CLERK 1037.5
使用了group by子句再使用order by子句的话，则order by子句使用的列要么是group by使用的列|表达式，要么是分组函数。如下：
SQL> select avg(sal),job
2 from emp
3 group by job
4 order by ename;
order by ename
*
第 4 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
ename列不在group by子句中，该sql出错，无法执行。
SQL> 4
4* order by ename
SQL> c /ename/max(ename)
4* order by max(ename)
SQL> l
1 select avg(sal),job
2 from emp
3 group by job
4* order by max(ename)
SQL> /
AVG(SAL) JOB
---------- ---------
2758.33333 MANAGER
5000 PRESIDENT
3000 ANALYST
1037.5 CLERK
1400 SALESMAN
SQL> select avg(sal),job from emp group by job order by job;
AVG(SAL) JOB
---------- ---------
3000 ANALYST
1037.5 CLERK
2758.33333 MANAGER
5000 PRESIDENT
1400 SALESMAN
SQL> select deptno+10 from emp group by deptno+10 order by deptno+10 desc;
DEPTNO+10
----------
40
30
20
七：group by 子句的特殊用法
GROUP BY子句中的列可以不在SELECT列表中。
SQL> select avg(sal)
2 from emp
3 group by job;
AVG(SAL)
----------
3000
1037.5
2758.33333
5000
1400
八：分组函数与group by子句的非法操作
SQL> select job, avg(sal)
2 from emp;
select job, avg(sal)
*
第 1 行出现错误:
ORA-00937: 非单组分组函数
注意： 如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在GROUP BY子句中。
SQL> select job, avg(sal)
2 from emp
3 group by job
4 order by deptno;
order by deptno
*
第 4 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
错在ORDER BY 子句上。因为deptno不在分组函数中，也不在GROUP BY子句中。
九：HAVING子句的使用
SQL> select job, avg(sal)
2 from emp
3 where avg(sal)
4 group by job;
where avg(sal)
*
第 3 行出现错误:
ORA-00934: 此处不允许使用分组函数
这是因为WHERE子句不能用于限制分组函数。在Oracle中您可以使用HAVING子句来限制分组函数。
SQL> select job, avg(sal)
2 from emp
3 having avg(sal) > 1500
4 group by job;
JOB AVG(SAL)
--------- ----------
ANALYST 3000
MANAGER 2758.33333
PRESIDENT 5000
SQL>
当使用了HAVING子句时，Oracle系统处理的顺序如下：
（1）首先对数据行（记录）进行分组；
（2）把所得到的分组应用于分组函数；
（3）最后显示满足HAVING子句所指定条件的结果。
虽然上例的查询语句得到了所需要的结果，但是它与 Oracle 处理带有 HAVING 子句的查询语句的逻辑顺序不同。因此最好把 HAVING子句放在GROUP BY子句之后。如下：
SQL> select job, avg(sal)
2 from emp
3 group by job
4 having avg(sal) > 1500
5 order by 2;
JOB AVG(SAL)
--------- ----------
MANAGER 2758.33333
ANALYST 3000
PRESIDENT 5000
十：分组函数的嵌套
如果想查询按职位分类最低和最高平均工资（不包括总裁）：
SQL> select min(avg(sal)),max(avg(sal))
2 from emp
3 where job not like 'PRESI%'
4 group by job;
MIN(AVG(SAL)) MAX(AVG(SAL))
------------- -------------
1037.5 3000
在上例的查询语句中我们使用了两层的分组函数的嵌套。在 Oracle系统中嵌套的分组函数计算顺序是由内到外。也就是说，在Oracle系统中按如下的顺序来执行上例的查询语句：
（1）在emp表中找到所有职位（job）不是以PRESI开头的数据行（记录）；
（2）将这些数据行（记录）按职位（job）分类；
（3）求出每一类的平均工资；
（4）最后求出这些平均工资的最小值和最大值。
与单值函数不同，分组函数只能嵌套两层。
注意： 尽管分组函数给我们编写 SQL 语句带来了很大的方便，但是使用起来可能会使系统的效率明显下降，特别是在对容量大的表格进行这样的操作时。
因为使用分组函数通常要扫描整个表，如果使用了order by子句，Oracle还要进行排序。
十一：分组函数的空值问题
除了COUNT（*）以外，其他的分组函数都不处理空值（NULL）。
如果想查询所有员工的平均佣金，而使用了如下的语句：
SQL> select avg(comm) from emp;
AVG(COMM)
----------
550
但是上例的结果似乎有些问题，因为所显示的平均佣金明显偏高。其原因是AVG(comm)不包括 comm 为空值（NULL）的记录行，而在整个公司中只有推销员
（SALESMAN）有佣金。所以上例显示的结果其实是推销员（SALESMAN）的平均佣金。可以使用下例的SQL语句来验证我们的分析。
SQL> select job, avg(comm),sum(comm),count(comm)
2 from emp
3 group by job;
JOB AVG(COMM) SUM(COMM) COUNT(COMM)
--------- ---------- ---------- -----------
ANALYST 0
CLERK 0
MANAGER 0
PRESIDENT 0
SALESMAN 550 2200 4
可以看到：AVG(comm)不包括 comm 为空值（NULL）的记录行，而在整个公司中只有推销员（SALESMAN）有佣金。
从上例显示的结果可以看出：Oracle先求出佣金的总数，再除以挣佣金的员工总人数（推销员的总人数），即2200/（4+0+0+0+0）= 550。
十二：NVL函数在分组函数中的使用
如果想让所有的员工都参加平均，可借助于 NVL 函数。
SQL> select avg(nvl(comm,0)),sum(nvl(comm,0)),count(nvl(comm,0))
2 from emp;
AVG(NVL(COMM,0)) SUM(NVL(COMM,0)) COUNT(NVL(COMM,0))
---------------- ---------------- ------------------
157.142857 2200 14
十三：其他的分组函数和分组函数小结
如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在GROUP BY子句中。
如果在一个查询中所使用的限制条件中包括了分组函数，则该限制条件必须放在HAVING子句中而不能放在WHERE子句中。
MIN和MAX函数不但可用于数字型数据而且还可用于字符型数据和日期型数据。
分组函数在决策支持系统（DSS：Decision Support System）中使用的比较多。虽然分组函数为管理者或决策者提供了丰富的信息但它们对系统效率的冲击是不容忽视的。
946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
