# 第十五章 SQL窗口函数概述(二) # ROW子句 `ROW`子句可以与`FIRST_VALUE`(字段)和`SUM`(字段)窗口函数一起使用。 可以为其他`windows`函数指定它,但不执行任何操作(有或没有`ROWS`子句的结果相同)。 `ROWS`子句有两种语法形式: ```sql ROWS framestart ROWS BETWEEN framestart AND frameend ``` `Framestart`和`frameend`有五个可能的值: ```sql 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,得到滚动平均分数: ```sql 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`: ```sql SELECT Name,Home_State,ROW_NUMBER() OVER (PARTITION BY Home_State),Age,AVG(Age),ROW_NUMBER() OVER (ORDER BY Age) FROM Sample.Person ``` 如果要指定另一个列名(列别名),请使用AS关键字: ```sql SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age FROM Sample.Person ``` 可以使用列别名在`ORDER BY`子句中指定一个窗口字段: ```sql SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age FROM Sample.Person ORDER BY StateRow ``` 不能在`ORDER BY`子句中使用默认的列名(如`Window_3`)。 # ORDER BY 因为在窗口函数求值之后,`ORDER BY`子句应用于查询结果集,所以`ORDER BY`不会影响选择项窗口函数分配的值。