[大数据]hive开窗函数partition by

创建表单：
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是根据行号排序分先后