新聞中心
DB2數(shù)據(jù)庫(kù)OLAP函數(shù)的使用是本文我們主要要介紹的內(nèi)容,我們知道,當(dāng)今的數(shù)據(jù)處理大致可以分成兩大類:聯(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è)重決策支持,并且提供直觀易懂的查詢結(jié)果。下表列出了OLTP與OLAP之間的比較。

專注于為中小企業(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 | |
用戶 |
操作人員,低層管理人員 |
決策人員,高級(jí)管理人員 |
功能 |
日常操作處理 |
分析決策 |
DB 設(shè)計(jì) |
面向應(yīng)用 |
面向主題 |
數(shù)據(jù) |
當(dāng)前的, 最新的細(xì)節(jié)的, 二維的分立的 |
歷史的, 聚集的, 多維的集成的, 統(tǒng)一的 |
存取 |
讀/寫數(shù)十條記錄 |
讀上百萬(wàn)條記錄 |
工作單位 |
簡(jiǎn)單的事務(wù) |
復(fù)雜的查詢 |
用戶數(shù) |
上千個(gè) |
上百個(gè) |
DB 大小 |
100MB-GB |
100GB-TB |
聯(lián)機(jī)分析處理 (OLAP) 可以用很好很強(qiáng)大來(lái)形容。這項(xiàng)功能特別適用于各種統(tǒng)計(jì)查詢,這些查詢用通常的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 部門,
- SALARY AS 工資
- FROM
- (
- --姓名 部門 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下:
- 序號(hào) 姓名 部門 工資
- 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ù)能這么寫呢?甚至有人懷疑上面的SQL語(yǔ)句是不是真的能執(zhí)行。其實(shí),ROW_NUMBER是個(gè)函數(shù)沒(méi)錯(cuò),它的作用從它的名字也可以看出來(lái),就是給查詢結(jié)果集編號(hào)。但是,OVER并不是一個(gè)函數(shù),而是一個(gè)表達(dá)式,它的作用是定義一個(gè)作用域(或者可以說(shuō)是結(jié)果集),OVER前面的函數(shù)只對(duì)OVER定義的結(jié)果集起作用。怎么樣,不明白?沒(méi)關(guān)系,我們后面還會(huì)詳細(xì)介紹。
從上面的SQL我們可以看出,典型的 DB2 在線分析處理的格式包括兩部分:函數(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ǔ)句的查詢結(jié)果的情況下,追加對(duì)部門員工的平均工資和全體員工的平均工資的查詢,怎么辦呢?用通常的SQL很難查詢,但是用OLAP函數(shù)則非常簡(jiǎn)單,如下SQL所示:
- SELECT
- ROW_NUMBER() OVER() AS 序號(hào),
- ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY SALARY) AS 部門序號(hào),
- NAME AS 姓名,
- DEPT AS 部門,
- SALARY AS 工資,
- AVG(SALARY) OVER(PARTITION BY DEPT) AS 部門平均工資,
- AVG(SALARY) OVER() AS 全員平均工資
- FROM
- (
- --姓名 部門 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下:
- 序號(hào) 部門序號(hào) 姓名 部門 工資 部門平均工資 全員平均工資
- 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)和部門序號(hào)之間的區(qū)別,我們?cè)诓樵儾块T序號(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 部門,
- SALARY AS 工資
- FROM
- (
- --姓名 部門 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(shù)部',4000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下:
- RN RK D_RK 姓名 部門 工資
- 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è)讓你查詢一下每個(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
- (
- --姓名 部門 工資
- VALUES
- ('張三','市場(chǎng)部',4000),
- ('趙紅','技術(shù)部',2000),
- ('李四','市場(chǎng)部',5000),
- ('李白','技術(shù)部',5000),
- ('王五','市場(chǎng)部',NULL),
- ('王藍(lán)','技術(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
- 王藍(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
“下線條件”可以是如下關(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 部門,
- 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
- ('張三','市場(chǎng)部',2000),
- ('趙紅','技術(shù)部',2400),
- ('李四','市場(chǎng)部',3000),
- ('李白','技術(shù)部',3200),
- ('王五','市場(chǎng)部',4000),
- ('王藍(lán)','技術(shù)部',5000)
- ) AS EMPLOY(NAME,DEPT,SALARY);
查詢結(jié)果如下:
- 姓名 部門 工資 部門最低工資 部門最高工資 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
- (
- --姓名 部門 工資
- VALUES
- ('張三','市場(chǎng)部',2000),
- ('趙紅','技術(shù)部',2400),
- ('李四','市場(chǎng)部',3000),
- ('李白','技術(shù)部',3200),
- ('王五','市場(chǎng)部',4000),
- ('王藍(lán)','技術(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
- 王藍(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的查詢結(jié)果就一目了然了。
到此為止,有關(guān)DB2 OLAP 函數(shù)的所有知識(shí)都介紹給大家了,下面我們?cè)俅位仡櫼幌?nbsp;DB2 在線分析處理 的組成部分,如下:
函數(shù) OVER(PARTITION BY 子句 ORDER BY 子句 ROWS或RANGE子句) 。
關(guān)于DB2數(shù)據(jù)庫(kù)中OLAP函數(shù)的使用的相關(guān)知識(shí)就介紹到這里了,希望本次的介紹能夠?qū)δ兴斋@!
分享文章:DB2數(shù)據(jù)庫(kù)OLAP函數(shù)的使用詳解
分享地址:http://fisionsoft.com.cn/article/djpggie.html


咨詢
建站咨詢
