DB2數據庫OLAP函數的使用詳解

DB2數據庫OLAP函數的使用是本文我們主要要介紹的內容，我們知道，當今的數據處理大致可以分成兩大類：聯機事務處理OLTP（on-line transaction processing）、聯機分析處理OLAP（On-Line Analytical Processing）。OLTP是傳統的關系型數據庫的主要應用，主要是基本的、日常的事務處理，例如銀行交易。OLAP是數據倉庫系統的主要應用，支持復雜的分析操作，側重決策支持，並且提供直觀易懂的查詢結果。下表列出了OLTP與OLAP之間的比較。

 

OLTP

OLAP

用戶

操作人員,低層管理人員

決策人員,高級管理人員

功能

日常操作處理

分析決策

DB 設計

面向應用

面向主題

數據

當前的, 最新的細節的, 二維的分立的

歷史的, 聚集的, 多維的集成的, 統一的

存取

讀/寫數十條記錄

讀上百萬條記錄

工作單位

簡單的事務

復雜的查詢

用戶數

上千個

上百個

DB 大小

100MB-GB

100GB-TB

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

SELECT    
ROW_NUMBER() OVER(ORDER BY SALARY) AS 序號,    
NAME AS 姓名,    
DEPT AS 部門,    
SALARY AS 工資    
FROM    
(    
--姓名    部門  工資    
VALUES    
('張三','市場部',4000),    
('趙紅','技術部',2000),    
('李四','市場部',5000),    
('李白','技術部',5000),    
('王五','市場部',NULL),    
('王藍','技術部',4000)    
) AS EMPLOY(NAME,DEPT,SALARY);  

查詢結果如下：  

序號       姓名       部門       工資    
1     趙紅       技術部    2000    
2     張三       市場部    4000    
3     王藍       技術部    4000    
4     李四       市場部    5000    
5     李白       技術部    5000    
6     王五       市場部    (null)  

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

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

• ROW_NUMBER  
• RANK  
• DENSE_RANK  
• FIRST_VALUE  
• LAST_VALUE  
• LAG  
• LEAD  
• COUNT  
• MIN  
• MAX  
• AVG  
• SUM  

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

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

SELECT    
ROW_NUMBER() OVER() 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    
(    
--姓名    部門  工資    
VALUES    
('張三','市場部',4000),    
('趙紅','技術部',2000),    
('李四','市場部',5000),    
('李白','技術部',5000),    
('王五','市場部',NULL),    
('王藍','技術部',4000)    
) AS EMPLOY(NAME,DEPT,SALARY);   

查詢結果如下：  

序號       部門序號       姓名       部門       工資       部門平均工資       全員平均工資    
1            1          張三       市場部    4000       4500                     4000    
2            2          李四       市場部    5000       4500                     4000    
3            3          王五       市場部    (null)     4500                     4000    
4            1          趙紅       技術部    2000       3666                     4000    
5            2          王藍       技術部    4000       3666                     4000    
6            3          李白       技術部    5000       3666                     4000   

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

OVER是一個表達式，它的作用是定義一個作用域（或者可以說是結果集），OVER前面的函數只對OVER定義的結果集起作用。

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

到此為止，大家應該對OLAP函數的套路有一定的了解和體會了吧。大家看一下上面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    
(    
--姓名    部門  工資    
VALUES    
('張三','市場部',4000),    
('趙紅','技術部',2000),    
('李四','市場部',5000),    
('李白','技術部',5000),    
('王五','市場部',NULL),    
('王藍','技術部',4000)    
) AS EMPLOY(NAME,DEPT,SALARY);   

查詢結果如下：  

RN  RK   D_RK     姓名       部門       工資    
1     1     1     王五       市場部    (null)    
2     2     2     李四       市場部    5000    
3     2     2     李白       技術部    5000    
4     4     3     張三       市場部    4000    
5     4     3     王藍       技術部    4000    
6     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 工資總額2    
FROM    
(    
--姓名    部門  工資    
VALUES    
('張三','市場部',4000),    
('趙紅','技術部',2000),    
('李四','市場部',5000),    
('李白','技術部',5000),    
('王五','市場部',NULL),    
('王藍','技術部',4000)    
) AS EMPLOY(NAME,DEPT,SALARY);   

查詢結果如下：  

姓名       工資       小於本人工資的總額    大於本人工資的總額    工資總額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>  PRECEDING  

CURRENT ROW  

“下線條件”可以是如下關鍵字：  

CURRENT ROW  

<number> FOLLOWING  

UNBOUNDED FOLLOWING  

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

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

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

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

SELECT    
NAME AS 姓名,    
DEPT AS 部門,    
SALARY AS 工資,    
FIRST_VALUE(SALARY, 'IGNORE NULLS') 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    
(    
--姓名    部門  工資    
VALUES    
('張三','市場部',2000),    
('趙紅','技術部',2400),    
('李四','市場部',3000),    
('李白','技術部',3200),    
('王五','市場部',4000),    
('王藍','技術部',5000)    
) AS EMPLOY(NAME,DEPT,SALARY);   

查詢結果如下：  

姓名       部門       工資       部門最低工資       部門最高工資       ROWS    RANGE    
張三       市場部    2000       2000              4000             4400       4400    
趙紅       技術部    2400       2400              5000             7400       4400    
李四       市場部    3000       2000              4000             8600       6200    
李白       技術部    3200       2400              5000             10200     6200    
王五       市場部    4000       2000              4000             12200     4000    
王藍       技術部    5000       2400              5000             9000       5000   

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

上面的SQL 還用到了FIRST_VALUE和LAST_VALUE兩個函數，它們的作用也非常簡單，用來求OVER 定義集合的最小值和最大值。值得注意的是這兩個函數有個參數，'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 LEAD    
FROM    
(    
--姓名    部門  工資    
VALUES    
('張三','市場部',2000),    
('趙紅','技術部',2400),    
('李四','市場部',3000),    
('李白','技術部',3200),    
('王五','市場部',4000),    
('王藍','技術部',5000)    
) AS EMPLOY(NAME,DEPT,SALARY);   

查詢結果如下：  

姓名       工資       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的查詢結果就一目了然了。 

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

函數 OVER(PARTITION BY 子句 ORDER BY 子句 ROWS或RANGE子句) 。

關於DB2數據庫中OLAP函數的使用的相關知識就介紹到這裡了，希望本次的介紹能夠對您有所收獲！