语法:row_number() over(partition by …)
分组取最大的objid那一条,根据objid倒叙排序,取rn=1
select * from (select a.* ,row_number()over(partiotion by col1,col2 order by col1,col2) r from tablename ) where r=1;
分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组,并且每组每行都可以返回一个统计值。
分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)
–利用开窗函数取每组数据的最后一条数据
sql>select * from (select row_number() over (partition by accountid order by updatetimestamp desc) rn, plm_openaccountrecon.* from plm_openaccountrecon) where rn = 1 and t.accountid = :accountid
–unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
–unbounded:不受控制的,无限的
–preceding:在…之前
–following:在…之后
–第一行到当前行
sql> select t.objid, t.applyempid, t.applydate, t.status, t.leavetype, t.leavedays, sum(leavedays) over(partition by applyempid order by objid rows between unbounded preceding and current row) lds from rt_gtcxleave t;
–当前行至最后一行
sql> select t.objid, t.applyempid, t.applydate, t.status, t.leavetype, t.leavedays, sum(leavedays) over(partition by applyempid order by objid rows between current row and unbounded following) lds from rt_gtcxleave t;
–当前行的上一行 (rownum – 1) 到当前行
–between 1 preceding and current row
sql> select t.objid, t.applyempid, t.applydate, t.status, t.leavetype, t.leavedays, sum(leavedays) over(partition by applyempid order by objid rows between 1 preceding and current row) lds from rt_gtcxleave t;
–当前行的上一行 (rownum – 1) 到当前行的下两行 (rownum + 2)
–rows between 1 preceding and 2 following
需要了解更多数据库技术:ORACLE开窗函数语法和使用介绍,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/799875.html