经验丰富的 DB2 开发人员 Bob Lyle 非常粗略地概述了 DB2 Universal Database 中强大的 OLAP 函数，并提供了关于如何使用这些函数的例子。
当我出席在佛罗伦萨召开的 IDUG EMEA 时，我惊讶地发现很少有用户对在 DB2® Universal Database 中引入的在线分析处理（OLAP）函数有较多的了解。虽然从表面上看这些函数只是一些简单的新函数，然而实际上它们是极其强大的。这种强大是建立在这些函数扩展了关系模型、使关系模型能够理解行集合内的排序方式（ordering）这一基础之上的。在此之前，如果您想执行任何与排序方式（按日期排序或其他方式的排序）有关的分析，您就不得不编写非常复杂的查询。现在，有了 OLAP 函数，这种复杂性大大减少了。在本文中，我希望对某些已经实现的函数给出一个非常粗略的概述，并提供一些关于如何使用这些函数的例子。本文中包括的所有函数都已经在 OLAP 修订案中标准化成 SQL99 标准。
排列函数
第一类引入到 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 函数，则所有的行都将被当作结中的行来对待，因而这些行是任意排序的。这对于在输出中给行排序来说很有用。
其他 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 子句使用的参数可以是表达式，而不仅仅是列值。在更复杂的例子中，甚至可能会将其他的聚集函数嵌入到标量-聚集函数调用中。这很有用，因为在执行分析之前先执行某种类型的聚集（例如，将销售量聚集到月的层次上）是十分常见的。这就引发了下面的问题：何时处理标量-聚集函数？答案是在处理选择清单中剩下的部分时处理这些函数。通常，一个查询的处理顺序是这样的：
- 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 打破局限的一个例子。
***************************************************************************************
另外,附上一些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 |