學(xué)習(xí)Oracle分析函數(shù)(AnalyticFunctions)

Oracle提供了一些功能很強(qiáng)大的分析函數(shù)，使用這些函數(shù)可以完成可能需要存儲(chǔ)過程來實(shí)現(xiàn)的需求。

創(chuàng)新互聯(lián)公司2013年至今，先為平和等服務(wù)建站，平和等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為平和企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

分析函數(shù)計(jì)算基于一組數(shù)據(jù)行的聚合值，它們不同于聚合函數(shù)的是，它們?yōu)槊恳唤M返回多行結(jié)果。分析函數(shù)是除ORDER BY子句之外，在查詢語(yǔ)句中最后執(zhí)行的。所有的join和所有的WHERE ,GROUP BY 和HAVING子句都在分析函數(shù)之前執(zhí)行。所以分析函數(shù)只能出現(xiàn)在select或ORDER BY子句中。

下圖為11.2版本官方文檔中給出的語(yǔ)法示意圖：

學(xué)習(xí)Oracle分析函數(shù)(Analytic Functions) 下面簡(jiǎn)單介紹一下各個(gè)部分：

analytic_function

指定分析函數(shù)的名字，后面列出了所有的分析函數(shù)

arguments

分析函數(shù)可以有0到3個(gè)參數(shù)。參數(shù)可以是任何數(shù)值類型或可以隱式轉(zhuǎn)換為數(shù)值類型的其他非數(shù)值類型。

analytic_clause

用OVER analytic_clause表明函數(shù)操作的是一個(gè)查詢結(jié)果集。如果想過濾基于分析函數(shù)的查詢結(jié)果，需要使用嵌套子查詢。

query_partition_clause

用PARTITION BY子句來把查詢結(jié)果集基于一個(gè)或多個(gè)value_expr分組。如果省略，分析函數(shù)把所有行當(dāng)作一組。

order_by_clause

用order_by_claus指定在一組中數(shù)據(jù)如何排序。

ASC(default)|DESC

NULLS FIRST(default in DESC)|NULLS LAST(default in ASC)

windowing_clause

部分分析函數(shù)允許使用windowing_clause子句。

只有當(dāng)指定了order_by_clause后才能指定這個(gè)子句。

ROWS指定使用物理行的window

RANGE指定使用邏輯偏移的window

詳細(xì)信息請(qǐng)參考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i81407

下面為所有的分析函數(shù)，帶*號(hào)的函數(shù)允許使用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為例介紹分析函數(shù)的使用：

AVG也是一個(gè)聚合函數(shù)：

scott@TEST>select avg(sal) from emp;

  AVG(SAL)
----------
2073.21429

作為分析函數(shù)的例子：

eg1：?jiǎn)为?dú)使用

scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over() avg from emp;

    DEPTNO ENAME			  HIREDATE		     SAL	AVG
---------- ------------------------------ ------------------- ---------- ----------
	20 SMITH			  1980-12-17 00:00:00	     800 2073.21429
	30 ALLEN			  1981-02-20 00:00:00	    1600 2073.21429
	30 WARD 			  1981-02-22 00:00:00	    1250 2073.21429
	20 JONES			  1981-04-02 00:00:00	    2975 2073.21429
	30 MARTIN			  1981-09-28 00:00:00	    1250 2073.21429
	30 BLAKE			  1981-05-01 00:00:00	    2850 2073.21429
	10 CLARK			  1981-06-09 00:00:00	    2450 2073.21429
	20 SCOTT			  1987-04-19 00:00:00	    3000 2073.21429
	10 KING 			  1981-11-17 00:00:00	    5000 2073.21429
	30 TURNER			  1981-09-08 00:00:00	    1500 2073.21429
	20 ADAMS			  1987-05-23 00:00:00	    1100 2073.21429
	30 JAMES			  1981-12-03 00:00:00	     950 2073.21429
	20 FORD 			  1981-12-03 00:00:00	    3000 2073.21429
	10 MILLER			  1982-01-23 00:00:00	    1300 2073.21429

從輸出可以看出函數(shù)計(jì)算出了整體的平均值，并輸出到每一行

eg2：使用query_partition_clause

scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno) avg from emp;

    DEPTNO ENAME			  HIREDATE		     SAL	AVG
