综合数据和分组函数

分组函数是对一批(一组)数据进行操作(综合)之后返回一个值。这批数据可能是整个表，也可能是按某种条件把该表分成的组。

一、分组函数

    Oracle常用的分组函数有以下5个：

    COUNT、AVG、SUM、MAX、MIN

    1.COUNT的格式如下：

    COUNT {*[DISTINCT|ALL] 表达式}

    该函数返回查询的行数。

SQL> select count(*) 
  2    from emp;

  COUNT(*)
----------
    14

备注：COUNT(*)返回表中所有的行包括空行和重复的行。
    2.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

    3.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 min(job),max(job) 
  2    from emp;

MIN(JOB)  MAX(JOB)
--------- ---------
ANALYST   SALESMAN

SQL> select min(hiredate) "First Day",max(hiredate) "Last Day"
  2   from emp;

First Day Last Day
--------- ---------
17-DEC-80 23-MAY-87

    4.GROUP BY 子句
    a.改变GROUP BY子句的排序次序

SQL> select job,avg(sal) "Average Salary"
  2    from emp
  3  group by job
  4     order by "Average Salary" desc;

JOB      Average Salary
--------- --------------
PRESIDENT        5000
ANALYST         3000
MANAGER       2758.33333
SALESMAN        1400
CLERK          1037.5

    b.GROUP BY子句的特殊用法
    GROUP BY 子句中的列可以不在SELECT列表中。

SQL> select avg(sal) "Average Salary"
  2    from emp
  3  group by job;

Average Salary
--------------
    1037.5
      1400
      5000
    2758.33333
      3000

备注：为了提高结果的可读性，应尽可能不使用这样的查询方法。
    c.分组函数与GROUP BY子句的非法操作

SQL> select job,avg(sal)
  2    from emp;
select job,avg(sal)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

备注：如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在GROUP BY子句中。
    5.HAVING子句的使用

    如果老板只想知道平均工资高于1500元的职位(工种)，该如何改写下面的查询语句？

SQL> select job,avg(sal) "Average Salary"
  2    from emp
  3  group by job
  4     order by "Average Salary" desc;

JOB      Average Salary
--------- --------------
PRESIDENT        5000
ANALYST         3000
MANAGER       2758.33333
SALESMAN        1400
CLERK          1037.5

例如改成这样：

SQL> select job,avg(sal)
  2    from emp
  3   where avg(sal) > 1500
  4  group by job;
 where avg(sal) > 1500
       *
ERROR at line 3:
ORA-00934: group function is not allowed here

这是因为WHERE子句不能用于限制分组函数。

在Oracle中您可以使用HAVING子句来限制分组函数。

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

  当使用了HAVING子句时，Oracle系统处理的顺序如下：
    (1)、首先对数据行(记录)进行分组；

    (2)、把所得到的的分组应用于分组函数；

    (3)、最后显示满足HAVING子句所指定条件的结果。

    6.分组函数的嵌套

    查询以工作分类的平均工资中最低和最高，但是不包括总裁。

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语句带来了很大的方便，但是使用起来可能会使系统的效率明显下降，特别是在对容量大的表格进行这样的操作时。因为使用分组函数通常要扫描整个表，如果使用了GROUP BY子句，Oracle还要进行排序。

    7.分组函数的空值问题

    除了COUNT(*)以外，其他的分组函数都不处理空值(NULL)。

    8.NVL函数在分组函数中的使用

    如果想让所有的员工都参加平均，可借助于NVL函数。

SQL> select avg(nvl(comm,0)) "Average Commission"
  2   from emp;

Average Commission
------------------
    157.142857