建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
DB2數(shù)據(jù)庫(kù)OLAP函數(shù)的使用是本文我們主要要介紹的內(nèi)容，我們知道，當(dāng)今的數(shù)據(jù)處理大致可以分成兩大類(lèi)：聯(lián)機(jī)事務(wù)處理OLTP（on-line transaction processing）、聯(lián)機(jī)分析處理OLAP（On-Line Analytical Processing）。OLTP是傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)的主要應(yīng)用，主要是基本的、日常的事務(wù)處理，例如銀行交易。OLAP是數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)的主要應(yīng)用，支持復(fù)雜的分析操作，側(cè)重決策支持，并且提供直觀(guān)易懂的查詢(xún)結(jié)果。下表列出了OLTP與OLAP之間的比較。
專(zhuān)注于為中小企業(yè)提供網(wǎng)站設(shè)計(jì)、網(wǎng)站制作服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)平定免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了上千企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過(guò)網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
OLTP |
OLAP | |
用戶(hù) |
操作人員,低層管理人員 |
決策人員,高級(jí)管理人員 |
功能 |
日常操作處理 |
分析決策 |
DB 設(shè)計(jì) |
面向應(yīng)用 |
面向主題 |
數(shù)據(jù) |
當(dāng)前的, 最新的細(xì)節(jié)的, 二維的分立的 |
歷史的, 聚集的, 多維的集成的, 統(tǒng)一的 |
存取 |
讀/寫(xiě)數(shù)十條記錄 |
讀上百萬(wàn)條記錄 |
工作單位 |
簡(jiǎn)單的事務(wù) |
復(fù)雜的查詢(xún) |
用戶(hù)數(shù) |
上千個(gè) |
上百個(gè) |
DB 大小 |
100MB-GB |
100GB-TB |
聯(lián)機(jī)分析處理 (OLAP) 可以用很好很強(qiáng)大來(lái)形容。這項(xiàng)功能特別適用于各種統(tǒng)計(jì)查詢(xún)，這些查詢(xún)用通常的SQL很難實(shí)現(xiàn)，或者根本就無(wú)發(fā)實(shí)現(xiàn)。首先，我們從一個(gè)簡(jiǎn)單的例子開(kāi)始，來(lái)一步一步揭開(kāi)它神秘的面紗，請(qǐng)看下面的SQL：
- SELECT
- ROW_NUMBER() OVER(ORDER BY SALARY) AS 序號(hào),
- NAME AS 姓名,
- DEPT AS 部門(mén),
- SALARY AS 工資
- FROM
- (
- --姓名 部門(mén) 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢(xún)結(jié)果如下：
- 序號(hào) 姓名 部門(mén) 工資
- 1 趙紅 技術(shù)部 2000
- 2 張三 市場(chǎng)部 4000
- 3 王藍(lán) 技術(shù)部 4000
- 4 李四 市場(chǎng)部 5000
- 5 李白 技術(shù)部 5000
- 6 王五 市場(chǎng)部 (null)
看到上面的ROW_NUMBER() OVER()了嗎？很多人非常不理解，怎么兩個(gè)函數(shù)能這么寫(xiě)呢？甚至有人懷疑上面的SQL語(yǔ)句是不是真的能執(zhí)行。其實(shí)，ROW_NUMBER是個(gè)函數(shù)沒(méi)錯(cuò)，它的作用從它的名字也可以看出來(lái)，就是給查詢(xún)結(jié)果集編號(hào)。但是，OVER并不是一個(gè)函數(shù)，而是一個(gè)表達(dá)式，它的作用是定義一個(gè)作用域（或者可以說(shuō)是結(jié)果集），OVER前面的函數(shù)只對(duì)OVER定義的結(jié)果集起作用。怎么樣，不明白？沒(méi)關(guān)系，我們后面還會(huì)詳細(xì)介紹。
從上面的SQL我們可以看出，典型的 DB2 在線(xiàn)分析處理的格式包括兩部分：函數(shù)部分和OVER表達(dá)式部分。那么，函數(shù)部分可以有哪些函數(shù)呢？如下：
- ROW_NUMBER
- RANK
- DENSE_RANK
- FIRST_VALUE
- LAST_VALUE
- LAG
- LEAD
- COUNT
- MIN
- MAX
- AVG
- SUM
上面這些函數(shù)的作用，我會(huì)在后面逐步給大家介紹，大家可以根據(jù)函數(shù)名猜測(cè)一下函數(shù)的作用。
假設(shè)我想在不改變上面語(yǔ)句的查詢(xún)結(jié)果的情況下，追加對(duì)部門(mén)員工的平均工資和全體員工的平均工資的查詢(xún)，怎么辦呢？用通常的SQL很難查詢(xún)，但是用OLAP函數(shù)則非常簡(jiǎn)單，如下SQL所示：
- SELECT
- ROW_NUMBER() OVER() AS 序號(hào),
- ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY SALARY) AS 部門(mén)序號(hào),
- NAME AS 姓名,
- DEPT AS 部門(mén),
- SALARY AS 工資,
- AVG(SALARY) OVER(PARTITION BY DEPT) AS 部門(mén)平均工資,
- AVG(SALARY) OVER() AS 全員平均工資
- FROM
- (
- --姓名 部門(mén) 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢(xún)結(jié)果如下：
- 序號(hào) 部門(mén)序號(hào) 姓名 部門(mén) 工資 部門(mén)平均工資 全員平均工資
- 1 1 張三 市場(chǎng)部 4000 4500 4000
- 2 2 李四 市場(chǎng)部 5000 4500 4000
- 3 3 王五 市場(chǎng)部 (null) 4500 4000
- 4 1 趙紅 技術(shù)部 2000 3666 4000
- 5 2 王藍(lán) 技術(shù)部 4000 3666 4000
- 6 3 李白 技術(shù)部 5000 3666 4000
請(qǐng)注意序號(hào)和部門(mén)序號(hào)之間的區(qū)別，我們?cè)诓樵?xún)部門(mén)序號(hào)的時(shí)候，在OVER表達(dá)式中多了兩個(gè)子句，分別是PARTITION BY 和ORDER BY。它們有什么作用呢？在介紹它們的作用之前，我們先來(lái)回顧一下OVER的作用，還記得嗎？
OVER是一個(gè)表達(dá)式，它的作用是定義一個(gè)作用域（或者可以說(shuō)是結(jié)果集），OVER前面的函數(shù)只對(duì)OVER定義的結(jié)果集起作用。
ORDER BY的作用大家應(yīng)該非常熟悉，用來(lái)對(duì)結(jié)果集排序。PARTITION BY的作用其實(shí)也很簡(jiǎn)單，和GROUP BY 的作用相同，用來(lái)對(duì)結(jié)果集分組。
到此為止，大家應(yīng)該對(duì)OLAP函數(shù)的套路有一定的了解和體會(huì)了吧。大家看一下上面SQL的結(jié)果集，發(fā)現(xiàn)王五的工資是null，當(dāng)我們按工資排序時(shí)，null被放到最后，我們想把null放在前邊該怎么辦呢？使用NULLS FIRST關(guān)鍵字即可，默認(rèn)是NULLS LAST，請(qǐng)看下面的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 部門(mén),
- SALARY AS 工資
- FROM
- (
- --姓名 部門(mén) 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢(xún)結(jié)果如下：
- RN RK D_RK 姓名 部門(mén) 工資
- 1 1 1 王五 市場(chǎng)部 (null)
- 2 2 2 李四 市場(chǎng)部 5000
- 3 2 2 李白 技術(shù)部 5000
- 4 4 3 張三 市場(chǎng)部 4000
- 5 4 3 王藍(lán) 技術(shù)部 4000
- 6 6 4 趙紅 技術(shù)部 2000
請(qǐng)注意ROW_NUMBER和RANK之間的區(qū)別，RANK是等級(jí)，排名的意思，李四和李白的工資都是5000，他們并列排名第二。張三和王藍(lán)的工資都是4000，怎么RANK函數(shù)的排名是第四，而DENSE_RANK的排名是第三呢？這正是這兩個(gè)函數(shù)之間的區(qū)別。由于有兩個(gè)第二名，所以RANK函數(shù)默認(rèn)沒(méi)有第三名。
現(xiàn)在又有個(gè)新問(wèn)題，假設(shè)讓你查詢(xún)一下每個(gè)員工的工資以及工資小于他的所有員工的平均工資，該怎么辦呢？怎么？沒(méi)聽(tīng)明白問(wèn)題？不要緊，請(qǐng)看下面的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
- (
- --姓名 部門(mén) 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢(xún)結(jié)果如下：
- 姓名 工資 小于本人工資的總額 大于本人工資的總額 工資總額1 工資總額2
- 王五 (null) (null) 20000 20000 20000
- 趙紅 2000 2000 20000 20000 20000
- 張三 4000 6000 18000 20000 20000
- 王藍(lán) 4000 10000 14000 20000 20000
- 李四 5000 15000 10000 20000 20000
- 李白 5000 20000 5000 20000 20000
上面SQL 中的OVER部分出現(xiàn)了一個(gè)ROWS子句，我們先來(lái)看一下ROWS子句的結(jié)構(gòu)：
ROWS BETWEEN <上限條件> AND <下限條件>
其中“上限條件”可以是如下關(guān)鍵字：
UNBOUNDED PRECEDING
CURRENT ROW
“下線(xiàn)條件”可以是如下關(guān)鍵字：
CURRENT ROW
UNBOUNDED FOLLOWING
注意，以上關(guān)鍵字都是相對(duì)當(dāng)前行的，UNBOUNDED PRECEDING表示當(dāng)前行前面的所有行，也就是說(shuō)沒(méi)有上限；
OVER表達(dá)式還可以有個(gè)子句，那就是RANGE，它的使用方式和ROWS 十分相似，或者說(shuō)一模一樣，作用也差多不，不過(guò)有點(diǎn)區(qū)別，如下所示：
RANGE BETWEEN <上限條件> AND <下限條件>
其中的<上限條件> 、<下限條件>和ROWS一模一樣，如下的SQL演示它們之間的區(qū)別：
- SELECT
- NAME AS 姓名,
- DEPT AS 部門(mén),
- SALARY AS 工資,
- FIRST_VALUE(SALARY, 'IGNORE NULLS') OVER(PARTITION BY DEPT) AS 部門(mén)最低工資,
- LAST_VALUE(SALARY, 'RESPECT NULLS') OVER(PARTITION BY DEPT) AS 部門(mén)最高工資,
- 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
- (
- --姓名 部門(mén) 工資
- VALUES
- ('張三','市場(chǎng)部',2000),
- ('趙紅','技術(shù)部',2400),
- ('李四','市場(chǎng)部',3000),
- ('李白','技術(shù)部',3200),
- ('王五','市場(chǎng)部',4000),
- ('王藍(lán)','技術(shù)部',5000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢(xún)結(jié)果如下：
- 姓名 部門(mén) 工資 部門(mén)最低工資 部門(mén)最高工資 ROWS RANGE
- 張三 市場(chǎng)部 2000 2000 4000 4400 4400
- 趙紅 技術(shù)部 2400 2400 5000 7400 4400
- 李四 市場(chǎng)部 3000 2000 4000 8600 6200
- 李白 技術(shù)部 3200 2400 5000 10200 6200
- 王五 市場(chǎng)部 4000 2000 4000 12200 4000
- 王藍(lán) 技術(shù)部 5000 2400 5000 9000 5000
上面SQL的RANGE 子句的作用是定義一個(gè)工資范圍，這個(gè)范圍的上限是當(dāng)前行的工資-500，下限是當(dāng)前行工資+500。例如：李四的工資是3000，所以上限是3000-500=2500，下限是3000+500=3500，那么有誰(shuí)的工資在2500-3500這個(gè)范圍呢？只有李四和李白，所以RANGE列的值就是3000(李四)+3200(李白)=6200。以上就是ROWS和RANGE得區(qū)別。
上面的SQL 還用到了FIRST_VALUE和LAST_VALUE兩個(gè)函數(shù)，它們的作用也非常簡(jiǎn)單，用來(lái)求OVER 定義集合的最小值和最大值。值得注意的是這兩個(gè)函數(shù)有個(gè)參數(shù)，'IGNORE NULLS' 或 'RESPECT NULLS'，它們的作用正如它們的名字一樣，用來(lái)忽略NULL值和考慮NULL值。
還有兩個(gè)函數(shù)我們沒(méi)有介紹，LAG和LEAD，這兩個(gè)函數(shù)的功能非常強(qiáng)大，請(qǐng)看下面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
- (
- --姓名 部門(mén) 工資
- VALUES
- ('張三','市場(chǎng)部',2000),
- ('趙紅','技術(shù)部',2400),
- ('李四','市場(chǎng)部',3000),
- ('李白','技術(shù)部',3200),
- ('王五','市場(chǎng)部',4000),
- ('王藍(lán)','技術(shù)部',5000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢(xún)結(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
- 王藍(lán) 5000 5000 4000 3200 3000 2400 (null)
我們先來(lái)看一下LAG 和 LEAD 函數(shù)的聲明，如下：
LAG(表達(dá)式或字段, 偏移量, 默認(rèn)值, IGNORE NULLS或RESPECT NULLS)
LAG是向下偏移，LEAD是想上偏移，大家看一下上面SQL的查詢(xún)結(jié)果就一目了然了。
到此為止，有關(guān)DB2 OLAP 函數(shù)的所有知識(shí)都介紹給大家了，下面我們?cè)俅位仡櫼幌?nbsp;DB2 在線(xiàn)分析處理 的組成部分，如下：
函數(shù) OVER(PARTITION BY 子句 ORDER BY 子句 ROWS或RANGE子句) 。
關(guān)于DB2數(shù)據(jù)庫(kù)中OLAP函數(shù)的使用的相關(guān)知識(shí)就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
名稱(chēng)欄目：DB2數(shù)據(jù)庫(kù)OLAP函數(shù)的使用詳解
網(wǎng)頁(yè)路徑：http://m.5511xx.com/article/djpggie.html
