經驗豐富的 DB2 開發人員 Bob Lyle 非常粗略地概述了 DB2 Universal Database 中強大的 OLAP 函數，并提供了關于如何使用這些函數的例子。

當我出席在佛羅倫薩召開的 IDUG EMEA 時，我驚訝地發現很少有用戶對在 DB2? Universal Database 中引入的在線分析處理（OLAP）函數有較多的了解。雖然從表面上看這些函數只是一些簡單的新函數，然而實際上它們是極其強大的。這種強大是建立在這些函數擴展了關系模型、使關系模型能夠理解行集合內的排序方式（ordering）這一基礎之上的。在此之前，如果您想執行任何與排序方式（按日期排序或其他方式的排序）有關的分析，您就不得不編寫非常復雜的查詢。現在，有了 OLAP 函數，這種復雜性大大減少了。在本文中，我希望對某些已經實現的函數給出一個非常粗略的概述，并提供一些關于如何使用這些函數的例子。本文中包括的所有函數都已經在 OLAP 修訂案中標準化成 SQL99 標準。

排列函數

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

1. 將分區（集合）定義為各個部門
2. 將集合內的排序方式定義為按薪水排序。

按照慣例，我們一般會將薪水高的排在前面，所以我們將指定一個對薪水的降序排序方式。下面的例子展示了這個查詢的查詢和輸出。

?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 ?

展示排序的例子

首先，讓我們看一下函數調用。注意，rank 函數本身沒有參數。這是因為 rank 函數不對任何參數執行任何計算。相反，rank 函數只是著眼于行集合--以及每一行在集合中的位置--正如排序方式所定義的那樣。那么，我們如何為這個函數定義集合和排序方式呢？兩者都是用 OVER 子句定義的。在這個例子中，因為我們要在每個部門內進行排列，因此我們通過按部門劃分分區來定義集合。這樣做的效果是可以確保只有那些在 dept 列具有相等值的行才參與排列。對排列函數的而言， 分區（partition） 和 集合（set）這兩個術語是等價的。在 PARTITION 子句后面，我們有一個 ORDER BY 子句，這個子句定義了分區內的排序方式。在這里，我們想將高薪排在前面，因此我們將排序方式定義為降序。除了指定降序以外，我們還指定 NULLS LAST。在 SQL 中，空值排在前面，意即空值顯得要大于所有其他非空的值。這就給排列帶來了問題，因為我們可能并不想將為空的薪水排在前面。因此，我們使用 NULLS LAST 子句來更改默認的排序方式，這樣就可以將空值排在后面了。（注意，NULLS LAST 子句是在 DB2 V7 中引入的；不過，在 V6 中使用一個 CASE 表達式來強加排序方式也是可以的。）

現在，讓我們看一下輸出。前 6 行都是 Department 1 的雇員，每一行都被賦予一個按薪水降序排列所得的名次。注意，在 Department 1 中，有兩個雇員的薪水都是 75000，這兩行都被賦予第二的名次。這是因為 rank 函數提供了一種 “奧林匹克式”的排列方式，在這種方式中，兩個相等的值得到相等的名次。因為有兩行“結在一起，同獲第二”，所以就沒有排在第 3 的行。相反，接下來的一行排在第 4，因為根據排序方式，有 3 行嚴格地排在這一行之前。

對于 Department 2，注意其中有一個行具有為空的薪水。因為我們指定了 NULLS LAST，所以這一行被排在非空行的后面。如果我們沒有指定 NULLS LAST 的話，Department 2 中兩行的排列順序就要倒過來了。

