在線分析處理函數(shù)（OLAP）是DB2中擴(kuò)展了關(guān)系模型、使關(guān)系模型能夠理解行集合內(nèi)的排序方式的重要函數(shù)之一，本文將為您詳細(xì)分析OLAP函數(shù)，并附相關(guān)實(shí)例，供您參考，希望對您有所幫助。

排列函數(shù)：第一類引入到 DB2 中的 OLAP 函數(shù)是 排列（ranking）函數(shù)，它們是在 DB2 Version 6 中引入的。這些排列函數(shù)提供了定義一個(gè)集合（使用 PARTITION 子句），然后根據(jù)某種排序方式對這個(gè)集合內(nèi)的元素進(jìn)行排列的能力。例如，假設(shè)我們有一個(gè)雇員表，現(xiàn)在要對每個(gè)部門內(nèi)的雇員薪水進(jìn)行排列。要實(shí)現(xiàn)這一點(diǎn)，我們需要一個(gè)函數(shù)調(diào)用，這個(gè)函數(shù)調(diào)用可以完成以下工作：

將分區(qū)（集合）定義為各個(gè)部門，將集合內(nèi)的排序方式定義為按薪水排序。 按照慣例，我們一般會(huì)將薪水高的排在前面，所以我們將指定一個(gè)對薪水的降序排序方式。下面的例子展示了這個(gè)查詢的查詢和輸出。
select empnum, dept, salary, rank() over (partition by dept order by salary desc nulls last) as rank, dense_rank() over (partition by dept order by salary desc nulls last)as denserank, row_number() over (partition by dept order by salary desc nulls last)as rownumber from emptab;

EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER
------ ---- ------ ---- --------- ---------
6 1 78000 1 1 1
2 1 75000 2 2 2
7 1 75000 2 2 3
11 1 53000 4 3 4
5 1 52000 5 4 5
1 1 50000 6 5 6
--------------------------------------------------
9 2 51000 1 1 1
4 2 - 2 2 2 #p#
注意，rank 函數(shù)本身沒有參數(shù)。這是因?yàn)?rank 函數(shù)不對任何參數(shù)執(zhí)行任何計(jì)算。相反，rank 函數(shù)只是著眼于行集合--以及每一行在集合中的位置--正如排序方式所定義的那樣。那么，我們?nèi)绾螢檫@個(gè)函數(shù)定義集合和排序方式呢？兩者都是用 OVER 子句定義的。在這個(gè)例子中，因?yàn)槲覀円诿總€(gè)部門內(nèi)進(jìn)行排列，因此我們通過按部門劃分分區(qū)來定義集合。這樣做的效果是可以確保只有那些在 dept 列具有相等值的行才參與排列。對排列函數(shù)的而言， 分區(qū)（partition） 和 集合（set）這兩個(gè)術(shù)語是等價(jià)的。在 PARTITION 子句后面，我們有一個(gè) ORDER BY 子句，這個(gè)子句定義了分區(qū)內(nèi)的排序方式。在這里，我們想將高薪排在前面，因此我們將排序方式定義為降序。除了指定降序以外，我們還指定 NULLS LAST。在 SQL 中，空值排在前面，意即空值顯得要大于所有其他非空的值。這就給排列帶來了問題，因?yàn)槲覀兛赡懿⒉幌雽榭盏男剿旁谇懊?。因此，我們使?NULLS LAST 子句來更改默認(rèn)的排序方式，這樣就可以將空值排在后面了。（注意，NULLS LAST 子句是在 DB2 V7 中引入的；不過，在 V6 中使用一個(gè) CASE 表達(dá)式來強(qiáng)加排序方式也是可以的。） 現(xiàn)在，讓我們看一下輸出。前 6 行都是 Department 1 的雇員，每一行都被賦予一個(gè)按薪水降序排列所得的名次。注意，在 Department 1 中，有兩個(gè)雇員的薪水都是 75000，這兩行都被賦予第二的名次。這是因?yàn)?rank 函數(shù)提供了一種 “奧林匹克式”的排列方式，在這種方式中，兩個(gè)相等的值得到相等的名次。因?yàn)橛袃尚小敖Y(jié)在一起，同獲第二”，所以就沒有排在第 3 的行。相反，接下來的一行排在第 4，因?yàn)楦鶕?jù)排序方式，有 3 行嚴(yán)格地排在這一行之前。 對于 Department 2，注意其中有一個(gè)行具有為空的薪水。因?yàn)槲覀冎付?NULLS LAST，所以這一行被排在非空行的后面。如果我們沒有指定 NULLS LAST 的話，Department 2 中兩行的排列順序就要倒過來了。 到現(xiàn)在，您可能會(huì)問自己，在上面的例子中，其他兩個(gè)輸出列 denserank 和 rownumber 是什么呢？DB2 實(shí)際上有三個(gè)不同的排列函數(shù)。首先是 rank 函數(shù)，它提供了奧林匹克式的排列方式，這在前面已經(jīng)描述過了。其他兩個(gè)函數(shù)分別是 dense_rank和 row_number。Dense_rank 很像 rank，在這個(gè)函數(shù)中，“結(jié)”中的行排名是相等的。這兩個(gè)函數(shù)惟一的不同之處在于對跟在結(jié)后面的值的處理方式，在 Dense_rank函數(shù)中排名是按 1 遞增的（而不是像 rank 函數(shù)那樣按結(jié)中行的數(shù)量來遞增）。因而，這里不會(huì)出現(xiàn)排名有間隔的現(xiàn)象（因此函數(shù)名中才用了“dense”）。雖然 Employee 11 的薪水在 rank 函數(shù)中獲得的名次是第 4，但是 denserank 函數(shù)返回的值是 3。 最后一列給出 row_number 函數(shù)的輸出。Row_number 也執(zhí)行一次排列，但是當(dāng)碰到有結(jié)的情況時(shí)，結(jié)中的行要進(jìn)行任意的（也就是說，不是確定的）排序。這在對有重復(fù)值的數(shù)據(jù)進(jìn)行分析時(shí)很有用。row_number 函數(shù)一個(gè)有趣的方面是它是惟一不要求提供排序方式的排列函數(shù)。如果在沒有指定排序方式的情況下調(diào)用 row_number 函數(shù)，則所有的行都將被當(dāng)作結(jié)中的行來對待，因而這些行是任意排序的。這對于在輸出中給行排序來說很有用。

