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