SQL Server 窗口函数详解：OVER()

语法

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

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 窗口框架分隔符。

示例

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

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

ROWS与RANGE之间的区别

当你用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
GO

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

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

现在假设你修改窗口为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，会发生什么：

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
GO

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

我们来尝试理解下为什么这里RANGE选项比ROWS选项给你不同的结果。

使用ROWS选项你定义当前行的固定前后记录。这里你看到的行取决于窗口的ORDER BY从句。你也可以说你在物理级别定义你的窗口。

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

小结

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