百度360必应搜狗淘宝本站头条
分布式事务框架框架图cad怎么画框架grpc框架flask框架
当前位置：网站首页 > 技术文章 > 正文

每日SQL自学知识点（第六天）—窗口函数全面渗透用法

ccwgpt 2025-01-17 11:11 31 浏览 0 评论

转载自：
www.cnblogs.com/lihaoyang/p/6756956.html


开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。

开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计

算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。

数据表（Oracle）：T_Person 表保存了人员信息，FName 字段为人员姓名，FCity 字段为人员所在的城市名，FAge 字段为人员年龄，FSalary 字段为人员工资

CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FAge INT,FSalary INT)

向 T_Person 表中插入一些演示数据：

INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Tom','BeiJing',20,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Tim','ChengDu',21,4000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Jim','BeiJing',22,3500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Lily','London',21,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('John','NewYork',22,1000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('YaoMing','BeiJing',20,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Swing','London',22,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Guo','NewYork',20,2800);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('YuQian','BeiJing',24,8000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Ketty','London',25,8500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Kitty','ChengDu',25,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Merry','BeiJing',23,3500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Smith','ChengDu',30,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Bill','BeiJing',25,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Jerry','NewYork',24,3300);

select * from t_person:

要计算所有人员的总数，我们可以执行下面的 SQL 语句：SELECT COUNT(*) FROM T_Person

除了这种较简单的使用方式，有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000 元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于 5000 元的员工个数：

select fname,
       fcity,
       fsalary,
       (select count(*) from t_person where fsalary < 5000) 工资少于5000员工总数
  from t_person
 where fsalary < 5000

虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的 SQL 语句展示了如果使用开窗函数来实现同样的效果：

select fname, fcity, fsalary, count(*) over() 工资小于5000员工数
  from t_person
 where fsalary < 5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个 OVER 关键字。

开窗函数格式： 函数名(列) OVER(选项)

OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
在上边的例子中，开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

PARTITION BY 子句：

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独
立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。下面的 SQL 语句用于显示每一个人员的信息以及所属城市的人员数：

select fname,fcity,fage,fsalary,count(*) over(partition by fcity) 所在城市人数 from t_person

COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。比如对于FName等于 Tom的行，它所属的城市是BeiJing，同
属于BeiJing的人员一共有6个，所以对于这一列的显示结果为6。

这就不需要先对fcity分组求和，然后再和t_person表连接查询了，省事儿。

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干
扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

--显示每一个人员的信息、所属城市的人员数以及同龄人的人数：
select fname,
       fcity,
       fage,
       fsalary,
       count(*) over(partition by fcity) 所属城市的人个数,
       count(*) over(partition by fage) 同龄人个数
  from t_person

ORDER BY子句：

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按
照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为：

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表：

“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又被称为定位框架。

例子程序一：查询从第一行到当前行的工资总和：

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到当前行工资求和
  from t_person
这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第
一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的和，这样的计算结果就是按照
工资进行排序的工资值的累积和。

“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是开窗函数中最常使用的定位框架，为了简化使用，如果使用的是这种定位框架，则可以省略定位框架声明部分，
也就是说上边的sql可以简化成：

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary) 到当前行工资求和
  from t_person

例子程序二：把例子程序一的row换成了range，是按照范围进行定位的

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和
  from t_person

区别：

这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”
是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式
主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元，如果按照
“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果 如果按照 “RANGE”进行
范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所
以计算的累积和为从第一条到2000元工资的人员结，所以对 Lily、Swing、Bill这三个人进行开
窗函数聚合计算的时候得到的都是7000（ “ 1000+2000+2000+2000 ”）。

下边这的估计不常用：

例子程序三：

SELECT FName,
       FSalary,
       SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)  前二后二和
  FROM T_Person;
这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2
PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行前两行（2
PRECEDING）到当前行后两行（2 FOLLOWING）的工资和，注意对于第一条和第二条而言它们
的“前两行”是不存在或者不完整的，因此计算的时候也是要按照前两行是不存在或者不完整进
行计算，同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的，同样要进行类似
的处理。