到現在，您可能會問自己，在上面的例子中，其他兩個輸出列 denserank 和 rownumber 是什么呢？DB2 實際上有三個不同的排列函數。首先是 rank 函數，它提供了奧林匹克式的排列方式，這在前面已經描述過了。其他兩個函數分別是 dense_rank和 row_number。Dense_rank 很像 rank，在這個函數中，“結”中的行排名是相等的。這兩個函數惟一的不同之處在于對跟在結后面的值的處理方式，在 Dense_rank函數中排名是按 1 遞增的（而不是像 rank 函數那樣按結中行的數量來遞增）。因而，這里不會出現排名有間隔的現象（因此函數名中才用了“dense”）。雖然 Employee 11 的薪水在 rank 函數中獲得的名次是第 4，但是 denserank 函數返回的值是 3。

最后一列給出 row_number 函數的輸出。Row_number 也執行一次排列，但是當碰到有結的情況時，結中的行要進行任意的（也就是說，不是確定的）排序。這在對有重復值的數據進行分析時很有用。row_number 函數一個有趣的方面是它是惟一不要求提供排序方式的排列函數。如果在沒有指定排序方式的情況下調用 row_number 函數，則所有的行都將被當作結中的行來對待，因而這些行是任意排序的。這對于在輸出中給行排序來說很有用。

其他 OLAP函數

在 DB2 Version 7 中還引入了許多其他的 OLAP 函數。在引入這些函數之前，DB2 支持兩類基本的函數，分別是 標量（scalar）函數和 聚集（aggregate） 函數。標量函數是那些對單個行中的值進行操作、并在每一行返回一個結果的函數。arithmetic 和 string 函數就是標量函數的例子。例如，下面的查詢使用了 DIGITS 標量函數來格式化 salary 字段。該函數對每一行執行結果計算，并且該計算只使用當前行中的 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 ?...

展示 DIGITS 標量函數的例子

聚集函數（也叫 列 或 集合 函數）的行為有所不同。聚集函數對一組行進行操作，并在輸出中將這些行聚集（或者合并）到單個的行中。聚集函數的一個例子是 sum 函數，這個函數計算一組值的和，并將這個和放入一個結果行中。例如，下面的查詢計算每個部門中所有雇員薪水的總和。GROUP BY 子句用于表明要聚集的集合（或分區）是各個部門中所有行的集合。對于每個部門都返回一行，給出該部門中所有薪水的總和。

?

?select dept, sum(salary) as sum ??????? from emptab ??????? group by dept;? ?DEPT??????? SUM? ?----------- ----------- ?????????? 1????? 383000 ?????????? 2?????? 51000 ?????????? 3????? 209000 ?????????? -?????? 84000

展示 SUM 聚集函數的例子

在 DB2 V7 中引入的 OLAP 函數引入了一類新的函數，我們稱之為 標量-聚集（scalar-aggregate） 函數。這些函數像標量函數，因為它們也是在每一行返回單個的值，但是它們也像聚集函數，因為它們要對一個集合中多個行中的值執行計算，以計算出結果。下面的標量-聚集函數執行的是與 sum 聚集函數一樣的計算，但是這個函數返回的是沒有合并行的結果：
?

?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

展示 SUM 報告函數的例子

注意，該查詢沒有包含 GROUP BY 子句。相反，該查詢使用了 OVER 子句來對數據分區，以便 sum 函數對同一部門中的行執行計算，并在每一個部門內的每一行中返回該部門所有薪水的總和。按慣例，為了在每一行中包括那樣的聚集結果，我們需要使用一個聯合，但是現在 OLAP 函數為此提供了更簡易的模式。我們推薦使用這種類型的函數作為 報告 函數，因為這種函數是對集合計算總和，并在每一行中都報告一次結果的。我曾經在前面和后面的例子中使用了 SUM， 但是大部分聚集函數（例如 AVG、MIN、MAX、STDEV，等等）都使用 OVER 子句。在 DEPTSUM 列右邊的其他列顯示了平均薪水、部門中雇員的人數以及部門中的最高薪水。惟一不支持作為標量-聚集函數的聚集函數是線性回歸函數。

