OLAP window 表达式的 OVER 子句

OVER 子句定义在其上执行 OLAP window 表达式的结果集。

语法

OVER 子句

编号函数的 OVER 子句

分等级函数的 OVER 子句

聚集函数的 OVER 子句

Window PARTITION 子句

Window ORDER 子句

Window Frame 子句

元素	描述	限制	语法
`offset`	表示从当前行位置的偏移量的无符号整数	不可为负的。如果为零，则指定当前的行	文字的整数。
`partition_key`	由其对行分区的列名称、别名或常量表达式	必须在 Projection 子句的选择列表中	列表达式
`sorting_key`	由其对行排序的列名称、别名或常量表达式	与对于 `partition_key` 的限制相同。对于 RANGE window frame，仅允许单个排序键，且数据类型必须为数值的、DATE 或 DATETIME。	列表达式

如果 OVER 子句为空，则您必须还包括空的圆括号。

Window PARTITION 子句

OLAP window 分区是由查询返回的行的子集。通过定义该 window 的 OVER 子句的 PARTITION BY 规范中的一个或多个列表达式定义每一分区。数据库服务器将指定的 OLAP window 函数应用于每一 window 分区中的所有行。如果在 OVER 子句中未定义分区，则将 window 函数应用于该查询的结果集中的每行。

Window ORDER 子句

数据库服务器根据 window ORDER 子句中的排序键（或多个排序键）对每一 window 分区中的行排序。如果您未指定升序（ASC）或降序（DESC）顺序，则缺省值为 ASC。如果未指定 ORDER 子句，则按照检索到的行的顺序排列符合条件的行。

Window Frame 子句

window Frame 子句返回每一 window 分区中的行的子集，称为聚集组。由特定数目的行或值的范围来定义 window frame。

基于行的 window frame

ROWS 关键字创建基于行的 window frame，这由在当前行之前或之后或之前与之后的特定数量的行组成。该偏移量表示要返回的行的数目。下列示例返回包括当前行以及当前行之前六行的七行：

AVG(price) OVER (ORDER BY year, day 
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

在基于行的 window frame 子句中，偏移量表示为无符号整数，因为关键字 FOLLOWING 指定从当前行的正偏移量，而关键字 PRECEDING 指定从当前行的负偏移量。关键字 UNBOUNDED 指的是从当前行至该 window 分区的限度的所有行。作为在 window Frame 规范中 ROWS 关键字之后的第一个术语，UNBOUNDED PRECEDING 意味着起始边界为该分区中的第一行，而 UNBOUNDED FOLLOWING 意味着终止边界为该分区中的最后一行。

基于值的 window frame

RANGE 关键字创建基于值的 frame 子句，由当前行与满足标准的行组成，通过 ORDER 子句中的排序键设置该标准并符合指定的偏移量。偏移量表示排序键的数据类型的单位数目。排序键必须为数值的、DATE 或 DATETIME 数据类型。例如，如果排序键为 DATE 数据类型，则偏移量表示特定的天数。下列示例返回发运日期在当前行的两天之内的行的数目加上当前行的总数：

COUNT(*) OVER (ORDER BY ship_date
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)

基于值的 window frame 定义在包含指定范围的数值值的 window 分区内的行。OVER 函数的 window ORDER 子句定义应用 RANGE 规范的数值的、DATE 或 DATETIME 列，现对于那一列的当前行值。在基于值的 window frame 的 ORDER 子句中仅允许排序键。

在基于行和基于值的这两种情况下，在此 window frame 的内容上计算 OLAP 函数，而不是在整个分区的固定的内容上计算。window frame 不需要包含当前行。例如，下列规范定义仅包含当前行之前的行的 window frame：

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING

如果您未为 window 聚集函数指定 window ORDER 子句，则在缺省情况下，不限制结果集，其等同于下列 window frame 规范：

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

如果您为 window 聚集函数指定 ORDER 子句但无 window frame 子句，则在缺省情况下，返回当前行之前的所有行以及当前行，其等同于下列 window frame 规范：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

示例：不带有 window frame 的 SUM 函数

下列查询返回按一年的季度的销售额，以及按季度的销售额的累积总和。

  SELECT sales, SUM(sales) OVER (ORDER BY quarter) 
  FROM sales WHERE year = 2012

         sales     (sum)
           120       120
           135       255
           127       382
           153       535

第四季度的销售额的总和等于所有四个季度中的销售额。

由于该查询未包括 window frame 子句，因此，SUM 函数如通过 FROM 子句指定的那样，在整个结果集上操作。

示例：基于行的 window frame

下列查询按照团队分区并按照分数排序来返回运动员。在每一分区内，对该运动员以及与前面的运动员的分数求平均值：

select team, player, points, 
   AVG(points) OVER(PARTITION BY team ORDER BY points
      ROWS 1 PRECEDING AND CURRENT ROW) AS olap_avg
FROM points;

TEAM       PLAYER     POINTS OLAP_AVG      
A          Singh           7  7.00000000000
A          Smith          14 10.50000000000
B          Osaka           8  8.00000000000
B          Ricci          12 10.00000000000
B          Baxter         18 15.00000000000
C          Chun           13 13.00000000000
D          Kwan            9  9.00000000000
D          Tran           16 12.50000000000

示例：基于范围的 window frame

下列查询按照团队分区并按照年龄排序，返回运动员。在每一分区内，对每一运动员以及最多大 9 岁的运动员的分数求平均值：

SELECT player, age, team, points,
   AVG(points) OVER(PARTITION BY team ORDER BY age 
      RANGE BETWEEN CURRENT ROW AND 9 FOLLOWING) AS olap_avg
FROM points_age;

PLAYER     AGE TEAM       POINTS OLAP_AVG      
Singh       25 A               7 10.50000000000
Smith       26 A              14 14.00000000000
Baxter      27 B              18 13.00000000000
Osaka       35 B               8 10.00000000000
Ricci       40 B              12 12.00000000000
Chun        21 C              13 13.00000000000
Kwan        22 D               9 12.50000000000
Tran        31 D              16 16.00000000000

在分区 A 中，Singh 的平均值包括 Smith 的分数，因为 Smith 比 Singh 大一岁。Smith 的平均值不包括来自 Singh 的分数，因为 Singh 比 Smith 年轻。

在分区 B 中，Baxter 的平均值包括 Osaka 的分数，其比 Baxter 大 8 岁，但不包括 Ricci，其比 Baxter 大 13 岁。

在分区 D 中，Kwan 的平均值包括 Tran 的分数，因为 Tran 比 Kwan 大 9 岁。

示例：不带有当前行的 Window frame

下列查询计算分区中前面两行的分数的平均值：

SELECT player, age, team, points,
   AVG(points) OVER(PARTITION BY team ORDER BY age
   ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS olap_avg
   FROM points_age;
PLAYER     AGE  TEAM   POINTS   OLAP_AVG        
Singh        25 A             7             NULL
Smith        26 A            14    7.00000000000
Baxter       27 B            18             NULL
Osaka        35 B             8   18.00000000000
Ricci        40 B            12   13.00000000000
Chun         21 C            13             NULL
Kwan         22 D             9             NULL
Tran         31 D            16    9.00000000000

在分区 B 中，Ricci 的平均值是基于 Baxter 和 Osaka 的分数合计：(18 + 8 = 26)/2 = 13。当当前行没有前面的行用于计算时，结果为 NULL。