在 DB2 Version 7 中還引入了許多其他的 OLAP 函數(shù)。在引入這些函數(shù)之前，DB2 支持兩類基本的函數(shù)，分別是 標(biāo)量（scalar）函數(shù)和 聚集（aggregate） 函數(shù)。標(biāo)量函數(shù)是那些對單個(gè)行中的值進(jìn)行操作、并在每一行返回一個(gè)結(jié)果的函數(shù)。arithmetic 和 string 函數(shù)就是標(biāo)量函數(shù)的例子。例如，下面的查詢使用了 DIGITS 標(biāo)量函數(shù)來格式化 salary 字段。#p#

該函數(shù)對每一行執(zhí)行結(jié)果計(jì)算，并且該計(jì)算只使用當(dāng)前行中的 salary 值。
select empnum, salary, digits(salary) as digits from emptab where dept = 1;
EMPNUM SALARY DIGITS
----------- ----------- ----------
1 50000 0000050000
2 75000 0000075000
5 52000 0000052000

聚集函數(shù)（也叫 列 或 集合 函數(shù)）的行為有所不同。聚集函數(shù)對一組行進(jìn)行操作，并在輸出中將這些行聚集（或者合并）到單個(gè)的行中。聚集函數(shù)的一個(gè)例子是 sum 函數(shù)，這個(gè)函數(shù)計(jì)算一組值的和，并將這個(gè)和放入一個(gè)結(jié)果行中。例如，下面的查詢計(jì)算每個(gè)部門中所有雇員薪水的總和。GROUP BY 子句用于表明要聚集的集合（或分區(qū)）是各個(gè)部門中所有行的集合。對于每個(gè)部門都返回一行，給出該部門中所有薪水的總和。
select dept, sum(salary) as sum from emptab group by dept;
DEPT SUM
----------- -----------
1 383000
2 51000
3 209000
- 84000 #p#