這些報告函數一個強大的用處就是計算比率和百分比。要計算某個雇員的薪水占整個部門薪水總和的百分比，只需簡單地用報告的薪水總和去除該雇員的薪水。

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 ??? 11???? 1??? 53000????? 383000???? 0.1383 ???? 4???? 2??????? -?????? 51000? ???? 9???? 2??? 51000?????? 51000???? 1.0000 ???? 8???? 3??? 79000????? 209000???? 0.3779 ??? 10???? 3??? 55000????? 209000???? 0.2631 ??? 12???? 3??? 75000????? 209000???? 0.3588 ???? 0???? -??????? -?????? 84000? ???? 3???? -??? 84000?????? 84000???? 1.0000

百分比的例子

??????用占總薪水支出的百分比表示的雇員薪水

如果我們在要進行聚集的集合中引入一個排序方式，會出現什么情況呢？答案是，我們不處理一個 報告（reporting） 函數，而是處理一個 累加（cumulative）函數。累加函數是一種標量-聚集函數，它對當前行 以及集合中當前行之前（相對排序方式而言）的所有行進行操作。讓我們為這個例子使用一個不同的表。假設我們有一個這樣的表，它記有當前歷年的每月銷售業績。那么，我們如何計算每個月的 年至今日（year-to-date） 銷售數字呢？這里，我們要計算每月銷售的累加和。我們可以這樣做：

?

?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

計算累加和的例子

??????????????????????每月銷售量和到當前日期的累加銷售量

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

如果我們有多年的數據，并且想計算 每一年內 到當月的累加和，那么我們也可以像下面這樣使用 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 ?...

使用 PARTITION BY 子句計算累加和

現在，請注意 2001年1月那一行是如何重置的。這是因為日期按年劃分了分區，而在 2001年內 沒有在一月份之前的行，因此 cume_sum 就等于一月份的銷售量。這個例子還演示了另一件有趣的事情，那就是 OVER 子句使用的參數可以是表達式，而不僅僅是列值。在更復雜的例子中，甚至可能會將其他的聚集函數嵌入到標量-聚集函數調用中。這很有用，因為在執行分析之前先執行某種類型的聚集（例如，將銷售量聚集到月的層次上）是十分常見的。這就引發了下面的問題：何時處理標量-聚集函數？答案是在處理選擇清單中剩下的部分時處理這些函數。通常，一個查詢的處理順序是這樣的：

1. From 子句
2. Where 子句
3. Group By 子句
4. Having 子句
5. 選擇清單

您可以看到，選擇清單是在查詢的所有其他部分處理完之后才被處理的。這意味著如果您有謂語（在 WHERE 或 HAVING 子句中），或者您有任何作為 GROUP BY 子句結果的聚集，那么在處理標量-聚集函數之前首先要應用這些東西。例如，讓我們看下面的查詢：
?

?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

對一個聚集的累加和

在這個例子中，我們訪問表（在 FROM 子句中指定）并應用 WHERE 子句，然后應用 GROUP BY 子句并計算每年的銷售總量。最后，我們處理選擇清單，包括所有的標量-聚集函數。

這里還要講一點。因為標量-聚集函數是在 WHERE 子句 之后處理的，因此在一個謂語中引用標量-聚集函數是不可能的。相反，如果您想這么做，您就必須 嵌套 標量-聚集函數調用，要么是嵌套在一個公共表表達式內，要么是嵌套在一個嵌套查詢內。這在執行返回前 n 行結果的查詢時變得很有用。一個這樣例子就是編寫一個用于選擇具有最高銷售總量的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查詢的例子

在這個例子中您可以看到，我們有一個公共表表達式，它執行聚集來計算每年的銷售總量，然后對銷售總量進行排列。接著，外圍的選擇使用這個結果表并添加一個謂語，使得查詢只返回那些名次 <=3（也就是銷售總量排在前3名）的行。要計算中數、百分位以及其他分布統計，也可以執行類似的查詢。

我希望至此我已經傳達了這些新 OLAP 函數是什么以及如何使用它們的大致信息。實際上，有關這些函數的內容比我在這里描述的要多得多。因此，敬請關注另一篇更詳細介紹這些函數的文章。

