Oracle 分析函數__C語言

-- Start

說起 Oracle 分析函數，可以用很好很強大來形容。這項功能特別適用於各種統計查詢，這些查詢用通常的SQL很難實現，或者根本就無發實現。首先，我們從一個簡單的例子開始，來一步一步揭開它神秘的面紗，請看下面的SQL：

CREATE TABLE EMPLOY(    NAME    VARCHAR2(10),  --姓名    DEPT    VARCHAR2(10),  --部門    SALARY  NUMBER         --工資);INSERT INTO EMPLOY VALUES ('張三','市場部',4000);INSERT INTO EMPLOY VALUES ('趙紅','技術部',2000);INSERT INTO EMPLOY VALUES ('李四','市場部',5000);INSERT INTO EMPLOY VALUES ('李白','技術部',5000);INSERT INTO EMPLOY VALUES ('王五','市場部',NULL);INSERT INTO EMPLOY VALUES ('王藍','技術部',4000);SELECT    ROW_NUMBER() OVER(ORDER BY SALARY) AS 序號,    NAME                               AS 姓名,    DEPT                               AS 部門,    SALARY                             AS 工資FROM EMPLOY; 查詢結果如下： 序號       姓名       部門      工資1          趙紅       技術部    20002          張三       市場部    40003          王藍       技術部    40004          李四       市場部    50005          李白       技術部    50006          王五       市場部    (null)

看到上面的ROW_NUMBER() OVER()了嗎。很多人非常不理解，怎麼兩個函數能這麼寫呢。甚至有人懷疑上面的SQL語句是不是真的能執行。其實，ROW_NUMBER是個函數沒錯，它的作用從它的名字也可以看出來，就是給查詢結果集編號。但是，OVER並不是一個函數，而是一個分析語句，它的作用是定義一個範圍（或者可以說是結果集），OVER前面的函數只對OVER定義的結果集起作用。怎麼樣，不明白。沒關係，我們後面還會詳細介紹。

從上面的SQL我們可以看出，典型的 Oracle 線上分析處理的格式包括兩部分：函數部分和OVER分析語句部分。那麼，函數部分可以有哪些函數呢。如下：

ROW_NUMBER         給查詢結果集編行號RANK               給查詢結果集編排名DENSE_RANK         給查詢結果集編排名MIN                求最小值MAX                求最大值AVG                求平均值SUM                求總和COUNT              求結果集行數FIRST_VALUE        求最小值LAST_VALUE         求最大值FIRST              求最小值， 配合 DENSE_RANK 使用LAST               求最大值， 配合 DENSE_RANK 使用LAG                向下位移LEAD               向上位移LISTAGG            串連列NTILE              平分組NTH_VALUE          返回第 n 行的值VARIANCE           方差VAR_POP            總體方差VAR_SAMP           樣本方差STDDEV             標準差STDDEV_POP         總體標準差STDDEV_SAMP        樣本標準差CORR               共變數COVAR_POP          總體共變數COVAR_SAMP         樣本共變數CUME_DIST          計算積分分布PERCENT_RANK       和 CUME_DIST 類似PERCENTILE_CONT    計算值的連續分布模型PERCENTILE_DISC    計算值的不連續分布模型RATIO_TO_REPORT    計算比率REGR_SLOPE         線性迴歸REGR_INTERCEPT     線性迴歸REGR_COUNT         線性迴歸REGR_R2            線性迴歸REGR_AVGX          線性迴歸REGR_AVGY          線性迴歸REGR_SXX           線性迴歸REGR_SYY           線性迴歸REGR_SXY           線性迴歸

上面這些函數的作用，我會在後面逐步給大家介紹，大家可以根據函數名猜測一下函數的作用。

假設我想在不改變上面語句查詢結果的情況下，追加對部門員工的平均工資和全體員工的平均工資的查詢，怎麼辦呢。用通常的SQL很難查詢，但是用分析函數則非常簡單，如下SQL所示：

SELECT    ROW_NUMBER() OVER(ORDER BY DEPT, SALARY)             AS 序號,    ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY SALARY) AS 部門序號,    NAME                                                 AS 姓名,    DEPT                                                 AS 部門,    SALARY                                               AS 工資,    AVG(SALARY) OVER(PARTITION BY DEPT)                  AS 部門平均工資,    AVG(SALARY) OVER()                                   AS 全員平均工資FROM EMPLOY;  查詢結果如下： 序號       部門序號     姓名       部門      工資       部門平均工資     全員平均工資1            1          張三       市場部    4000       4500             40002            2          李四       市場部    5000       4500             40003            3          王五       市場部    (null)     4500             40004            1          趙紅       技術部    2000       3666.67          40005            2          王藍       技術部    4000       3666.67          40006            3          李白       技術部    5000       3666.67          4000

