SQL Server 开窗函数over()选项RANGE/ROWS详解

1. 语法

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

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

<PARTITION BY clause> ::=  PARTITION BY value_expression , ... [ n ]  
<ORDER BY clause> ::=  ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ]  
<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> }

 

【窗口分区】：就是将窗口指定列具有相同值的那些行进行分区，分区与分组比较类似，但是分组指定后对于整个SELECT语句只能按照这个分组，不过分区可以在一条语句中指定不同的分区。
【窗口排序】：分区之后可以指定排序列，那么在窗口计算之前，各个窗口的行的逻辑顺序将确定。
【窗口框架】：框架是对窗口进行进一步的分区，框架有两种范围限定方式：一种是使用ROWS子句，通过指定当前行之前或之后的固定数目的行来限制分区中的行数；另一种是RANGE子句，按照排序列的当前值，根据相同值来确定分区中的行数。

• 当使用框架时，必须要有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 窗口框架分隔符。

 

2. RANGE | ROWS的区别 

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

 RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表：

  

 “RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又被称为定位框架。 

 当你用OVER()子句进行你的分析计算来打开你的窗口，你也可以在窗口里看到的，通过ROWS与RANGE选项来限制你的行数。来看下面的T-SQL语句：  

SELECT
    t.OrderYear, t.OrderMonth, t.TotalDue,
    SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM
(
    SELECT
        YEAR(OrderDate) AS 'OrderYear', MONTH(OrderDate) AS 'OrderMonth', SalesPersonID, TotalDue
    FROM Sales.SalesOrderHeader 
) AS t
WHERE
    t.SalesPersonID = 274 AND t.OrderYear = 2005

 这个T-SQL语句用SUM()聚合函数进行汇总计算。窗口本身从第1行（UNBOUNDED PRECEDING）至当前行（CURRENT ROW）。对于记录级中的每1行，窗口变得越来越大，因此很容易进行汇总运算。下图演示了这个概念。

从输出你可以看到，结果是个自增长的汇总——运行合计汇总的结果。

现在假设你修改窗口为 RANGE 会发生什么：

SELECT
    t.OrderYear, t.OrderMonth, t.TotalDue,
    SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM
(
    SELECT
        YEAR(OrderDate) AS 'OrderYear', MONTH(OrderDate) AS 'OrderMonth', SalesPersonID, TotalDue
    FROM Sales.SalesOrderHeader 
) AS t
WHERE
    t.SalesPersonID = 274 AND t.OrderYear = 2005

 从下图你可以看到，你得到了不同的结果，对于2005年11月的记录显示同样的汇总。

 

结果分析：

• 使用ROWS选项定义当前行的固定前后记录。这里看到的行取决于窗口的ORDER BY从句，也可以说在物理级别定义的窗口。
• 使用RANGE选项事情就改变了，RANGE选项包含窗口里的所有行，和当前行有相同ORDER BY值。从刚才的图片可以看到，对于2005年11月的2条记录拿到同个汇总，因为这2行有同样的ORDER BY值（2005年11月）。使用RANGE选项在逻辑级别定义的窗口。如果更多的行有同个ORDER BY值，当使用ROWS选项的窗口会包含更多的行。

  

3. PARTITION BY

3.1 未使用 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

 

3.2 使用 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 指定分区就是先进行分区，然后再根据指定窗口和指定窗口取值范围进行计算。

 

4. 小结

使用ROWS选项你在物理级别定义在你窗口里有多少行。使用RANGE选项取决于ORDER BY值在窗口里有多少行被包含。因此当你使用RANGE选项时有性能上的巨大区别。

 

 

[参考]: SQL开窗函数