row_number(),rank(),dese_rank()区别 博客分类: hadoophive
程序员文章站
2024-03-15 10:26:41
...
数据源:
student | subject | score |
a | yingyu | 95 |
a | yuwen | 95 |
b | yingyu | 95 |
b | yuwen | 91 |
c | yingyu | 90 |
c | yuwen | 88 |
d | yingyu | 92 |
建表语句:
create table tmp_test_rank( student string, subject string, score string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
row_number():
如果排序结果一样,也会区分出先后顺序。
select student,subject,score,row_number() OVER (partition by subject order by score desc) as rn FROM tmp.tmp_test_rank;
结果:
b yingyu 95 1
a yingyu 95 2
d yingyu 92 3
c yingyu 90 4
a yuwen 95 1
b yuwen 91 2
c yuwen 88 3
rank():
如果排序结果一样,值一样,但后续值会产生跳跃。
select student,subject,score,rank() OVER (partition by subject order by score desc) as rn FROM tmp.tmp_test_rank;
结果:
b yingyu 95 1
a yingyu 95 1
d yingyu 92 3
c yingyu 90 4
a yuwen 95 1
b yuwen 91 2
c yuwen 88 3
dense_rank():
如果排序结果一样,值一样,后续值不会产生跳跃。
select student,subject,score,dense_rank() OVER (partition by subject order by score desc) as rn FROM tmp.tmp_test_rank;
结果:
b yingyu 95 1
a yingyu 95 1
d yingyu 92 2
c yingyu 90 3
a yuwen 95 1
b yuwen 91 2
c yuwen 88 3