40.1 OVER的定义

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

 

40.2 OVER的语法

OVER([PARTITION BY column] [ORDER BY column])

--PARTITION BY 子句进行分组
--ORDER BY 子句进行排序

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

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

 

40.3 OVER的用法

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

 

40.4 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);

 

40.5 SUM后的开窗函数

SELECT *,
SUM(Salary) OVER(PARTITION BY GroupName) AS 每个组的总工资,
SUM(Salary) OVER(PARTITION BY GroupName ORDER BY ID) AS 每个组的累计总工资,
SUM(Salary) OVER(ORDER BY ID) AS 累计工资,
SUM(Salary) OVER() AS 总工资
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进行汇总处理。

 

40.6 COUNT后的开窗函数

SELECT *,
COUNT(*) OVER(PARTITION BY GroupName) AS 每个组的个数,
COUNT(*) OVER(PARTITION BY GroupName ORDER BY ID) AS 每个组的累积个数,
COUNT(*) OVER(ORDER BY ID) AS 累积个数,
COUNT(*) OVER() AS 总个数
FROM Employee;

 

40.7 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;

 

40.8 ROW_NUMBER()

定义：ROW_NUMBER()函数的作用就是将SELECT查询到的数据进行排序，每一条数据加一个序号，一般多用于分页查询，比如查询前10个、查询10-100个学生。ROW_NUMBER()必须与ORDER BY一起使用，否则会报错。

对学生成绩排序：

SELECT *,
ROW_NUMBER() OVER(PARTITON BY ClassName ORDER BY SCORE DESC) AS 班内排序,
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
) AS t
WHERE t.总排序=2;

结果如下：

 

 

40.9 RANK()

定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里和ROW_NUMBER()有什么不一样呢？ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而RANK()则不一样，如果出现相同的，他们的排名是一样的。

示例：

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


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

结果：