窗口和窗口函数

所谓窗口，是指对于Select子句查询的结果集，OVER()子句按照指定的分区字段定义的行集，也就是说，一个窗口是数据行的集合。如下图所示，按照Province字段来对结果集分窗口：

窗口函数是应用于窗口的函数，像排名函数，分析函数和聚合函数，都可以计算窗口中的行集的值。您可以把OVER子句与窗口函数一起使用来计算聚合值，例如移动平均值，累积聚合，运行总计或每组结果的前N个。基于窗口的计算，可以把每一个窗口看作是一个分组，或分区。

窗口具有移动（或者滑动）的特性，这是由OVER子句中的ORDER BY子句来定义的，按照特定的顺序做基于窗口的计算。

注意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 子句把结果集分为多个窗口。基于窗口的运算主要是指：

• 基于窗口做聚合运算时：把每个窗口作为一个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

查询的结果等价于（传统写法）

select ID,Code, (select count(0) from dbo.dt_test as ij where ij.Code=oj.Code) as Count_Over,
               (select sum(id) from dbo.dt_test as ij where ij.Code=oj.Code) as Sum_Over
from dbo.dt_test oj

相比传统写法，Over函数写法查询性能更好。

二，滑动窗口

窗口具有滑动的特性：在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
from dbo.dt_test

查询的结果按照 Code 排序，在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

三，基于窗口排名

Over 函数用于排名函数时，Order by子句是必需的，如果省略partition by 子句，那么整个查询结果集是一个窗口，排名函数对整个窗口排名。

select ID,Code, row_number() over(order by Code) as RowID
from dbo.dt_test

如果使用partition by子句，那么排名函数在每一个分区中进行排名，每一行的排名是基于当前的分区。

select ID,Code, row_number() over(partition by code order by ID) as RowID
from dbo.dt_test

四，限制窗口的数据行

 

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

<ROW or RANGE clause> ::= { ROWS | RANGE } <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 PRECEDING  | CURRENT ROW
<window frame following> ::= UNBOUNDED FOLLOWING  | unsigned_value FOLLOWING  | CURRENT ROW

 

 

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

• 必需条件：Rows 和 Range必须跟在Order by 子句之后，对排序的结果进行限制；
• Rows：使用固定的行数来限制分区中的数据行数量；The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row.
• Range：使用Value的范围来限制分区中的数据行数量，排序列的重复值，被认为是一个值；The RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row.
• 在分区中，如果排序行不存在重复值，Rows和Range返回的结果是相同的；如果排序行存储在重复值，Rows和Range返回的结果可能不同；
• Range子句只能从分区的开始或结尾到当前行开始计算，不能使用 <UINT_Number> PRECEDING 和<UINT_Number>  FOLLOWING；

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

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

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 unbounded preceding) as SumCode_Rows,
    sum(code) over(partition by ID order by Code range unbounded preceding) 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