OLAP 规范

在线分析处理（OLAP）规范允许在查询结果中以标量值的形式返回排名、行编号和聚合信息。 OLAP规范可以包含在表达式、选择列表或选择语句的ORDER BY子句中。应用OLAP规范的查询结果是包含OLAP规范的最内层子查询的结果表。

OLAP规范的语法

ordered-OLAP-specification

注意：

¹ FL 504 仅适用于直通功能，在 Db2 for z/OS® 上无法运行，且无法加速。请参阅 “仅使用直通表达式加速查询 ”。

lag-function

lead-function

window-partition-clause

window-order-clause

numbering-specification

aggregation-specification

聚合函数

注意：

¹ 您不能为聚合说明中包含的聚合函数指定 DISTINCT 或 ALL。

OLAP聚合函数

注意：

¹ FL 504 这些仅适用于直通的功能无法在 Db2 for z/OS 上运行，除非加速。请参阅 “仅使用直通表达式加速查询 ”。

first-value-function

last-value-function

nth-value-function

ratio-to-report-function

window-aggregation-group-clause

group-start

group-between

group-bound-1

group-bound-2

group-end

RANK、DENSE_RANK和ROW_NUMBER有时被称为窗口函数。

OLAP规范不适用于 WHERE， VALUESGROUP BY， HAVING或 SET 子句中。 OLAP规范不能用作聚合函数的参数。

调用OLAP规范时，会指定一个窗口，该窗口定义了应用函数的行以及应用顺序。

RANK、DENSE_RANK或ROW_NUMBER规格的结果为BIGINT。结果不能为空。

CUME_DIST

FL 504 返回OLAP窗口中某行的累积分布，以 0.0 – 1.0 范围内的值表示。计算结果是通过将OLAP窗口中当前行之前或等于当前行的行数除以OLAP窗口中的行数得出的。

结果的数据类型是 DECFLOAT(34)。结果不能为空。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

PERCENT_RANK

FL 504 返回OLAP窗口中某行的相对百分位数，以 0.0 – 1.0 范围内的值表示。当OLAP窗口中的行数大于1时，结果计算方法为：将OLAP窗口中当前行的RANK减1，再除以OLAP窗口中的行数减1。否则，结果为 0.0。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

RANK 或者 DENSE_RANK

指定计算指定窗口内某行的序数。在指定窗口内排序不明显的行将被赋予相同的等级。排名结果可以包含或排除重复值导致的数字差异。

RANK: 规定某行的等级等于1加上严格排在它前面的行数。因此，如果两行或更多行在排序上不分明，则顺序编号中会出现一个或多个空格。
DENSE_RANK: 规定某行的等级为1加上相对于排序而言不同的前几行的数量。因此，顺序排名编号中不存在间隙。

NTILE

FL 504 返回某行的分位数。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

数字瓷砖: 用于指定分位数的表达式。该表达式必须返回一个内置数字、CHAR 或 VARCHAR 数据类型的值。在 Unicode 数据库中，该表达式也可以是 GRAPHIC 或 VARGRAPHIC 数据类型。使用隐式强制类型转换，可以支持 CHAR、VARCHAR、GRAPHIC 和 VARGRAPHIC。如果表达式不是 SMALLINT、INTEGER 或 BIGINT，那么在对该函数进行求值之前，会将其强制转换为 BIGINT。该值必须大于 0。表达式必须是一个常数、一个变量或一个常数或变量的转换。

NTILE的结果数据类型与num-tiles 的数据类型相同，经过任何隐式转换后都是如此。如果参数可以为空值，那么结果可以为空值。如果参数为空值，那么结果为空值。

NTILE函数通过将OLAP窗口中的有序行划分为 num-tiles个分位数来计算行的分位数，并返回一个介于1和 MIN (n, num-tiles) 之间的值，其中 n 是OLAP窗口中的行数。如果 n 能被 num-tiles 整除，则OLAP窗口中的行将按 num-tiles 分位数分组，每个分位数包含（n / num-tiles ）行。否则，从 1 到 MOD( n, num-tiles ) 的每个分位数都分配 ( n / num-tiles + 1) 行，从 (MOD( n, num-tiles ) + 1) 到 num-tiles 的每个分位数都分配 ( n / num-tiles ) 行。结果是与当前行关联的分位数排名。

