SQL Server窗口框架——ROWS、RANGE

q968

　　

<ROW or RANGE clause> ::=　　
{ ROWS | RANGE } <window frame extent>  
　　

　　
<window frame extent> ::=   
　　
{   <window frame preceding>  
　　
   | <window frame between>  
　　
}  
　　
<window frame between> ::=   
　　
   BETWEEN <window frame bound> AND <window frame bound>  
　　

　　
<window frame bound> ::=   
　　
{   <window frame preceding>  
　　
   | <window frame following>  
　　
}  
　　

　　
<window frame preceding> ::=   
　　
{  
　　
     UNBOUNDED PRECEDING  
　　
   | <unsigned_value_specification> PRECEDING  
　　
   | CURRENT ROW  
　　
}  
　　

　　
<window frame following> ::=   
　　
{  
　　
     UNBOUNDED FOLLOWING  
　　
   | <unsigned_value_specification> FOLLOWING  
　　
   | CURRENT ROW  
　　
}  
　　

　　
<unsigned value specification> ::=   
　　
{  <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 指定分区示例：
　　

select *,　　

sum(U_Id) over(order by U_Id) 列1,　　

sum(U_Id) over(order by U_Id RANGE BETWEEN unbounded preceding AND CURRENT ROW) 列2,　　

sum(U_Id) over(order by U_Id rows  BETWEEN unbounded preceding AND CURRENT ROW) 列3,　　

sum(U_Id) over(order by U_Id rows BETWEEN 1 preceding AND 2 following) 列4,　　

sum(U_Id) over(order by U_Id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 列5　　

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 指定分区示例：
　　

select *,　　

sum(U_Id) over(partition by U_Pwd order by U_Id) 列1,　　

sum(U_Id) over(partition by U_Pwd order by U_Id RANGE BETWEEN unbounded preceding AND CURRENT ROW) 列2,　　

sum(U_Id) over(partition by U_Pwd order by U_Id rows  BETWEEN unbounded preceding AND CURRENT ROW) 列3,　　

sum(U_Id) over(partition by U_Pwd order by U_Id rows BETWEEN 1 preceding AND 2 following) 列4,　　

sum(U_Id) over(partition by U_Pwd order by U_Id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 列5　　

from UserInfo　　

　　以上根据 列（U_Pwd）被分为3个区，使用 partition by 指定分区就是先进行分区，然后再根据指定窗口和指定窗口取值范围进行计算。
　　参考：
　　https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql