Oracle 聚合函數(shù)詳解

Oracle 聚合函數(shù)詳解

一 聚合函數(shù)的定義

聚合函數(shù)也叫組函數(shù)，有的地方也叫集合函數(shù)，它的數(shù)據(jù)源一般來自多組數(shù)據(jù)，但返回的時(shí)候一般是一組數(shù)據(jù)，聚合函數(shù)對一組行中的某個列執(zhí)行計(jì)算并返回單一的值。聚合函數(shù)經(jīng)常與 SELECT 語句的 GROUP BY 子句一同使用,所以有的時(shí)候也把其稱之為分組函數(shù)。有一點(diǎn)需要注意的是，除了count和grouping之后，其它的統(tǒng)計(jì)運(yùn)算均會忽略值為null的列。

二 聚合函數(shù)的分類

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

注意事項(xiàng)：

SQL> select avg(comm) from emp;

 AVG(COMM)

----------

       550              -------------550=sum(comm)/4其中4是comm不為null的員工數(shù)

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)

求標(biāo)準(zhǔn)差,ALL表示對所有的值求標(biāo)準(zhǔn)差,DISTINCT表示只對不同的值求標(biāo)準(zhǔn)差

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)

求協(xié)方差  ALL表示對所有的值求協(xié)方差,DISTINCT表示只對不同的值求協(xié)方差

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)

求記錄、數(shù)據(jù)個數(shù)。 ALL對所有記錄，數(shù)組做統(tǒng)計(jì)， DISTINCT只對不同值統(tǒng)計(jì)(相同值只取一次)

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

求中位數(shù)

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 選項(xiàng)在此函數(shù)中禁用。

三  Group by子句

Group By語句從英文的字面意義上理解就是“根據(jù)(by)一定的規(guī)則進(jìn)行分組(Group)”。它的作用是通過一定的規(guī)則將一個數(shù)據(jù)集劃分成若干個小的區(qū)域，然后針對若干個小區(qū)域進(jìn)行數(shù)據(jù)處理。 如果在查詢的過程中需要按某一列的值進(jìn)行分組,以統(tǒng)計(jì)該組內(nèi)數(shù)據(jù)的信息時(shí),就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

注意：group by子句一定要與分組函數(shù)結(jié)合使用,否則沒有意義

1  求出每個部門的人數(shù)

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         求出某個部門中相同職位的員工人數(shù)   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  非法使用組函數(shù)

(1) 所用包含于SELECT 列表中，而未包含于組函數(shù)中的列都必須包含于 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 子句中使用組函數(shù)（注意）。

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

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

此處不允許使用分組函數(shù)

(3)  Having 子句

   HAVING 子句對 GROUP BY 子句設(shè)置條件的方式與 WHERE 子句和 SELECT 語句交互的方式類似。WHERE 子句搜索條件在進(jìn)行分組操作之前應(yīng)用；而 HAVING 搜索條件在進(jìn)行分組操作之后應(yīng)用。HAVING 語法與 WHERE 語法類似，但 HAVING 可以包含聚合函數(shù)。HAVING 子句可以引用選擇列表中出現(xiàn)的任意項(xiàng)。

備注：having子句通常與group by子句結(jié)合使用

語法：

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

查詢部門員工人數(shù)大于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后不能接數(shù)字

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 后出現(xiàn)的列，在group by后必須全部出現(xiàn)

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，因?yàn)?span>where是在分組之前起作用的，分組后的數(shù)據(jù)在進(jìn)行過濾需要使用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過濾數(shù)據(jù)

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能過濾數(shù)據(jù)不要在having里過濾，A和B都能達(dá)到同樣的目的，但是A性能相對好一些，因?yàn)?span>A現(xiàn)將deptno=30的數(shù)據(jù)篩選出來，然后在將篩選的數(shù)據(jù)放入到臨時(shí)表空間內(nèi)進(jìn)行分組；而B將全部的數(shù)據(jù)都讀到臨時(shí)表空間內(nèi)，然后在臨時(shí)表空間進(jìn)行篩選數(shù)據(jù)，這樣一來B就需要更大的臨時(shí)表空間進(jìn)行分組篩選，索引性能較差；

參考與--- http://www.2cto.com/database/201305/214634.html