一、窗口函数的作用
窗口函数是对一组值进行操作,不需要使用GROUP BY 子句对数据进行分组,还能够在同一行中同时返回基础行的列和聚合列。窗口函数,基础列和聚合列的查询都非常简单。
二、语法格式
窗口函数的语法格式如下:
1
|
OVER([PARTITION
BY
value_expression,..[n] ] <
ORDER
BY
BY_Clause>)
|
-
PARTITION:分组;
-
ORDER BY:排序;
首先建一张调试表如下:
1
2
3
4
5
6
|
CREATE
TABLE
[dbo].[xxx](
[Id] [
int
]
NULL
,
[
Name
] [nvarchar](50)
NULL
,
[Operate] [nvarchar](50)
NULL
,
[Score] [
int
]
NULL
,
[CreateTime] [datetime]
NULL
)
ON
[
PRIMARY
]
|
往里面添加如下数据。
三、应用场景
1、聚合列与数据列共同显示
1
2
|
--查询姓名、分数、以及全体平均分
SELECT
Name
, Score,
CAST
(
AVG
(Score) OVER()
AS
decimal
(5,2) )
AS
'平均分'
FROM
xxx
|
2、分组日期最新
1
2
3
4
|
--对每个人查询日期最新列
SELECT
*
FROM
(
SELECT
row_number() OVER(PARTITION
BY
Name
ORDER
BY
CreateTime)
AS
part ,Score,
Name
, CreateTime
FROM
xxx
)
AS
CWHERE C.part = 1
|
3、分页
返回结果集内的行号,每个分区从1开始,ORDER BY可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
四、排名函数
1、ROW_NUMBER()
返回结果集内的行号,每个分区从1开始计算,ORDER BY可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
1
|
SELECT
row_number() OVER(PARTITION
BY
Name
ORDER
BY
CreateTime) ,Score,
Name
, CreateTime
FROM
xxx
|
输出如下:
2、RANK()
返回结果集的分区内每行的排序。行的排名是从1开始算。如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。
1
|
SELECT
RANK() OVER(PARTITION
BY
Name
ORDER
BY
SCORE) ,Score,
Name
, CreateTime
FROM
xxx
|
下面一张图片很好地说明了Rank与ROW_NUMBER的区别。
3、DENSE_RANK()
返回结果集分区中行的排名,与Rank()类似,只是对并列的处理稍有不同,详见示例。
SELECT DENSE_RANK() OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTimeFROM xxx
下面的示例展示了Rank()与Dense_Rank()的区别。
4、NTILE()
NTILE函数把结果中的行关联到组,并为每一行分配一个所属的组的编号,编号从1开始。对于每一个行,NTILE 将返回此行所属的组的编号。
如果分区的行数不能被 integer_expression(就是传入的那个参数,表示分几个组的意思) 整除,则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序,较大的组排在较小的组前面。
--每个分区分2个组,该列是改行所属的组名SELECT NTILE(2) OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTimeFROM xxx
示例如下: