窗口和窗口函数

对于Select子句查询的结果集，可以按照指定的字段进行分区，如下图所示，按照Province字段来对查询的结果集进行分区，可以认为，每一个分区就是一个窗口，因此，窗口是数据行的集合，是Select查询结果集的一个子集。

在TSQL脚本的OVER()子句中，使用Partition By 子句进行分区。在同一分区中，可以按照一定的条件，把分区中的数据行再次细分，按照固定数量（rows）或值范围（range）来限制数据行，把这个数据行集称作一个窗口。

通常把分区和窗口不加区分，统称为窗口。在下文中，为了便于区分，我们把Partition By作用的结果记作分区，把rows 和 range作用的结果记作窗口，窗口中的数据行是分区的子集。

窗口函数是应用于窗口和分区的函数，共分为三类：排名函数，分析函数和聚合函数。注意OVER()子句执行的顺序：OVER()子句在SELECT子句和DISTINCT子句之后执行，在ORDER BY子句之前执行，DISTINCT子句是在SELECT子句之后执行。

下文使用的示例数据使用以下代码创建：

create table dbo.dt_test
(
ID int,
Code int
)
go

--insert data
insert into dbo.dt_test(ID,Code)
values(3,1),(3,2),(1,1),(1,2),(2,3),(1,2)
go
View Code

一，基于分区的运算

窗口和分区都是通过OVER()子句来定义的，使用partition by 子句把结果集划分为多个分区，使用rows 或 range命令把分区分为多个窗口。

基于分区的运算主要是指：

  • 基于分区做聚合运算时：把每个分区作为一个GROUP BY的分组，基于分组做聚合运算。
  • 基于分区做排名运算时：分区中的行集按照OVER()子句的ORDER BY子句指定的顺序排名。
  • 基于分区做数据分析：主要是基于分区做百分位、相对位置百分比、排名百分比等运算。

当使用Over()函数计算整个分区的聚合值时，partition by子句是必需的，Order by 子句要省略：

select ID
    ,Code
    ,count(0) over(partition by Code) as Count_Over
    ,sum(ID) over(partition by Code) as Sum_Over
from dbo.dt_test

二，限制窗口的数据行

OVER()子句使用Rows和Range来限制分区中的数据行，用于对分区的数据行进行细分，得到分区的部分数据行。这两个两个关键字必须跟在Order By子句，都是基于当前行（Current Row）向前或向后来限制分区的数据行。

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

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

<window frame between> ::= BETWEEN <window frame preceding> AND <window frame following>
<window frame preceding> ::= UNBOUNDED PRECEDING  | unsigned_value PRECEDING  | CURRENT ROW
<window frame following> ::= UNBOUNDED FOLLOWING  | unsigned_value FOLLOWING  | CURRENT ROW

Rows 和 Range子句中的特殊关键字：

  • UNBOUNDED PRECEDING：用于指定分区的第一行
  • UNBOUNDED FOLLOWING：用于指定分区的最后一行
  • CURRENT ROW：指定当前数据行
  • <unsigned_value> PRECEDING：在分区中，指定相对于当前行之前的数据行数量，unsigned_value是>0的整数
  • <unsigned_value> FOLLOWING：在分区中，指定相对于当前行之后的数据行数量，unsigned_value是>0的整数

在Over()子句中，使用Rows 或Range 命令进一步限制分区的数据行，在对分区进行细分时，必须注意：

1，必需条件

Rows 和 Range必须跟在Order by 子句之后，在同一个分区中对排序的结果集进行限制。经过我的观测，当在分区中使用order by子句后，SQL Server 默认的操作是：在order by子句之后追加 range between unbounded preceding and current row

range between unbounded preceding and current row

2，Rows 关键字

在同一个分区中，基于当前行（Current Row），通过指定一个固定数量的行数来限制分区中的数据行，

例子1，在同一个分区中，从分区的第一行到当前行：

rows between unbounded preceding and current row

例子2，同一分区中，从当前行到下一行：

rows between current row and 1 following

3，Range 关键字