在 DB2 V7 中引入的 OLAP 函數(shù)引入了一類新的函數(shù)，我們稱之為 標(biāo)量-聚集（scalar-aggregate） 函數(shù)。這些函數(shù)像標(biāo)量函數(shù)，因?yàn)樗鼈円彩窃诿恳恍蟹祷貑蝹€(gè)的值，但是它們也像聚集函數(shù)，因?yàn)樗鼈円獙σ粋€(gè)集合中多個(gè)行中的值執(zhí)行計(jì)算，以計(jì)算出結(jié)果。下面的標(biāo)量-聚集函數(shù)執(zhí)行的是與 sum 聚集函數(shù)一樣的計(jì)算，但是這個(gè)函數(shù)返回的是沒有合并行的結(jié)果：
select dept, salary, sum(salary) over (partition by dept) as deptsum, avg(salary) over (partition by dept) as avgsal, count(*) over (partition by dept) as deptcount, max(salary) over (partition by dept) as maxsal from emptab;
DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL
----- ------- - ------- ------- --------- --------
1 50000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 52000 383000 63833 6 78000
1 78000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 53000 383000 63833 6 78000
2 - 51000 51000 2 51000
2 51000 51000 51000 2 51000
3 79000 209000 69666 3 79000
3 55000 209000 69666 3 79000
3 75000 209000 69666 3 79000
- - 84000 84000 2 84000
- 84000 84000 84000 2 84000 #p#
注意，該查詢沒有包含 GROUP BY 子句。相反，該查詢使用了 OVER 子句來對數(shù)據(jù)分區(qū)，以便 sum 函數(shù)對同一部門中的行執(zhí)行計(jì)算，并在每一個(gè)部門內(nèi)的每一行中返回該部門所有薪水的總和。按慣例，為了在每一行中包括那樣的聚集結(jié)果，我們需要使用一個(gè)聯(lián)合，但是現(xiàn)在 OLAP 函數(shù)為此提供了更簡易的模式。我們推薦使用這種類型的函數(shù)作為 報(bào)告 函數(shù)，因?yàn)檫@種函數(shù)是對集合計(jì)算總和，并在每一行中都報(bào)告一次結(jié)果的。我曾經(jīng)在前面和后面的例子中使用了 SUM， 但是大部分聚集函數(shù)（例如 AVG、MIN、MAX、STDEV，等等）都使用 OVER 子句。在 DEPTSUM 列右邊的其他列顯示了平均薪水、部門中雇員的人數(shù)以及部門中的最高薪水。惟一不支持作為標(biāo)量-聚集函數(shù)的聚集函數(shù)是線性回歸函數(shù)。

這些報(bào)告函數(shù)一個(gè)強(qiáng)大的用處就是計(jì)算比率和百分比。要計(jì)算某個(gè)雇員的薪水占整個(gè)部門薪水總和的百分比，只需簡單地用報(bào)告的薪水總和去除該雇員的薪水。
select empnum, dept, salary, sum(salary) over (partition by dept) as deptsum, decimal(salary,10,2) / sum(salary) over(partition by dept) as percentage from emptab;
EMPNUM DEPT SALARY DEPTSUM PERCENTAGE
------ ----- -------- ----------- --------
1 1 50000 383000 0.1305
2 1 75000 383000 0.1958
5 1 52000 383000 0.1357
6 1 78000 383000 0.2036
7 1 75000 383000 0.1958 #p#
……
如果我們在要進(jìn)行聚集的集合中引入一個(gè)排序方式，會(huì)出現(xiàn)什么情況呢？答案是，我們不處理一個(gè) 報(bào)告（ reporting ） 函數(shù)，而是處理一個(gè) 累加（ cumulative ）函數(shù)。累加函數(shù)是一種標(biāo)量-聚集函數(shù)，它對當(dāng)前行 以及集合中當(dāng)前行之前（相對排序方式而言）的所有行進(jìn)行操作。讓我們?yōu)檫@個(gè)例子使用一個(gè)不同的表。假設(shè)我們有一個(gè)這樣的表，它記有當(dāng)前歷年的每月銷售業(yè)績。那么，我們?nèi)绾斡?jì)算每個(gè)月的 年至今日（year-to-date） 銷售數(shù)字呢？這里，我們要計(jì)算每月銷售的累加和。我們可以這樣做：
select date, sales, sum(sales) over (order by date) as cume_sum, count(*) over (order by date) as setcount from sales where year(date) = 2000;
DATE SALES CUME_SUM SETCOUNT
---------- ------------ ------------ ---------
01/01/2000 968871.12 968871.12 1
02/01/2000 80050.05 1048921.17 2
03/01/2000 757866.14 1806787.31 3
04/01/2000 58748.13 1865535.44 4
05/01/2000 40711.69 1906247.13 5
06/01/2000 241187.78 2147434.91 6
07/01/2000 954924.16 3102359.07 7
08/01/2000 502822.96 3605182.03 8
09/01/2000 97201.45 3702383.48 9
10/01/2000 853999.45 4556382.93 10
11/01/2000 358775.59 4915158.52 11
12/01/2000 437513.35 5352671.87 12
每月銷售量和到當(dāng)前日期的累加銷售量 #p#

