在線分析處理函數（OLAP）是DB2中擴展了關系模型、使關系模型能夠理解行集合內的排序方式的重要函數之一，本文將為您詳細分析OLAP函數，並附相關實例，供您參考，希望對您有所幫助。
排列函數：第一類引入到 DB2 中的 OLAP 函數是 排列（ranking）函數，它們是在 DB2 Version 6 中引入的。這些排列函數提供了定義一個集合（使用 PARTITION 子句），然後根據某種排序方式對這個集合內的元素進行排列的能力。例如，假設我們有一個雇員表，現在要對每個部門內的雇員薪水進行排列。要實現這一點，我們需要一個函數調用，這個函數調用可以完成以下工作：
將分區（集合）定義為各個部門，將集合內的排序方式定義為按薪水排序。 按照慣例，我們一般會將薪水高的排在前面，所以我們將指定一個對薪水的降序排序方式。下面的例子展示了這個查詢的查詢和輸出。
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 函數，則所有的行都將被當作結中的行來對待，因而這些行是任意排序的。這對於在輸出中給行排序來說很有用。
在 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
聚集函數（也叫 列 或 集合 函數）的行為有所不同。聚集函數對一組行進行操作，並在輸出中將這些行聚集（或者合並）到單個的行中。聚集函數的一個例子是 sum 函數，這個函數計算一組值的和，並將這個和放入一個結果行中。例如，下面的查詢計算每個部門中所有雇員薪水的總和。GROUP BY 子句用於表明要聚集的集合（或分區）是各個部門中所有行的集合。對於每個部門都返回一行，給出該部門中所有薪水的總和。
select dept, sum(salary) as sum from emptab group by dept;
DEPT SUM
----------- -----------
1 383000
2 51000
3 209000
- 84000
在 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
注意，該查詢沒有包含 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
……
如果我們在要進行聚集的集合中引入一個排序方式，會出現什麼情況呢？答案是，我們不處理一個 報告（ 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 子句使用的參數可以是表達式，而不僅僅是列值。
在更復雜的例子中，甚至可能會將其他的聚集函數嵌入到標量-聚集函數調用中。這很有用，因為在執行分析之前先執行某種類型的聚集（例如，將銷售量聚集到月的層次上）是十分常見的。這就引發了下面的問題：何時處理標量-聚集函數？答案是在處理選擇清單中剩下的部分時處理這些函數。通常，一個查詢的處理順序是這樣的：
From 子句
Where 子句
Group By 子句
Having 子句
選擇清單
您可以看到，選擇清單是在查詢的所有其他部分處理完之後才被處理的。這意味著如果您有謂語（在 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 打破局限的一個例子。
