SQL中常见的开窗函数

OVER的定义

OVER⽤于为⾏定义⼀个窗⼝，它对⼀组值进⾏操作，不需要使⽤GROUP BY⼦句对数据进⾏分组，能够在同⼀⾏中同时返回基础⾏的列和聚合列。

OVER的语法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )PARTITION BY ⼦句进⾏分组；ORDER BY ⼦句进⾏排序。

窗⼝函数OVER()指定⼀组⾏，开窗函数计算从窗⼝函数输出的结果集中各⾏的值。

开窗函数不需要使⽤GROUP BY就可以对数据进⾏分组，还可以同时返回基础⾏的列和聚合列。

OVER的⽤法

OVER开窗函数必须与聚合函数或排序函数⼀起使⽤，聚合函数⼀般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数⼀般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

OVER在聚合函数中使⽤的⽰例

我们以SUM和COUNT函数作为⽰例来给⼤家演⽰。

--建⽴测试表和测试数据CREATE TABLE Employee(

ID INT PRIMARY KEY,Name VARCHAR(20),

GroupName VARCHAR(20),Salary INT)

INSERT INTO Employee

VALUES(1,'⼩明','开发部',8000), (4,'⼩张','开发部',7600), (5,'⼩⽩','开发部',7000), (8,'⼩王','财务部',5000), (9, null,'财务部',NULL), (15,'⼩刘','财务部',6000), (16,'⼩⾼','⾏政部',4500), (18,'⼩王','⾏政部',4000), (23,'⼩李','⾏政部',4500), (29,'⼩吴','⾏政部',4700);

SUM后的开窗函数

SELECT *,

SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总⼯资,

SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总⼯资, SUM(Salary) OVER(ORDER BY ID) 累计⼯资, SUM(Salary) OVER() 总⼯资from Employee

(提⽰：可以左右滑动代码)结果如下：

其中开窗函数的每个含义不同，我们来具体解读⼀下：SUM(Salary) OVER (PARTITION BY Groupname)

只对PARTITION BY后⾯的列Groupname进⾏分组，分组后求解Salary的和。SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

对PARTITION BY后⾯的列Groupname进⾏分组，然后按ORDER BY 后的ID进⾏排序，然后在组内对Salary进⾏累加处理。SUM(Salary) OVER (ORDER BY ID)

只对ORDER BY 后的ID内容进⾏排序，对排完序后的Salary进⾏累加处理。SUM(Salary) OVER ()对Salary进⾏汇总处理

COUNT后的开窗函数

SELECT *,

COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数,

COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数, COUNT(*) OVER(ORDER BY ID) 累积个数 , COUNT(*) OVER() 总个数from Employee

返回的结果如下图：

后⾯的每个开窗函数就不再⼀⼀解读了，可以对照上⾯SUM后的开窗函数进⾏⼀⼀对照。

OVER在排序函数中使⽤的⽰例

我们对4个排序函数⼀⼀演⽰

--先建⽴测试表和测试数据WITH t AS

(SELECT 1 StuID,'⼀班' ClassName,70 ScoreUNION ALL

SELECT 2,'⼀班',85UNION ALL

SELECT 3,'⼀班',85UNION ALL

SELECT 4,'⼆班',80UNION ALL

SELECT 5,'⼆班',74UNION ALL

SELECT 6,'⼆班',80)

SELECT * INTO Scores FROM t;SELECT * FROM Scores

ROW_NUMBER()

定义：ROW_NUMBER()函数作⽤就是将SELECT查询到的数据进⾏排序，每⼀条数据加⼀个序号，他不能⽤做于学⽣成绩的排名，⼀般多⽤于分页查询，⽐如查询前10个 查询10-100个学⽣。ROW_NUMBER()必须与ORDER BY⼀起使⽤，否则会报错。

对学⽣成绩排序

SELECT *,

ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序,ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序FROM Scores;

结果如下：

这⾥的PARTITION BY和ORDER BY的作⽤与我们在上⾯看到的聚合函数的作⽤⼀样，都是⽤来进⾏分组和排序使⽤的。

此外ROW_NUMBER()函数还可以取指定顺序的数据。

SELECT * FROM (

SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序FROM Scores

) t WHERE t.总排序=2;

结果如下：

RANK()

定义：RANK()函数，顾名思义排名函数，可以对某⼀个字段进⾏排名，这⾥和ROW_NUMBER()有什么不⼀样呢？ROW_NUMBER()是排序，当存在相同成绩的学⽣时，ROW_NUMBER()会依次进⾏排序，他们序号不相同，⽽Rank()则不⼀样。如果出现相同的，他们的排名是⼀样的。下⾯看例⼦: ⽰例

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],*

FROM Scores;

SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*FROM Scores;

结果：

其中上图是ROW_NUMBER()的结果，下图是RANK()的结果。当出现两个学⽣成绩相同是⾥⾯出现变化。RANK()是1-1-3-3-5-6，⽽ROW_NUMBER()则还是1-2-3-4-5-6，这就是RANK()和ROW_NUMBER()的区别了。

DENSE_RANK()

定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进⾏排名，那它和RANK()到底有什么不同那？特别是对于有成绩相同的情况，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，⼀般情况下⽤的排名函数就是RANK() 我们看例⼦： ⽰例

SELECT

RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*FROM Scores;

SELECT

DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*FROM Scores;

结果如下：

上⾯是RANK()的结果，下⾯是DENSE_RANK()的结果 NTILE()

定义：NTILE()函数是将有序分区中的⾏分发到指定数⽬的组中，各个组有编号，编号从1开始，就像我们说的'分区'⼀样 ，分为⼏个区，⼀个区会有多少个。

SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;

结果如下：

就是将查询出来的记录根据NTILE函数⾥的参数进⾏平分分区。