SQL中的开窗函数是什么

119次阅读

没有评论

共计 3433 个字符，预计需要花费 9 分钟才能阅读完成。

本篇内容主要讲解“SQL 中的开窗函数是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“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 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;

结果如下：