将行划分为分位数时，不会考虑等效排序键。可以根据这些排序键的不确定顺序将具有等效排序键的行分配给不同的分位数。因此，NTILE 是一个非确定性函数。

LAG

FL 504 返回当前行之前偏移行中该行的表达式值。 偏移量必须为正整数常数。 偏移值为0表示当前行。如果指定了窗口分区子句，则偏移量表示当前行之前且在当前分区内的偏移行数。如果指定了 offset，那么将使用值 1。如果指定了默认值 （可以是表达式），当偏移量超出当前分区的范围时，将返回该默认值。否则，将返回空值。如果指定了“忽略空值”，则计算时不会考虑表达式值为空值的所有行。如果指定了“忽略空值”，且所有行均为空，则返回默认值（如果未指定默认值 ，则返回空值）。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

LEAD

FL 504 返回当前行之后偏移行中该行的表达式值。 偏移量必须为正整数常数。 偏移值为0表示当前行。如果指定了窗口分区子句，则偏移量表示在当前分区中当前行之后的偏移行数。如果指定了 offset，那么将使用值 1。如果指定了默认值 （可以是表达式），当偏移量超出当前分区的范围时，将返回该默认值。否则，将返回空值。如果指定了“忽略空值”，则计算时不会考虑表达式值为空值的所有行。如果指定了“忽略空值”，且所有行均为空，则返回默认值（如果未指定默认值 ，则返回空值）。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

ROW_NUMBER

指定为排序定义的行计算顺序行号，从第一行的1开始。条件未在窗口中指定，则返回的行（不根据任何 ORDER BY 子句未在窗口中指定，则行号以任意顺序分配给返回的行（不根据任何 ORDER BY select语句中的任何子句）。

PARTITION BY 分区表达式，...

定义应用OLAP操作的分区。分区表达式用于定义结果表的分区。分区表达式中引用的每个列名必须明确引用包含OLAP规范的子选择结果表的列。分区表达式不能包含标量全选、XMLQUERY或XMLEXISTS表达式，也不能包含任何非确定性或具有外部动作的函数。

ORDER BY 排序关键字表达式，...

定义分区中行的排序，用于确定OLAP规范的值。它不定义结果表的排序。

sort-key-expression: 指定用于定义窗口分区中行排序的表达式。在排序键表达式中引用的每个列名必须明确引用子选择结果表的列，包括OLAP规范。排序键表达式不能包含标量全选、XMLQUERY或XMLEXISTS表达式，或任何非确定性或具有外部操作的函数。
ASC: 指定按升序使用 sort-key-expression 的值。
DESC: 指定按降序使用 sort-key-expression 的值。
NULLS FIRST: 指定窗口排序在排序顺序中优先考虑空值，然后才是非空值。
NULLS LAST: 指定窗口排序在排序顺序中所有非空值之后考虑空值。

FIRST_VALUE

FL 504 返回OLAP窗口中第一行的表达式值。如果指定了“忽略空值”，则计算时不会考虑表达式值为空值的所有行。如果指定了“IGNORE NULLS”并且 OLAP 窗口中的所有值都为空值，那么 FIRST_VALUE 返回空值。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

LAST_VALUE

FL 504 返回OLAP窗口中最后一行的表达式值。如果指定了“忽略空值”，则计算时不会考虑表达式值为空值的所有行。如果指定了“IGNORE NULLS”并且 OLAP 窗口中的所有值都为空值，那么 LAST_VALUE 返回空值。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

NTH_VALUE

FL 504 返回OLAP窗口中第n行的表达式值。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

表达式: 用于指定 OLAP 窗口中当前行的表达式。该表达式必须返回一个内置数据类型的值。
nth-row: 用于指定要返回的 OLAP 窗口行的表达式。该表达式必须返回内置数字数据类型、CHAR 或 VARCHAR 数据类型的值。在 Unicode 数据库中，该表达式也可以是 GRAPHIC 或 VARGRAPHIC 数据类型。使用隐式强制类型转换，可以支持 CHAR、VARCHAR、GRAPHIC 和 VARGRAPHIC。如果表达式不是 SMALLINT、INTEGER 或 BIGINT，那么在对该函数进行求值之前，会将其强制转换为 BIGINT。该值必须大于 0。表达式必须是一个常数、一个变量或一个常数或变量的转换。

