row_number() OVER(PARTITION BY)函数介绍

SQL中的开窗函数 OVER的定义 OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。 OVER的语法 OVER ([ PARTITION BYcolumn ][ ORDER BY culumn ]) PARTITION BY 子句进行分组; ORDER BY 子句进行排序。 窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。 开窗函数不需要使用GROUP BY就可以对数据进行分组,... 阅读详情

转自:http://blog.youkuaiyun.com/631799/article/details/7419797


OVER(PARTITION BY)函数介绍

开窗函数          
     Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。

      开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
1:over后的写法:    
   over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数
   over(partition by deptno)按照部门分区

 

     over(partition by deptno order by salary)

 

2:开窗的窗口范围:
over(order by salary range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。

举例:

 

--sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和

  select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following) mm from t2
adf               3               45               45   --45加2减2即43到47,但是s在这个范围内只有45
asdf             3               55               55
cfe               2               74               74
3dd               3               78               158 --78在76到80范围内有78,80,求和得158
fda               1               80               158
gds               2               92               92
ffd               1               95               190
dss               1               95               190
ddd               3               99               198

gf         3        99        198

 

 

 

over(order by salary   rows  between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。

举例:

 

--sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内
select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2
adf               3               45               174   (45+55+74=174)
asdf             3               55               252     (45+55+74+78=252)
cfe               2               74               332       (74+55+45+78+80=332)
3dd               3               78               379       (78+74+55+80+92=379)
fda               1               80               419
gds               2               92               440
ffd               1               95               461
dss               1               95               480
ddd               3               99               388
gf                 3               99               293

 

 

over(order by salary  range  between unbounded preceding and unbounded following)或者
over(order by salary  rows  between unbounded preceding and unbounded following):窗口不做限制

 

3、与over函数结合的几个函数介绍

row_number()over()、rank()over()和dense_rank()over()函数的使用

下面以班级成绩表t2来说明其应用

t2表信息如下:
cfe               2               74
dss               1               95
ffd               1               95
fda               1               80
gds               2               92
gf                 3               99
ddd               3               99
adf               3               45
asdf             3               55
3dd               3               78

select * from                                                                                                                                           
       (                                                                                                                                                     
       select name,class,s,rank()over(partition by class order by s desc) mm from t2
       )                                                                                                                                                     
       where mm=1;
得到的结果是:
dss               1               95               1
ffd               1               95               1
gds               2               92               1
gf                 3               99               1
ddd               3               99               1 

注意:
       1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果;
select * from                                                                                                                                           
       (                                                                                                                                                     
       select name,class,s,row_number()over(partition by class order by s desc) mm from t2
       )                                                                                                                                                     
       where mm=1;
1               95               1   --95有两名但是只显示一个
2               92               1
3               99               1 --99有两名但也只显示一个

       2.rank()和dense_rank()可以将所有的都查找出来:
如上可以看到采用rank可以将并列第一名的都查找出来;
         rank()和dense_rank()区别:
         --rank()是跳跃排序,有两个第二名时接下来就是第四名;
select name,class,s,rank()over(partition by class order by s desc) mm from t2
dss               1               95               1
ffd               1               95               1
fda               1               80               3 --直接就跳到了第三
gds               2               92               1
cfe               2               74               2
gf                 3               99               1
ddd               3               99               1
3dd               3               78               3
asdf             3               55               4
adf               3               45               5
         --dense_rank()l是连续排序,有两个第二名时仍然跟着第三名
select name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
dss               1               95               1
ffd               1               95               1
fda               1               80               2 --连续排序(仍为2)
gds               2               92               1
cfe               2               74               2
gf                 3               99               1
ddd               3               99               1
3dd               3               78               2
asdf             3               55               3
adf               3               45               4

--sum()over()的使用
select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进行分数求和
dss               1               95               190   --由于两个95都是第一名,所以累加时是两个第一名的相加
ffd               1               95               190 
fda               1               80               270   --第一名加上第二名的
gds               2               92               92
cfe               2               74               166
gf                 3               99               198
ddd               3               99               198
3dd               3               78               276
asdf             3               55               331
adf               3               45               376

first_value() over()和last_value() over()的使用  



--找出这三条电路每条电路的第一条记录类型和最后一条记录类型

SELECT opr_id,res_type,
       first_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) low,
       last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type rows BETWEEN unbounded preceding AND unbounded following) high
  FROM rm_circuit_route
