Oracle 分析函數的使用(主要是rollup用法)

    分析函數是oracle 8.1.6中就引入的一個全新的概念,為我們分析資料提供了一種簡單高效的處理方式.在分析函數出現以前,我們必須使用自聯查詢,子查詢或者內聯視圖,甚至複雜的預存程序實現的語句,現在只要一條簡單的sql語句就可以實現了,而且在執行效率方面也有相當大的提高.

分析函數參考手冊：http://xsb.itpub.net/post/419/33028

分析函數的使用方法
1. 自動彙總函數rollup,cube,
2. rank 函數, rank,dense_rank,row_number
3. lag,lead函數
4. sum,avg,的移動增加,移動平均數
5. ratio_to_report報表處理函數
6. first,last取基數的分析函數

 

      本人在項目中由於用到小計、合計的統計，前面想到用union all，但這樣有點麻煩並且效率也不高，就從網上查到資料說是oracle 8i、oracl 9i、oracle 10g 中已經分析函數對資料統計的處理，於是就順便學習了一下這些函數的用法，拿出來分享給大家共同學習。

1、Oracle ROLLUP和CUBE 用法    
      Oracle的GROUP BY語句除了最基本的文法外，還支援ROLLUP和CUBE語句。如果是Group by  ROLLUP(A, B, C)的話，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。

      如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函數，還可以使用GROUPING_ID來標識GROUP BY的結果。

       也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 這樣任意按自己想要的形式結合統計資料，非常方便。

 2、Rank的用法

       功能描述：根據ORDER BY子句中運算式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的資料按ORDER BY子句排序，然後給每一行賦一個號，從而形成一個序列，該序列從1開始，往後累加。每次ORDER BY運算式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數將隨後跳躍。若兩行序數為1，則沒有序數2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。
       rank()是跳躍排序，比如有兩個第二名時接下來就是第四名（同樣是在各個分組內）

       dense_rank()l是連續排序，比如有兩個第二名時仍然跟著第三名。  

3、First的用法

       功能描述：從DENSE_RANK返回的集合中取出排在最前面的一個值的行（可能多行，因為值可能相等），因此完整的文法需要在開始處加上一個集合函數以從中取出記錄。

4、Last的用法

     功能描述：從DENSE_RANK返回的集合中取出排在最後面的一個值的行（可能多行，因為值可能相等），因此完整的文法需要在開始處加上一個集合函數以從中取出記錄。

5、Lag的用法

       功能描述：可以訪問結果集中的其它行而不用進行自串連。它允許去處理遊標，就好像遊標是一個數組一樣。在給定組中可參考當前行之前的行，這樣就可以從組中與當前行一起選擇以前的行。Offset是一個正整數，其預設值為1，若索引超出視窗的範圍，就返回預設值（預設返回的是組中第一行），其相反的函數是LEAD。

 

具體各個函數的使用方法可去baidu裡學習，這裡只是簡要的介紹一下作用。

掌握這些函數的用法，可以在程式開發中減少很多彎路，並且還可以大大提高運行效率。