分析函式 over

tthero00boo發表於2013-09-26
函式
 
聚合函式:
count,min,max,sum,avg

特徵:

     除了count(*),grouping,和grouping_id外，所有的聚合函式都會忽略null.當我們遇到Null 時，可以在聚合函式中使用NVL 函式來處理null
     Count 和Regr_count 聚合函式不會返回null，而是返回0或者某個數字。 其他的聚合函式當沒有rows 則返回null

分析函式 over關鍵字

分析函式用於計算基於組的某種聚合值，它和聚合函式的不同之處是：對於每個組返回多行，而聚合函式對於每個組只返回一行
如一個函式sum, 什麼都不帶就是sum()聚集函式, sum()over(...)這樣就是sum()分析函式

FUNCTION_NAME(,...)
OVER
()

例如: sum(sal) over (partition by deptno order by ename) new_alias

sum就是函式名
(sal)是分析函式的引數,每個函式有0~3個引數,引數可以是表示式,例如:sum(sal+comm)
over 是一個關鍵字,用於標識分析函式,否則查詢分析器不能區別sum()聚集函式和sum()分析函式

partition by deptno 是可選的分割槽子句,如果不存在任何分割槽子句,則全部的結果集可看作一個單一的大區

order by ename 是可選的order by 子句,有些函式需要它,有些則不需要.依靠已排序資料的那些函式,如:用於訪問結果集中前一行和後一行的LAG和LEAD,必須使用,其它函式,如AVG,則不需要.在使用了任何排序的開窗函式時,該子句是強制性的,它指定了在計算分析函式時一組內的資料是如何排序的.

WINDOWING子句    
用於定義分析函式將在其上操作的行的集合
Windowing子句給出了一個定義變化或固定的資料視窗的方法,分析函式將對這些資料進行操作
預設的視窗是一個固定的視窗,僅僅在一組的第一行開始,一直繼續到當前行,要使用視窗,必須使用ORDER BY子句
根據2個標準可以建立視窗:資料值的範圍(RANGE)或與當前行的行偏移量(ROWS).
select a.*,sum(dataid)over( order by dataid range between 3 preceding and 1 following ) from datadic a;
select a.*,sum(dataid)over( order by dataid rows between 3 preceding and 1 following ) from datadic a;

range視窗的限制是:只能用於number或date型別的資料中，因為只有number和date可以進行加減操作,而不能從VARCHAR2中增加或減去N個單元.
另外的限制是ORDER BY中只能有一列,因而範圍實際上是一維的,不能在N維空間中

ROW分割槽,就沒有RANGE分割槽那樣的限制了,資料可以是任何型別,且ORDER BY 可以包括很多列
    
range是數值上進行操作，rows是行的偏移量，可以用特定的子句進行指定特定的視窗，
n preceding ：如果是range，表示比當前行dataid值小n的行開始。如果是rows，表示從當前行之前的n行開始。
n following ：如果是range，表示比當前行dataid值大n的行結束，如果是rows，表示從當前行開始，當前行之後的n行結束。
between unbounded preceding and current rows ：從視窗的第一行到當前行。

over後的寫法：
over（order by salary） 按照salary排序進行累計，order by是個預設的開窗函式
over（partition by deptno）按照部門分割槽

over（partition by deptno order by salary）     

與over函式結合的幾個函式介紹
　
row_number()和rownum差不多，功能更強一點（可以在各個分組內從1開時排序）．

rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）．

dense_rank()是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重複值的 

row_number()  rank()  dense_rank()
        1        1            1 
        2        2            2   
        3        2            2   
        4        4            3   

lag（arg1,arg2,arg3):
arg1是返回的列或表示式
arg2是希望檢索的當前行分割槽的偏移量,是表示取前第幾行的。
arg3是在arg2表示的數目超出了分組的範圍時返回的預設值.

lag 和lead 可以 獲取結果集中，按一定排序所排列的當前行的上下相鄰若干offset 的某個行的某個列(不用結果集的自關聯）；
lag ，lead 分別是向前，向後；
lag 和lead 有三個引數，第一個引數是列名，第二個引數是偏移的offset，第三個引數是 超出記錄視窗時的預設值）
select a.dataid,
       lag(dataid, 1 , 9999) over(order by a.dataid) lag,
       lead(dataid, 1 , 8888) over(order by a.dataid) lead
  from datadic a;

    DATAID        LAG       LEAD
---------- ---------- ----------
         1       9999          2
         2          1          3
         3          2          4
         4          3          5
         5          4        140
       140          5        141
       141        140       8888
lag(expression)函式可以訪問組內當前行之前的行，
而lead(expression)函式則正相反，組內當前行之後的行．

其中，offset是正整數，預設為1．因組內第一個條記錄沒有之前的行，最後一行沒有之後的行，default就是用於處理這樣的資訊，預設為空．

注意：這2個函式必須指定 order By 字句．

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28859270/viewspace-773429/，如需轉載，請註明出處，否則將追究法律責任。

相關文章