學習Oracle分析函數(Analytic Functions)

Oracle提供了一些功能很強大的分析函數，使用這些函數可以完成可能需要存儲過程來實作的需求。

分析函數計算基于一組資料行的聚合值，它們不同于聚合函數的是，它們為每一組傳回多行結果。分析函數是除ORDER BY子句之外，在查詢語句中最後執行的。所有的join和所有的WHERE ,GROUP BY 和HAVING子句都在分析函數之前執行。是以分析函數隻能出現在select或ORDER BY子句中。

下圖為11.2版本官方文檔中給出的文法示意圖：

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

下面為所有的分析函數，帶*号的函數允許使用windowing_clause：

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions052.htm#i1064409" target="_blank">DENSE_RANK</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions065.htm#i1000901" target="_blank">FIRST</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#CJABDFBD" target="_blank">LISTAGG</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions099.htm#i1279886" target="_blank">MEDIAN</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions115.htm#i85619" target="_blank">NTILE</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions126.htm#i1043951" target="_blank">PERCENT_RANK</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions127.htm#i1000909" target="_blank">PERCENTILE_CONT</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions128.htm#i1000913" target="_blank">PERCENTILE_DISC</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions142.htm#i85800" target="_blank">RATIO_TO_REPORT</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions156.htm#i86310" target="_blank">ROW_NUMBER</a>

以AVG為例介紹分析函數的使用：

AVG也是一個聚合函數：

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

作為分析函數的例子：

eg1：單獨使用

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

<code> </code><code>DEPTNO ENAME HIREDATE SAL </code><code>AVG</code>

<code> </code><code>20 SMITH 1980-12-17 00:00:00 800 2073.21429</code>

<code> </code><code>30 ALLEN 1981-02-20 00:00:00 1600 2073.21429</code>

<code> </code><code>20 JONES 1981-04-02 00:00:00 2975 2073.21429</code>

<code> </code><code>30 MARTIN 1981-09-28 00:00:00 1250 2073.21429</code>

<code> </code><code>30 BLAKE 1981-05-01 00:00:00 2850 2073.21429</code>

<code> </code><code>10 CLARK 1981-06-09 00:00:00 2450 2073.21429</code>

<code> </code><code>20 SCOTT 1987-04-19 00:00:00 3000 2073.21429</code>

<code> </code><code>30 TURNER 1981-09-08 00:00:00 1500 2073.21429</code>

<code> </code><code>20 ADAMS 1987-05-23 00:00:00 1100 2073.21429</code>

<code> </code><code>30 JAMES 1981-12-03 00:00:00 950 2073.21429</code>

<code> </code><code>10 MILLER 1982-01-23 00:00:00 1300 2073.21429</code>

從輸出可以看出函數計算出了整體的平均值，并輸出到每一行

eg2：使用query_partition_clause

<code> </code><code>10 CLARK 1981-06-09 00:00:00 2450 2916.66667</code>

<code> </code><code>10 MILLER 1982-01-23 00:00:00 1300 2916.66667</code>

<code> </code><code>20 JONES 1981-04-02 00:00:00 2975 2175</code>

<code> </code><code>20 ADAMS 1987-05-23 00:00:00 1100 2175</code>

<code> </code><code>20 SMITH 1980-12-17 00:00:00 800 2175</code>

<code> </code><code>20 SCOTT 1987-04-19 00:00:00 3000 2175</code>

<code> </code><code>30 TURNER 1981-09-08 00:00:00 1500 1566.66667</code>

<code> </code><code>30 ALLEN 1981-02-20 00:00:00 1600 1566.66667</code>

<code> </code><code>30 JAMES 1981-12-03 00:00:00 950 1566.66667</code>

<code> </code><code>30 BLAKE 1981-05-01 00:00:00 2850 1566.66667</code>

<code> </code><code>30 MARTIN 1981-09-28 00:00:00 1250 1566.66667</code>

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

<code> </code><code>DEPTNO </code><code>AVG</code><code>(SAL)</code>

從輸出可以看出，AVG計算出了每個部門的平均值，并輸出到對應的行。

eg3：使用order_by_clause

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

<code> </code><code>10 MILLER 1982-01-23 00:00:00 1300 1300</code>

<code> </code><code>10 CLARK 1981-06-09 00:00:00 2450 1875</code>

<code> </code><code>20 SMITH 1980-12-17 00:00:00 800 800</code>

<code> </code><code>20 ADAMS 1987-05-23 00:00:00 1100 950</code>

<code> </code><code>20 JONES 1981-04-02 00:00:00 2975 1625</code>

<code> </code><code>30 JAMES 1981-12-03 00:00:00 950 950</code>

<code> </code><code>30 MARTIN 1981-09-28 00:00:00 1250 1150</code>

<code> </code><code>30 TURNER 1981-09-08 00:00:00 1500 1237.5</code>

<code> </code><code>30 ALLEN 1981-02-20 00:00:00 1600 1310</code>

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

eg4：使用windowing_clause

<code>scott@TEST></code><code>select</code> <code>deptno,ename,hiredate,sal,</code><code>avg</code><code>(sal) over(partition </code><code>by</code> <code>deptno </code><code>order</code> <code>by</code> <code>sal </code><code>rows</code> <code>BETWEEN</code> <code>1 PRECEDING </code><code>AND</code> <code>1 FOLLOWING) </code><code>avg</code> <code>from</code> <code>emp;</code>

<code> </code><code>10 MILLER 1982-01-23 00:00:00 1300 1875</code>

<code> </code><code>20 SMITH 1980-12-17 00:00:00 800 950</code>

<code> </code><code>20 ADAMS 1987-05-23 00:00:00 1100 1625</code>

<code> </code><code>20 JONES 1981-04-02 00:00:00 2975 2358.33333</code>

<code> </code><code>20 SCOTT 1987-04-19 00:00:00 3000 2991.66667</code>

<code> </code><code>30 JAMES 1981-12-03 00:00:00 950 1100</code>

<code> </code><code>30 TURNER 1981-09-08 00:00:00 1500 1450</code>

<code> </code><code>30 ALLEN 1981-02-20 00:00:00 1600 1983.33333</code>

<code> </code><code>30 BLAKE 1981-05-01 00:00:00 2850 2225</code>

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

分析函數太多，這裡就不一一介紹功能了，有興趣的同學可以點開上面的連接配接，去檢視對應的功能。

本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1918614，如需轉載請自行聯系原作者

學習Oracle分析函數(Analytic Functions)

繼續閱讀

LoadRunner 的多種Vuser類型

華為筆試軟體

性能測試（并發負載壓力）測試分析－簡要篇

項目管理那些事兒

OS --written test1

OS-written test2

壓縮編碼M-JPEG、MPEG4、H.264

賦予使用者dba權限--解釋以上：

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

為什麼要選擇UniDAC

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List