WHERE opr_id IN ('000100190000000000021311','000100190000000000021355','000100190000000000021339')
 ORDER BY opr_id;

 

注:rows BETWEEN unbounded preceding AND unbounded following 的使用

--取last_value时不使用rows BETWEEN unbounded preceding AND unbounded following的结果

 

SELECT opr_id,res_type,
       first_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) low,
       last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) high
  FROM rm_circuit_route
 WHERE opr_id IN ('000100190000000000021311','000100190000000000021355','000100190000000000021339')
 ORDER BY opr_id;

如下图可以看到,如果不使用

rows BETWEEN unbounded preceding AND unbounded following,取出的last_value由于与res_type进行进行排列,因此取出的电路的最后一行记录的类型就不是按照电路的范围提取了,而是以res_type为范围进行提取了。

 

 

 

 

 

在first_value和last_value中ignore nulls的使用
数据如下:

 

 

取出该电路的第一条记录,加上ignore nulls后,如果第一条是判断的那个字段是空的,则默认取下一条,结果如下所示:

 

 

--lag() over()函数用法(取出前n行数据)
lag(expresstion,<offset>,<default>)
with a as 
(select 1 id,'a' name from dual
  union
  select 2 id,'b' name from dual
  union
  select 3 id,'c' name from dual
  union
  select 4 id,'d' name from dual
  union
  select 5 id,'e' name from dual
) 
select id,name, lag(id,1,'')over(order by name) from a;

--lead() over()函数用法(取出后N行数据)

lead(expresstion,<offset>,<default>)
with a as 
(select 1 id,'a' name from dual
 union
 select 2 id,'b' name from dual
 union
 select 3 id,'c' name from dual
 union
 select 4 id,'d' name from dual
 union
 select 5 id,'e' name from dual
) 
select id,name,lead(id,1,'')over(order by name) from a;

--ratio_to_report(a)函数用法 Ratio_to_report() 括号中就是分子,over() 括号中就是分母
with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over(partition by a) b from a 
order by a; 

with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over() b from a --分母缺省就是整个占比
order by a; 

with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over() b from a
group by a order by a;--分组后的占比

开窗函数 - first_value/last_value select distinct a.date,a.name,first_value(date)over(partition by name order by date asc)as `每个人对应最早的date`select '张三'as name,'2021-04-11' as date。select '张三'as name,'2021-04-11' as date。select '张三'as name,'2021-04-11' as date。 阅读详情

相关推荐

Amos实战:如何用Bootstrap法检验中介效应?附完整操作步骤

本文详细介绍了如何使用Amos软件通过Bootstrap法检验中介效应,包括模型设定、参数配置和结果解读。Bootstrap方法因其不依赖正态分布假设、统计功效高等优势,成为中介效应分析的首选方法。文章提供了完整的操作步骤和实用建议,帮助研究者准确评估变量间的中介作用机制。

zz67890的博客 1446

row_number() OVER(PARTITION BY)

row_number()排序函数 统计每个部门薪资最高的员工信息(同一个部门的员工按照薪资进行降序排序) 第一种写法:row_number() over(partition by 一个或多个分组列 order by 一个或多个排序列 asc/desc) as 别名 //如果不写asc/desc的话,默认为asc 第二种写法:row_number() over...

あずにゃん梓喵的博客 14万+

从原理到实践:MIPI D-PHY v1.2的HS-Deskew功能全解析(附时序图详解)

本文深入解析MIPI D-PHY v1.2的HS-Deskew功能,从技术背景到实现机制,详细介绍了其在2.5Gbps高速传输中的关键作用。通过时序图详解和实测数据,帮助工程师理解并应用这一技术,解决信号完整性问题,提升系统可靠性。

weixin_30300225的博客 309

SQL中row_number() over(partition by)详解

row_number 语法 ROW_NUMBER()函数将针对SELECT语句返回的每一行,从1开始编号,赋予其连续的编号。在查询时应用了一个排序标准后,只有通过编号才能够保证其顺序是一致的,当使用ROW_NUMBER函数时,也需要专门一列用于预先排序以便于进行编号 partition by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反...

yilulvxing的博客 22万+

Tube-based MPC完全指南:RCI集合构造与Matlab实现

