Oracle 聚合函数详解

Oracle 聚合函数详解

一 聚合函数的定义

聚合函数也叫组函数，有的地方也叫集合函数，它的数据源一般来自多组数据，但返回的时候一般是一组数据，聚合函数对一组行中的某个列执行计算并返回单一的值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数。有一点需要注意的是，除了count和grouping之后，其它的统计运算均会忽略值为null的列。

二 聚合函数的分类

1:   AVG(DISTINCT|ALL)

ALL表示对所有的值求平均值,DISTINCT只对不同的值求平均值

SQL> select avg(sal) from scott.emp;

  AVG(SAL)

----------

2073.21429

SQL> select avg(distinct sal) from scott.emp;

AVG(DISTINCTSAL)

----------------

      2064.58333

SQL> select avg(all sal) from scott.emp;

AVG(ALLSAL)

-----------

 2073.21429

注意事项：

SQL> select avg(comm) from emp;

 AVG(COMM)

----------

       550              -------------550=sum(comm)/4其中4是comm不为null的员工数

SQL> select sum(comm)/14 from emp;

SUM(COMM)/14

------------

  157.142857

SQL> select avg(nvl(comm,0)) from emp;

AVG(NVL(COMM,0))

----------------

      157.142857

2:   MAX(DISTINCT|ALL)

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

SQL> select max(sal) from emp;

  MAX(SAL)

----------

      5000

SQL> select max(all sal) from emp;

MAX(ALLSAL)

-----------

       5000

SQL> select max(distinct sal) from emp;

MAX(DISTINCTSAL)

----------------

            5000

SQL> select max(hiredate) from emp;

