总结:
RANK()排序相同的时候会重复,总数不会变。
DENSE_RANK()排序相同时会重复,总数量会减少。
ROW_NUMBER()会根据顺序计算。
这三个函数常常和开窗函数结合在一起使用。
1、hive中查询一组中的前几名,就用到dense_rank(),rank(),row_number()这几个函数,他们的区别在于rank()就是排序相同的排序是一样的,但是下一个小的会跳着排序,比如等级排序。
23 1
23 1
22 3
dense_rank()相同的排序相同,下一个小的会紧挨着排序,比如等级排序。
23 1
23 1
22 2
这样总个数是相对减少的,适合求某些指标前几个等级的个数,row_number()就很简单,顺序排序比如等级排序。
23 1
23 2
22 3
这种排序 总个数是不变的,适合求某些值的前几名。
案例操作:
1,计算没门科目的成绩排名:
[isea@hadoop108 datas]$ cat score.txt
悟空语文87
悟空数学95
悟空英语68
八戒语文94
八戒数学56
八戒英语84
张飞语文64
张飞数学86
张飞英语84
小乔语文65
小乔数学85
小乔英语78
创建hive表,并导入数据:
create table score(name string,subject string,score int)
row format delimited
fields terminated by '\t';
load data local inpath '/opt/module/datas/score.txt' into table score;
> select * from score;
+-------------+----------------+--------------+--+
| score.name | score.subject | score.score |
+-------------+----------------+--------------+--+
| 悟空 | 语文 | 87 |
| 悟空 | 数学 | 95 |
| 悟空 | 英语 | 68 |
| 八戒 | 语文 | 94 |
| 八戒 | 数学 | 56 |
| 八戒 | 英语 | 84 |
| 张飞 | 语文 | 64 |
| 张飞 | 数学 | 86 |
| 张飞 | 英语 | 84 |
| 小乔 | 语文 | 65 |
| 小乔 | 数学 | 85 |
| 小乔 | 英语 | 78 |
+-------------+----------------+--------------+--+
计算每门科目的成绩排名:每门科目,表示要对科目进行分组,排名所以要排序。这里面的rank只是打标记而已
select *,rank() over(partition by subject order by score desc) rank,
dense_rank() over(partition by subject order by score desc) dense_rank,
row_number() over(partition by subject order by score desc) row_number
from score;
+-------------+----------------+--------------+-------+-------------+-------------+--+
| score.name | score.subject | score.score | rank | dense_rank | row_number |
+-------------+----------------+--------------+-------+-------------+-------------+--+
| 悟空 | 数学 | 95 | 1 | 1 | 1 |
| 张飞 | 数学 | 86 | 2 | 2 | 2 |
| 小乔 | 数学 | 85 | 3 | 3 | 3 |
| 八戒 | 数学 | 56 | 4 | 4 | 4 |
| 张飞 | 英语 | 84 | 1 | 1 | 1 |
| 八戒 | 英语 | 84 | 1 | 1 | 2 |
| 小乔 | 英语 | 78 | 3 | 2 | 3 |
| 悟空 | 英语 | 68 | 4 | 3 | 4 |
| 八戒 | 语文 | 94 | 1 | 1 | 1 |
| 悟空 | 语文 | 87 | 2 | 2 | 2 |
| 小乔 | 语文 | 65 | 3 | 3 | 3 |
| 张飞 | 语文 | 64 | 4 | 4 | 4 |
+-------------+----------------+--------------+-------+-------------+-------------+--+
2,求出每门学科前三名的学生?
select * from
(
select *,row_number() over(partition by subject order by score desc) row_number
from score)t1
where t1.row_number <= 3;
+----------+-------------+-----------+----------------+--+
| t1.name | t1.subject | t1.score | t1.row_number |
+----------+-------------+-----------+----------------+--+
| 悟空 | 数学 | 95 | 1 |
| 张飞 | 数学 | 86 | 2 |
| 小乔 | 数学 | 85 | 3 |
| 张飞 | 英语 | 84 | 1 |
| 八戒 | 英语 | 84 | 2 |
| 小乔 | 英语 | 78 | 3 |
| 八戒 | 语文 | 94 | 1 |
| 悟空 | 语文 | 87 | 2 |
| 小乔 | 语文 | 65 | 3 |
+----------+-------------+-----------+----------------+--+
转载自https://blog.csdn.net/qq_31807385/article/details/84783269