HQL按班级排序问题

排序函数

row_number():没有并列，相同名次顺序排列
rank():有并列，相同名次采取空位
dense_rank():有并列，相同名次不空位

数据

01 gp1802 84
02 gp1801 84
03 gp1802 84
04 gp1802 84
05 gp1801 81
06 gp1802 81
07 gp1802 81
08 gp1801 81
09 gp1802 81
10 gp1802 81
11 gp1803 81
12 gp1802 89
13 gp1802 89
14 gp1802 89
15 gp1803 89
16 gp1802 91
17 gp1802 97
18 gp1802 72
19 gp1804 73
20 gp1802 77
21 gp1802 71
22 gp1802 61
23 gp1803 65
24 gp1804 67
25 gp1804 62
26 gp1804 61
27 gp1802 91
28 gp1801 93
29 gp1802 91
30 gp1804 92
31 gp1803 41
32 gp1802 41
33 gp1802 42

建表

create table `users`.`t_class`(
sid string,
sclass string,
score int
)
row format delimited
fields terminated by ' '
;

load data local inpath '/root/data/class.txt' into table `users`.`t_class`;

案例

# 查询每个班级的成绩，按分数降序排序

select 
sid,sclass,score,rank() over(distribute by(sclass) sort by (score desc)) rank
from t_class;

select 
sid,sclass,score,row_number() over(distribute by(sclass) sort by (score desc)) rank
from t_class;

select 
sid,sclass,score,dense_rank() over(distribute by(sclass) sort by (score desc)) rank
from t_class;

# 查询每个班级的前三名

select 
class_rank.*
from (
select 
sid,sclass,score,rank() over(distribute by(sclass) sort by (score desc)) rank
from t_class ) class_rank
where class_rank.rank<=3

select 
class_rank.*
from (
select 
sid,sclass,score,row_number() over(distribute by(sclass) sort by (score desc)) rank
from t_class ) class_rank
where class_rank.rank<=3

select 
class_rank.*
from (
select 
sid,sclass,score,dense_rank() over(distribute by(sclass) sort by (score desc)) rank
from t_class ) class_rank
where class_rank.rank<=3