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 ;