MAX(HIRED

---------

23-MAY-87

3:   MIN(DISTINCT|ALL)

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

SQL> select min(sal) from emp;

  MIN(SAL)

----------

       800

SQL> select min(all sal) from emp;

MIN(ALLSAL)

-----------

        800

SQL> select min(distinct sal) from emp;

MIN(DISTINCTSAL)

----------------

             800

SQL> select min(hiredate),min(to_char(sysdate,'yyyy-mm-dd hh34:mi:ss')) from emp;

MIN(HIRED MIN(TO_CHAR(SYSDATE

--------- -------------------

17-DEC-80 2014-08-23 22:10:49

4:  STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

SQL> select stddev(sal) from emp;

STDDEV(SAL)

-----------

 1182.50322

SQL> select stddev(all sal) from emp;

STDDEV(ALLSAL)

--------------

    1182.50322

SQL> select stddev(distinct sal) from emp;

STDDEV(DISTINCTSAL)

-------------------

         1229.95096

5:   VARIANCE(DISTINCT|ALL)

求协方差  ALL表示对所有的值求协方差,DISTINCT表示只对不同的值求协方差

SQL> select variance(sal) from emp;

VARIANCE(SAL)

-------------

   1398313.87

SQL> select variance(all sal) from emp;

VARIANCE(ALLSAL)

----------------

      1398313.87

SQL> select variance(distinct sal) from emp;

VARIANCE(DISTINCTSAL)

---------------------

           1512779.36

6:  SUM(DISTINCT|ALL)

求和  ALL表示对所有值求和，DISTINCT表示只对不同值求和(相同值只取一次)

SQL> select sum(sal) from emp;

  SUM(SAL)

----------

     29025

SQL> select sum(all sal) from emp;

SUM(ALLSAL)

-----------

      29025

SQL> select sum(distinct sal) from emp;

SUM(DISTINCTSAL)

----------------

           24775

7：COUNT(DISTINCT|ALL)

求记录、数据个数。 ALL对所有记录，数组做统计， DISTINCT只对不同值统计(相同值只取一次)

SQL> select count(sal) from emp;

COUNT(SAL)

----------

        14

SQL> select count(all sal) from emp;

COUNT(ALLSAL)

-------------

           14

SQL> select count(distinct sal) from emp;

COUNT(DISTINCTSAL)

------------------

                12

8： MEDIAN

求中位数

SQL> select median(sal) from emp;

MEDIAN(SAL)

-----------

       1550

SQL> select median(all sal) from emp;

MEDIAN(ALLSAL)

--------------

          1550

SQL> select median(distinct sal) from emp;

select median(distinct sal) from emp

       *

ERROR at line 1:

ORA-30482: DISTINCT option not allowed for this function    --错误：DISTINCT 选项在此函数中禁用。

三  Group by子句

Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

注意：group by子句一定要与分组函数结合使用,否则没有意义

1  求出每个部门的人数

SQL> select deptno,count(*) num from emp group by deptno order by deptno;

    DEPTNO        NUM

----------       ----------

        10          3

        20          5

30             6

2  每个部门员工的平均工资

SQL>  select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)

---------- ----------

        30 1566.66667

        20       2175

        10 2916.66667

SQL>  select deptno,avg(nvl(sal,0)) from emp group by deptno;

    DEPTNO AVG(NVL(SAL,0))

---------- ---------------

        30      1566.66667

        20            2175

10         2916.66667

3  每个部门员工的工资+奖金

SQL>  select deptno,avg(sal+nvl(comm,0)) from emp group by deptno;

    DEPTNO AVG(SAL+NVL(COMM,0))

---------- --------------------

        30           1933.33333

        20                 2175

        10           2916.66667

SQL>  select deptno,avg(nvl(sal,0)+nvl(comm,0)) from emp group by deptno;

    DEPTNO AVG(NVL(SAL,0)+NVL(COMM,0))

---------- ---------------------------

        30                  1933.33333

        20                        2175

        10                  2916.66667

注意：group by 子句中的列不必包含在SELECT 列表中

4         求出某个部门中相同职位的员工人数   group by 后可以跟多个分组的字段

SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

    DEPTNO JOB         COUNT(*)

---------- --------- ----------

        10 CLERK              1

        10 MANAGER          1

        10 PRESIDENT          1

        20 ANALYST            2

        20 CLERK               2

        20 MANAGER            1

        30 CLERK               1

        30 MANAGER            1

        30 SALESMAN            4

9         rows selected.

5  非法使用组函数

(1) 所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。

举例：

SQL> select deptno,count(job) from emp;

select deptno,count(job) from emp

       *

ERROR at line 1:

ORA-00937: not a single-group group function

正确写法如下：

SQL> select deptno,count(job) from emp group by deptno;

    DEPTNO COUNT(JOB)

---------- ----------

        30          6

        20          5

        10          3

(2)  不能在 WHERE 子句中使用组函数（注意）。

SQL> select deptno from emp where count(job)>0 group by deptno;

备注ERROR at line 1: ORA-00933: SQL command not properly ended

此处不允许使用分组函数

(3)  Having 子句

   HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

备注：having子句通常与group by子句结合使用

语法：

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

查询部门员工人数大于3的部门编号

SQL> select deptno,count(*) from emp group by deptno having count(*)>3 order by deptno;

DEPTNO     COUNT(*)

----------      ----------

        20          5

        30          6

注意：

1 group by后不可以接别名

SQL> select deptno d,sum(sal) from emp group by deptno;

         D   SUM(SAL)

---------- ----------

        30       9400

        20      10875

        10       8750

SQL> select deptno d,sum(sal) from emp group by d;

select deptno d,sum(sal) from emp group by d

                                           *

ERROR at line 1:

ORA-00904: "D": invalid identifier

2  group by后不能接数字

SQL> select job,sum(sal) from emp group by 1;

select job,sum(sal) from emp group by 1

       *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

SQL> select job,sum(sal) from emp group by job;

JOB         SUM(SAL)

--------- ----------

CLERK           4150

SALESMAN        5600

PRESIDENT       5000

MANAGER         8275

ANALYST         6000

3  group by后可以接select后没有的列

SQL> select sum(sal) from emp group by deptno;

  SUM(SAL)

----------

      9400

     10875

      8750

4  select 后出现的列，在group by后必须全部出现

SQL> select job,deptno,sum(sal) from emp group by job,deptno;

JOB           DEPTNO   SUM(SAL)

--------- ---------- ----------

MANAGER           20       2975

PRESIDENT         10       5000

CLERK             10       1300

SALESMAN          30       5600

ANALYST           20       6000

MANAGER           30       2850

MANAGER           10       2450

CLERK             30        950

CLERK             20       1900

9 rows selected.

SQL> select job,deptno,sum(sal) from emp group by job;

select job,deptno,sum(sal) from emp group by job

           *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

SQL> select job,deptno,sum(sal) from emp group by deptno;

select job,deptno,sum(sal) from emp group by deptno

       *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

4         group by后不能使用where，因为where是在分组之前起作用的，分组后的数据在进行过滤需要使用having

SQL> select deptno,avg(sal) from emp group by deptno where deptno>10;

select deptno,avg(sal) from emp group by deptno where deptno>10

                                                *

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL> select deptno,avg(sal) from emp group by deptno having deptno>10;

    DEPTNO   AVG(SAL)

---------- ----------

        30 1566.66667

20          2175

5  group by之前可以使用where过滤数据

A:

SQL> select deptno,sum(sal) from emp where deptno!=30 group by deptno having sum(sal)>5000;

    DEPTNO   SUM(SAL)

---------- ----------

        20      10875

        10       8750

B：

SQL> select deptno,sum(sal) from emp  group by deptno having sum(sal)>5000 and deptno!=30;

    DEPTNO   SUM(SAL)

---------- ----------

        20      10875

        10       8750

性能：能在where能过滤数据不要在having里过滤，A和B都能达到同样的目的，但是A性能相对好一些，因为A现将deptno=30的数据筛选出来，然后在将筛选的数据放入到临时表空间内进行分组；而B将全部的数据都读到临时表空间内，然后在临时表空间进行筛选数据，这样一来B就需要更大的临时表空间进行分组筛选，索引性能较差；

参考与--- http://www.2cto.com/database/201305/214634.html