db2 分析函数的一篇文章

--OLAP：DB2® Universal Database 中引入的在线分析处理（OLAP）函数，这些函数扩展了关系模型、使关系模型能够理解行集合内的排序方式（ordering）。ITPUB个人空间DB,bFA){Sx
排列函数第一类引入到 DB2 中的 OLAP 函数是 排列（ranking）函数，它们是在 DB2 Version 6 中引入的。这些排列函数提供了定义一个集合（使用 PARTITION 子句），然后根据某种排序方式对这个集合内的元素进行排列的能力。例如，假设我们有一个雇员表，现在要对每个部门内的雇员薪水进行排列。要实现这一点，我们需要一个函数调用，这个函数调用可以完成以下工作：ITPUB个人空间??|V'R
将分区（集合）定义为各个部门，将集合内的排序方式定义为按薪水排序。 按照惯例，我们一般会将薪水高的排在前面，所以我们将指定一个对薪水的降序排序方式。下面的例子展示了这个查询的查询和输出。
nRQIABWJo$E-[Q0select 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;
(p$t?!@ Z0ITPUB个人空间0Z$n7l!|Q
EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER
rmYS3i g0------ ---- ------ ---- --------- ---------
]f v up06 1 78000 1 1 1ITPUB个人空间
][]:zUTqL
2 1 75000 2 2 2
:W5^r&l;O"vO }07 1 75000 2 2 3ITPUB个人空间u4t'}~1\ G'B9x
11 1 53000 4 3 4ITPUB个人空间{+pDCHV5ZI
5 1 52000 5 4 5
N(N8AX%OM0`)j }S01 1 50000 6 5 6
"?no-nPDrL%C['{0--------------------------------------------------
H:O.x*wU&p:oM[09 2 51000 1 1 1ITPUB个人空间6]Y:zf(MB+R
4 2 - 2 2 2ITPUB个人空间`3I}7O!G4U
注意，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 函数，则所有的行都将被当作结中的行来对待，因而这些行是任意排序的。这对于在输出中给行排序来说很有用。ITPUB个人空间|cUM7z
在 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
4oLo"]1D/D7f:yi*w0----------- ----------- ----------ITPUB个人空间z:A6z-Q+FO5d
1 50000 0000050000ITPUB个人空间T3}@~?'n+Uq
2 75000 0000075000
Ht*eeR*i$_(p7q(^05 52000 0000052000
3E Zd\|B0DRY$tHn/Kk0聚集函数（也叫 列 或 集合 函数）的行为有所不同。聚集函数对一组行进行操作，并在输出中将这些行聚集（或者合并）到单个的行中。聚集函数的一个例子是 sum 函数，这个函数计算一组值的和，并将这个和放入一个结果行中。例如，下面的查询计算每个部门中所有雇员薪水的总和。GROUP BY 子句用于表明要聚集的集合（或分区）是各个部门中所有行的集合。对于每个部门都返回一行，给出该部门中所有薪水的总和。
\|}sB0select dept, sum(salary) as sum from emptab group by dept;
8D7YxD.U p7kqk0DEPT SUMITPUB个人空间Lj(X PlE'Se
----------- -----------ITPUB个人空间6H*~2}gN{nv5I%t
1 383000ITPUB个人空间]~f?w9NU9W2w
2 51000ITPUB个人空间Q$sBw
U9O?7b
3 209000
$~T(C3o6r0T:ei8ym0- 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;ITPUB个人空间 ZC-c)_~_|Y3n3\"h
DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL
H.f@:y{Jl1UW0----- ------- - ------- ------- --------- --------
w2M9Vv/?1Kz,Ml01 50000 383000 63833 6 78000ITPUB个人空间'ur+bg3l
1 75000 383000 63833 6 78000ITPUB个人空间8s3cw/yV4O x&D
1 52000 383000 63833 6 78000ITPUB个人空间m%kXp }j
1 78000 383000 63833 6 78000ITPUB个人空间0yqO'u;l
\
1 75000 383000 63833 6 78000ITPUB个人空间Gr"@wRt%S
1 53000 383000 63833 6 78000ITPUB个人空间zl+w2{,a
2 - 51000 51000 2 51000
M$^
I(Su_^02 51000 51000 51000 2 51000
%hk2WQ{2u ~5la`03 79000 209000 69666 3 79000
6aVXT)Tf \03 55000 209000 69666 3 79000
Z0B"e,fc03 75000 209000 69666 3 79000ITPUB个人空间:H$a0~{rrPmJ7Zv
- - 84000 84000 2 84000ITPUB个人空间!QH4p+J5lL
- 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;
5sEBrOj"u@+e0EMPNUM DEPT SALARY DEPTSUM PERCENTAGEITPUB个人空间?Zrf7@rmy8}^
------ ----- -------- ----------- --------

1 1 50000 383000 0.1305ITPUB个人空间$V5_"K,D.c0qje
2 1 75000 383000 0.1958ITPUB个人空间%g?ba2F7m
5 1 52000 383000 0.1357ITPUB个人空间RP|&CjPu
6 1 78000 383000 0.2036ITPUB个人空间ITL)`
X;G&P2il:X4{
7 1 75000 383000 0.1958
,HZNUI]r(f `0……ITPUB个人空间2R+H Lvd"w
如果我们在要进行聚集的集合中引入一个排序方式，会出现什么情况呢？答案是，我们不处理一个报告（ 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;ITPUB个人空间^&~#q!^2Jv6SB
DATE SALES CUME_SUM SETCOUNTITPUB个人空间0y6JLQ-vE/k3~;r
---------- ------------ ------------ ---------ITPUB个人空间h%l ]%I)V GY!t
01/01/2000 968871.12 968871.12 1
(S0d9jm}bxJ002/01/2000 80050.05 1048921.17 2ITPUB个人空间3EKz'XUc1J
03/01/2000 757866.14 1806787.31 3
&p.{-~Ic004/01/2000 58748.13 1865535.44 4ITPUB个人空间*U)~1Xx;_&r${E
05/01/2000 40711.69 1906247.13 5ITPUB个人空间l,[5aZ.uAV
06/01/2000 241187.78 2147434.91 6
B;o@`+]O$Jr007/01/2000 954924.16 3102359.07 7
~7} ?+E;Fnt008/01/2000 502822.96 3605182.03 8ITPUB个人空间o]?I"}&R%U8T,q]*X
09/01/2000 97201.45 3702383.48 9ITPUB个人空间 i n/j}2d6C(M
10/01/2000 853999.45 4556382.93 10ITPUB个人空间m[b;|D@Z2X
11/01/2000 358775.59 4915158.52 11ITPUB个人空间B `ly^:Ij
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;ITPUB个人空间~`$R0m1D2v?!c&h7n8_
DATE SALES CUME_SUMITPUB个人空间8i/A'b4S zr
---------- ------------ -----------
$j+kkr3`'\-[001/01/2000 968871.12 968871.12ITPUB个人空间 U|![!CS5D8v
02/01/2000 80050.05 1048921.17ITPUB个人空间gEX0l-SP}
03/01/2000 757866.14 1806787.31ITPUB个人空间U9H7qs:n;w#O
04/01/2000 58748.13 1865535.44ITPUB个人空间.a
~4n4B [8K
05/01/2000 40711.69 1906247.13ITPUB个人空间5K8JYW
Ve"kqE
06/01/2000 241187.78 2147434.91ITPUB个人空间-|bB
I.OC
07/01/2000 954924.16 3102359.07
wJ-`e(rx008/01/2000 502822.96 3605182.03ITPUB个人空间O7b4e m4JU
09/01/2000 97201.45 3702383.48
tNi(sT010/01/2000 853999.45 4556382.93ITPUB个人空间,C'[y0WD7v:b
11/01/2000 358775.59 4915158.52
4NL7uH] x012/01/2000 437513.35 5352671.87ITPUB个人空间3~V [5uN:hl0UF
01/01/2001 476851.71 476851.71ITPUB个人空间j$@a6dK~ KQ`
02/01/2001 593768.12 1070619.83
z2N:skGc"\003/01/2001 818597.97 1889217.80ITPUB个人空间\Pk {8~
...

使用 PARTITION BY 子句计算累加和

现在，请注意 2001年1月那一行是如何重置的。这是因为日期按年划分了分区，而在 2001年内 没有在一月份之前的行，因此 cume_sum 就等于一月份的销售量。这个例子还演示了另一件有趣的事情，那就是 OVER 子句使用的参数可以是表达式，而不仅仅是列值。
t0q Zii t,N3l m]0在更复杂的例子中，甚至可能会将其他的聚集函数嵌入到标量-聚集函数调用中。这很有用，因为在执行分析之前先执行某种类型的聚集（例如，将销售量聚集到月的层次上）是十分常见的。这就引发了下面的问题：何时处理标量-聚集函数？答案是在处理选择清单中剩下的部分时处理这些函数。通常，一个查询的处理顺序是这样的：
A
[5o+E?/K ?#\0From 子句ITPUB个人空间+btk*Sur%H
Where 子句ITPUB个人空间4@"@yGcA
Group By 子句ITPUB个人空间
Z]8R%kic-}3Kq
Having 子句
kn3s%okQM1N7o+l/a&{ I0选择清单
&kE8G]:Dn0您可以看到，选择清单是在查询的所有其他部分处理完之后才被处理的。这意味着如果您有谓语（在 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);
/[|"Psx0YEAR SUM CUME_SUMITPUB个人空间&V;n-?[4A+PJ)[
----------- ------------- ------------ITPUB个人空间\ g%Ev1M2y_!cS
1995 7731162.39 7731162.39
,z:V1PpF!f*hh01996 4127017.98 11858180.37
'|*js#K%L)@,E r01997 7211584.76 19069765.13ITPUB个人空间#{3[O&`1o$`9b
1998 4149296.50 23219061.63
I.`t$[2_z8@-K01999 6278023.54 29497085.17ITPUB个人空间7FQ"q8lu
2000 5352671.87 34849757.04
"j/^?k:tY}02001 5736777.81 40586534.85

对一个聚集的累加和

在这个例子中，我们访问表（在 FROM 子句中指定）并应用 WHERE 子句，然后应用 GROUP BY 子句并计算每年的销售总量。最后，我们处理选择清单，包括所有的标量-聚集函数。

这里还要讲一点。因为标量-聚集函数是在 WHERE 子句 之后处理的，因此在一个谓语中引用标量-聚集函数是不可能的。相反，如果您想这么做，您就必须 嵌套 标量-聚集函数调用，要么是嵌套在一个公共表表达式内，要么是嵌套在一个嵌套查询内。这在执行返回前 n 行结果的查询时变得很有用。
dGMqO0ITPUB个人空间v8n m9s(u6P`l
一个这样例子就是编写一个用于选择具有最高销售总量的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;
Jy7V XA.e b6eY0YEAR SUM RANKITPUB个人空间c.}/F"lM2TH
----------- ------------- -------
k%n7H6Fl3X+P01995 7731162.39 1
(l$DNK4]01997 7211584.76 2
%W(Ha3rCy"I8e01999 6278023.54 3

Top n查询的例子

在这个例子中您可以看到，我们有一个公共表表达式，它执行聚集来计算每年的销售总量，然后对销售总量进行排列。接着，外围的选择使用这个结果表并添加一个谓语，使得查询只返回那些名次 <=3（也就是销售总量排在前3名）的行。要计算中数、百分位以及其他分布统计，也可以执行类似的查询。

我希望至此我已经传达了这些新 OLAP 函数是什么以及如何使用它们的大致信息。实际上，有关这些函数的内容比我在这里描述的要多得多。因此，敬请关注另一篇更详细介绍这些函数的文章。

我想告诉你的是，DB2 已经使用这些 OLAP 函数的实现扩展了关系模型，因而现在关系模型就可以理解相对于数据集合的 排序方式。如果您曾经试过编制牵涉到排序方式的查询，您就知道这些查询可以变得多么的困难和复杂（即使是像中数这样的简单查询也是如此）。OLAP 函数为您提供了可以高效、简明地编制那样的查询的工具。随着对 DBMS 的需求的日益增长，显然必须将传统的关系模型加以扩展,以便能够处理那些越来越复杂的分析，而这些函数正是 DB2 打破局限的一个例子。