玖叶教程网

比较row_number() over() , rank() over() ,dense_rank() over() 不同之处

创建临时表

create table tmp.tmp_20210510

(

name string

,sex string

,age int

)

插入数据进行测试

insert into table tmp.tmp_20210510 values

('aa','male',16)

,('bb','male',16)

,('cc','male',15)

,('gg','male',18)

,('dd','female',25)

,('ee','female',25)

,('ff','female',26)

,('ee','female',35)

,('hh','female',45)

;

1.row_number()

select

name

,sex

,age

,row_number() over(partition by sex order by age desc) as rank

from tmp.tmp_20210510

2.rank()

select

name

,sex

,age

,rank() over(partition by sex order by age desc) as rank

from tmp.tmp_20210510

3.dense_rank()

select

name

,sex

,age

,dense_rank() over(partition by sex order by age desc) as rank

from tmp.tmp_20210510