![oracle row_number() over(partition by .. order by ..)和rank() over(partition by .. order by ..) 和dense_rank() over(partition by .. order by ..)的相似点与区别 oracle row_number() over(partition by .. order by ..)和rank() over(partition by .. order by ..) 和dense_rank() over(partition by .. order by ..)的相似点与区别](https://image.shishitao.com:8440/aHR0cHM6Ly9ia3FzaW1nLmlrYWZhbi5jb20vdXBsb2FkL2NoYXRncHQtcy5wbmc%2FIQ%3D%3D.png?!?w=700&webp=1)
新建一个测试表
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 ..)排序是顺序的,排序比较有意义;