一篇文章看懂SQL中的开窗函数
作者:萧木易 发布时间:2024-01-16 07:22:05
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;
结果如下:
就是将查询出来的记录根据NTILE函数里的参数进行平分分区。
来源:https://blog.csdn.net/weixin_43997319/article/details/124964670
猜你喜欢
- 本文实例讲述了python实现将pvr格式转换成pvr.ccz的方法。分享给大家供大家参考。具体实现方法如下:import zlibimpo
- 前言在之前的一篇文章Python可视化神器-Plotly动画展示展现了可视化神器-Plotly的动画的基本应用,本文介绍如何在Python中
- 前段时间做一个小项目碰到了一个导航制作的方式然后突然想到曾经很久以前看到的梯形状的不规则导航,就尝试做了一下。结果碰到了几个问题,后来在同事
- 假设我们有一段程序,从 Redis 中读取数据,解析以后提取出里面的 name 字段:import jsonimport redisclie
- 为了自定义一个模板标签,你需要告诉Django当遇到你的标签时怎样进行这个过程。当Django编译一个模板时,它将原始模板分成一个个 节点
- Python模块,简单说就是一个.py文件,其中可以包含我们需要的任意Python代码。迄今为止,我们所编写的所有程序都包含在单独的.py文
- 本文实例讲述了python orm 框架中sqlalchemy用法。分享给大家供大家参考,具体如下:一.ORM简介1. ORM(Object
- 实战场景经常有朋友问,学 Python 面向对象时,翻阅别人代码,会发现一个 super() 函数,那这个函数的作用到底是什么?super(
- 本文实例讲述了Python Flask框架模板操作。分享给大家供大家参考,具体如下:模板在前面的示例中,视图函数的主要作用是生成请求的响应,
- Function closeUBB(strContent) '*************************
- 一、背景希望根据企业名称查询其经纬度,所在的省份、城市等信息。直接将企业名称传给百度地图提供的API,得到的经纬度是非常不准确的,因此希望获
- 简介OpenCV中使用VideoCapture类写的视频是没有音频的,如果要进一步处理音频则需要用到一个库——MoviePy,这个库是Pyt
- 递归查询对于同一个表父子关系的计算提供了很大的方便,这个示例使用了SQL server 2005中的递归查询,使用的表是CarParts,这
- 西北望乡何处是,东南见月几回圆。月亮又慢悠悠的挂上了天空,趁着睡前梦呓,我就带领各位可爱的读者们探索MySql最后的子查询部分。说明:有些查
- 从文件中读取数据读取整个文件这里假设在当前目录下有一个文件名为'pi_digits.txt'的文本文件,里面的数据如下:3.
- 学习Python Web和Django开发不能只学习Python。我们有时必需借助其它技术比如AJAX实现我们想要的功能。今天我们就要利用D
- 延时摄影(英语:Time-lapse photography)是以一种较低的帧率拍 下图像或者视频,然后用正常或者较快的速率播放画面的摄影技
- 1、cat:拼接直接合并数据2、stack拼接:与cat不同的是,stack创建了一个新的维度,在拼接的同时,给数据增加了类别。并且stac
- 优雅的设计经常包含一些特殊的字体,而这些字体并不存在于用户的字体库中,我们并不能奢求每一个访客都是设计师。 :-)虽然CSS3标
- 参数数量及其作用该函数共有两个参数,分别是key和scope。def get_collection(key, scope=None) Wra