hive取不同班级前三名问题
1.hive只支持from里的子查询。
1.通过开窗函数实现
ROW_NUMBER() OVER 顺序排序。
RANK() OVER 跳跃排序,如果有两个第一级别时,接下来是第三级别。
DENSE_RANK() OVER 连续排序,如果有两个第一级别时,接下来是第二级别。
select * from
(select
a.math
,studentid
,a.classid
,row_number() over(partition by a.classid order by math desc) t1
from zxw_student_scores a) px
where t1<=3
;
select * from
(select
a.math
,studentid
,a.classid
,DENSE_RANK() over(partition by a.classid order by math desc) t1
from zxw_student_scores a) px
where t1<=3
;
select a.id,a.subject_id,a.student_id,a.score from score as a left join score as b on a.subject_id=b.subject_id and a.score>=b.score
group by a.subject_id,a.student_id,a.score
having count(a.subject_id)>=4
order by a.subject_id,a.score desc;