例子程序四：

SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和
FROM T_Person;
这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1
FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行后一行（1
FOLLOWING）到后三行（3 FOLLOWING）的工资和。注意最后一行没有后续行，其计算结果为
空值NULL而非0。

例子程序五：算工资排名

SELECT FName, FSalary,
COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
FROM T_Person;
这里的开窗函数“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第一行
（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的个数，这个可以看作是计算
人员的工资水平排名。

不再用ROWNUM 了 省事了。这个over简写就会出错。

例子程序6：结合max求到目前行的最大值

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值
FROM T_Person;
这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary)
OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”
的简化写法，它表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）
到当前行（CURRENT ROW）的人员的最大工资值。

例子程序6：over(partition by XX order by XX) partition by和order by 结合

员工信息+同龄人最高工资，按工资排序

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(PARTITION BY FAge order by fsalary) 同龄人最高工资
FROM T_Person;

PARTITION BY子句和ORDER BY 可以 共 同 使用，从 而 可以 实现 更 加复 杂 的 功能
==================================================================================

高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数，
还可以在开窗函数中使用一些高级的函数，有些函数同时被DB2和Oracle同时支持，比如
RANK()、DENSE_RANK()、ROW_NUMBER()，而有些函数只被Oracle支持，比如
RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。
下面对这几个函数进行详细介绍。

RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式
不同；ROW_NUMBER()函数计算一行在结果集中的行号，同样可以将其当成排名函数。这三个
函数的功能存在一定的差异，举例如下：工资从高到低排名：

SELECT FName, FSalary,FAge,
RANK() OVER(ORDER BY fsalary desc) f_RANK,
DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER
FROM T_Person;

rank(),dense_rank()语法：

RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
    dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） 
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

row_number() 函数语法：

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 
row_number() 返回的主要是“行”的信息，并没有排名
【参数】
【说明】Oracle分析函数

主要功能：用于取前几名，或者最后几名等

===================================================================

排序函数实际场景使用：计算排行榜，排名

微信活动，每天参与，有得分，活动结束后选出排名靠前的发奖。

每参与一次，就是一个订单，表结构：

比如要查询期号issue为20170410期的排行榜，按得分倒叙排序，得分一样按订单创建先后，算排行，sql需要这么写：

select ROWNUM rank, t.*
  from (select *
          from t_zhcw_order
         where issue = '20170410'
         order by integral desc, create_date asc) t

使用了开窗函数后就可以简化：

select t.*,
               row_number() over(order by t.integral desc, t.create_date asc) 排名
          from t_zhcw_order t
         where issue = '20170410'

如果想只要排名范围，可以在外边再包一层，这也是高效分页的一种方式：

select tt.*  from (
select t.id,
       t.integral,
       t.cell,
       t.create_date,
       row_number() over(order by t.integral desc, t.create_date asc) rankNum
  from t_zhcw_order t
 where t.issue = 20170331
)tt where tt.rankNum<=50

相关推荐

详解DNFSB2毒王的各种改动以及大概的加点框架

首先附上改动部分，然后逐项分析第一个，毒攻掌握技能意思是力量智力差距超过15%的话差距会被强行缩小到15%，差距不到15%则无效。举例：2000力量，1650智力，2000*0.85=1700，则智力...

通篇干货!纵观 PolarDB-X 并行计算框架

作者：玄弟七锋PolarDB-X面向HTAP的混合执行器一文详细说明了PolarDB-X执行器设计的初衷，其初衷一直是致力于为PolarDB-X注入并行计算的能力，兼顾TP和AP场景，逐渐...

字节新推理模型逆袭DeepSeek，200B参数战胜671B，豆包史诗级加强

梦晨发自凹非寺量子位|公众号QbitAI字节最新深度思考模型，在数学、代码等多项推理任务中超过DeepSeek-R1了？而且参数规模更小。同样是MoE架构，字节新模型Seed-Thinkin...