讓我們看一下結(jié)果。對于第一行，累加和就等于這一行的銷售量。對于第二行，累加和等于一月份和二月份銷售量的和（968871.12 + 80050.05 = 1048921.17）。類似地，第三行的結(jié)果是一月份、二月份和三月份銷售量的和。在 CUME_SUM 列右邊的列執(zhí)行一個(gè)累加計(jì)數(shù)，給出在集合中行的數(shù)量。例如，第一行只有一行被求和（也就是該行本身），第二行有兩行被求和（該行本身以及前一行），依此類推。上面的圖給出了銷售數(shù)字以及在前面的查詢中計(jì)算出的累加和的圖形化表示。

如果我們有多年的數(shù)據(jù)，并且想計(jì)算 每一年內(nèi) 到當(dāng)月的累加和，那么我們也可以像下面這樣使用 PARTITION BY 子句：
select date, sales, sum(sales) over (partition by year(date) order by month(date)) as cume_sum from sales where year(date) >= 2000;
DATE SALES CUME_SUM
---------- ------------ -----------
01/01/2000 968871.12 968871.12
02/01/2000 80050.05 1048921.17
03/01/2000 757866.14 1806787.31
04/01/2000 58748.13 1865535.44
05/01/2000 40711.69 1906247.13
06/01/2000 241187.78 2147434.91
07/01/2000 954924.16 3102359.07
08/01/2000 502822.96 3605182.03
09/01/2000 97201.45 3702383.48
10/01/2000 853999.45 4556382.93
11/01/2000 358775.59 4915158.52
12/01/2000 437513.35 5352671.87
01/01/2001 476851.71 476851.71
02/01/2001 593768.12 1070619.83
03/01/2001 818597.97 1889217.80 #p#
...

使用 PARTITION BY 子句計(jì)算累加和

現(xiàn)在，請注意 2001年1月那一行是如何重置的。這是因?yàn)槿掌诎茨陝澐至朔謪^(qū)，而在 2001年內(nèi) 沒有在一月份之前的行，因此 cume_sum 就等于一月份的銷售量。這個(gè)例子還演示了另一件有趣的事情，那就是 OVER 子句使用的參數(shù)可以是表達(dá)式，而不僅僅是列值。

在更復(fù)雜的例子中，甚至可能會(huì)將其他的聚集函數(shù)嵌入到標(biāo)量-聚集函數(shù)調(diào)用中。這很有用，因?yàn)樵趫?zhí)行分析之前先執(zhí)行某種類型的聚集（例如，將銷售量聚集到月的層次上）是十分常見的。這就引發(fā)了下面的問題：何時(shí)處理標(biāo)量-聚集函數(shù)？答案是在處理選擇清單中剩下的部分時(shí)處理這些函數(shù)。通常，一個(gè)查詢的處理順序是這樣的：
From 子句
Where 子句
Group By 子句
Having 子句

選擇清單

