SQLServer 窗口函数（转载）

一、窗口函数的作用

---

 

窗口函数是对一组值进行操作，不需要使用GROUP BY 子句对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。窗口函数，基础列和聚合列的查询都非常简单。

 

 

二、语法格式

---

 

窗口函数的语法格式如下：

OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)

• PARTITION：分组;
• ORDER BY：排序;

 

首先建一张调试表如下：

CREATE TABLE [dbo].[xxx](
　　[Id] [int] NULL,
　　[Name] [nvarchar](50) NULL,
　　[Operate] [nvarchar](50) NULL,
　　[Score] [int] NULL,
　　[CreateTime] [datetime] NULL
) ON [PRIMARY]

往里面添加如下数据。

 

 

三、应用场景

---

 

1、聚合列与数据列共同显示

　--查询姓名、分数、以及全体平均分
　SELECT Name, Score, CAST(AVG(Score) OVER() AS decimal(5,2) )AS '平均分' FROM xxx

注意在下面的微软官方文档中有提到，如果窗口函数OVER不加任何参数，相当于应用在整个查询的结果集上：

If you don't specify any argument, the window functions are applied on the entire result set.

Arguments

 

2、分组日期最新

--对每个人查询日期最新列
SELECT * FROM 
(
    SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) AS part ,Score, Name, CreateTime FROM xxx
) AS C
WHERE C.part = 1

 

3、分页

返回结果集内的行号，每个分区从1开始，ORDER BY可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

 

 

四、排名函数

---

 

1、ROW_NUMBER()

返回结果集内的行号，每个分区从1开始计算，ORDER BY可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

    SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) ,Score, Name, CreateTime
    FROM xxx

输出如下：

 

2、RANK()

返回结果集的分区内每行的排序。行的排名是从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, CreateTime
FROM xxx

下面的示例展示了Rank()与Dense_Rank()的区别。主要区别就是Rank返回的排名数字可能是非连续的，因为同样大小的排名数字会占位，而Dense_Rank返回的排名数字一定是连续的，因为同样大小的排名数字不会占位。

Rank:排名相同的名次一样，同一排名有几个，后面排名就会跳过几次
Dense_Rank:排名相同的名次一样，且后面名次不跳跃

 

 

4、NTILE()

NTILE函数把结果中的行关联到组，并为每一行分配一个所属的组的编号，编号从1开始。对于每一个行，NTILE 将返回此行所属的组的编号。

如果分区的行数不能被 integer_expression(就是传入的那个参数,表示分几个组的意思) 整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。

--每个分区分2个组，该列是改行所属的组名
SELECT NTILE(2) OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTime
FROM xxx

示例如下：

 

关于窗口函数，详情还可以参考下面的微软官方文档： 

SELECT - OVER clause (Transact-SQL)

 

原文链接