ROWNUMBER()OVER(PARTITIONBYCOL1ORDERBYCOL2)用法 rowguidcol

ROWNUMBER()OVER(PARTITIONBYCOL1ORDERBYCOL2)用法
今天在使用多字段去重时,由于某些字段有多种可能性,只需根据部分字段进行去重,在网上看到了rownumber()over(partitionbycol1orderbycol2)去重的方法,很不错,在此记录分享下:

row_number()OVER(PARTITIONBYCOL1ORDERBYCOL2) 表示根据COL1分组,在分组内部根据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
函数语法:OPAP函数语法四部分:1.function 本身用于对窗口中的数据进行操作;2.partitioning clause用于将结果集分区;3.order by clause用于对分区中的数据进行排序;4.windowing clause用于定义function在其上操作的行的集合,即function所影响的范围;
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是连续排序,有两个第二名时仍然跟着第三名。
【说明】Oracle分析函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) row_number() 返回的主要是“行”的信息,并没有排名
【参数】
【说明】Oracle分析函数主要功能:用于取前几名,或者最后几名等
sum(...) over ...
【功能】连续求和分析函数
【参数】具体参示例
【说明】Oracle分析函数
lag()和lead()
【语法】lag(EXPR,,) OVER ( [query_partition_clause] order_by_clause )LEAD(EXPR,,) OVER ( [query_partition_clause] order_by_clause )
【功能】表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) lead () 下一个值 lag() 上一个值
【参数】EXPR是从其他行返回的表达式 OFFSET是缺省为1 的正数,表示相对行数。希望检索的当前行分区的偏移量DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。
【说明】Oracle分析函数
---TEST FOR ROW_NUMBER()OVER(PARTITION BY COL1 ORDER BY COL2)DROPTABLETEST_YCREATETABLETEST_Y(IDVARCHAR2(32)PRIMARYKEY,NAMEVARCHAR2(20),AGENUMBER(3),DETAILSVARCHAR2(1000));INSERTINTOTEST_YVALUES(SYS_GUID(),'海子',20,'面朝大海,春暖花开');INSERTINTOTEST_YVALUES(SYS_GUID(),'海子',30,'面朝大海,春暖花开');INSERTINTOTEST_YVALUES(SYS_GUID(),'贝多芬',43,'致爱丽丝');INSERTINTOTEST_YVALUES(SYS_GUID(),'普希金',34,'假如生活欺骗了你');INSERTINTOTEST_YVALUES(SYS_GUID(),'杨过',23,'黯然销魂掌');INSERTINTOTEST_YVALUES(SYS_GUID(),'小龙女',32,'神雕侠侣');INSERTINTOTEST_YVALUES(SYS_GUID(),'李清照',21,'寻寻觅觅、冷冷清清');INSERTINTOTEST_YVALUES(SYS_GUID(),'周芷若',18,'峨眉');INSERTINTOTEST_YVALUES(SYS_GUID(),'赵敏',18,'自由');INSERTINTOTEST_YVALUES(SYS_GUID(),'张无忌',20,'倚天屠龙记');INSERTINTOTEST_YVALUES(SYS_GUID(),'张无忌',30,'倚天屠龙记');
SELECT*FROMTEST_Y;

----1. ROW_NUMBER()OVER(PARTITION BY COL1 ORDER BY COL2)---查询所有姓名,如果同名,则按年龄降序SELECTNAME,AGE,DETAILS,ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;

----通过上面的语句可知,ROW_NUMBER()OVER(PARTITION BY COL1 ORDER BYCOL2)中是按照NAME字段分组,按AGE字段排序的。----如果只需查询出不重复的姓名即可,则可使用如下的语句SELECT*FROM(SELECTNAME,AGE,DETAILS,ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYAGEDESC)RNFROMTEST_Y)WHERERN=1;----由查询结果可知,姓名相同年龄小的数据被过滤掉了;可以使用ROW_NUMBER()OVER(PARTITION BY COL1 ORDER BYCOL2)对部分子弹进行去重处理

