DB2數(shù)據(jù)庫OLAP函數(shù)的使用詳解
DB2數(shù)據(jù)庫OLAP函數(shù)的使用是本文我們主要要介紹的內(nèi)容，我們知道，當(dāng)今的數(shù)據(jù)處理大致可以分成兩大類：聯(lián)機事務(wù)處理OLTP（on-line transaction processing）、聯(lián)機分析處理OLAP（On-Line Analytical Processing）。OLTP是傳統(tǒng)的關(guān)系型數(shù)據(jù)庫的主要應(yīng)用，主要是基本的、日常的事務(wù)處理，例如銀行交易。OLAP是數(shù)據(jù)倉庫系統(tǒng)的主要應(yīng)用，支持復(fù)雜的分析操作，側(cè)重決策支持，并且提供直觀易懂的查詢結(jié)果。下表列出了OLTP與OLAP之間的比較。
|
OLTP |
OLAP |
|
|
用戶 |
操作人員,低層管理人員 |
決策人員,高級管理人員 |
|
功能 |
日常操作處理 |
分析決策 |
|
DB 設(shè)計 |
面向應(yīng)用 |
面向主題 |
|
數(shù)據(jù) |
當(dāng)前的, 最新的細節(jié)的, 二維的分立的 |
歷史的, 聚集的, 多維的集成的, 統(tǒng)一的 |
|
存取 |
讀/寫數(shù)十條記錄 |
讀上百萬條記錄 |
|
工作單位 |
簡單的事務(wù) |
復(fù)雜的查詢 |
|
用戶數(shù) |
上千個 |
上百個 |
|
DB 大小 |
100MB-GB |
100GB-TB |
- SELECT
- ROW_NUMBER() OVER(ORDER BY SALARY) AS 序號,
- NAME AS 姓名,
- DEPT AS 部門,
- SALARY AS 工資
- FROM
- (
- --姓名 部門 工資
- VALUES
- ('張三','市場部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場部',NULL),
- ('王藍','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下：
- 序號 姓名 部門 工資
- 1 趙紅 技術(shù)部 2000
- 2 張三 市場部 4000
- 3 王藍 技術(shù)部 4000
- 4 李四 市場部 5000
- 5 李白 技術(shù)部 5000
- 6 王五 市場部 (null)
看到上面的ROW_NUMBER() OVER()了嗎？很多人非常不理解，怎么兩個函數(shù)能這么寫呢？甚至有人懷疑上面的SQL語句是不是真的能執(zhí)行。其實，ROW_NUMBER是個函數(shù)沒錯，它的作用從它的名字也可以看出來，就是給查詢結(jié)果集編號。但是，OVER并不是一個函數(shù)，而是一個表達式，它的作用是定義一個作用域（或者可以說是結(jié)果集），OVER前面的函數(shù)只對OVER定義的結(jié)果集起作用。怎么樣，不明白？沒關(guān)系，我們后面還會詳細介紹。
從上面的SQL我們可以看出，典型的 DB2 在線分析處理的格式包括兩部分：函數(shù)部分和OVER表達式部分。那么，函數(shù)部分可以有哪些函數(shù)呢？如下：
- ROW_NUMBER
- RANK
- DENSE_RANK
- FIRST_VALUE
- LAST_VALUE
- LAG
- LEAD
- COUNT
- MIN
- MAX
- AVG
- SUM
上面這些函數(shù)的作用，我會在后面逐步給大家介紹，大家可以根據(jù)函數(shù)名猜測一下函數(shù)的作用。
假設(shè)我想在不改變上面語句的查詢結(jié)果的情況下，追加對部門員工的平均工資和全體員工的平均工資的查詢，怎么辦呢？用通常的SQL很難查詢，但是用OLAP函數(shù)則非常簡單，如下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),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場部',NULL),
- ('王藍','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下：
- 序號 部門序號 姓名 部門 工資 部門平均工資 全員平均工資
- 1 1 張三 市場部 4000 4500 4000
- 2 2 李四 市場部 5000 4500 4000
- 3 3 王五 市場部 (null) 4500 4000
- 4 1 趙紅 技術(shù)部 2000 3666 4000
- 5 2 王藍 技術(shù)部 4000 3666 4000
- 6 3 李白 技術(shù)部 5000 3666 4000
請注意序號和部門序號之間的區(qū)別，我們在查詢部門序號的時候，在OVER表達式中多了兩個子句，分別是PARTITION BY 和ORDER BY。它們有什么作用呢？在介紹它們的作用之前，我們先來回顧一下OVER的作用，還記得嗎？
OVER是一個表達式，它的作用是定義一個作用域（或者可以說是結(jié)果集），OVER前面的函數(shù)只對OVER定義的結(jié)果集起作用。
ORDER BY的作用大家應(yīng)該非常熟悉，用來對結(jié)果集排序。PARTITION BY的作用其實也很簡單，和GROUP BY 的作用相同，用來對結(jié)果集分組。
到此為止，大家應(yīng)該對OLAP函數(shù)的套路有一定的了解和體會了吧。大家看一下上面SQL的結(jié)果集，發(fā)現(xiàn)王五的工資是null，當(dāng)我們按工資排序時，null被放到最后，我們想把null放在前邊該怎么辦呢？使用NULLS FIRST關(guān)鍵字即可，默認是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),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場部',NULL),
- ('王藍','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下：
- RN RK D_RK 姓名 部門 工資
- 1 1 1 王五 市場部 (null)
- 2 2 2 李四 市場部 5000
- 3 2 2 李白 技術(shù)部 5000
- 4 4 3 張三 市場部 4000
- 5 4 3 王藍 技術(shù)部 4000
- 6 6 4 趙紅 技術(shù)部 2000
請注意ROW_NUMBER和RANK之間的區(qū)別，RANK是等級，排名的意思，李四和李白的工資都是5000，他們并列排名第二。張三和王藍的工資都是4000，怎么RANK函數(shù)的排名是第四，而DENSE_RANK的排名是第三呢？這正是這兩個函數(shù)之間的區(qū)別。由于有兩個第二名，所以RANK函數(shù)默認沒有第三名。
現(xiàn)在又有個新問題，假設(shè)讓你查詢一下每個員工的工資以及工資小于他的所有員工的平均工資，該怎么辦呢？怎么？沒聽明白問題？不要緊，請看下面的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),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場部',NULL),
- ('王藍','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下：
- 姓名 工資 小于本人工資的總額 大于本人工資的總額 工資總額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部分出現(xiàn)了一個ROWS子句，我們先來看一下ROWS子句的結(jié)構(gòu)：
ROWS BETWEEN <上限條件> AND <下限條件>
其中“上限條件”可以是如下關(guān)鍵字：
UNBOUNDED PRECEDING
<number> PRECEDING
CURRENT ROW
“下線條件”可以是如下關(guān)鍵字：
CURRENT ROW
<number> FOLLOWING
UNBOUNDED FOLLOWING
注意，以上關(guān)鍵字都是相對當(dāng)前行的，UNBOUNDED PRECEDING表示當(dāng)前行前面的所有行，也就是說沒有上限；<number> PRECEDING表示從當(dāng)前行開始到它前面的<number>行為止，例如，number=2，表示的是當(dāng)前行前面的2行；CURRENT ROW表示當(dāng)前行。至于其它兩個關(guān)鍵字，我想，不用我說，你也應(yīng)該知道了吧。如果你還不明白，請仔細分析上面SQL的查詢結(jié)果。
OVER表達式還可以有個子句，那就是RANGE，它的使用方式和ROWS 十分相似，或者說一模一樣，作用也差多不，不過有點區(qū)別，如下所示：
RANGE BETWEEN <上限條件> AND <下限條件>
其中的<上限條件> 、<下限條件>和ROWS一模一樣，如下的SQL演示它們之間的區(qū)別：
- 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),
- ('趙紅','技術(shù)部',2400),
- ('李四','市場部',3000),
- ('李白','技術(shù)部',3200),
- ('王五','市場部',4000),
- ('王藍','技術(shù)部',5000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下：
- 姓名 部門 工資 部門最低工資 部門最高工資 ROWS RANGE
- 張三 市場部 2000 2000 4000 4400 4400
- 趙紅 技術(shù)部 2400 2400 5000 7400 4400
- 李四 市場部 3000 2000 4000 8600 6200
- 李白 技術(shù)部 3200 2400 5000 10200 6200
- 王五 市場部 4000 2000 4000 12200 4000
- 王藍 技術(shù)部 5000 2400 5000 9000 5000
上面SQL的RANGE 子句的作用是定義一個工資范圍，這個范圍的上限是當(dāng)前行的工資-500，下限是當(dāng)前行工資+500。例如：李四的工資是3000，所以上限是3000-500=2500，下限是3000+500=3500，那么有誰的工資在2500-3500這個范圍呢？只有李四和李白，所以RANGE列的值就是3000(李四)+3200(李白)=6200。以上就是ROWS和RANGE得區(qū)別。
上面的SQL 還用到了FIRST_VALUE和LAST_VALUE兩個函數(shù)，它們的作用也非常簡單，用來求OVER 定義集合的最小值和最大值。值得注意的是這兩個函數(shù)有個參數(shù)，'IGNORE NULLS' 或 'RESPECT NULLS'，它們的作用正如它們的名字一樣，用來忽略NULL值和考慮NULL值。
還有兩個函數(shù)我們沒有介紹，LAG和LEAD，這兩個函數(shù)的功能非常強大，請看下面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),
- ('趙紅','技術(shù)部',2400),
- ('李四','市場部',3000),
- ('李白','技術(shù)部',3200),
- ('王五','市場部',4000),
- ('王藍','技術(shù)部',5000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下：
- 姓名 工資 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 函數(shù)的聲明，如下：
LAG(表達式或字段, 偏移量, 默認值, IGNORE NULLS或RESPECT NULLS)
LAG是向下偏移，LEAD是想上偏移，大家看一下上面SQL的查詢結(jié)果就一目了然了。
到此為止，有關(guān)DB2 OLAP 函數(shù)的所有知識都介紹給大家了，下面我們再次回顧一下 DB2 在線分析處理 的組成部分，如下：
函數(shù) OVER(PARTITION BY 子句 ORDER BY 子句 ROWS或RANGE子句) 。
關(guān)于DB2數(shù)據(jù)庫中OLAP函數(shù)的使用的相關(guān)知識就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
【編輯推薦】
- Oracle臨時表在實際開發(fā)中的應(yīng)用詳解
- MySQL數(shù)據(jù)庫my.cnf配置文件注釋詳解
- SQL Server數(shù)據(jù)庫復(fù)制失敗的原因及解決方案
- SQL Server 2005數(shù)據(jù)庫游標(biāo)調(diào)用函數(shù)實例解析
- MySQL數(shù)據(jù)庫中EXPLAIN的使用及其注意事項詳解
