SQL窗⼝函数OVER详细⽤法，⼀学就会
点击关注上⽅“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),
rank函数的用法
(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 Score
UNION ALL
SELECT 2,'⼀班',85
UNION ALL
SELECT 3,'⼀班',85
UNION ALL
SELECT 4,'⼆班',80
UNION ALL
SELECT 5,'⼆班',74
UNION 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;
结果如下：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论