【轉】分析函式語法解析

分析函式語法解析

1 分析函式整體語法
分析函式的語法如下：
analytic_function([ arguments ]) OVER (analytic_clause)

這裡：
Analytic_function是分析函式的名稱；
arguments是分析函式的引數；
over用來標識函式是一個分析函式；
analytic_clause用來確定分析函式的操作規則。包括query_partition_clause、order_by_clause和windowing_clause三個子句。


2 Analytic_function
分析函式的名稱。Oracle10gR2帶的內建分析函式有39個；對於使用者自定義的分析函式，分析函式名稱需要滿足識別符號規則。


3 Arguments
分析函式所帶引數，內建分析函式一般帶0-3個引數。引數可以是任何數字型別或是可以隱式轉換為數字型別的資料型別。Oracle根據最高數字優先順序別確定函式引數，並且隱式地將需要處理的引數轉換為數字型別。
使用者自定義分析函式的引數，可以根據實際情況使用。


4 Over
用以標識函式是一個分析函式，對於既可作為聚集函式又可作為分析函式的函式，Oracle無法識別，必須用over來標識此函式為分析函式。也不是說只可用作分析函式的函式就可以省略該關鍵字，over關鍵字是必須的，是分析函式就必須使用。
Over後面的()中是analytic_clause，即使analytic_clause的三個部分全不使用，()也必不可省，否則Oracle因不能識別函式為分析函式而報錯。


5 Analytic_clause
Analytic_clause的語法如下：
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

這裡：
query_partition_clause是查詢分組子句；
order_by_clause是分組排序子句；
windowing_clause是視窗範圍子句。

分析函式在查詢結果集確定之後才開始進行計算，Analytic_clause就是用來定義函式怎樣對查詢結果集進行分組計算的。

根據Oracle對查詢和分析函式的處理方法可知，在select和order by子句中都可以使用分析函式。

query_partition_by、order_by_clause和windowing_clause三個子句是可選的，將三個子句分別簡記為p，o，w。
合法的組合方式有如下6種：
1).        Pow
(query_partition_clause order_by_clause windowing_clause)
分組，排序，定義視窗範圍
2).        Po
(query_partition_clause order_by_clause)
分組，排序，視窗預設為range between unbounded preceding and current row
3).        P
(query_partition_clause)
分組，不排序，沒有視窗
4).        Ow
(order_by_clause windowing_clause)
分組為整個查詢結果集，排序，定義視窗範圍
5).        O
(order_by_clause)
分組為整個查詢結果集，排序，視窗預設為range between unbounded preceding and current row
6).        Null
()
分組為整個查詢結果集，不排序，沒有視窗

因為只有存在order_by_clause，才能有windowing_clause，故不存在如下兩種形式的組合：
pw(query_partition_clause windowing_clause)
w(windowing_clause)
總結：
1).        對於是否存在order_by_clause，分析函式可以分為兩類，含有order_by_clause的一般稱為windowing function，不含的稱為reporting function。
2).        Windowing function，對查詢結果集進行分組，排序，根據視窗範圍計算分組中每一行的函式結果。
3).        Reporting function，對查詢結果集進行分組，不排序，視窗範圍為整個分組，在每一個分組內，計算整個分組的函式值，再將函式值分別賦給分組內的每一行。

注意事項：
1)．Analytic_clause內不能包含任何分析函式。
2). 使用者自定義分析函式和內建函式分析函式都可以使用over (analytic_clause)。

5.1 Query_partition_clause
query_partition_clause的語法如下：
PARTITION BY value_expr[, value_expr ]...

這裡：
partition by是關鍵字，表示要將查詢結果集分組；
value_expr是分組表示式。

Partition by子句根據一個或多個value_expr將查詢結果集分成若干組。分組的原理與group by分組的原理類似，將value_expr相同的行集中在一起。

若不使用該子句，那末函式將整個查詢結果集作為一個分組。

value_expr可以為常量、表列、非分析函式、函式表示式，或者前面這些元素的任意組合表示式。

5.2 Order_by_clause
Order_by_clause的語法如下：
ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
[, expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]...

這裡：
order by是關鍵字，表示要將分組排序；
expr是排序表示式；
asc|desc和nulls first|null last是排序的附加規則。

Order_by_clause根據一個或多個expr對分組進行排序。

Expr只能為表示式，不能為位置編號或列別名。

注意事項：
1).        當視窗範圍為
Range between unbounded preceding and current row 或
Range between current row and unbounded following 時，
可以在order_by_clause中使用多個expr對分組進行排序。其他range視窗範圍只能使用一個expr。使用rows定義視窗範圍無此限制。
2). asc|desc 指定排序是按expr升序還是降序排序，若不指定則預設按asc進行升序排序。
3). nulls first|nulls last 指定若返回行包含空值，該值應該出現在排序序列的開始還是末尾。升序排序的預設值是nulls last，降序排序的預設值是nulls first。

5.3 Windowing_clause
Windowing_clause的語法如下：
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND
   { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } }
| { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING }
}

這裡，
rows 是關鍵字，指定視窗由物理行構成；
range 是關鍵字，指定視窗由邏輯偏移量構成；
between…and是關鍵字，用來指定視窗的起始點和終結點；
Unbounded preceding指明視窗開始於分組的第一行；
Current row，作為起始點，指明視窗開始於當前行或當前值；作為終結點，指明視窗結束於當前行或當前值；
Unbounded following指明視窗結束於分組的最後一行；
Value_expr為物理或邏輯偏移量表示式。