阿里智能化研发起飞!RTP-LLM 实现 Cursor AI 1000 token/s 推理技术揭秘

作者|赵骁勇阿里巴巴智能引擎事业部审校|刘侃，KittyRTP-LLM是阿里巴巴大模型预测团队开发的高性能LLM推理加速引擎。它在阿里巴巴集团内广泛应用，支撑着淘宝、天猫、高德、饿...

多功能高校校园小程序/校园生活娱乐社交管理小程序/校园系统源码

校园系统通常是为学校、学生和教职工提供便捷的数字化管理工具。综合性社交大学校园小程序源码:同城校园小程序-大学校园圈子创业分享，校园趣事，同校跑腿交友综合性论坛。小程序系统基于TP6+Uni-app...

婚恋交友系统nuiAPP前端解决上传视频模糊的问题

婚恋交友系统-打造您的专属婚恋交友平台系统基于TP6+Uni-app框架开发；客户移动端采用uni-app开发，管理后台TH6开发支持微信公众号端、微信小程序端、H5端、PC端多端账号同步，可快速打包...

已节省数百万GPU小时!字节再砍MoE训练成本，核心代码全开源

COMET团队投稿量子位|公众号QbitAI字节对MoE模型训练成本再砍一刀，成本可节省40%！刚刚，豆包大模型团队在GitHub上开源了叫做COMET的MoE优化技术。COMET已应用于字节...

通用电气完成XA102发动机详细设计审查 将为第六代战斗机提供动力

2025年2月19日，美国通用电气航空航天公司（隶属于通用电气公司）宣布，已经完成了“下一代自适应推进系统”（NGAP）计划下提供的XA102自适应变循环发动机的详细设计审查阶段。XA102是通用电气...

tpxm-19双相钢材质（双相钢f60材质）

TPXM-19双相钢是一种特殊的钢材，其独特的化学成分、机械性能以及广泛的应用场景使其在各行业中占有独特的地位。以下是对TPXM-19双相钢的详细介绍。**化学成分**TPXM-19双相钢的主要化学成...

thinkphp6里怎么给layui数据表格输送数据接口

layui官网已经下架了，但是产品还是可以使用。今天一个朋友问我怎么给layui数据表格发送数据接口，当然他是学前端的，后端不怎么懂，自学了tp框架问我怎么调用。其实官方文档上就有相应的数据格式，js...

完美可用的全媒体广告精准营销服务平台PHP源码

今天测试了一套php开发的企业网站展示平台，还是非常不错的，下面来给大家说一下这套系统。1、系统架构这是一套基于ThinkPHP框架开发的HTML5响应式全媒体广告精准营销服务平台PHP源码。现在基于...

一对一源码开发，九大方面完善基础架构

以往的直播大多数都是一对多进行直播社交，弊端在于不能满足到每个用户的需求，会降低软件的体验感。伴随着用户需求量的增加，一对一直播源码开始出现。一个完整的一对一直播流程即主播发起直播→观看进入房间观看→...

Int J Biol Macromol .|交联酶聚集体在分级共价有机骨架上的固定化:用于卤代醇不对称合成的高稳定酶纳米反应器

大家好，今天推送的文章发表在InternationalJournalofBiologicalMacromolecules上的“Immobilizationofcross-linkeden...

【推荐】一款开源免费的 ChatGPT 聊天管理系统，支持PC、H5等多端

如果您对源码&技术感兴趣，请点赞+收藏+转发+关注，大家的支持是我分享最大的动力！！！项目介绍GPTCMS是一款开源且免费（基于GPL-3.0协议开源）的ChatGPT聊天管理系统，它基于先进的GPT...

高性能计算(HPC)分布式训练:训练框架、混合精度、计算图优化

在深度学习模型愈发庞大的今天，分布式训练、高效计算和资源优化已成为AI开发者的必修课。本文将从数据并行vs模型并行、主流训练框架（如PyTorchDDP、DeepSpeed）、混合精度训练（...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表