SQL Server窗口框架——ROWS、RANGE

说到窗口框架就不得不提起开窗函数。

开窗函数支持分区、排序和框架三种元素，其语法格式如下：

OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  

窗口分区：

就是将窗口指定列具有相同值的那些行进行分区，分区与分组比较类似，但是分组指定后对于整个SELECT语句只能按照这个分组，不过

分区可以在一条语句中指定不同的分区。

<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]  

窗口排序：

分区之后可以指定排序列，那么在窗口计算之前，各个窗口的行的逻辑顺序将确定。

<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  

窗口框架：

框架是对窗口进行进一步的分区，框架有两种范围限定方式：一种是使用ROWS子句，通过指定当前行之前或之后的固定数目的行来限制分区中的行数；

另一种是RANGE子句，按照排序列的当前值，根据相同值来确定分区中的行数。

<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