rownum, row_number(), rank() , dense_rank(), partition by ,max() keep 语句的区别与用法

rownum,rownumber(), rank(),dense_rank()都是用来为记录分配序号的，

rownum只能在orderby语句排完序后，在外层嵌套查询才能获得正确的行号，用起来相当复杂

select rownum, designator from airport_heliport_ts; --这样写是无效的

正因为这样，产生了row_number() over(order by ) 用法：

select row_number() over(order by designator),  designator from airport_heliport_ts ;

它的好处是用一级sql查询就能实现上面的 加行号功能。

rank(),dense_rank()的功能与之非常相似，区别是：

row_number()对重复的排序值分配不同的行号；

rank()对重复排序值分配相同的序号，且行号不连续；如前三个值重复，序号就是1,1,1,4

dense_rank()对重复排序值分配相同的序号，但行号连续；如前三个值重复，序号就是1,1,1,2

如果只需返回上述排序后的最大值或最小值，可用：（其中的first可换成last实现升、降序）

select max(designator) keep(dense_rank first order by designator)   from airport_heliport_ts t;

分组排序partition

前面的几种排序方法都只能整体排序，如果要按条件分组后再排序，就要用partition函数，如：

select row_number() over(partition by bureau_id order by bureau_id,designator), bureau_id,  designator from airport_heliport_ts t ;

实现按bureau_id分区后，在每个相同的bureau_id分区内排列序号，类似于group by函数，但查询结果不会分组

除了row_number()外，parttion还支持wm_concat等集合函数，能比wm_concat(distinct) +group by实现更灵活的排序方式

select wm_concat(designator) over(partition by t.bureau_id order by bureau_id,designator ), bureau_id,  designator from airport_heliport_ts t ;

 

select rownum, designator from airport_heliport_ts;
select row_number() over(order by designator),  designator from airport_heliport_ts ;
select rank()over(order by designator),  designator from airport_heliport_ts ;
select dense_rank()over(order by designator),  designator from airport_heliport_ts ;
select max(designator) keep(dense_rank first order by designator)   from airport_heliport_ts t;
select row_number() over(partition by t.bureau_id order by bureau_id,designator), bureau_id,  designator from airport_heliport_ts t ;
select wm_concat(designator) over(partition by t.bureau_id order by bureau_id,designator ), bureau_id,  designator from airport_heliport_ts t ;