OVER 子句
在应用关联的开窗函数前确定行集的分区和排序。 也就是说，OVER 子句定义查询结果集内的窗口或用户指定的行集。 然后，开窗函数将计算窗口中每一行的值。 可以将 OVER 子句与函数一起使用，以便计算各种聚合值，例如移动平均值、累积聚合、运行总计或每组结果的前 N 个结果。
创建表
create table [dbo].[B]
([ID] [smallint] not null primary key,[FirstName] [varchar](3) NOT NULL,[LastName] [varchar](3) NULL,[Age] [tinyint] NOT NULL,
); ID FirstName LastName Age
---- --------- -------- ----1 AA aa 432 AA bb 233 AA aa 404 AA dd 185 AA bb 156 AA ff 207 BB gg 208 BB hh 589 BB hh 1310 BB bb 2311 BB gg 2012 DD dd 4313 DD ff 4314 EE ee 4815 FF ss 5016 GG cc 4817 HH dd 50
排名函数
排名函数为分区中的每一行返回一个排名值。 根据所用函数的不同，某些行可能与其他行接收到相同的值。 排名函数具有不确定性。
-
DENSE_RANK 此函数返回结果集分区中每行的排名，排名值没有间断。 特定行的排名等于该特定行之前不同排名值的数量加一。
-
ROW_NUMBER 对结果集的输出进行编号。 具体来说，返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
ROW_NUMBER和RANK类似。ROW_NUMBER按顺序对所有行进行编号（例如 1、2、3、4、5）。RANK为关系提供相同的数（例如 1、2、2、4、5）。 -
RANK 返回结果集的分区内每行的排名。 行的排名是相关行之前的排名数加一。
-
NTILE 将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，NTILE 将返回此行所属的组的编号。
例1：不将结果集重新划分
select *,
DENSE_RANK() over (order by [FirstName]) as [DENSE_RANK],
ROW_NUMBER() over (order by [FirstName]) as [ROW_NUMBER],
RANK() over (order by [FirstName]) as [RANK],
NTILE(4) over (order by [FirstName]) as [NTILE]
from [B]; ID FirstName LastName Age DENSE_RANK ROW_NUMBER RANK NTILE
---- --------- -------- ---- -------------------- -------------------- -------------------- --------------------1 AA aa 43 1 1 1 12 AA bb 23 1 2 1 13 AA aa 40 1 3 1 14 AA dd 18 1 4 1 15 AA bb 15 1 5 1 16 AA ff 20 1 6 1 27 BB gg 20 2 7 7 28 BB hh 58 2 8 7 29 BB hh 13 2 9 7 210 BB bb 23 2 10 7 311 BB gg 20 2 11 7 312 DD dd 43 3 12 12 313 DD ff 43 3 13 12 314 EE ee 48 4 14 14 415 FF ss 50 5 15 15 416 GG cc 48 6 16 16 417 HH dd 50 7 17 17 4例2：将结果集重新划分 （没有划分相当于只有一个分区，划分之后就根据划分依据生成几个分区）
select *,
DENSE_RANK() over (Partition BY [FirstName] order by [Age]) as [DENSE_RANK],
ROW_NUMBER() over (Partition BY [FirstName] order by [Age]) as [ROW_NUMBER],
RANK() over (Partition BY [FirstName] order by [Age]) as [RANK],
NTILE(4) over (Partition BY [FirstName] order by [Age]) as [NTILE]
from [B]; ID FirstName LastName Age DENSE_RANK ROW_NUMBER RANK NTILE
---- --------- -------- ---- -------------------- -------------------- -------------------- --------------------5 AA bb 15 1 1 1 14 AA dd 18 2 2 2 16 AA ff 20 3 3 3 22 AA bb 23 4 4 4 23 AA aa 40 5 5 5 31 AA aa 43 6 6 6 49 BB hh 13 1 1 1 111 BB gg 20 2 2 2 17 BB gg 20 2 3 2 210 BB bb 23 3 4 4 38 BB hh 58 4 5 5 412 DD dd 43 1 1 1 113 DD ff 43 1 2 1 214 EE ee 48 1 1 1 115 FF ss 50 1 1 1 116 GG cc 48 1 1 1 117 HH dd 50 1 1 1 1聚合函数
聚合函数对一组值执行计算，并返回单个值。 除了 COUNT(*) 外，聚合函数都会忽略 Null 值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。
所有聚合函数均为确定性函数。 换言之，每次使用一组特定的输入值调用聚合函数时，它们所返回的值都是相同的。 有关函数确定性的详细信息，请参阅确定性函数和不确定性函数。 OVER 子句可以跟在除 STRING_AGG、GROUPING 或 GROUPING_ID 函数以外的所有聚合函数后面。
只能在以下位置将聚合函数作为表达式使用：
- SELECT 语句的选择列表（子查询或外部查询）。
- HAVING 子句。
Transact-SQL 提供下列聚合函数：
- APPROX_COUNT_DISTINCT
- AVG
- CHECKSUM_AGG
- COUNT
- COUNT_BIG
- GROUPING --不能用OVER子句
- GROUPING_ID --不能用OVER子句
- MAX
- MIN
- STDEV
- STDEVP
- STRING_AGG --不能用OVER子句
- SUM
- VAR
- VARP
select *, sum ([Age]) over (partition by [LastName] order by [FirstName]) as [Sum] from [B] ID FirstName LastName Age Sum
---- --------- -------- ---- -----------1 AA aa 43 833 AA aa 40 832 AA bb 23 385 AA bb 15 3810 BB bb 23 6116 GG cc 48 484 AA dd 18 1812 DD dd 43 6117 HH dd 50 11114 EE ee 48 486 AA ff 20 2013 DD ff 43 6311 BB gg 20 407 BB gg 20 408 BB hh 58 719 BB hh 13 7115 FF ss 50 50
分析函数
分析函数基于一组行计算聚合值。 但是，与聚合函数不同，分析函数可能针对每个组返回多行。 可以使用分析函数来计算移动平均线、运行总计、百分比或一个组内的前 N 个结果。
SQL Server 支持以下分析函数：
- CUME_DIST (Transact-SQL)
- FIRST_VALUE (Transact-SQL)
- LAG (Transact-SQL)
- LAST_VALUE (Transact-SQL)
- LEAD (Transact-SQL)
- PERCENT_RANK (Transact-SQL)
- PERCENTILE_CONT (Transact-SQL)
- PERCENTILE_DISC (Transact-SQL)
select *, LEAD ([FirstName], 2, 'No') OVER (order by [LastName]) as [LEAD2] from [B];【LEAD】访问相同结果集的后续行中的数据，而不使用自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。
ID FirstName LastName Age LEAD2
---- --------- -------- ---- -----1 AA aa 43 AA3 AA aa 40 AA2 AA bb 23 BB5 AA bb 15 GG10 BB bb 23 HH16 GG cc 48 DD17 HH dd 50 AA12 DD dd 43 EE4 AA dd 18 DD14 EE ee 48 AA13 DD ff 43 BB6 AA ff 20 BB7 BB gg 20 BB11 BB gg 20 BB8 BB hh 58 FF9 BB hh 13 No15 FF ss 50 No