----2.RANK() OVER(PARTITIONBY COL1 ORDER BY COL2)----跳跃排序SELECTNAME,AGE,DETAILS,RANK()OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;----由查询结果可知,相同的并列,下一个则跳跃到并列所替的序列后:如有两个并列1,那么下一个则直接排为3,跳过2;
----3.DENSE_RANK()OVER(PARTITION BY COL1 ORDER BY COL2)----连续排序,当有多个并列时,下一个仍然连续有序----由查询结果可知,当两个并列为1时,下一个仍连续有序为2,不跳跃到3

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率.Lag和Lead偏移量函数,其用途是:可以查出同一字段下一个值或上一个值,并作为新列存在表中.-----4.LAG(exp_str,offset,defval)OVER(PARTITION BY NAME ORDER BY AGE)-----exp_str返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的下一个exp_str;defval当该函数无值可用的情况下返回该值。(1)SELECTNAME,AGE,DETAILS,LAG(NAME,1,'sue')OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;(2)SELECTNAME,AGE,DETAILS,LAG(NAME,2,'sue')OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;
(3)SELECTNAME,AGE,DETAILS,LAG(NAME,2,'sue')OVER(ORDERBYAGEDESC)FROMTEST_Y;
----5.LEAD(EXP_STR,OFFSET,DEFVAL)OVER(PARTITION BY NAME ORDER BY AGE)-----exp_str返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的上一个exp_str;-----defval当该函数无值可用的情况下返回该值。(1)SELECTNAME,AGE,DETAILS,LEAD(NAME,1,'sue')OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;
(2)SELECTNAME,AGE,DETAILS,LEAD(NAME,2,'sue')OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;(3)SELECTNAME,AGE,DETAILS,LEAD(NAME,1,'sue')OVER(ORDERBYAGEDESC)FROMTEST_Y;

-----6.SUM(COL1)OVER([PARTITION BY COL2 ] [ORDER BY COL3])(1)SELECTNAME,AGE,DETAILS,ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYAGEDESC),SUM(AGE)OVER(PARTITIONBYNAMEORDERBYAGEDESC)FROMTEST_Y;(2)SELECTNAME,AGE,DETAILS,ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYAGEDESC),SUM(AGE)OVER(PARTITIONBYNAME)FROMTEST_Y;(3)SELECTNAME,AGE,DETAILS,ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYAGEDESC),SUM(AGE)OVER(ORDERBYAGEDESCNULLSLAST)FROMTEST_Y;(4)SELECTNAME,AGE,DETAILS,SUM(AGE)OVER()FROMTEST_Y;

  

爱华网本文地址 » http://www.413yy.cn/a/25101016/323196.html

更多阅读

的地得的用法 的和地的区别及用法

的地得的用法——简介日常工作中,常会有人提到“的”“地”“得”用法的问题,不少学生对“的、地、得”用法也是含糊不清,乱用一气,作业自然会在“的”“地”“得”用法上出错,并且屡改屡犯。大家都知道,“的”、“地”、“得”这三个字的

眼线膏的用法画出迷人眼线 美宝莲眼线膏

眼线膏的用法画出迷人眼线——简介美眉们一般都习惯用眼线笔来画眼线,今天小编要在这里教大家如何用眼线膏来描画眼线,步骤很简单,想要画好眼妆的美眉们,赶紧准备好工具,跟小编一起来学习吧!眼线膏的用法画出迷人眼线——工具/原料

睫毛膏的正确用法 睫毛膏如何使用

  睫毛膏对于很多女孩子来说其实并不陌生,但是大家真的知道睫毛膏的正确用法吗,如何使用睫毛膏才能让自己的睫毛更翘、更卷,而且看起来不会显得很僵硬呢,如果你还不懂那就来一起学习一下睫毛膏的正确用法吧。睫毛膏的正确用法——工

财务凭证装订机用法 财务凭证装订机维修

财务凭证装订机用法——简介热熔管财务装订机它不仅装订速度快为我们大大的节约时间、而且没有噪音、无污染,下面看具体操作步骤:财务凭证装订机用法——热熔管财务装订机操作步骤:财务凭证装订机用法 1、接通电源后,插入装订管财务凭

护发素的用法 护发素发膜的正确用法

护发素的用法——简介护发素可以为头发提供一层保护,或许就因此得名了吧。比起洗发用品,护发素能够令头发看起来更自然和健康。 下面来介绍一下护发素的用法供参考。 护发素的用法——工具/原料护发素护发素的用法——方法/步骤护发

声明:《ROWNUMBER()OVER(PARTITIONBYCOL1ORDERBYCOL2)用法 rowguidcol》为网友风急风也清分享!如侵犯到您的合法权益请联系我们删除