---------- ------------------------------ ------------------- ---------- ----------
	10 CLARK			  1981-06-09 00:00:00	    2450 2916.66667
	10 KING 			  1981-11-17 00:00:00	    5000 2916.66667
	10 MILLER			  1982-01-23 00:00:00	    1300 2916.66667
	20 JONES			  1981-04-02 00:00:00	    2975       2175
	20 FORD 			  1981-12-03 00:00:00	    3000       2175
	20 ADAMS			  1987-05-23 00:00:00	    1100       2175
	20 SMITH			  1980-12-17 00:00:00	     800       2175
	20 SCOTT			  1987-04-19 00:00:00	    3000       2175
	30 WARD 			  1981-02-22 00:00:00	    1250 1566.66667
	30 TURNER			  1981-09-08 00:00:00	    1500 1566.66667
	30 ALLEN			  1981-02-20 00:00:00	    1600 1566.66667
	30 JAMES			  1981-12-03 00:00:00	     950 1566.66667
	30 BLAKE			  1981-05-01 00:00:00	    2850 1566.66667
	30 MARTIN			  1981-09-28 00:00:00	    1250 1566.66667

scott@TEST>select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
	30 1566.66667
	20	 2175
	10 2916.66667

從輸出可以看出，AVG計(jì)算出了每個(gè)部門的平均值，并輸出到對(duì)應(yīng)的行。

eg3：使用order_by_clause

scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal) avg from emp;

    DEPTNO ENAME			  HIREDATE		     SAL	AVG
---------- ------------------------------ ------------------- ---------- ----------
	10 MILLER			  1982-01-23 00:00:00	    1300       1300
	10 CLARK			  1981-06-09 00:00:00	    2450       1875
	10 KING 			  1981-11-17 00:00:00	    5000 2916.66667
	20 SMITH			  1980-12-17 00:00:00	     800	800
	20 ADAMS			  1987-05-23 00:00:00	    1100	950
	20 JONES			  1981-04-02 00:00:00	    2975       1625
	20 SCOTT			  1987-04-19 00:00:00	    3000       2175
	20 FORD 			  1981-12-03 00:00:00	    3000       2175
	30 JAMES			  1981-12-03 00:00:00	     950	950
	30 MARTIN			  1981-09-28 00:00:00	    1250       1150
	30 WARD 			  1981-02-22 00:00:00	    1250       1150
	30 TURNER			  1981-09-08 00:00:00	    1500     1237.5
	30 ALLEN			  1981-02-20 00:00:00	    1600       1310
	30 BLAKE			  1981-05-01 00:00:00	    2850 1566.66667

從輸出結(jié)果可以看出，每個(gè)部門的行都按sal做了升序排序。

eg4：使用windowing_clause

scott@TEST>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		     SAL	AVG
---------- ------------------------------ ------------------- ---------- ----------
	10 MILLER			  1982-01-23 00:00:00	    1300       1875
	10 CLARK			  1981-06-09 00:00:00	    2450 2916.66667
	10 KING 			  1981-11-17 00:00:00	    5000       3725
	20 SMITH			  1980-12-17 00:00:00	     800	950
	20 ADAMS			  1987-05-23 00:00:00	    1100       1625
	20 JONES			  1981-04-02 00:00:00	    2975 2358.33333
	20 SCOTT			  1987-04-19 00:00:00	    3000 2991.66667
	20 FORD 			  1981-12-03 00:00:00	    3000       3000
	30 JAMES			  1981-12-03 00:00:00	     950       1100
	30 MARTIN			  1981-09-28 00:00:00	    1250       1150
	30 WARD 			  1981-02-22 00:00:00	    1250 1333.33333
	30 TURNER			  1981-09-08 00:00:00	    1500       1450
	30 ALLEN			  1981-02-20 00:00:00	    1600 1983.33333
	30 BLAKE			  1981-05-01 00:00:00	    2850       2225

從輸出的結(jié)果可以看出，分析函數(shù)對(duì)每一組中的每一行的輸出結(jié)果是把它自己與它的上一行和下一行這三行求平均值。

分析函數(shù)太多，這里就不一一介紹功能了，有興趣的同學(xué)可以點(diǎn)開上面的連接，去查看對(duì)應(yīng)的功能。

分享標(biāo)題：學(xué)習(xí)Oracle分析函數(shù)(AnalyticFunctions)
新聞來源：http://muchs.cn/article10/ghgggo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營(yíng)銷、虛擬主機(jī)、小程序開發(fā)、外貿(mào)建站、網(wǎng)頁(yè)設(shè)計(jì)公司、建站公司

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容