神武八荒一颗小说,辰东完美世界有声小说

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案

DB2數(shù)據(jù)庫(kù)OLAP函數(shù)的使用詳解

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

PRECEDING

CURRENT ROW

“下線條件”可以是如下關(guān)鍵字：

CURRENT ROW

FOLLOWING

UNBOUNDED FOLLOWING

注意，以上關(guān)鍵字都是相對(duì)當(dāng)前行的，UNBOUNDED PRECEDING表示當(dāng)前行前面的所有行，也就是說(shuō)沒(méi)有上限； PRECEDING表示從當(dāng)前行開(kāi)始到它前面的行為止，例如，number=2，表示的是當(dāng)前行前面的2行；CURRENT ROW表示當(dāng)前行。至于其它兩個(gè)關(guān)鍵字，我想，不用我說(shuō)，你也應(yīng)該知道了吧。如果你還不明白，請(qǐng)仔細(xì)分析上面SQL的查詢結(jié)果。

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

新聞中心

其他資訊