ORACLE中ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了ORACLE中ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
row_number() OVER ( PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．
  • row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．
  • rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．
  • dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的.
  • lag（arg1,arg2,arg3):
    • arg1是从其他行返回的表达式
    • arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，是一个往回检索以前的行的数目。
    • arg3是在arg2表示的数目超出了分组的范围时返回的值。
@H_404_89@
函数语法： @H_404_89@
OPAP函数语法四部分： @H_404_89@
1.function 本身用于对窗口中的数据进行操作； @H_404_89@
2.partitioning clause 用于将结果集分区； @H_404_89@
3.order by clause 用于对分区中的数据进行排序； @H_404_89@
4.windowing clause 用于定义function在其上操作的行的集合，即function所影响的范围； @H_404_89@ 
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,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
LEAD(EXPR,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 
lead （） 下一个值 lag（） 上一个值

【参数】
EXPR是从其他行返回的表达式 
OFFSET是缺省为1 的正数，表示相对行数。希望检索的当前行分区的偏移量
DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。
【说明】Oracle分析函数

 
@H_404_89@ 

 
 ---TEST FOR ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 
@H_404_89@ 

 
 DROP 
 TABLE 
 TEST_Y 
@H_404_89@ 

 
 CREATE 
 TEST_Y( 
@H_404_89@ 

 
  
 ID 
 VARCHAR2 
 ( 
 32 
 ) 
 PRIMARY 
 KEY 
 , 
@H_404_89@ 

 
 NAME 
 20 
 ),128)"> AGE 
 NUMBER 
 3 
 DETAILS 
 1000 
 ) 
@H_404_89@ 

 
 ); 
@H_404_89@ 

 
 INSERT 
 INTO 
 TEST_Y 
 VALUES 
 (SYS_GUID(), 
 '海子' 
 '面朝大海，春暖花开' 
 30 
 '贝多芬' 
 43 
 '致爱丽丝' 
 '普希金' 
 34 
 '假如生活欺骗了你' 
 '杨过' 
 23 
 '黯然销魂掌' 
 '小龙女' 
 '神雕侠侣' 
 '李清照' 
 21 
 '寻寻觅觅、冷冷清清' 
 '周芷若' 
 18 
 '峨眉' 
 '赵敏' 
 '自由' 
 '张无忌' 
 '倚天屠龙记' 
 
  
@H_404_89@ 

 
 SELECT 
 * 
 FROM 
 TEST_Y; 
@H_404_89@ 

 
 
  
@H_404_89@ 

 
 ----1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 
@H_404_89@ 

 
 ---查询所有姓名，如果同名，则按年龄降序 
@H_404_89@ 

 
 OVER 
 PARTITION 
 BY 
 ORDER 
 AGE 
 DESC 
 TEST_Y; 
@H_404_89@ 

 
  
@H_404_89@ 

 
 
  
@H_404_89@ 

 
 
 
  ----通过上面的语句可知，ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分组，按AGE字段排序的。 
 @H_404_89@ 
 
 
  ----如果只需查询出不重复的姓名即可，则可使用如下的语句 
 @H_404_89@ 
 
 
  
 
   SELECT 
   * 
   FROM 
   ( 
   NAME 
   DETAILS 
   OVER 
   PARTITION 
   BY 
   ORDER 
   AGE 
   DESC 
   )RN 
   TEST_Y ) 
   WHERE 
   RN= 
   1 
   ; 
  @H_404_89@ 
  
 
    
  @H_404_89@ 
  
 
   
 
    ----由查询结果可知，姓名相同年龄小的数据被过滤掉了；可以使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)对部分子弹进行去重处理 
   @H_404_89@ 
   
 
    
  
   @H_404_89@ 
   
 
    
  
   @H_404_89@ 
   
 
    
 
     ----2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2) 
    @H_404_89@ 
    
 
     ----跳跃排序 
    @H_404_89@ 
    
 
     SELECT 
     NAME 
     DETAILS 
     RANK 
     () 
     OVER 
     ( 
     PARTITION 
     BY 
     ORDER 
     AGE 
     DESC 
     ) 
     FROM 
     TEST_Y; 
    @H_404_89@ 
   @H_404_89@ 
   
 
     
   @H_404_89@ 
   
 
    
 
     ----由查询结果可知，相同的并列，下一个则跳跃到并列所替的序列后：如有两个并列1，那么下一个则直接排为3，跳过2； 
    @H_404_89@ 
    
 
     
  
    @H_404_89@ 
    
 
     
 
      ----3.DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2) 
     @H_404_89@ 
     
 
      
 
       ----连续排序，当有多个并列时，下一个仍然连续有序 
      @H_404_89@ 
     @H_404_89@ 
    @H_404_89@ 
    
 
      
    @H_404_89@ 
    
 
     
 
      ----由查询结果可知，当两个并列为1时，下一个仍连续有序为2，不跳跃到3 
     @H_404_89@ 
     
 
      
  
     @H_404_89@ 
     

       Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率. 
     @H_404_89@ 
     

       Lag和Lead偏移量函数，其用途是：可以查出同一字段下一个值或上一个值，并作为新列存在表中. 
     @H_404_89@ 
     
 
      
 
       -----4.LAG(exp_str,offset,defval) OVER(PARTITION BY NAME ORDER BY AGE) 
      @H_404_89@ 
      
 
       -----exp_str 返回显示的字段；offset是exp_str字段的偏移量，默认是1，如offset=1表示返回当前exp_str的下一个exp_str；defval当该函数无值可用的情况下返回该值。 
      @H_404_89@ 
      
 
       
 
        (1) SELECT 
        NAME 
        DETAILS 
        LAG 
        ( 
        1 
        'sue' 
        ) 
        OVER 
        PARTITION 
        BY 
        ORDER 
        AGE 
        DESC 
        FROM 
        TEST_Y; 
       @H_404_89@ 
      @H_404_89@ 
     @H_404_89@ 
     
 
       
     @H_404_89@ 
     
 
      
 
       (2) SELECT 
       NAME 
       DETAILS 
       LAG 
       ( 
       2 
       'sue' 
       ) 
       OVER 
       PARTITION 
       BY 
       ORDER 
       AGE 
       DESC 
       FROM 
       TEST_Y; 
      @H_404_89@ 
     @H_404_89@ 
     
 
       
     @H_404_89@ 
    @H_404_89@ 
   @H_404_89@ 
  @H_404_89@ 
 @H_404_89@ 
