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