請注意序號和部門序號之間的區別，我們在查詢部門序號的時候，在OVER運算式中多了兩個子句，分別是 PARTITION BY和 ORDER BY。它們有什麼作用呢。在介紹它們的作用之前，我們先來回顧一下OVER的作用，還記得嗎。

OVER是一個分析語句，它的作用是定義一個範圍（或者可以說是結果集），OVER前面的函數只對OVER定義的結果集起作用。

ORDER BY的作用大家非常熟悉，用來對結果集排序。PARTITION BY的作用其實也很簡單，和GROUP BY的作用相同，用來對結果集分組。

到此為止，大家應該對分析函數的套路有一定的瞭解和體會了吧。大家看一下上面SQL的結果集，發現王五的工資是null，當我們按工資排序時，null被放到最後，我們想把 null 放在前邊該怎麼辦呢。使用NULLS FIRST關鍵字即可，預設是NULLS LAST，請看下面的SQL：

SELECT    ROW_NUMBER() OVER(ORDER BY SALARY DESC NULLS FIRST)    AS RN,    RANK() OVER(ORDER BY SALARY DESC NULLS FIRST)          AS RK,    DENSE_RANK() OVER(ORDER BY SALARY DESC NULLS FIRST)    AS D_RK,    NAME                                                   AS 姓名,    DEPT                                                   AS 部門,    SALARY                                                 AS 工資FROM EMPLOY; 查詢結果如下： RN  RK   D_RK     姓名       部門       工資1     1     1     王五       市場部    (null)2     2     2     李四       市場部    50003     2     2     李白       技術部    50004     4     3     張三       市場部    40005     4     3     王藍       技術部    40006     6     4     趙紅       技術部    2000

請注意ROW_NUMBER和RANK之間的區別，RANK是等級，排名的意思，李四和李白的工資都是5000，他們並列排名第二。張三和王藍的工資都是4000，怎麼RANK函數的排名是第四，而DENSE_RANK的排名是第三呢。這正是這兩個函數之間的區別。由於有兩個第二名，所以RANK函數預設沒有第三名。

現在又有個新問題，假設讓你查詢一下每個員工的工資以及工資小於他的所有員工的平均工資，該怎麼辦呢。怎麼。沒聽明白問題。不要緊，請看下面的SQL：

SELECT    NAME                                                                AS 姓名,    SALARY                                                              AS 工資,    SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)         AS 小於本人工資的總額,    SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)         AS 大於本人工資的總額,    SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 工資總額1,    SUM(SALARY) OVER()                                                  AS 工資總額2FROM EMPLOY; 查詢結果如下： 姓名       工資       小於本人工資的總額  大於本人工資的總額    工資總額1     工資總額2王五       (null)     (null)              20000                 20000         20000趙紅       2000       2000                20000                 20000         20000張三       4000       6000                18000                 20000         20000王藍       4000       10000               14000                 20000         20000李四       5000       15000               10000                 20000         20000李白       5000       20000               5000                  20000         20000

上面SQL 中的OVER部分出現了一個ROWS子句，我們先來看一下ROWS子句的結構：

ROWS BETWEEN <上限條件> AND <下限條件> 其中“上限條件”可以是如下關鍵字：UNBOUNDED PRECEDING<number>  PRECEDINGCURRENT ROW “下線條件”可以是如下關鍵字：CURRENT ROW<number> FOLLOWINGUNBOUNDED FOLLOWING

注意，以上關鍵字都是相對當前行的，UNBOUNDED PRECEDING表示當前行前面的所有行，也就是說沒有上限；<number> PRECEDING表示從當前行開始到它前面的<number>行為止，例如，number=2，表示的是當前行前面的2行；CURRENT ROW表示當前行。至於其它兩個關鍵字，我想，不用我說，你也應該知道了吧。如果你還不明白，請仔細分析上面SQL的查詢結果。

OVER 分析語句還可以有個子句，那就是RANGE，它的使用方式和ROWS十分相似，或者說一模一樣，作用也差多不，不過有點區別，如下所示：

RANGE BETWEEN <上限條件>AND <下限條件>

其中的<上限條件>、<下限條件>和ROWS一模一樣，如下的SQL示範它們之間的區別：