在同一分区中，使用排序列的值的范围来限制分区中的数据行，当排序列存在重复值，重复的多行属于同一个范围。Range命令只能用于从分区的开始或者从结尾到当前行，不能使用 <unsigned_value> PRECEDING 和<unsigned_value>  FOLLOWING，Range命令的格式只能是：

range between unbounded preceding and current row
range between current row and unbounded following

4，Rows 和 Range的区别

在同一个分区中，对于下面的命令，如果排序列不存在重复值，那么Rows和Range返回的结果是相同的；如果排序行存储在重复值，那么Rows和Range返回的结果可能不同。

range|rows between unbounded preceding and current row
range|rows between current row and unbounded following

三，举例说明基于值范围（Range）的滑动窗口

窗口可以基于值范围进行滑动，从窗口的第一行开始，到当前行的值结束。在Over()子句中，Partition By定义分区，Order By定义窗口中滑动的方向。

举个例子，使用以下代码创建基于值范围的滑动窗口：

select ID
    ,Code
    ,count(0) over(partition by Code order by ID) as Count_Over
    ,sum(ID) over(partition by Code order by ID) as Sum_Over
    ,count(0) over(partition by Code order by ID range between unbounded preceding and current row) as Count_Over
    ,sum(ID) over(partition by Code order by ID range between unbounded preceding and current row) as Sum_Over
from dbo.dt_test
order by Code
　　,ID

查询的结果按照 Code 和ID 排序，在Over()子句中，按照Code分区，按照ID排序。

分析查询结果

1，当Code=1时

当Code=1时，在这个分区（窗口）中，有两行数据，ID分别是1，3

当Code=1，ID=1时，是分区的第一行，Count_Over=1，Sum_Over=1，这是滑动窗口的第一行，聚合值是的计算逻辑是

select count(0) as Count_Over,
    sum(ID) as Sum_Over
from dbo.dt_test
where Code=1 
     and ID<=1

当Code=1，ID=3时，是分区的第二行，Count_Over=2，Sum_Over=4，这是滑动窗口的第二行，聚合值是的计算逻辑是

select count(0) as Count_Over,
    sum(ID) as Sum_Over
from dbo.dt_test
where Code=1 
     and ID<=3

2，当Code=2时

当Code=2时，在这个分区中，有三种，ID分别是1，1，3

当Code=2，ID=1时，是分区的第一行，Count_Over=2，Sum_Over=2，这是滑动窗口的第一行，聚合值是的计算逻辑是

select count(0) as Count_Over,
    sum(ID) as Sum_Over
from dbo.dt_test
where Code=2 
and ID<=1

当Code=2，ID=1时，是分区的第二行，Count_Over=2，Sum_Over=2，这是滑动窗口的第二行，聚合值是的计算逻辑是

select count(0) as Count_Over,
    sum(ID) as Sum_Over
from dbo.dt_test
where Code=2 
and ID<=1

当Code=2，ID=3时，是分区的第三行，Count_Over=3，Sum_Over=5，这是滑动窗口的第三行，聚合值是的计算逻辑是

select count(0) as Count_Over,
    sum(ID) as Sum_Over
from dbo.dt_test
where Code=2 
and ID<=3

四，基于滑动窗口的运算举例

例子1，对分区中的连续两行计算加和

计算逻辑是：在当前分区中，对当前行和其之后的1行数据计算加和；

select ID,Code,sum(code) over(partition by ID order by Code rows between current row and 1 following) as SumCode
from dbo.dt_test

例子2，对分区中的数据行，按照特定顺序，计算从第一行到当前行的累加值

计算逻辑是：从第一行到当前行，计算累加值

select ID,Code,
    sum(code) over(partition by ID order by Code rows between unbounded preceding and current row) as SumCode_Rows,
    sum(code) over(partition by ID order by Code range between unbounded preceding and current row) as SumCode_Range
from dbo.dt_test

参考文档：

OVER Clause (Transact-SQL)

SQL Server Window Function 窗体函数读书笔记二 - A Detailed Look at Window Functions

总结SQL Server窗口函数的简单使用

SQL Server中的窗口函数

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ljhdo/p/4533456.html