假设一个场景:存在表user_score,该表的数据如下
[table]
|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|
[/table]
现在要求用一条查询语句取出每种rate下score最大的两条记录,也就算取出id为:2,3,6,7,10,11的记录,要求分别给出SQL和HIVESQL的查询语句
以下是我的想到的方案:
SQL:对user_score根据rate进行分区后根据score进行倒序排序,然后应用row_number函数于每个分区从而筛选出每个分区的前两条记录。
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的行为是无意义的。
这里有两点需要说明:
[list]
[*]使用子查询保证ROW_NUMBER在reduce端执行。
[*]使用BY rate SORT BY rete ASC, score DESC 来保证rate相同的记录被分配到相同的REDUCE中。
[/list]
[table]
|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|
[/table]
现在要求用一条查询语句取出每种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
这里有两点需要说明:
[list]
[*]使用子查询保证ROW_NUMBER在reduce端执行。
[*]使用BY rate SORT BY rete ASC, score DESC 来保证rate相同的记录被分配到相同的REDUCE中。
[/list]