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

 


本文标题:Oracle聚合函数详解
文章转载:http://scyanting.com/article/gjsigg.html