oracle row_number() over()分析函数用法

row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。 
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。 
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） 
dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。 

oracle 分析函数 row_number(),返回一个整数值(>=1); 

直接上示例：

with row_number_test as
(    
     select 'USER1' owner, 'Tab1' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab1' TABLE_NAME, 'P2' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab1' TABLE_NAME, 'P3' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab2' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab3' TABLE_NAME, 'P1' PARTITION_NAME from dual union all 
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P2' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P3' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P4' PARTITION_NAME from dual union all 
     select 'USER2' owner, 'Tab5' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER2' owner, 'Tab5' TABLE_NAME, 'P2' PARTITION_NAME from dual union all    
     select 'USER3' owner, 'Tab4' TABLE_NAME, 'P1' PARTITION_NAME from dual      
)    
select owner , TABLE_NAME,  PARTITION_NAME,  
       row_number() over (partition by owner,TABLE_NAME order by PARTITION_NAME) row_number    
from row_number_test  ;

OWNER TABLE_NAME PARTITION_NAME ROW_NUMBER
----- ---------- -------------- ----------
USER1 Tab1       P1                      1
USER1 Tab1       P2                      2
USER1 Tab1       P3                      3
USER1 Tab2       P1                      1
USER1 Tab3       P1                      1
USER1 Tab4       P1                      1
USER1 Tab4       P2                      2
USER1 Tab4       P3                      3
USER1 Tab4       P4                      4
USER2 Tab5       P1                      1
USER2 Tab5       P2                      2
USER3 Tab4       P1                      1
 
12 rows selected

注意，在此我稍微做了下修改，将partition by owner,TABLE_NAME去除，发现报如下错：

ORA-30485: 在窗口说明中丢失ORDER BY表达式。

而将order by PARTITION_NAME删除没有任何问题，其实这就相当于rownum功能一样。

with row_number_test as
(    
     select 'USER1' owner, 'Tab1' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab1' TABLE_NAME, 'P2' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab1' TABLE_NAME, 'P3' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab2' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab3' TABLE_NAME, 'P1' PARTITION_NAME from dual union all 
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P2' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P3' PARTITION_NAME from dual union all    
     select 'USER1' owner, 'Tab4' TABLE_NAME, 'P4' PARTITION_NAME from dual union all 
     select 'USER2' owner, 'Tab5' TABLE_NAME, 'P1' PARTITION_NAME from dual union all    
     select 'USER2' owner, 'Tab5' TABLE_NAME, 'P2' PARTITION_NAME from dual union all    
     select 'USER3' owner, 'Tab4' TABLE_NAME, 'P1' PARTITION_NAME from dual      
)    
select owner , TABLE_NAME,  PARTITION_NAME,  
       row_number() over (order by PARTITION_NAME) row_number
from row_number_test 

OWNER TABLE_NAME PARTITION_NAME ROW_NUMBER
----- ---------- -------------- ----------
USER1 Tab1       P1                      1
USER1 Tab4       P1                      2
USER2 Tab5       P1                      3
USER3 Tab4       P1                      4
USER1 Tab3       P1                      5
USER1 Tab2       P1                      6
USER1 Tab4       P2                      7
USER2 Tab5       P2                      8
USER1 Tab1       P2                      9
USER1 Tab4       P3                     10
USER1 Tab1       P3                     11
USER1 Tab4       P4                     12

以上是本人结合别人博客再自己测试所得，相信看了上面的测试，大家心里肯定又得出了一个结论。

FROM：

http://yang8787jie-126-com.iteye.com/blog/1312086

http://chenxy.blog.51cto.com/blog/729966/728838