@H_404_89@ 

 
 
  
@H_404_89@ 

 
 
 
  (3) SELECT 
  NAME 
  DETAILS 
  LAG 
  ( 
  2 
  'sue' 
  ) 
  OVER 
  ORDER 
  BY 
  AGE 
  DESC 
  FROM 
  TEST_Y; 
 @H_404_89@ 
 
 
   
 @H_404_89@ 
 
 
  
 
 @H_404_89@ 
 
 
  
 
   ----5.LEAD(EXP_STR,OFFSET,DEFVAL) OVER(PARTITION BY NAME ORDER BY AGE) 
  @H_404_89@ 
  
 
   -----exp_str 返回显示的字段；offset是exp_str字段的偏移量，默认是1，如offset=1表示返回当前exp_str的上一个exp_str； 
  @H_404_89@ 
  
 
   -----defval当该函数无值可用的情况下返回该值。 
  @H_404_89@ 
  
 
   (1)SELECT 
   NAME 
   DETAILS 
   LEAD 
   ( 
   1 
   'sue' 
   ) 
   OVER 
   PARTITION 
   BY 
   ORDER 
   AGE 
   DESC 
   FROM 
   TEST_Y; 
  @H_404_89@ 
  
 
    
  @H_404_89@ 
 @H_404_89@ 
 
 
  
 
   (2) SELECT 
   2 
   TEST_Y; 
  @H_404_89@ 
 @H_404_89@ 
 
(3) SELECT 
   -----6.SUM(COL1) OVER([PARTITION BY COL2 ] [ORDER BY COL3]) 
  @H_404_89@ 
  
 
   (1) SELECT 
   SUM 
   (AGE) 
   TEST_Y; 
  @H_404_89@ 
  
 
    
  @H_404_89@ 
  
 
   (3) 
   SELECT 
   NULLS 
   LAST 
   (4) SELECT 
   () 
   TEST_Y; 
  @H_404_89@ 
  
 
    
  @H_404_89@  
 @H_404_89@ 
@H_404_89@ 原文链接：https://www.f2er.com/oracle/209113.html

      

      
                 
              

            

          

          
          
          

            

              




            

          


          

            

              

                  
    
猜你在找的Oracle相关文章



                                    
[Oracle]记一次由sequence引发的enq sv-contention等待事件

                                
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...

                                
                                
                            



                                    
oracle统计信息的锁定与解锁

                                
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...

                                
                                
                            



                                    
【转】RMAN删除过期备份或非过期备份

                                
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...

                                
                                
                            



                                    
数据恢复顾问（DRA）

                                
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...

                                
                                
                            



                                    
RMAN备份与恢复（一）--认识RMAN

                                
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...

                                
                                
                            







                                    
RMAN备份与恢复（二）--RMAN常用操作学习

                                
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...

                                
                                
                            



                                    
ORA-00245问题总结

                                
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...

                                
                                
                            



                                    
使用RMAN对数据文件进行恢复

                                
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...

                                
                                
                            



                                    
Oracle使用fy_recover_data恢复truncate删除的数据

                                
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...

                                
                                
                            



                                    
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库

                                
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...

                                
                                
                            







            

          

        

        
        
        
        

          
          

            

              

                
                
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC

                

              

            

          

          
         
          

            

              
 



              

            

          

          
          
          
          

            

              

                
                
              

            

          

          


          
          

            

              

                
                
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问