需求场景,组内排序,例如统计某个用户前10次消费金额,如求某个相同id或组内的top值
步骤一,创建测试表
create table tmp_partition_test
(
name string,
subject string,
score int
)
步骤二,插入测试数据
insert into tmp_partition_test (name,subject,score)
values ('张多','语文',87 );
insert into tmp_partition_test (name,subject,score)
values ('张多','数学',95) ;
insert into tmp_partition_test (name,subject,score)
values ('张多','英语',68) ;
insert into tmp_partition_test (name,subject,score)
values ('王就','语文',94) ;
insert into tmp_partition_test (name,subject,score)
values ('王就','数学',56) ;
insert into tmp_partition_test (name,subject,score)
values ('王就','英语',84) ;
insert into tmp_partition_test (name,subject,score)
values ('蔡结','语文',64) ;
insert into tmp_partition_test (name,subject,score)
values ('蔡结','数学',86) ;
insert into tmp_partition_test (name,subject,score)
values ('蔡结','英语',84) ;
insert into tmp_partition_test (name,subject,score)
values ('金胡','语文',65) ;
insert into tmp_partition_test (name,subject,score)
values ('金胡','数学',85) ;
insert into tmp_partition_test (name,subject,score)
values ('金胡','英语',78) ;
步骤二,进行数据查询,对比结果
select name,subject,score,
rank() over(partition by subject order by score desc) rp, --RANK() 排序相同时会重复,总数不会变
dense_rank() over(partition by subject order by score desc) drp, --DENSE_RANK() 排序相同时会重复,总数会减少
row_number() over(partition by subject order by score desc) rmp --ROW_NUMBER() 会根据顺序计算
from tmp_partition_test;