hive 排序函数

row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
dense_rank() 是连续排序，两个第二名仍然跟着第三名
rank() 是跳跃拍学，两个第二名下来就是第四名

使用方法 fun() over( partition by field,field… order by flag.. asc/desc)

按照分区排序，即 field…一样排序
也可不加partition 则直接按照flag排序

测试：
创建表（暂时用，没设置文件等）
create table study_test
(
name string,
score double
)

插入数据
insert into table study_test values(“xiaoxiao”,66.6)
,(“xiaoxiao”,33.6) ,(“xiaoxiao”,55.6) ,(“xiaoxiao”,555.6)
,(“xiaoming”,22),(“xiaoming”,11),(“xiaoming”,88)
,(“daming”,55),(“daming”,66),(“daming”,77),(“daming”,88),(“daming”,99)
,(“xiaocai”,88),
(“sherlock”,55),(“sher”,55),(“huhu”,100) ;

测试rank（）
select name,score,rank() over(partition by name order by score) tt from t;
结果
| name | score | tt |
+———–+——–+—–+–+
| daming | 55.0 | 1 |
| daming | 55.0 | 1 |
| daming | 66.0 | 3 |
| daming | 77.0 | 4 |
| daming | 88.0 | 5 |
| daming | 99.0 | 6 |
| huhu | 100.0 | 1 |
| sher | 55.0 | 1 |
| sherlock | 55.0 | 1 |
| xiaocai | 88.0 | 1 |
| xiaocai | 88.0 | 1 |
| xiaoming | 11.0 | 1 |
| xiaoming | 22.0 | 2 |
| xiaoming | 22.0 | 2 |
| xiaoming | 88.0 | 4 |
| xiaoxiao | 33.6 | 1 |
| xiaoxiao | 55.6 | 2 |
| xiaoxiao | 66.6 | 3 |
| xiaoxiao | 66.6 | 3 |
| xiaoxiao | 555.6 | 5 |
| zhixin | 66.6 | 1 |

测试dense_rank（）
select name,score,dense_rank() over(partition by name order by score) n from study_test;
+———–+——–+—-+–+
| name | score | n |
+———–+——–+—-+–+
| daming | 55.0 | 1 |
| daming | 55.0 | 1 |
| daming | 66.0 | 2 |
| daming | 77.0 | 3 |
| daming | 88.0 | 4 |
| daming | 99.0 | 5 |
| huhu | 100.0 | 1 |
| sher | 55.0 | 1 |
| sherlock | 55.0 | 1 |
| xiaocai | 88.0 | 1 |
| xiaocai | 88.0 | 1 |
| xiaoming | 11.0 | 1 |
| xiaoming | 22.0 | 2 |
| xiaoming | 22.0 | 2 |
| xiaoming | 88.0 | 3 |
| xiaoxiao | 33.6 | 1 |
| xiaoxiao | 55.6 | 2 |
| xiaoxiao | 66.6 | 3 |
| xiaoxiao | 66.6 | 3 |
| xiaoxiao | 555.6 | 4 |
| zhixin | 66.6 | 1 |

测试row_number( )
select name,score,row_number() over(partition by name order by score) n from study_test;

| name | score | n |
+———–+——–+—-+–+
| daming | 55.0 | 1 |
| daming | 55.0 | 2 |
| daming | 66.0 | 3 |
| daming | 77.0 | 4 |
| daming | 88.0 | 5 |
| daming | 99.0 | 6 |
| huhu | 100.0 | 1 |
| sher | 55.0 | 1 |
| sherlock | 55.0 | 1 |
| xiaocai | 88.0 | 1 |
| xiaocai | 88.0 | 2 |
| xiaoming | 11.0 | 1 |
| xiaoming | 22.0 | 2 |
| xiaoming | 22.0 | 3 |
| xiaoming | 88.0 | 4 |
| xiaoxiao | 33.6 | 1 |
| xiaoxiao | 55.6 | 2 |
| xiaoxiao | 66.6 | 3 |
| xiaoxiao | 66.6 | 4 |
| xiaoxiao | 555.6 | 5 |
| zhixin | 66.6 | 1 |

结论一目了然。。。。。。。

完全按照成绩排序
select name,score,row_number() over(order by score desc) n from study_test;
| name | score | n |
+———–+——–+—–+–+
| xiaoxiao | 555.6 | 1 |
| huhu | 100.0 | 2 |
| daming | 99.0 | 3 |
| xiaocai | 88.0 | 4 |
| daming | 88.0 | 5 |
| xiaocai | 88.0 | 6 |
| xiaoming | 88.0 | 7 |
| daming | 77.0 | 8 |
| zhixin | 66.6 | 9 |
| xiaoxiao | 66.6 | 10 |
| xiaoxiao | 66.6 | 11 |
| daming | 66.0 | 12 |
| xiaoxiao | 55.6 | 13 |
| sher | 55.0 | 14 |
| daming | 55.0 | 15 |
| sherlock | 55.0 | 16 |
| daming | 55.0 | 17 |
| xiaoxiao | 33.6 | 18 |
| xiaoming | 22.0 | 19 |
| xiaoming | 22.0 | 20 |
| xiaoming | 11.0 | 21 |