创建表单:
CREATE TABLE hive.tmp.tmp_lc_test_0722 ( name varchar, class integer, s integer )
插入数据:
insert into tmp.tmp_lc_test_0722 values('a3',1,95)
name class s
a9 3 55
a10 3 78
a1 2 74
a2 1 95
a4 1 80
a5 2 92
a6 3 99
a7 3 99
a8 3 45
a3 1 95
rank():
select * from (
select name,class,s,rank() over(partition by class order by s desc) num from tmp.tmp_lc_test_0722)
-- where num=1
name class s num
a2 1 95 1
a3 1 95 1
a4 1 80 3
a6 3 99 1
a7 3 99 1
a10 3 78 3
a9 3 55 4
a8 3 45 5
a5 2 92 1
a1 2 74 2
row_number()
select * from
(
select name,class,s,row_number() over(partition by class order by s desc) num from tmp.tmp_lc_test_0722
)
-- where num=1
name class s num
a5 2 92 1
a1 2 74 2
a2 1 95 1
a3 1 95 2
a4 1 80 3
a6 3 99 1
a7 3 99 2
a10 3 78 3
a9 3 55 4
a8 3 45 5
rank与row_number区别,rank对应s值相同,排名相同。row_number是根据行号排序分先后