DELETE FROM EMPLOY;  INSERT INTO EMPLOY VALUES ('張三','市場部',2000);  INSERT INTO EMPLOY VALUES ('趙紅','技術部',2400);  INSERT INTO EMPLOY VALUES ('李四','市場部',3000);  INSERT INTO EMPLOY VALUES ('李白','技術部',3200);  INSERT INTO EMPLOY VALUES ('王五','市場部',4000);  INSERT INTO EMPLOY VALUES ('王藍','技術部',5000);     SELECT    NAME                                                                              AS 姓名,    DEPT                                                                              AS 部門,    SALARY                                                                            AS 工資,    FIRST_VALUE(SALARY IGNORE NULLS) OVER(PARTITION BY DEPT)                  AS 部門最低工資,    NTH_VALUE(SALARY, 2) OVER(PARTITION BY DEPT)                          AS 部門倒數第二工資,    LAST_VALUE(SALARY RESPECT NULLS) OVER(PARTITION BY DEPT)                  AS 部門最高工資,    SUM(SALARY) OVER(ORDER BY SALARY ROWS BETWEEN 1 PRECEDING  AND 1 FOLLOWING)     AS "ROWS",     SUM(SALARY) OVER(ORDER BY SALARY RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING) AS "RANGE" FROM EMPLOY;   查詢結果如下：     姓名    部門      工資    部門最低工資    部門倒數第二工資   部門最高工資       ROWS    RANGE  張三    市場部    2000    2000            3000               4000               4400    4400趙紅    技術部    2400    3200            5000               2400               7400    4400李四    市場部    3000    2000            3000               4000               8600    6200李白    技術部    3200    3200            5000               2400               10200   6200王五    市場部    4000    2000            3000               4000               12200   4000王藍    技術部    5000    3200            5000               2400               9000    5000

上面SQL的RANGE子句的作用是定義一個工資範圍，這個範圍的上限是當前行的工資-500，下限是當前行工資+500。例如：李四的工資是3000，所以上限是3000-500=2500，下限是3000+500=3500，那麼有誰的工資在2500-3500這個範圍呢。只有李四和李白，所以RANGE列的值就是3000(李四)+3200(李白)=6200。以上就是ROWS和RANGE得區別。

上面的 SQL 還用到了FIRST_VALUE，NTH_VALUE 和 LAST_VALUE 三個函數，它們的作用也非常簡單，用來求OVER定義集合的最小值，第 n 行的值和最大值。值得注意的是這兩個函數有個關鍵字，IGNORE NULLS 或 RESPECT NULLS，它們的作用正如它們的名字一樣，用來忽略NULL值和考慮NULL值。

還有兩個函數我們沒有介紹，LAG和LEAD，這兩個函數的功能非常強大，請看下面SQL：

SELECT    NAME                                                     AS 姓名,    SALARY                                                   AS 工資,    LAG(SALARY,0) OVER(ORDER BY SALARY)                      AS LAG0,    LAG(SALARY) OVER(ORDER BY SALARY)                        AS LAG1,    LAG(SALARY,2) OVER(ORDER BY SALARY)                      AS LAG2,    LAG(SALARY,3 ,0) IGNORE NULLS OVER(ORDER BY SALARY)      AS LAG3,    LAG(SALARY,4, -1) RESPECT NULLS OVER(ORDER BY SALARY)    AS LAG4,    LEAD(SALARY) OVER(ORDER BY SALARY)                       AS LEADFROM EMPLOY; 查詢結果如下： 姓名       工資       LAG0      LAG1      LAG2      LAG3      LAG4      LEAD張三       2000       2000      (null)   (null)       0       -1        2400趙紅       2400       2400       2000    (null)       0       -1        3000李四       3000       3000       2400     2000       0        -1        3200李白       3200       3200       3000     2400       2000     -1        4000王五       4000       4000       3200     3000       2400     2000      5000王藍       5000       5000       4000     3200       3000     2400      (null)

我們先來看一下LAG和 LEAD 函數的聲明，如下：

LAG(運算式或欄位,位移量, 預設值) IGNORE NULLS或RESPECT NULLS

LAG是向下位移，LEAD是向上位移，大家看一下上面SQL的查詢結果就一目瞭然了。

到此為止，有關Oracle 分析函數的所有知識都介紹給大家了，下面我們再次回顧一下Oracle 分析函數的組成部分，如下：

分析函數 OVER(PARTITIONBY子句 ORDER BY 子句 ROWS或RANGE子句)

要想熟練掌握這些知識還需要一定的時間和練習，一旦你掌握了，你將擁有一項絕世武學，可以縱橫 Oracle。

-- 更多參見：Oracle SQL 精萃

-- 聲明：轉載請註明出處

-- Last Edited on 2015-02-28

-- Created by ShangBo on 2014-12-19

-- End