Windowing_clause用來指定分組中當前行的計算範圍。

不論rows還是range視窗，視窗總是在分組中從上至下滑動的。

視窗範圍可以由between…and限定，也可以不用between…and，不用的都表示視窗到當前行結束。

ROWS視窗，是由分組排序後分組中若干連續的行構成的視窗。

以下是全部合法的ROWS視窗範圍:
1).        Rows between unbounded preceding and unbounded following
視窗開始於分組第一行，結束於分組最後一行。

2).        Rows [between] unbounded preceding [and current row]
視窗開始於分組第一行，結束於當前行。

3).        Rows between unbounded preceding and value_expr preceding
視窗開始於分組第一行，結束於當前行前value_expr行。

4).        Rows between unbounded preceding and value_expr following
視窗開始於分組第一行，結束於當前行後value_expr行。

5).        Rows between current row and unbounded following
視窗開始於當前行，結束於分組最後一行。

6).        Rows [between current row and] current row
視窗開始於當前行，結束於當前行。

7).        Rows between current row and value_expr following
視窗開始於當前行，結束於當前行後value_expr行。

8).        Rows between value_expr preceding and unbounded following
視窗開始於當前行前value_expr行，結束於分組最後一行。

9).        Rows [between value_expr] preceding [and current row]
視窗開始於當前行前value_expr行，結束於當前行。

10).        Rows between value_expr1 preceding and value_expr2 preceding
視窗開始於當前行前value_expr1行，結束於當前行前value_expr2行。這裡一定要滿足value_expr1>=value_expr2。

11).        Rows between value_expr1 preceding and value_expr2 following
視窗開始於當前行前value_expr1行，結束於當前行後value_expr2行。

12).        Rows between value_expr following and unbounded following
視窗開始於當前行後value_expr行，結束於分組最後一行。

13).        Rows between value_expr1 following and value_expr2 following
視窗開始於當前行後value_expr1行，結束於當前行後value_expr2行。這裡一定要滿足value_expr1<=value_expr2

14).        Rows unbounded preceding
與2等價。

15).        Rows current row
與6等價。

16).        Rows value_expr preceding
與9等價。

RANGE視窗，相當於給order_by_clause中的expr加一個where限定條件，分組中滿足條件
(當order by expr asc時，where expr between a and b
當order by expr desc時 where expr between b and a)的所有行構成一個邏輯視窗。
其中a由分組中第ra行的值計算而來，b由分組中第rb行的值計算而來，且ra<=rb。

根據range中是否包含unbounded，可以分為兩類，含unbounded range視窗，和不含unbounded range視窗。
對於前者，有ra<=rb。當order by expr asc時，a<=b，須使用where expr between a and b；當order by expr desc時，a>b，須使用where expr between b and a。
對於後者，有ra=rb。總有a<=b，使用where expr between a and b。

對於在order_by_clause中可以使用多個expr的視窗：
Range between unbounded preceding and current row
Range between current row and unbounded following
當它們使用多個expr排序時(注意不是一個)分別等價於：
Rows between unbounded preceding and current row
Rows between current row and unbounded following

假設分組第一行的值為first_value，最後一行的值為last_value。當前行的值為current_value。
1).        Range between unbounded preceding and unbounded following
Where expr between first_value and last_value –-when asc
or expr between last_value and first_value     --when desc

2).        Range [between] unbounded preceding [and current row]
Where expr between first_value and current_value
or expr between current_value and first_value

3).        Range between unbounded preceding and value_expr preceding
Where expr between first_value and current_value-value_expr
or expr between current_value-value_expr and first_value

4).        Range between unbounded preceding and value_expr following
Where expr between first_value and current_value+value_expr
or expr between current_value+value_expr and first_value

5).        Range between current row and unbounded following
Where expr between current_value and last_value
or expr between last_value and current_value

6).        Range [between current row and] current row
Where expr = current_value

7).        Range between current row and value_expr following
Where expr between current_value and current_value+value_expr

8).        Range between value_expr preceding and unbounded following
Where expr between current_value-value_expr and last_value

9).        Range [between value_expr] preceding [and current row]
Where expr between current_value-value_expr and current_value

10).        Range between value_expr1 preceding and value_expr2 preceding
這裡一定要滿足value_expr1>=value_expr2。
Where expr between current_value-value_expr1 and current_value-value_expr2

11).        Range between value_expr1 preceding and value_expr2 following
Where expr between current_value-value_expr1 and current_value+value_expr2

12).        Range between value_expr following and unbounded following
Where expr between current_value+value_expr and last_value
or expr between last_value and current_value+value_expr

13).        Range between value_expr1 following and value_expr2 following
這裡一定要滿足value_expr1<=value_expr2。
Where expr between current_value+value_expr1 and current_value+value_expr2

14).        Range unbounded preceding
與2等價。

15).        Range current row
與6等價。

16).        Range value_expr preceding
與9等價。

注意事項：
1).若windowing_clause由rows指定，則：
(1)value_expr是物理偏移量，它必須是常量或值為非負數的表示式。
(2)若value_expr是起點的一部分，那末它必須在終點之前對行求值。
2).若windowing_clause由range指定，則:
(1)value_expr是邏輯偏移量。它必須是常量或值為非負數的表示式或時間間隔文字常量。
(2)若value_expr值為一個數字，那末order_by_clause中 expr必須為數字或date型別。
(3)若value_expr為一個間隔值，那末order_by_clause中expr必須是一個date型別。
3).若完全忽略windowing_clause，那末預設的視窗範圍為 range between unbounded preceding and current row 。