Oracle 分析函數(shù)（10G）

一、Oracle分析函數(shù)簡介

1、分析函數(shù)，最早是從ORACLE8.1.6開始出現(xiàn)的，它的設(shè)計目的是為了解決諸如“累計計算”，“找出分組內(nèi)百分比”，“前-N條查詢”，“移動平均數(shù)計算”"等問題。其實大部分的問題都可以用PL/SQL解決，但是它的性能并不能達到你所期望的效果。分析函數(shù)是SQL言語的一種擴充，它并不是僅僅試代碼變得更簡單而已，它的速度比純粹的SQL或者PL/SQL更快?，F(xiàn)在這些擴展已經(jīng)被納入了美國國家標準化組織SQL委員會的SQL規(guī)范說明書中。

2、在日常的生產(chǎn)環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即OnlineTransaction Process），這些系統(tǒng)的特點是具備實時要求，或者至少說對響應(yīng)的時間多長有一定的要求；其次這些系統(tǒng)的業(yè)務(wù)邏輯一般比較復(fù)雜，可能需要經(jīng)過多次的運算。比如我們經(jīng)常接觸到的電子商城。

二、Oracle分析函數(shù)原理

1、分析函數(shù)通過將行分組后，再計算這些分組的值。它們與聚集函數(shù)不同之處在于能夠?qū)γ恳粋€分組返回多行值。分析函數(shù)根據(jù)analytic claues(分析子句）將行分組，一個分組稱為：一個窗口（可通過Windowsing Clause子句進行控制），并通過分析語句定義，對于每一行都對應(yīng)有一個在行上滑動的窗口。該窗口確定當前行的計算范圍。窗口大小可以用多個物理行（例如：rowid實際編號）進行度量，也可以使用邏輯區(qū)間進行度量，比如時間。

2、分析函數(shù)是查詢中除需要在最終處理的order by 子句之外最后執(zhí)行的操作。所有連接、WHERE、GROUP BY、HAVING子句都是分析函數(shù)處理之前完成的。因此，分析函數(shù)只出現(xiàn)在SELECT LIST或ORDER BY（按…排序）語句中，而不能出現(xiàn)在where或having子句中

3、分析函數(shù)通常用于計算：數(shù)據(jù)累積值、數(shù)據(jù)移動值、數(shù)據(jù)中間值，和輸出集合報表。


三、Oracle分析函數(shù)的語法

Analytic-Function（<Argument>，<Argument>，…） 
over（
       <Query-Partition-Clause>
       <Order-by-Clause>
       <Windowing-Clause>
）
例如：sum（sal） over（partition by deptno order by ename）new_alias
1）sum：就是函數(shù)名
2）（sal）： 是分析函數(shù)的參數(shù)，每個函數(shù)有0~3個參數(shù)，參數(shù)可以是表達式，例如：sum(sal+comm）
3）over：是一個關(guān)鍵字，用于標識分析函數(shù)，否則查詢分析器不能區(qū)別sum(）聚集函數(shù)和sum(）分析函數(shù)
4）partition by deptno：是可選的分區(qū)子句，如果不存在任何分區(qū)子句，則全部的結(jié)果集可看作一個單一的大區(qū)
5）order by ename：是可選的order by 子句，有些函數(shù)需要它，有些則不需要。依靠已排序數(shù)據(jù)的那些函數(shù)，例如：用于訪問結(jié)果集中前一行和后一行的LAG和LEAD，它們就必須使用；其它函數(shù)，例如：AVG，則不需要用到order by 子句。在使用了任何排序的開窗函數(shù)時，該子句是強制性的，它指定了在計算分析函數(shù)時一組內(nèi)的數(shù)據(jù)是如何排序的.（即：如果要使用Windowing-Clause子句，那么一定要先使用Order by 子句）

1、Analytic-Function


ORACLE提供了28個分析函數(shù)（包括如下：
AVG *，CORR *，COVAR_POP *，COVAR_SAMP *，COUNT*，CUME_DIST，DENSE_RANK，F(xiàn)IRST，F(xiàn)IRST_VALUE *，LAG，LAST，LAST_VALUE *，LEAD，MAX *，
MIN *，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），按功能分5類

1）分析函數(shù)分類
（1）等級(ranking）函數(shù)：用于尋找前N種查詢，如：RANK、DENSE_RANK等
（2）開窗(windowing）函數(shù)：用于計算不同的累計，如：SUM，COUNT，AVG，MIN，MAX等，作用于數(shù)據(jù)的一個窗口上
例如：如下函數(shù)
sum(t.sal） over (order by t.deptno，t.ename） running_total，
sum(t.sal） over (partition by t.deptno order by t.ename） department_total
（3）制表(reporting）函數(shù)：與開窗函數(shù)同名，作用于一個分區(qū)或一組上的所有列
例如：如下函數(shù)
sum(t.sal） over (） running_total2，
sum(t.sal） over (partition by t.deptno ） department_total2
說明：制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處：在于OVER語句上缺少一個ORDER BY子句
（4）LAG，LEAD函數(shù)：這類函數(shù)允許在結(jié)果集中向前或向后檢索值，為了避免數(shù)據(jù)的自連接，它們是非常用用的.
（5）VAR_POP，VAR_SAMP，STDEV_POPE及線性的衰減函數(shù)：計算任何未排序分區(qū)的統(tǒng)計值

2） 分析函數(shù)函數(shù)，及返回值
分析函數(shù)可取0-3個參數(shù)。參數(shù)可以是任何數(shù)字類型或是可以隱式轉(zhuǎn)換為數(shù)字類型的數(shù)據(jù)類型。Oracle根據(jù)最高數(shù)字優(yōu)先級別確定函數(shù)參數(shù)，并且隱式地將需要處理的參數(shù)轉(zhuǎn)換為數(shù)字類型。函數(shù)的返回類型也為數(shù)字類型，除非此函數(shù)另有說明。

2、Analytic_Clause

[ query_partition_clause ] [ order_by_clause [ windows_clause ] ]

1）Over Analytic clause用以指明函數(shù)操作的是一個查詢結(jié)果集。也就是說分析函數(shù)是在from，where，group by，和having子句之后才開始進行計算的。因此在選擇列或order by子句中可以使用分析函數(shù)。為了過濾分析函數(shù)計算的查詢結(jié)果，可以將它作為子查詢嵌套在外部查詢中，然后在外部查詢中過濾其查詢結(jié)果。

2）使用Analytic_Clause子名時，注意如下
（1）Analytic clause中不能包含其他任何分析函數(shù)。也就是說，分析函數(shù)不能嵌套。然而可以在一個子查詢中應(yīng)用分析函數(shù)，并且通過它計算另外的分析函數(shù)。
（2）用戶自定義分析函數(shù)和內(nèi)置函數(shù)分析函數(shù)，都可以使用OverAnalytic_Clause。

3、PARTITION子句

partition by { value_expr [，value_expr ]… | （value_expr [，value_expr ] …）}  
說明：按照表達式分區(qū)(就是分組），如果省略了分區(qū)子句，則全部的結(jié)果集被看作是一個單一的組
1）Partition by子句根據(jù)一個或多個valueexpr將查詢結(jié)果集分成若干組。若不使用該子句，那么函數(shù)將查詢結(jié)果集的所有行當作一個組。
2）在分析函數(shù)中使用query_partition_clause，應(yīng)該使用語法圖中上分支中的語法(不帶圓括號）。model查詢(位于model column clauses中）或被分隔的外部連接（位于outer_join_clause中）中使用該子句，應(yīng)該使用語法圖中下分支中的語法（帶有圓括號）。
3) 在同一個查詢中可以使用多個分析函數(shù)，它們可以有相同或不同的partition by鍵值
4) 若被查詢的對象具有并行特性，并且分析函數(shù)中包含query_partition_clause，那么函數(shù)的計算也是并行的。
5) value expr的有效值：包括常量，表列，非分析函數(shù)，函數(shù)表達式，或者前面這些元素的任意組合表達式。


4、ORDER BY子句

分析函數(shù)中ORDER BY的存在將添加一個默認的開窗子句（默認窗口為：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），這意味著計算中所使用的行的集合是當前分區(qū)中當前行和前面所有行，沒有ORDER BY時，默認的窗口是全部的分區(qū) 在Order by 子句后可以添加nulls last，如：order by comm desc nullslast   表示排序時忽略comm列為空的行. 

1）Order_by_clause用以指定分組中數(shù)據(jù)的排序形式。除了percentile_cont和percentile_disc之外（它們只能取唯一的鍵值）外的分析函數(shù)，分組中可以使用多個鍵值對值進行排序，每個鍵值在value expr中定義，并且被排序序列限定。

2）每個函數(shù)內(nèi)可以指定多個排序表達式。當使用函數(shù)給值排名時，尤其顯得意義非凡，因為第二個表達式能夠解決按照第一個表達式排序后仍然存在相同排名的問題。

3）只要使用order_by_clause后，仍存在值相同的行，則每一行都會返回相同的結(jié)果。

4）使用Ordery_by_clause子句的限制： 
（1）分析函數(shù)中的order_by_clause必須是一個表達式(expr)。Sibling關(guān)鍵字在此處是非法的(它僅僅與層次查詢有關(guān))。位置(position)和列別名(c_alias)也是非法的。除此之外，order_by_clause的用法與整個查詢或者子查詢中的相同。
（2）當分析函數(shù)使用range關(guān)鍵字限定窗口時，若使用的窗口是下列兩個窗口之一，那么可以在分析函數(shù)的order_by_clause中使用多個排序健值。
① range between UNBOUNDEDPRECEDING and CURRENT ROW  <=>  range UNBOUNDED PRECEDING
② range between CURRENT ROWand UNBOUNDED FOLLOWING  <=> range UNBOUNDED FOLLOWING
注意： 若窗口范圍由range關(guān)鍵字指定的分析函數(shù)中指定的不是這兩個窗口范圍（即：range unbounded preceding與range unboundedfollowing），那么order_by子句中僅能使用一個排序鍵值。
（3）若分析函數(shù)的窗口范圍由row關(guān)鍵字指定，order_by子句中排序鍵值的使用沒有這個限制。

5）asc | desc：指定排序順序(升序或降序)，asc是默認值。

6）nulls first | nulls last：指定返回行包含空值，該值應(yīng)該出現(xiàn)在排序序列的開始還是末尾。

7）升序排序的默認值為：nulls last，降序排序的默認值為：nulls first。

8）分析函數(shù)總是按order_by_clause對行排序。然而，分析函數(shù)中的order_by_clause只對各個分組進行排序，而不能保證查詢結(jié)果有序。要保證最后的查詢結(jié)果有序，可以使用查詢的order_by_clause。

5、WINDOWING子句


1）有些分析函數(shù)允許使用windowing clause。在上述的分析函數(shù)列表中，帶有星號(*)的函數(shù)都允許使用windowing_clause。

2）用于定義分析函數(shù)將在其上操作的行的集合，Windowing子句給出了一個定義變化或固定的數(shù)據(jù)窗口的方法，分析函數(shù)將對這些數(shù)據(jù)進行操作默認的窗口是一個固定的窗口，僅僅在一組的第一行開始，一直繼續(xù)到當前行（即：range unbounded preceding），要使用窗口，必須使用ORDER BY子句。

3）row | range：這些關(guān)鍵字為每一行定義一個窗口，該窗口用于計算函數(shù)結(jié)果（物理或者邏輯的行的集合）。然后對窗口中的每一行應(yīng)用分析函數(shù)。窗口在查詢結(jié)果集或者分組中從上至下移動。

4）根據(jù)2個標準可以建立窗口：數(shù)據(jù)值的范圍（邏輯偏移量--range）或與當前行的行偏移量（物理單位--rows）。

5）只有指定order_by_clause后才能指定windowing_clause。有些range子句定義的窗口范圍只能在order_by_clause中指定一個排序表達式。

6）一個帶邏輯偏移量的分析函數(shù)的返回值總是確定的。然而，除非排序表達式能產(chǎn)生唯一的排序，否則帶有物理偏移量的分析函數(shù)的返回值可能會產(chǎn)生不確定的結(jié)果。為了解決此問題，你可能不得不在order by clause中指定多個列以獲得唯一的排序。
（1）between…and：用來指定窗口的起點和終點。第一個表達式(位于and之前)定義起點，第二個表達式(位于and之后)定義終點。若不使用between而僅指定一個終點，那么oracle認為它是起點，終點默一認為當前行。
（2）unbounded preceding：指明窗口開始于分組的第一行。它只用于指定起點而不能用于指定終點
（3）unbounded following：指明窗口結(jié)束于分組的最后一行。它只用于指定終點而不能用于指定起點
（4）current row：
① 用作起點：指定窗口開始于當前行或者當前值（依賴于是否分別指定row或者range）。在這種情況下終點不能為value_expre preceding。
② 用作終點：指定窗口結(jié)束于當前行或者當前值（依賴于是否分別指定row或者range）。在這種情況下起點不能為value_expr following。

7）range或者row中的value_expr preceding或者value_expr following：
（1）若value_expr  FOLLOWING是起點，那么終點必須為：value_exprFOLLOWING。
（2）若value_expr  PRECEDING是終點，那么起點必須是：value_exprPRECEDING。
（3）若要定義一個數(shù)字格式的時間間隔的邏輯窗口，那么可能需要用到轉(zhuǎn)換函數(shù)（numtoyminterval與numtodsinterval）

8）若windowing_clause由range指定：
（1）value_expr是一個邏輯偏移量。它必須是常量，或者值為正數(shù)值的表達式，或者時間間隔文字常量。
（2）只能在order_by_clause中指定一個表達式。
（3）若value_expr求值為一個數(shù)字值，那么order_by_expr必須為數(shù)字或者date類型。
（4）若value_expr求值為一個間隔值，那么order_by_expr必須是一個date類型。
（5）若完全忽略windowing_clause，那么默認值為: range between unbounded preceding and current row。
注意：range 5 preceding：將產(chǎn)生一個滑動窗口，它在組中擁有當前行以及前5行的集合；
RANGE窗口僅對NUMBERS和DATES起作用，因為不可能從VARCHAR2中增加或減去N個單元，另外的限制是ORDER BY中只能有一列，因而范圍實際上是一維的，不能在N維空間中
例：avg（t.sal） over（order by t.hiredate asc range 100preceding） 統(tǒng)計前100天平均工資

8）若windowing_clause由rows指定：
（1）value_expr是一個物理偏移量，它必須是一個常量或者表達式，并且表達式的值必須是正數(shù)值
（2）若value_expr是起點的一部分，那么它必須在終點之前對行求值。
（3）利用ROW分區(qū)，就沒有RANGE分區(qū)那樣的限制了，數(shù)據(jù)可以是任何類型，且ORDER BY 可以包括很多列

9）常用的Specifying窗口
（1）UNBOUNDED PRECEDING：這個窗口從當前分區(qū)的每一行開始，并結(jié)束于正在處理的當前行
（2）CURRENT ROW：該窗口從當前行開始（并結(jié)束）
（3）Numeric Expression PRECEDING：對該窗口從當前行之前的數(shù)字表達式（Numeric Expression）的行開始，對RANGE來說，從從行序值小于數(shù)字表達式的當前行的值開始.
（4）Numeric Expression FOLLOWING：該窗口在當前行Numeric Expression行之后的行終止（或開始），且從行序值大于當前行NumericExpression行的范圍開始（或終止）
例如：range between 100 preceding and 100 following：當前行100前，當前后100后
注意：分析函數(shù)允許你對一個數(shù)據(jù)集進排序和篩選，這是SQL從來不能實現(xiàn)的.除了最后的Order by子句之外，分析函數(shù)是在查詢中執(zhí)行的最后的操作集，這樣的話，就不能直接在謂詞中使用分析函數(shù)，即不能在上面使用where或having子句!!