04.18 oracle聚合函數介紹

聚合函數也叫組函數，有的地方也叫集合函數，它的數據源一般來自多組數據，但返回的時候一般是一組數據，聚合函數對一組行中的某個列執行計算並返回單一的值。聚合函數經常與 SELECT 語句的 GROUP BY 子句一同使用,所以有的時候也把其稱之為分組函數。

1: AVG(DISTINCT|ALL)

ALL表示對所有的值求平均值,DISTINCT只對不同的值求平均值。

例如：select avg(sal) from scott.emp;

2: MAX(DISTINCT|ALL)

求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次。

例如：select max(sal) from emp;

3: MIN(DISTINCT|ALL)

求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次。

例如：select min(sal) from emp;

4: STDDEV(distinct|all)

求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差

例如： select stddev(sal) from emp;

5: VARIANCE(DISTINCT|ALL)

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

例如： select variance(sal) from emp;

6: SUM(DISTINCT|ALL)

求和 ALL表示對所有值求和，DISTINCT表示只對不同值求和(相同值只取一次)

例如： select sum(sal) from emp;

7：COUNT(DISTINCT|ALL)

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

例如： select count(sal) from emp;

8： MEDIAN

求中位數

例如：select median(sal) from emp;

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;

2 每個部門員工的平均工資

SQL> select deptno,avg(sal) from emp group by deptno;

3 每個部門員工的工資+獎金

SQL> select deptno,avg(sal+nvl(comm,0)) from emp group by deptno;

4 求出某個部門中相同職位的員工人數 group by 後可以跟多個分組的字段

SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

5 非法使用組函數

(1) 所用包含於SELECT 列表中，而未包含於組函數中的列都必須包含於 GROUP BY 子句中。

(2) 不能在 WHERE 子句中使用組函數（注意）。

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

(3) Having 子句

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

1 group by後不可以接別名

例如： select deptno d,sum(sal) from emp group by deptno; 正確的。

select deptno d,sum(sal) from emp group by d;錯誤的。

2 group by後不能接數字

SQL> select job,sum(sal) from emp group by 1;錯誤的。

3 group by後可以接select後沒有的列

SQL> select sum(sal) from emp group by deptno;錯誤的。

4 select 後出現的列，在group by後必須全部出現

SQL> select job,deptno,sum(sal) from emp group by job,deptno;正確的。

5

SQL> select deptno,avg(sal) from emp group by deptno where deptno>10;錯誤的。

SQL> select deptno,avg(sal) from emp group by deptno having deptno>10;正確的。

6 group by之前可以使用where過濾數據

A:

SQL> select deptno,sum(sal) from emp where deptno!=30 group by deptno having sum(sal)>5000;

B：

SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>5000 and deptno!=30;

性能：能在where能過濾數據不要在having裡過濾，A和B都能達到同樣的目的，但是A性能相對好一些，因為A現將deptno=30的數據篩選出來，然後在將篩選的數據放入到臨時表空間內進行分組；而B將全部的數據都讀到臨時表空間內，然後在臨時表空間進行篩選數據，這樣一來B就需要更大的臨時表空間進行分組篩選，索引性能較差；