Db2 for z/OS 不支持此函数的 FROM FIRST、FROM LAST、RESPECT NULLS 和 IGNORE NULLS 关键字。在 Db2 for Linux®, UNIX, and Windows 中省略这些关键字会导致默认行为 FROM FIRST RESPECT NULLS。

RATIO_TO_REPORT

FL 504 返回OLAP分区中参数与参数之和的比率。始终使用 DECFLOAT(34) 执行此除法运算。结果的数据类型为 DECFLOAT(34)。如果自变量可以为空，那么结果可以为空；如果自变量为空，那么结果为空值。

仅传递表达式：此功能仅传递，无法在 Db2 for z/OS 上运行，且无法加速。有关调用此功能的信息，请参阅 “使用仅传递表达式加速查询 ”。

窗口聚合组子句

给定行的聚合组是与给定行相关联的一组行（按照给定行所在分区的行顺序排列）。窗口聚合组子句指定聚合组。如果未指定此子句，且未指定窗口顺序子句，则聚合组由窗口分区中的所有行组成。可以使用 RANGE 或 ROWS 子句明确指定窗口分区所有行的聚合组。

如果指定了 window-order-clause ，但未指定 window-aggregation-group-clause ，则窗口聚合组由给定行分区中给定行之前的所有行或按 window-order-clause 定义的窗口分区的窗口顺序与给定行对等的所有行组成

ROW: 指定聚合组通过计算行数来定义。
RANGE: 指定聚合组由排序键的偏移量定义。
小组开始: 指定聚集组的起始点。聚集组端是当前行。指定组开始相当于指定组之间为 BETWEEN 组开始 AND CURRENT ROW。
组间: 指定聚合组基于行数或范围开始和结束。
组端: 指定聚集组的结束点。聚集组起点是当前行。指定组端等同于指定组间为 BETWEEN CURRENT ROW AND 组端。
UNBOUNDED PRECEDING: 指定当前行之前的整个分区包含在聚合组中。这可以通过ROWS或RANGE子句来指定。在窗口顺序子句中，也可以使用多个排序键表达式来指定当前行之前的整个分区。
UNBOUNDED FOLLOWING: 指定当前行之后的整个分区包含在聚合组中。这可以通过ROWS或RANGE子句来指定。在窗口顺序子句中，也可以使用多个排序键表达式来指定当前行之后的整个分区。
CURRENT ROW: 指定聚合组基于当前行开始或结束。如果指定了 ROWS，那么当前行是聚集组边界。如果指定了范围，则聚合组边界包括当前行中为排序键表达式指定的值的一组行。本条款不能在 group-bound-2 如果 group-bound-1 指定了未签名常量 FOLLOWING。
无符号常数 PRECEDING: 指定当前行之前的行数或范围。如果指定了 ROWS，则 unsigned-constant 必须为零或一个正整数，表示行数。如果指定了范围，则无符号常量的数据类型必须与窗口顺序子句的排序键表达式的数据类型兼容。只允许使用一种排序键表达式，且排序键表达式的数据类型必须允许减法运算。本条款不能在 group-bound-2 如果 group-bound-1 当前行或未定义常量 FOLLOWING。
无符号常数 FOLLOWING: 指定当前行之后的行数或行数范围。如果指定了 ROWS，则 unsigned-constant 必须为零或一个正整数，表示行数。如果指定了范围，则无符号常量的数据类型必须与窗口顺序子句的排序键表达式的数据类型兼容。只允许使用一种排序键表达式，且排序键表达式的数据类型必须允许加法运算。

OLAP规范说明

使用具有 OLAP 规范的列掩码： 如果使用列掩码来屏蔽最终结果表中的列值，并且在用于派生最终结果表的选择列表中引用了 OLAP 规范，则不能将列掩码应用于 OLAP 规范中的分区表达式或排序键表达式中指定的列。

语法替代和同义词 ：为了兼容性，关键词 DENSERANK 和 ROWNUMBER 可以作为 DENSE_RANK 和 ROW_NUMBER 。

