hive 编写sql语句实现每班前三名，分数一样并列，同时求出前三名 按名次排序的一次的分差

1.4 编写sql语句实现每班前三名，分数一样并列，同时求出前三名
按名次排序的一次的分差

Stu_no class score
1 1901 90
2 1901 90
3 1901 83
4 1901 60
5 1902 66
6 1902 23
7 1902 99
8 1902 67
9 1902 87

create table if not exists stu(
stu_no string,
class string,
score int
)
row format delimited fields terminated by ' '
lines terminated by '\n'
stored as textfile
;

load data local inpath '/root/hivedata/stu.txt' into table stu;

select
class,
stu_no,
score,
num,
score-nvl(lag(score) over(distribute by class sort by num),0)
from
(select
stu_no,
class,
score,
rank() over(distribute by class sort by score desc) num
from stu) t1
where t1.num<4
;