首页 > 代码库 > SQL Server窗口框架——ROWS、RANGE
SQL Server窗口框架——ROWS、RANGE
说到窗口框架就不得不提起开窗函数。
开窗函数支持分区、排序和框架三种元素,其语法格式如下:
1 OVER ( 2 [ <PARTITION BY clause> ] 3 [ <ORDER BY clause> ] 4 [ <ROW or RANGE clause> ] 5 )
窗口分区:
1 <PARTITION BY clause> ::= 2 PARTITION BY value_expression , ... [ n ]
窗口排序:
分区之后可以指定排序列,那么在窗口计算之前,各个窗口的行的逻辑顺序将确定。
1 <ORDER BY clause> ::= 2 ORDER BY order_by_expression 3 [ COLLATE collation_name ] 4 [ ASC | DESC ] 5 [ ,...n ]
窗口框架:
1 <ROW or RANGE clause> ::= 2 { ROWS | RANGE } <window frame extent> 3 4 <window frame extent> ::= 5 { <window frame preceding> 6 | <window frame between> 7 } 8 <window frame between> ::= 9 BETWEEN <window frame bound> AND <window frame bound> 10 11 <window frame bound> ::= 12 { <window frame preceding> 13 | <window frame following> 14 } 15 16 <window frame preceding> ::= 17 { 18 UNBOUNDED PRECEDING 19 | <unsigned_value_specification> PRECEDING 20 | CURRENT ROW 21 } 22 23 <window frame following> ::= 24 { 25 UNBOUNDED FOLLOWING 26 | <unsigned_value_specification> FOLLOWING 27 | CURRENT ROW 28 } 29 30 <unsigned value specification> ::= 31 { <unsigned integer literal> }
当使用框架时,必须要有ORDER BY子句,如果仅指定了ORDER BY子句而未指定框架,那么默认框架将采用 RANGE UNBOUNDED PRECEDING AND CURRENT ROW。
如果窗口函数没有指定ORDER BY子句,也就不存在ROWS/RANGE窗口的计算。
如果ROWS/RANGE子句采用 <window frame preceding>,那么CURRENT ROW 作为框架的默认结束行,例如:“ROWS 5 PRECEDING” 等价于 “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”。
PS:RANGE 只支持使用 UNBOUNDED 和 CURRENT ROW 窗口框架分隔符。
未使用 partition by 指定分区示例:
1 select *, 2 sum(U_Id) over(order by U_Id) 列1, 3 sum(U_Id) over(order by U_Id RANGE BETWEEN unbounded preceding AND CURRENT ROW) 列2, 4 sum(U_Id) over(order by U_Id rows BETWEEN unbounded preceding AND CURRENT ROW) 列3, 5 sum(U_Id) over(order by U_Id rows BETWEEN 1 preceding AND 2 following) 列4, 6 sum(U_Id) over(order by U_Id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 列5 7 from UserInfo
结果分析:
RANGE 是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内。
ROWS 是物理窗口,即根据order by 子句排序后,取的前N行及后N行的数据计算。(与当前行的值无关,只与排序后的行号相关)
【列1】未指定窗口,所以默认为 RANGE UNBOUNDED PRECEDING AND CURRENT ROW,故此与【列2】值相同。
【列2】RANGE BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为 当前行与当前行前面的所有行的值。
即第一行的值为:1 第二行的值为:3+1 第三行的值为:4+3+1
【列3】rows BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为 当前行与当前行前面的所有行的值。
即第一行的值为:1 第二行的值为:3+1 第三行的值为:4+3+1
【列4】rows BETWEEN 1 preceding AND 2 following 表示指定取值范围为 当前行与前一行和后两行的值。
即第一行的值为:1+3+4 第二行的值为:1+3+4+5 第三行的值为:3+4+5+6
【列5】ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示指定取值范围为 当前行与前一行的值。
即第一行的值为:1 第二行的值为:1+3 第三行的值为:3+4
使用 partition by 指定分区示例:
1 select *, 2 sum(U_Id) over(partition by U_Pwd order by U_Id) 列1, 3 sum(U_Id) over(partition by U_Pwd order by U_Id RANGE BETWEEN unbounded preceding AND CURRENT ROW) 列2, 4 sum(U_Id) over(partition by U_Pwd order by U_Id rows BETWEEN unbounded preceding AND CURRENT ROW) 列3, 5 sum(U_Id) over(partition by U_Pwd order by U_Id rows BETWEEN 1 preceding AND 2 following) 列4, 6 sum(U_Id) over(partition by U_Pwd order by U_Id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 列5 7 from UserInfo
以上根据 列(U_Pwd)被分为3个区,使用 partition by 指定分区就是先进行分区,然后再根据指定窗口和指定窗口取值范围进行计算。
参考:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
SQL Server窗口框架——ROWS、RANGE