hive排名函数 实战

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

一、准备数据

1.准备数据 score.txt

孙悟空  语文    87
孙悟空  数学    95
孙悟空  英语    68
大海    语文    94
大海    数学    56
大海    英语    84
宋宋    语文    64
宋宋    数学    86
宋宋    英语    84
婷婷    语文    65
婷婷    数学    85
婷婷    英语    78

2. 创建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;

二、需求

1. 按照科目进行排名

select *,rank() over(partition by subject order by score desc) from score

score.name	score.subject	score.score	rank_window_0
孙悟空	数学	95	1
宋宋	数学	86	2
婷婷	数学	85	3
大海	数学	56	4
宋宋	英语	84	1
大海	英语	84	1
婷婷	英语	78	3
孙悟空	英语	68	4
大海	语文	94	1
孙悟空	语文	87	2
婷婷	语文	65	3
宋宋	语文	64	4

2. 给每个学生的总分进行排名

select name,sumscore,rank() over( order by sumscore desc)
from
(select name,sum(score) sumscore
from score
group by name) tmp

结果：
孙悟空 250 1
宋宋 234 2
大海 234 2
婷婷 228 4

3. 求每个学生的成绩明细及给每个学生的总分和总分排名

select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name) sumscore
from score) tmp

４.只查询每个科目的成绩的前2名

select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2

５.查询学生成绩明细，并显示当前科目最高分

select *,max(score) over(partition by subject)
from score

或
select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score

6.查询学生成绩，并显示当前科目最低分

select *,min(score) over(partition by subject)
from score

或
select *,FIRST_VALUE(score) over(partition by subject order by score )
from score

三、排名函数加油站（概念复习）

排名函数可以跟Over()，但是不能定义window_clause.
在计算名次前，需要先排序！
RANK: 允许并列，一旦有并列跳号！
ROW_NUMBER: 行号！ 连续的，每个号之间差1！
DENSE_RANK： 允许并列，一旦有并列不跳号！
CUME_DIST： 从排序后的第一行到当前值之间数据 占整个数据集的百分比！
PERCENT_RANK： rank-1/ 总数据量-1
NTILE(x): 将数据集均分到X个组中，返回每条记录所在的组号

select  *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score

执行结果：

score.name	score.subject	score.score	ranknum	rnnum	drnum	cdnum	prnum
大海	数学	56	1	1	1	0.08333333333333333	0.0
宋宋	语文	64	2	2	2	0.16666666666666666	0.09090909090909091
婷婷	语文	65	3	3	3	0.25	0.18181818181818182
孙悟空	英语	68	4	4	4	0.3333333333333333	0.2727272727272727
婷婷	英语	78	5	5	5	0.4166666666666667	0.36363636363636365
宋宋	英语	84	6	6	6	0.5833333333333334	0.45454545454545453
大海	英语	84	6	7	6	0.5833333333333334	0.45454545454545453
婷婷	数学	85	8	8	7	0.6666666666666666	0.6363636363636364
宋宋	数学	86	9	9	8	0.75	0.7272727272727273
孙悟空	语文	87	10	10	9	0.8333333333333334	0.8181818181818182
大海	语文	94	11	11	10	0.9166666666666666	0.9090909090909091
孙悟空	数学	95	12	12	11	1.0	1.0

select *,ntile(5) over()
from score

count row_number rank dense_rank
3 1 1 1
3 2 1 1
2 3 3 2
1 4 4 3