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 |
- 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函數的使用的相關知識就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
- Oracle臨時表在實際開發中的應用詳解
- MySQL數據庫my.cnf配置文件注釋詳解
- SQL Server數據庫復制失敗的原因及解決方案
- SQL Server 2005數據庫游標調用函數實例解析
- MySQL數據庫中EXPLAIN的使用及其注意事項詳解
