標籤:oracle 分析函數 analytic functions
Oracle提供了一些功能很強大的分析函數,使用這些函數可以完成可能需要預存程序來實現的需求。
分析Function Compute基於一組資料行的彙總值,它們不同於彙總函式的是,它們為每一組返回多行結果。分析函數是除ORDER BY子句之外,在查詢語句中最後執行的。所有的join和所有的WHERE ,GROUP BY 和HAVING子句都在分析函數之前執行。所以分析函數只能出現在select或ORDER BY子句中。
為11.2版本官方文檔中給出的文法:
650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/92/23/wKiom1j8YE-zfXLsAAGOKuDzgbs859.png" title="1.png" alt="wKiom1j8YE-zfXLsAAGOKuDzgbs859.png" />下面簡單介紹一下各個部分:
analytic_function
指定分析函數的名字,後面列出了所有的分析函數
arguments
分析函數可以有0到3個參數。參數可以是任何數實值型別或可以隱式轉換為數實值型別的其他非數實值型別。
analytic_clause
用OVER analytic_clause表明函數操作的是一個查詢結果集。如果想過濾基於分析函數的查詢結果,需要使用嵌套子查詢。
query_partition_clause
用PARTITION BY子句來把查詢結果集基於一個或多個value_expr分組。如果省略,分析函數把所有行當作一組。
order_by_clause
用order_by_claus指定在一組中資料如何排序。
ASC(default)|DESC
NULLS FIRST(default in DESC)|NULLS LAST(default in ASC)
windowing_clause
部分分析函數允許使用windowing_clause子句。
只有當指定了order_by_clause後才能指定這個子句。
ROWS指定使用物理行的window
RANGE指定使用邏輯位移的window
詳細資料請參考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i81407
下面為所有的分析函數,帶*號的函數允許使用windowing_clause:
AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
以AVG為例介紹分析函數的使用:
AVG也是一個彙總函式:
[email protected]>select avg(sal) from emp; AVG(SAL)----------2073.21429
作為分析函數的例子:
eg1:單獨使用
[email protected]>select deptno,ename,hiredate,sal,avg(sal) over() avg from emp; DEPTNO ENAME HIREDATE SALAVG---------- ------------------------------ ------------------- ---------- ----------20 SMITH 1980-12-17 00:00:00 800 2073.2142930 ALLEN 1981-02-20 00:00:00 1600 2073.2142930 WARD 1981-02-22 00:00:00 1250 2073.2142920 JONES 1981-04-02 00:00:00 2975 2073.2142930 MARTIN 1981-09-28 00:00:00 1250 2073.2142930 BLAKE 1981-05-01 00:00:00 2850 2073.2142910 CLARK 1981-06-09 00:00:00 2450 2073.2142920 SCOTT 1987-04-19 00:00:00 3000 2073.2142910 KING 1981-11-17 00:00:00 5000 2073.2142930 TURNER 1981-09-08 00:00:00 1500 2073.2142920 ADAMS 1987-05-23 00:00:00 1100 2073.2142930 JAMES 1981-12-03 00:00:00 950 2073.2142920 FORD 1981-12-03 00:00:00 3000 2073.2142910 MILLER 1982-01-23 00:00:00 1300 2073.21429
從輸出可以看出Function Compute出了整體的平均值,並輸出到每一行
eg2:使用query_partition_clause
[email protected]>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno) avg from emp; DEPTNO ENAME HIREDATE SALAVG---------- ------------------------------ ------------------- ---------- ----------10 CLARK 1981-06-09 00:00:00 2450 2916.6666710 KING 1981-11-17 00:00:00 5000 2916.6666710 MILLER 1982-01-23 00:00:00 1300 2916.6666720 JONES 1981-04-02 00:00:00 2975 217520 FORD 1981-12-03 00:00:00 3000 217520 ADAMS 1987-05-23 00:00:00 1100 217520 SMITH 1980-12-17 00:00:00 800 217520 SCOTT 1987-04-19 00:00:00 3000 217530 WARD 1981-02-22 00:00:00 1250 1566.6666730 TURNER 1981-09-08 00:00:00 1500 1566.6666730 ALLEN 1981-02-20 00:00:00 1600 1566.6666730 JAMES 1981-12-03 00:00:00 950 1566.6666730 BLAKE 1981-05-01 00:00:00 2850 1566.6666730 MARTIN 1981-09-28 00:00:00 1250 1566.66667[email protected]>select deptno,avg(sal) from emp group by deptno; DEPTNO AVG(SAL)---------- ----------30 1566.6666720 217510 2916.66667
從輸出可以看出,AVG計算出了每個部門的平均值,並輸出到對應的行。
eg3:使用order_by_clause
[email protected]>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal) avg from emp; DEPTNO ENAME HIREDATE SALAVG---------- ------------------------------ ------------------- ---------- ----------10 MILLER 1982-01-23 00:00:00 1300 130010 CLARK 1981-06-09 00:00:00 2450 187510 KING 1981-11-17 00:00:00 5000 2916.6666720 SMITH 1980-12-17 00:00:00 80080020 ADAMS 1987-05-23 00:00:00 110095020 JONES 1981-04-02 00:00:00 2975 162520 SCOTT 1987-04-19 00:00:00 3000 217520 FORD 1981-12-03 00:00:00 3000 217530 JAMES 1981-12-03 00:00:00 95095030 MARTIN 1981-09-28 00:00:00 1250 115030 WARD 1981-02-22 00:00:00 1250 115030 TURNER 1981-09-08 00:00:00 1500 1237.530 ALLEN 1981-02-20 00:00:00 1600 131030 BLAKE 1981-05-01 00:00:00 2850 1566.66667
從輸出結果可以看出,每個部門的行都按sal做了升序排序。
eg4:使用windowing_clause
[email protected]>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal rows BETWEEN 1 PRECEDING AND 1 FOLLOWING) avg from emp; DEPTNO ENAME HIREDATE SALAVG---------- ------------------------------ ------------------- ---------- ----------10 MILLER 1982-01-23 00:00:00 1300 187510 CLARK 1981-06-09 00:00:00 2450 2916.6666710 KING 1981-11-17 00:00:00 5000 372520 SMITH 1980-12-17 00:00:00 80095020 ADAMS 1987-05-23 00:00:00 1100 162520 JONES 1981-04-02 00:00:00 2975 2358.3333320 SCOTT 1987-04-19 00:00:00 3000 2991.6666720 FORD 1981-12-03 00:00:00 3000 300030 JAMES 1981-12-03 00:00:00 950 110030 MARTIN 1981-09-28 00:00:00 1250 115030 WARD 1981-02-22 00:00:00 1250 1333.3333330 TURNER 1981-09-08 00:00:00 1500 145030 ALLEN 1981-02-20 00:00:00 1600 1983.3333330 BLAKE 1981-05-01 00:00:00 2850 2225
從輸出的結果可以看出,分析函數對每一組中的每一行的輸出結果是把它自己與它的上一行和下一行這三行求平均值。
分析函數太多,這裡就不一一介紹功能了,有興趣的同學可以點開上面的串連,去查看對應的功能。
本文出自 “DBA Fighting!” 部落格,請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1918614
學習Oracle分析函數(Analytic Functions)