Hive 通过查询获取组中的前 n 条记录 [英] Hive getting top n records in group by query

问题描述

我在 hive 中有下表

I have following table in hive

用户 ID、用户名、用户地址、点击次数、展示次数、页面 ID、页面名称

user-id, user-name, user-address,clicks,impressions,page-id,page-name

我需要通过点击每个页面 [page-id,page-name] 找出前 5 个用户 [user-id,user-name,user-address]

I need to find out top 5 users[user-id,user-name,user-address] by clicks for each page [page-id,page-name]

我知道我们需要先按 [page-id,page-name] 分组，在每个组内我想按 [clicks,impressions] desc 排序，然后只发出前 5 个用户 [user-id, user-name], user-address] 每个页面，但我发现很难构建查询.

I understand that we need to first group by [page-id,page-name] and within each group I want to orderby [clicks,impressions] desc and then emit only top 5 users[user-id, user-name, user-address] for each page but I am finding it difficult to construct the query.

我们如何使用 HIve UDF 做到这一点?

How can we do this using HIve UDF ?

推荐答案

您可以使用此处描述的 rank() UDF 来实现:http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-group-in-hadoophive/

You can do it with a rank() UDF described here: http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-group-in-hadoophive/

SELECT page-id, user-id, clicks
FROM (
    SELECT page-id, user-id, rank(user-id) as rank, clicks
    FROM mytable
    DISTRIBUTE BY page-id, user-id
    SORT BY page-id, user-id, clicks desc
) a 
WHERE rank < 5
ORDER BY page-id, rank

这篇关于Hive 通过查询获取组中的前 n 条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！