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 43
2 AA bb 23
3 AA aa 40
4 AA dd 18
5 AA bb 15
6 AA ff 20
7 BB gg 20
8 BB hh 58
9 BB hh 13
10 BB bb 23
11 BB gg 20
12 DD dd 43
13 DD ff 43
14 EE ee 48
15 FF ss 50
16 GG cc 48
17 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 1
2 AA bb 23 1 2 1 1
3 AA aa 40 1 3 1 1
4 AA dd 18 1 4 1 1
5 AA bb 15 1 5 1 1
6 AA ff 20 1 6 1 2
7 BB gg 20 2 7 7 2
8 BB hh 58 2 8 7 2
9 BB hh 13 2 9 7 2
10 BB bb 23 2 10 7 3
11 BB gg 20 2 11 7 3
12 DD dd 43 3 12 12 3
13 DD ff 43 3 13 12 3
14 EE ee 48 4 14 14 4
15 FF ss 50 5 15 15 4
16 GG cc 48 6 16 16 4
17 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 1
4 AA dd 18 2 2 2 1
6 AA ff 20 3 3 3 2
2 AA bb 23 4 4 4 2
3 AA aa 40 5 5 5 3
1 AA aa 43 6 6 6 4
9 BB hh 13 1 1 1 1
11 BB gg 20 2 2 2 1
7 BB gg 20 2 3 2 2
10 BB bb 23 3 4 4 3
8 BB hh 58 4 5 5 4
12 DD dd 43 1 1 1 1
13 DD ff 43 1 2 1 2
14 EE ee 48 1 1 1 1
15 FF ss 50 1 1 1 1
16 GG cc 48 1 1 1 1
17 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 83
3 AA aa 40 83
2 AA bb 23 38
5 AA bb 15 38
10 BB bb 23 61
16 GG cc 48 48
4 AA dd 18 18
12 DD dd 43 61
17 HH dd 50 111
14 EE ee 48 48
6 AA ff 20 20
13 DD ff 43 63
11 BB gg 20 40
7 BB gg 20 40
8 BB hh 58 71
9 BB hh 13 71
15 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 AA
3 AA aa 40 AA
2 AA bb 23 BB
5 AA bb 15 GG
10 BB bb 23 HH
16 GG cc 48 DD
17 HH dd 50 AA
12 DD dd 43 EE
4 AA dd 18 DD
14 EE ee 48 AA
13 DD ff 43 BB
6 AA ff 20 BB
7 BB gg 20 BB
11 BB gg 20 BB
8 BB hh 58 FF
9 BB hh 13 No
15 FF ss 50 No
2300
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