OLAP规范的示例

示例1 ：显示总工资超过30,000美元的员工的排名，按姓氏排序：

SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
  RANK() OVER(ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
  FROM EMP WHERE SALARY+BONUS > 30000
  ORDER BY LASTNAME;

如果结果按等级排序，则 ORDER BY LASTNAME 应替换为 ORDER BY RANK_SALARY。

示例2 ：根据部门平均总薪资对部门进行排名：

SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
  RANK() OVER(ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
  FROM EMP
  GROUP BY WORKDEPT
  ORDER BY RANK_AVG_SAL;

示例3 ：根据教育水平对部门进行排名。部门内多名员工具有相同级别不应增加下一个级别的数值：

SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL,
  DENSE_RANK() OVER
   (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
  FROM EMP
  ORDER BY WORKDEPT, LASTNAME;

示例4 ：在查询结果中提供行号：

SELECT ROW_NUMBER() OVER(ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
  LASTNAME, SALARY
  FROM EMP
  ORDER BY WORKDEPT, LASTNAME;

例5： 列出收入最高的五个人：

SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY
  FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
        RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
        FROM EMP) AS RANKED_EMPLOYEE
  WHERE RANK_SALARY < 6
  ORDER BY RANK_SALARY;

嵌套表表达式用于首先计算结果（包括排名），然后才能将排名用于 WHERE 子句中使用。也可以使用公共表表达式。

例6： 以下示例用于计算2005年期间股票“ABC”和“XYX”的30天移动平均线：

CREATE VIEW V1 AS
	SELECT SYMBOL, TRADINGDATE,
				AVG(CLOSINGPRICE) OVER (PARTITION BY SYMBOL
														  ORDER BY TRADINGDATE
														  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
		FROM DAILYSTOCKDATA
		WHERE SYMBOL IN ('ABC', 'XYZ')
				  AND TRADINGDATE BETWEEN DATE('2005-01-01') - 2 MONTHS AND '2005-12-31';

SELECT SYMBOL, TRADINGDATE, MOVINGAVG30DAY
	FROM V1
  WHERE TRADINGDATE BETWEEN '2005-01-01' AND '2005-12-31'
	ORDER BY SYMBOL, TRADINGDATE;

例7 ：计算每位员工的工资处于哪个四分位数（4个四分位数）。

   SELECT EMPNO, SALARY, NTILE(4) OVER 
         (ORDER BY SALARY) AS QUARTILE
      FROM EMPLOYEE 
      ORDER BY SALARY

结果集为：

EMPNO  SALARY      QUARTILE
------ ----------- -----------
200340    31840.00           1
000290    35340.00           1
200330    35370.00           1
000310    35900.00           1
200310    35900.00           1
000280    36250.00           1
000270    37380.00           1
000300    37750.00           1
200240    37760.00           1
200120    39250.00           1
000320    39950.00           1
000230    42180.00           2
000340    43840.00           2
000170    44680.00           2
000330    45370.00           2
200280    46250.00           2
200010    46500.00           2
000260    47250.00           2
000240    48760.00           2
000250    49180.00           2
000120    49250.00           2
000220    49840.00           2
000190    50450.00           3
000180    51340.00           3
000150    55280.00           3
000200    57740.00           3
000160    62250.00           3
200170    64680.00           3
000110    66500.00           3
000210    68270.00           3
000140    68420.00           3
200140    68420.00           3
200220    69840.00           4
000060    72250.00           4
000130    73800.00           4
000050    80175.00           4
000100    86150.00           4
000090    89750.00           4
000020    94250.00           4
000070    96170.00           4
000030    98250.00           4
000010   152750.00           4

  42 record(s) selected.

示例8： 以下示例中的查询将行按最高工资分为3组。包括最高薪水以显示每个存储区中的值：

   SELECT NTILE(3) OVER (ORDER BY MAX_SALARY) AS Bucket, 
MAX_SALARY FROM GOSALESDW.EMP_POSITION_DIM;

查询的部分输出如下表所示：

表 1. 示例输出
BUCKET	MAX_SALARY
1	0.00
...	...
1	35000.00
2	5000.00
...	...
2	12000.00
3	13000.00
...	...
3	301500.00