背景
假设有一个学生各门课的成绩的表单,应用hive取出每科成绩前100名的学生成绩。
这个就是典型在分组取Top N的需求。
解决思路
对于取出每科成绩前100名的学生成绩,针对学生成绩表,根据学科,成绩做order by排序,然后对排序后的成绩,执行自定义函数row_number(),必须带一个或者多个列参数,如ROW_NUMBER(col1, ....),它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时,若两条记录的a,b列相同,则行序列+1,否则重新计数。
只要返回row_number()返回值小于100的的成绩记录,就可以返回每个单科成绩前一百的学生。
解决过程
成绩表结构
create table score_table (
subject string,
student string,
score int
)
partitioned by (date string)
如果要查询2012年每科成绩前100的学生成绩,sql如下
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
select subject,score,student from
(select subject,score,student from score where dt='2012' order by subject,socre desc) order_score
where row_number(subject) <= 100;