求每个课程分数最高的前三名学生（考虑分数相同的情况） 用hive 的开窗函数

Question

  
有三份源数据文件，如下所示,请根据需求，编写对对那个的HQL语句。
文件1： 学生表student <学号sid,姓名sname,性别ssex,年龄sage,系department> 
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,赵丽,女,20,CS

文件2： 成绩表score <学生编号sid,课程编号cid，成绩sscore>
95001,1,81
95001,2,85
95001,3,88
95001,4,70
95002,1,90
95002,2,80
95002,3,90
95002,4,80
95003,1,70
95003,2,78
95003,3,65
95003,4,65
95004,1,70
95004,2,90
95004,3,85
95004,4,90
95005,1,70
95005,2,90
95005,3,70
95005,4,90
95006,1,70
95006,2,90
95006,3,70
95006,4,90

select * from score 

文件3： 课程表course<课程编号cid,课程名cname>
1,数据库
2,数学
3,信息系统
4,操作系统

Answer 1

可以使用Hive的开窗函数来求每个课程分数最高的前三名学生。具体的HQL语句如下：

SELECT c.cname, s.sname, s.sscore 
FROM (
    SELECT cid, sname, sscore, 
        ROW_NUMBER() OVER(PARTITION BY cid ORDER BY sscore DESC) as rank
    FROM score
) s
JOIN course c ON s.cid = c.cid
WHERE s.rank <= 3
ORDER BY c.cname, s.sscore DESC;

上述HQL语句中，首先在成绩表score中使用开窗函数ROW_NUMBER()，对每个课程按照成绩降序排列，生成每个学生在该课程中的排名（rank）。然后，将该结果集与课程表course进行JOIN操作，将课程名cname与学生姓名sname、成绩sscore联合在一起。最后，按照课程名和成绩降序排列，筛选出每个课程排名前三位的学生信息。