您可以看到，選擇清單是在查詢的所有其他部分處理完之后才被處理的。這意味著如果您有謂語（在 WHERE 或 HAVING 子句中），或者您有任何作為 GROUP BY 子句結(jié)果的聚集，那么在處理標(biāo)量-聚集函數(shù)之前首先要應(yīng)用這些東西。例如，讓我們看下面的查詢： #p#
select year(date) as year, sum(sales) as sum, sum(sum(sales)) over (order by year(date)) as cume_sum from sales where year(date) >= 1995 group by year(date);
YEAR SUM CUME_SUM
----------- ------------- ------------
1995 7731162.39 7731162.39
1996 4127017.98 11858180.37
1997 7211584.76 19069765.13
1998 4149296.50 23219061.63
1999 6278023.54 29497085.17
2000 5352671.87 34849757.04
2001 5736777.81 40586534.85

對一個(gè)聚集的累加和

在這個(gè)例子中，我們訪問表（在 FROM 子句中指定）并應(yīng)用 WHERE 子句，然后應(yīng)用 GROUP BY 子句并計(jì)算每年的銷售總量。最后，我們處理選擇清單，包括所有的標(biāo)量-聚集函數(shù)。

這里還要講一點(diǎn)。因?yàn)闃?biāo)量-聚集函數(shù)是在 WHERE 子句 之后處理的，因此在一個(gè)謂語中引用標(biāo)量-聚集函數(shù)是不可能的。相反，如果您想這么做，您就必須 嵌套 標(biāo)量-聚集函數(shù)調(diào)用，要么是嵌套在一個(gè)公共表表達(dá)式內(nèi)，要么是嵌套在一個(gè)嵌套查詢內(nèi)。這在執(zhí)行返回前 n 行結(jié)果的查詢時(shí)變得很有用。

一個(gè)這樣例子就是編寫一個(gè)用于選擇具有最高銷售總量的3年的查詢。我們可以通過對每年的銷售量排列、然后選擇名次為 3 或者更小的行這種方法來做這件事。
with ranked_years (year, sum, rank) as (select year(date) as year, sum(sales) as sum, rank() over (order by sum(sales) desc) as rank from sales group by year(date) ) select year, sum, rank from ranked_years where rank <= 3;
YEAR SUM RANK
----------- ------------- -------
1995 7731162.39 1
1997 7211584.76 2
1999 6278023.54 3

Top n查詢的例子

在這個(gè)例子中您可以看到，我們有一個(gè)公共表表達(dá)式，它執(zhí)行聚集來計(jì)算每年的銷售總量，然后對銷售總量進(jìn)行排列。接著，外圍的選擇使用這個(gè)結(jié)果表并添加一個(gè)謂語，使得查詢只返回那些名次 <=3（也就是銷售總量排在前3名）的行。要計(jì)算中數(shù)、百分位以及其他分布統(tǒng)計(jì)，也可以執(zhí)行類似的查詢。
我希望至此我已經(jīng)傳達(dá)了這些新 OLAP 函數(shù)是什么以及如何使用它們的大致信息。實(shí)際上，有關(guān)這些函數(shù)的內(nèi)容比我在這里描述的要多得多。因此，敬請關(guān)注另一篇更詳細(xì)介紹這些函數(shù)的文章。
我想告訴你的是，DB2 已經(jīng)使用這些 OLAP 函數(shù)的實(shí)現(xiàn)擴(kuò)展了關(guān)系模型，因而現(xiàn)在關(guān)系模型就可以理解相對于數(shù)據(jù)集合的 排序方式。如果您曾經(jīng)試過編制牽涉到排序方式的查詢，您就知道這些查詢可以變得多么的困難和復(fù)雜（即使是像中數(shù)這樣的簡單查詢也是如此）。OLAP 函數(shù)為您提供了可以高效、簡明地編制那樣的查詢的工具。隨著對 DBMS 的需求的日益增長，顯然必須將傳統(tǒng)的關(guān)系模型加以擴(kuò)展,以便能夠處理那些越來越復(fù)雜的分析，而這些函數(shù)正是 DB2 打破局限的一個(gè)例子。