第十五章 SQL窗口函数概述（二）

ROW子句

ROW子句可以与FIRST_VALUE(字段)和SUM(字段)窗口函数一起使用。
可以为其他windows函数指定它，但不执行任何操作(有或没有ROWS子句的结果相同)。

ROWS子句有两种语法形式:

ROWS framestart
ROWS BETWEEN framestart AND frameend

Framestart和frameend有五个可能的值:

UNBOUNDED PRECEDING                    /* 从当前分区的开头开始 */
offset PRECEDING   /* 开始偏移当前行之前的行数 */
CURRENT ROW                                      /* 从当前行开始 */
offset FOLLOWING  /* 继续偏移当前行后面的行数 */
UNBOUNDED FOLLOWING                  /* 继续到当前分区的末尾 */

ROWS子句语法可以在两个方向上指定一个范围。
例如，在无界前面和1后面之间的行以及在1后面和无界前面之间的行是完全相等的。

ROWS框架的开始语法默认为CURRENT ROW作为范围的第二个未指定的边界。
因此，以下是等价的:

ROWS UNBOUNDED PRECEDING	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS 1 PRECEDING	ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
ROWS CURRENT ROW	ROWS BETWEEN CURRENT ROW AND CURRENT ROW
ROWS 1 FOLLOWING	ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
ROWS UNBOUNDED FOLLOWING	ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

如果没有指定ROWS子句，则默认为在无界的前一行和当前行之间的行数。

ROW子句示例

下面的查询返回包含大量“噪声noise”(随机变量)的分数。ROWS子句用于“平滑smooth”这些变化，方法是按排序顺序将每个分数与其前一个分数和紧随其后的分数相加，然后除以3，得到滚动平均分数：

SELECT Item,Score,SUM(Score)
  OVER (ORDER BY Score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)/3
  AS CohortScore FROM Sample.TestScores

操作为：(PrecedingScore+CurrentScore+FollowingScore)/3，注意底部和顶部的CohortScore值不准确，因为它们是将0加到两个计分值上，然后除以3：(0+CurrentScore+FollowingScore)/3和(PrecedingScore+CurrentScore+0)/3。

使用窗口函数

窗口函数可用于：

• SELECT list as a listed select-item.

窗口函数不能嵌入到子查询或选择项列表中的聚合函数中。

• ORDER BY clause.

窗口函数不能在and ON、WHERE、GROUP BY或HAVING子句中使用。
尝试这样做会导致SQLCODE -367错误。

列名和别名

默认情况下，分配给窗口函数结果的列名是Window_n，其中n号后缀是列序号，正如在SELECT列表中指定的那样。
因此，下面的例子创建了列名Window_3和Window_6:

SELECT Name,Home_State,ROW_NUMBER() OVER (PARTITION BY Home_State),Age,AVG(Age),ROW_NUMBER() OVER (ORDER BY Age)
FROM Sample.Person

image.png

如果要指定另一个列名(列别名)，请使用AS关键字:

SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age
FROM Sample.Person

image.png

可以使用列别名在ORDER BY子句中指定一个窗口字段:

SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age
FROM Sample.Person
ORDER BY StateRow

image.png

不能在ORDER BY子句中使用默认的列名(如Window_3)。

ORDER BY

因为在窗口函数求值之后，ORDER BY子句应用于查询结果集，所以ORDER BY不会影响选择项窗口函数分配的值。