我想告訴你的是，DB2 已經使用這些 OLAP 函數的實現擴展了關系模型，因而現在關系模型就可以理解相對于數據集合的 排序方式。如果您曾經試過編制牽涉到排序方式的查詢，您就知道這些查詢可以變得多么的困難和復雜（即使是像中數這樣的簡單查詢也是如此）。OLAP 函數為您提供了可以高效、簡明地編制那樣的查詢的工具。隨著對 DBMS 的需求的日益增長，顯然必須將傳統的關系模型加以擴展,以便能夠處理那些越來越復雜的分析，而這些函數正是 DB2 打破局限的一個例子。

***************************************************************************************

另外,附上一些OLAP函數的簡介.

DB2 Cube Views 支持由 DB2 UDB 提供的下列 OLAP 函數：

RANK

對行排序并對每行指定等級。等級定義為就排序而言唯一的之前行的數目加 1。如果兩行或多行的相對順序因為具有重復行值而不能確定，則會指定相同等級編號。如果有重復的行值，則分等級結果可能造成編號中有間隔。表 7 顯示哪些分等級結果來自一組樣本行值的 RANK 函數的示例。

RANK 函數的典型語法為：

RANK ( ) OVER (ORDER BY sort-key-expressionexpression-order)

其中 sort-key-expression 是要分等級的數據集，而 expression-order 是關鍵字（ASC 或 DESC），它按升序或降序次序對 sort-key-expression 的值排序。DB2 Cube Views 要求 sort-key-expression 充當現有量度而不是列或屬性。而且，DB2 Cube Views 不支持由 DB2 UDB 隨此函數提供的 PARTITION BY 子句。有關 RANK 函數的更多信息，可在“DB2 UDB 信息中心”中找到。

DENSERANK

對行排序并對每行指定等級。行的等級定義為確實在該行之前的行數加 1。因此，分等級結果將是連續的，在等級編號中沒有間隔。表 7 顯示哪些分等級結果來自一組樣本行值的 DENSERANK 函數的示例。

DENSERANK 函數的典型語法為：

DENSERANK ( ) OVER (ORDER BY sort-key-expressionexpression-order)

其中 sort-key-expression 是要分等級的數據集，而 expression-order 是關鍵字（ASC 或 DESC），它按升序或降序次序對 sort-key-expression 的值排序。DB2 Cube Views 要求 sort-key-expression 充當現有量度而不是列或屬性。而且，DB2 Cube Views 不支持由 DB2 UDB 隨此函數提供的 PARTITION BY 子句。有關 DENSERANK 函數的更多信息，可以在“DB2 UDB 信息中心”中找到。

ROWNUMBER

根據排序計算該行的順序行號，第一行從 1 開始。如果未指定 ORDER BY 子句，則以任何順序將行號指定給各行。

ROWNUMBER 函數的典型語法為：

ROWNUMBER ( ) OVER ([ORDER BY sort-key-expressionexpression-order])
 

其中 sort-key-expression 是要分等級的數據集，而 expression-order 是關鍵字（ASC 或 DESC），它按升序或降序次序對 sort-key-expression 的值排序。DB2 Cube Views 需要現有量度（而不是列或屬性）用作此函數的數據源。而且，DB2 Cube Views 不支持由 DB2 UDB 隨此函數提供的 PARTITION BY 子句。有關 ROWNUMBER 函數的更多信息，可以在“DB2 UDB 信息中心”中找到。

這些 OLAP 函數未列示在“SQL 表達式構建器”函數和常量列表中。

表 7. 使用 RANK 和 DENSERANK 函數對一組樣本值分等級的結果

行值	排序	來自 RANK 函數的分等級結果	來自 DENSERANK 函數的分等級結果
100	1	1	1
35	2	2	2
23	3	3	3
8	4	4	4
8	4	4	5
6	5	6	6