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 hh24: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就需要更大的臨時表空間進行分組篩選，索引效能較差；

參考與---

Oracle 聚合函式詳解

相關文章