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