模型预测控制(MPC)在实际工程中常因模型失配和外部扰动导致硬约束被突破,而鲁棒控制中的Tube-based MPC通过将单条预测轨迹扩展为“管道”集合,为不确定性提供了严格的安全边界。其核心在于利用鲁棒控制不变集(RCI)离线构造安全域,再结合在线标称MPC与反馈校正实现约束的硬保证。针对非线性系统,利普希茨条件为等效扰动的放大和RCI的构造提供了理论基础,使得算法既能保留非线性描述,又不牺牲在线计算效率。该方法适用于带扰动和参数漂移的机械系统、过程控制及嵌入式控制场景。本文以摩擦力非线性系统为例,从RC

weixin_32642109的博客 216

Cartographer从安装到实战:手把手教你运行2D/3D建图与定位

本文提供了一份详尽的Cartographer从零安装到实战应用的完整指南。内容涵盖环境准备、依赖编译、源码构建等关键步骤,并手把手指导读者运行2D/3D官方数据集进行建图与定位测试。文章深入解析了参数配置与自定义数据适配方法,帮助开发者快速掌握这一强大的实时同步定位与建图工具,并将其应用于机器人导航与自动驾驶等实际场景。

rice5的博客 953

row_number() over (partition by 分组列 order by 排序列 desc)、row_number() 函数、分组排序函数

row_number() over (partition by 分组列 order by 排序列 desc)、row_number() 函数、分组排序函数

weixin_49114503的博客 2万+

Python爬虫实战:逆向解析央视频加密参数(附完整代码)

本文详细解析了央视频加密体系的技术实现,通过Python逆向工程实战演示如何解密动态密钥、AES加密和自定义哈希算法等核心组件。文章提供完整代码示例,涵盖GUID生成、参数构造和异步请求处理,帮助开发者掌握现代视频平台的反爬机制破解方法。

weixin_42532473的博客 1133

ROW_NUMBER() OVER(PARTITION BY)函数介绍

