hive中分组取前N个值的实现

需求：假设有一个学生各门课的成绩的表单，应用hive取出每科成绩前2名

数据如下表：

id      clsno   score

1       c1      20
2       c1      30
3       c1      40
4       c1      50
5       c1      80
11      c1      80
12      c1      60
6       c2      20
7       c2      30
8       c2      40
9       c2      50
10      c2      80
13      c1      90

 

在sql中可以实现的方式是：SELECT * FROM table AS T WHERE ID IN ( SELECT TOP 2 ID FROM table WHERE clsno = T.clsno ORDER BY score DESC )

 

在hive中没有in这个函数，所以需要别的方式实现。

定义一个rank函数：

package com.example.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
	 
public final class Rank extends UDF{
	private int  counter;
	private String last_key;
	public int evaluate(final String key){
	  if ( !key.equalsIgnoreCase(this.last_key) ) {
	     this.counter = 0;
	     this.last_key = key;
	  }
	  return this.counter++;
	}
}

 

加入$HIVE_HOME/lib/hive-serde-1.7.jar:$HIVE_HOME/lib/hive- exec .jar:$HADOOP_HOME/hadoop-core.jar打成一个jar包Rank.jar

hive>add jar Rank.jar;

hive>create temporary function rank as 'com.example.hive.udf.Rank';

hive>select clsno,rank(clsno),id,score from (select clsno,id,score from byl_topn_test distribute by clsno sort by clsno,score desc)a;

得到结果：

 

取各科成绩中rank值小于2的记录即可。

 

英文原文链接：http://www.findnwrite.com/musings/extract-top-n-records-in-each-group-in-hadoophive/