新建一个测试表
create table dim_ia_test2(device_number varchar2(20),desc2 varchar2(20))
插入数据后得到:
一、oracle row_number() over(partition by .. order by ..)
只以电话号码排序,默认升序
select device_number,desc2,row_number() over(order by device_number ) rn from dim_ia_test2;
以电话号码分组,并排序
select device_number,desc2,row_number() over(partition by device_number order by device_number ) rn from dim_ia_test2;
row_number的排序特点为顺序排
二、rank() over(partition by .. order by ..)
select device_number,desc2,rank() over(order by device_number ) rn from dim_ia_test2;
select device_number,desc2,rank() over(partition by device_number order by device_number ) rn from dim_ia_test2;
三、dense_rank() over(partition by .. order by ..)
select device_number,desc2,dense_rank() over(order by device_number ) rn from dim_ia_test2;
select device_number,desc2,dense_rank() over(partition by device_number order by device_number ) rn from dim_ia_test2;
以上是三个排序的开窗函数,row_number() over(partition by .. order by ..)由于是顺序排序,分组和排序都比较有意义;
rank() over(partition by .. order by ..)排序是跳跃的,排序比较有意义;
和dense_rank() over(partition by .. order by ..)排序是顺序的,排序比较有意义;