[SQL Server]窗口函数

参考文献

https://www.cnblogs.com/zhaoshujie/p/9594676.html

开窗函数支持的三种元素

开窗函数支持的三种元素：分区、排序、框架

窗口分区：将窗口中指定列具有相同值的行进行分区处理（类似于分组）

1. 分组：整个select语句只能按照指定分组进行
2. 分区：可以在一条语句中指定不同的分区

窗口排序：分区之后可以指定排序列，即在窗口函数计算之前，各个窗口的行的逻辑顺序将确定

窗口框架：框架是对于窗口进行进一步分区处理，框架有两种范围限定方式：

1. Rows子句（物理窗口）：通过指定当前行之前或者之后固定数目的行来限制分区中的行数
   a.根据order by子句排序
   b.取前N行及后N行的数据进行计算
   c.与当前行的值无关，只与排序后的行号相关
2. Range子句（逻辑窗口）：按照排序列的当前值，根据相同值来确定分区的中的行数
   a.指定当前行对应值的范围进行取值
   b.列数不固定
   c.对应列都包含在内

用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语句用：

1. 用SUM()聚合函数进行汇总计算
2. 窗口从第1行（UNBOUNDED PRECEDING）至当前行（CURRENT ROW）
3. 记录每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

输出：

得到了不同的结果，对于2005年11月的记录显示同样的汇总

原因：

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

示例(未使用/使用 partition by指定分区 )

未使用 partition by

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

RANGE BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为：当前行与当前行前面的所有行的值。

即第一行的值为：1　　第二行的值为：3+1　　第三行的值为：4+3+1

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

rows BETWEEN 1 preceding AND 2 following 表示指定取值范围为： 当前行与前一行和后两行的值

即第一行的值为：1+3+4　　第二行的值为：1+3+4+5　　第三行的值为：3+4+5+6

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

rows BETWEEN 1 PRECEDING AND CURRENT ROW 表示指定取值范围为： 当前行与前一行的值

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

以上根据:

1. 列U_Pwd 被分为 3个区
2. 使用 partition by 指定分区就是先进行分区
3. 然后再根据指定窗口和指定窗口取值范围进行计算

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