DB2中OLAP函数的示例剖析

在线剖析解决函数（OLAP）是中裁减了相关模型、使相关模型能够了解行汇合内的排序形式的关键函数之一，本文将为您具体剖析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 12 1 75000 2 2 27 1 75000 2 2 311 1 53000 4 3 45 1 52000 5 4 51 1 50000 6 5 6--------------------------------------------------9 2 51000 1 1 14 2 - 2 2 2 #p#留意，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 字段。#p#

该函数对每一行口头结果计算，并且该计算只经常使用行中的 salary 值。select empnum, salary, digits(salary) as digits from emptab where dept = 1;EMPNUM SALARY DIGITS----------- ----------- ----------1 50000 00000500002 75000 00000750005 52000 0000052000

汇集函数（也叫 列 或 汇合 函数）的行为有所不同。汇集函数对一组前启动操作，并在输入中将这些行汇集（或许兼并）到单个的行中。汇集函数的一个例子是 sum 函数，这个函数计算一组值的和，并将这个和放入一个结果行中。例如，上方的查问计算每个部门中一切雇员薪水的总和。GROUP BY 子句用于标明要汇集的汇合（或分区）是各个部门中一切行的汇合。关于每个部门都前往一行，给出该部门中一切薪水的总和。select dept, sum(salary) as sum from emptab group by dept;DEPT SUM----------- -----------1 3830002 510003 209000- 80 #p#

在 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 780001 75000 383000 63833 6 780001 52000 383000 63833 6 780001 78000 383000 63833 6 780001 75000 383000 63833 6 780001 53000 383000 63833 6 780002 - 51000 51000 2 510002 51000 51000 51000 2 510003 79000 209000 69666 3 790003 55000 209000 69666 3 790003 75000 209000 69666 3 79000- - 80 80 2 80- 80 80 80 2 80 #p#留意，该查问没有蕴含 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.13052 1 75000 383000 0.19585 1 52000 383000 0.13576 1 78000 383000 0.20367 1 75000 383000 0.1958 #p#……假设咱们在要启动汇集的汇合中引入一个排序形式，会出现什么状况呢？答案是，咱们不解决一个 报告（ 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 102/01/2000 80050.05 1048921.17 203/01/2000 757866.14 1806787.31 304/01/2000 58748.13 1865535.44 405/01/2000 40711.69 1906247.13 506/01/2000 241187.78 2147434.91 607/01/2000 954924.16 3102359.07 708/01/2000 502822.96 3605182.03 809/01/2000 97201.45 3702383.48 910/01/2000 853999.45 4556382.93 1011/01/2000 358775.59 4915158.52 1112/01/2000 437513.35 5352671.87 12每月开售量和到日期的累加开售量 #p#

让咱们看一下结果。关于第一行，累加和就等于这一行的开售量。关于第二行，累加和等于一月份和二月份开售量的和（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.1202/01/2000 80050.05 1048921.1703/01/2000 757866.14 1806787.3104/01/2000 58748.13 1865535.4405/01/2000 40711.69 1906247.1306/01/2000 241187.78 2147434.9107/01/2000 954924.16 3102359.0708/01/2000 502822.96 3605182.0309/01/2000 97201.45 3702383.4810/01/2000 853999.45 4556382.9311/01/2000 358775.59 4915158.5212/01/2000 437513.35 5352671.8701/01/2001 476851.71 476851.7102/01/2001 593768.12 1070619.8303/01/2001 818597.97 1889217.80 #p#...

经常使用 PARTITION BY 子句计算累加和

在更复杂的例子中，甚至或许会将其余的汇集函数嵌入到标量-汇集函数调用中。这很有用，由于在口头剖析之前先口头某种类型的汇集（例如，将开售量汇集到月的档次上）是十分经常出现的。这就引发了上方的疑问：何时解决标量-汇集函数？答案是在解决选用清单中剩下的局部时解决这些函数。通常，一个查问的解决顺序是这样的：From 子句Where 子句Group By 子句Having 子句

选用清单

您可以看到，选用清单是在查问的一切其余局部解决完之后才被解决的。这象征着假设您有谓语（在 WHERE 或 HAVING 子句中），或许您有任何作为 GROUP BY 子句结果的汇集，那么在解决标量-汇集函数之前首先要运行这些物品。例如，让咱们看上方的查问： #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.391996 4127017.98 11858180.371997 7211584.76 19069765.131998 4149296.50 23219061.631999 6278023.54 29497085.172000 5352671.87 34849757.042001 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 11997 7211584.76 21999 6278023.54 3

Top n查问的例子

在这个例子中您可以看到，咱们有一个公共表表白式，它口头汇集来计算每年的开售总量，而后对开售总量启动陈列。接着，中心的选用经常使用这个结果表并参与一个谓语，使得查问只前往那些名次 <=3（也就是开售总量排在前3名）的行。要计算中数、百分位以及其余散布统计，也可以口头相似的查问。我宿愿至此我曾经传播了这些新 OLAP 函数是什么以及如何经常使用它们的大抵消息。实践上，无关这些函数的内容比我在这里形容的要多得多。因此，敬请关注另一篇更具体引见这些函数的文章。我想通知你的是，DB2 曾经经常使用这些 OLAP 函数的成功裁减了相关模型，因此如今相关模型就可以了解相关于数据汇合的 排序形式。假设您曾经试过编制关涉到排序形式的查问，您就知道这些查问可以变得如许的艰巨和复杂（即使是像中数这样的便捷查问也是如此）。OLAP 函数为您提供了可以高效、扼要地编制那样的查问的工具。随着对 DBMS 的需求的日益增长，显然必定将传统的相关模型加以裁减,以便能够解决那些越来越复杂的剖析，而这些函数正是 DB2 冲破局限的一个例子。