Oracle 聚合函數(shù)詳解
專注于為中小企業(yè)提供成都做網(wǎng)站、網(wǎng)站制作、成都外貿(mào)網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)德令哈免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上千多家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
一 聚合函數(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)閣here是在分組之前起作用的，分組后的數(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)锳現(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
重慶分公司
重慶分公司