row_number() OVER(PARTITION BY)函数介绍 SELECT t1.* FROM( SELECT tr.trail_id, tr.biz_form_id, tr.trail_number, tr.main_opinion, tr.node_name, ROW_NUMBER ( ) OVER ( PARTITION BY tr.biz_form_id ORDER BY tr.create_time DESC ) AS rn

ph_9077的博客 502

【ComfyUI】Wan2.2 + IndexTTS 图像数字人或动物口播视频生成

本文介绍了一个基于ComfyUI的数字人口播视频生成工作流,整合了IndexTTS和图像处理技术。该系统通过多模型协同工作,实现从形象图、文本和音频输入到自动生成口播视频的全流程。核心包含WanVideo系列模型,通过CLIP Vision、T5文本编码器和Wav2Vec语音模型分别处理视觉特征、语义理解和口型同步。工作流涵盖素材加载、特征提取、音频处理和视频生成等节点,支持人物、动物等多种主体的口播内容创作。该方案自动化程度高,适用于知识讲解、剧情旁白等多种场景,能帮助创作者高效产出高质量口播视频。

Mr数据杨 2061

APP爬虫:Airtest的使用和爬取实战详解

Airtest是一个跨平台的自动化测试工具,主要用于移动应用和游戏的UI自动化测试。它支持图像识别、触摸操作、文本输入等功能,能够模拟真实用户的操作行为。此外,Airtest还具备强大的日志记录和报告生成功能,方便开发者进行调试和分析。图像识别:通过截图匹配元素,无需依赖 UI 层次结构。跨平台支持:支持 Android、iOS 和 Windows 应用。简单易用:提供 IDE 和脚本录制功能,降低使用门槛。

数据知道的博客 9621

row_number() OVER(PARTITION BY)函数介绍 .

OVER(PARTITIONBY)函数介绍 开窗函数Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。 开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:1:over后的写法:...

weixin_30877227的博客 87

基于STM32L4XX 、HAL库的ATD5984步进电机驱动器驱动程序应用设计

一、简介: ATD5984是一款内部集成了译码器的微特步进电机 驱动器,能使双极步进电机以全、半、1/4、/1/8、1/16 和1/32步进模式工作。步进模式由逻辑输入管脚MSx选 择。其输出驱动能力达到28V±1.6A。 译码器是ATD5984易于使用的关键,通过STEP管脚 输入一个脉冲就可以使电机完成一次步进,省去了相序 表,高频控制线及复杂的编程接口,使其更适于没有复 杂的微处理器或微处理器负担过重的场合。 在步进操作期间,ATD5984的内部电路可以自动

PP_Cottage的博客 410

MySQL如何实现row_number()及row_number over(partition by column)

MySQL使用row_number()及row_number over(partition by column) 在oracle中在处理某些数据的时候使用row_number() over(partition by a order by b desc)函数是相当方便的。遗憾的这么顺手的工具在MySQL中确没有。要想用只能自己想办法了。 首先我们来实现row_number() 先建个表 D...

seawaterzhou的博客 1万+

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

oracle常用函数over(partition by order by ) 介绍,以及和row_number(),rank()和dense_rank配合使用

部分参考:https://www.cnblogs.com/qiuting/p/7880500.html 1、over(partition by order by ) 语法 over(partition by expr2 order by expr3) over在什么条件只是,函数不能单独使用,需要与row_number(),rank()和dense_rank,lag()和lead()等配合...

qq_29658333的博客 1484

贝叶斯统计 [韦来生编]

贝叶斯(Bayes)统计是近几十年来迅速发展起来的数理统计的一个重要分支。贝叶斯方法与经典统计方法的主要不同之处是进行统计推断时除了利用样本信息外,还要利用参数的先验信息,因此可以提高统计推断或统计决策的效果。它在经济、金融、生物、医学、自然科学和社会科学等许多领域具有广泛的应用。贝叶斯方法的研究已渗透到了统计学的几乎所有领域。作者在给中国科学技术大学概率论与数理统计专业本科生开设的“数理统计”课程中有一章专门讲授“贝叶斯方法和统计决策理论”,近十年来也给中国科学技术大学概率统计专业研究生开设过几次“贝叶斯分析”课。本书是在对过去讲稿的内容作了适当的增减和调整的基础上完成的。  本书共分六章。第1章是绪论,介绍了贝叶斯统计的若干基本概念,同时对必要的数理统计的基础知识有重点地作了回顾。第二章介绍了确定先验分布的若干可供选择的方法。第三章介绍了几类常见统计模型参数的后验分布的主要结果和计算方法。第四章和第五章分别介绍了“贝叶斯统计推断”和“贝叶斯统计决策”的内容。第六章介绍了贝叶斯统计计算的若干方法,包括蒙特卡洛方法和马尔可夫链蒙特卡洛(MCMC)方法以及相关统计软件的简介。本书的每一章附右大量的例题和习题,书末附有常用的几个表格和部分习题解答供读者查用。  大约可在54学时内讲授本书的第1章至第六章的主要内容,教师可根据学时需要选讲本书第1章至第六章的部分内容。书中标“*”号的章节可略去不讲,留给读者作为阅读材料。第六章例题中的R代码和部分例题的数据文件等可从作者个人主页下载。  作者在编写本书的过程中参考了J.O.Berger教授的《统计决策论及贝叶斯分析》和茆诗松教授编写的《贝叶斯统计》等书中的一些内容,以及《贝叶斯分析》一书中由张伟平博士编写的有关贝叶斯统计计算的一些内容。在此表示衷心感谢。  本书编写准备过程中,中国科学技术大学统计与金融系的几个研究生帮助完成了本书部分中文的录入和排版,作者对他们的辛勤工作表示真诚的感谢。高等教育出版社为本书的出版给予了大力支持。在此一并致谢。

row_number()over()和partition by()分析函数的用法总结

做项目的时候碰到一个需求,需要去查找某个病人对应的做过的项目的历史结果,因为一个病人每次做很多项目,所以如何高效率的查出所有历史数据就是关键,经过好基友的提示,最后用了row_number()over(partition by)函数。 一、row_number()over() 该函数是oracle数据库的分析函数,会在数据表生成一个排序列,给每一条数据加一个序号,而over里面一般放排序的规则,比如order by 或者 partition by,order by是根据某个字段进行升序或降序,而partit

wd402922965的博客 6807

win10 跳过U8环境检测的IE WebControls.rar

win10 安装u8的时候 提示IE WebControls 不通过,这个工具可以欺骗u8的检查。 跳过U8环境检测的IE WebControls。提示导入失败后,重新检测通过。

上一篇: LTRIM、RTRIM和TRIM在ORACLE中的用法
下一篇: Oracle中REGEXP_SUBSTR函数
ZHA0KAl
博客等级 码龄16年 26粉丝 35原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值