HIVE中row_number函数的描述与使用场景

假设一个场景：存在表user_score,该表的数据如下

id	rate	score
1	'0-4'	10
2	'0-4'	40
3	'0-4'	30
4	'0-4'	20
5	'5-10'	10
6	'5-10'	40
7	'5-10'	30
8	'5-10'	20
9	'11-20'	10
10	'11-20'	40
11	'11-20'	30
12	'11-20'	20

现在要求用一条查询语句取出每种rate下score最大的两条记录，也就算取出id为：2，3，6，7，10，11的记录，要求分别给出SQL和HIVESQL的查询语句

以下是我的想到的方案：

SQL：对user_score根据rate进行分区后根据score进行倒序排序，然后应用row_number函数于每个分区从而筛选出每个分区的前两条记录。

WITH pus AS (
 SELECT *, rid = ROW_NUMBER() OVER 
 (PARTITION BY rete ORDER BY score DESC) FROM user_score
)

SELECT id, rate, score FROM pus WHERE rid <= 2

HIVESQL: HIVE中不存在以上SQL的那种实现方式，ROW_NUMBER（）在HIVE中是一个函数，必须带一个或者多个列参数，如ROW_NUMBER(col1, ....)，它的作用是按指定的列进行分组生成行序列，在ROW_NUMBER(a,b) 时，若两条记录的a，b列相同，则行序列+1，否则重新计数。因为HIVE是基于MAPREADUCE的，必须保证ROW_NUMBER执行是在REDUCE中，并且ROW_NUMBER中使用的列中，列值相同的记录要再同一个reduce中，否则ROW_NUMBER的行为是无意义的。

 SELECT id, rate, score FROM(SELECT * FROM user_score DISTRIBUTE BY rate SORT BY rete ASC, score DESC) WHERE ROW_NUMBER(rete) <= 2

这里有两点需要说明：

• 使用子查询保证ROW_NUMBER在reduce端执行。
• 使用BY rate SORT BY rete ASC, score DESC 来保证rate相同的记录被分配到相同的REDUCE中。

评论

2 楼 bo_hai 2016-04-27  

第二个sql没有执行成功，请赐教？

1 楼 jkdcdlly 2015-03-12  

测试，不行，帮你修改了一下
select * from (SELECT id, rate, score,ROW_NUMBER(rate) as num FROM(SELECT id, rate, score FROM user_score DISTRIBUTE BY rate SORT BY rate ASC, score DESC) a) b WHERE num <= 2;