Top N 问题——排名函数
-
编写sql语句实现每班前三名,分数一样并列(排名函数),同时求出前三名按名次排序的分差
-
数据
-- sid 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
-- 待求结果数据如下:
class score rank lagscore
1901 90 1 0
1901 90 1 0
1901 83 2 -7 --比前一名差的分数
1901 60 3 -23
1902 99 1 0
1902 87 2 -12
1902 67 3 -20
create table stu(
sno int,
class string,
score int
row format delimited fields terminated by ' ';
load data local inpath '/home/hadoop/data/stu.dat' into table stu;
思路:
-- 1、上排名函数,分数一样并列,所以用dense_rank
-- 2、将上一行数据下移,相减即得到分数差
-- 3、处理 NULL
with tmp as (
select sno, class, score,
dense_rank() over (partition by class order by score desc)
as rank
from stu
)
select sno, class, score,
dense_rank() over (partition by class order by score desc)
as rank,
--将数据下移,相减求分差