基于hive的排名函数
以stu_score表为例
create table if not exists stu_score(
dt string,
name string,
score int
)row format delimited fields terminated by ",";
laod数据
load data inpath "/user/hdfs/stu_data.txt"
into table stu_score;
三种排序函数
- row_number() 有合并,合并后没有跳序号。如123456
- rank() 有合并,合并后有跳序号.如12245
- dense_rank() 无合并,无跳号。既122234
--查出前七条数据
select *
from
(
select * ,
row_number() over(distribute by dt sort by dt asc,score desc) rn,
rank() over(distribute by dt sort by dt asc, score desc) rk,
dense_rank() over(distribute by dt sort by dt asc, score desc) drk
from stu_score
) r
where r.drk<7;