Hive通过查询获取组中的前n条记录

Question

我在配置单元中有下表

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

我需要找出前 5 个用户 [用户 ID、用户名、用户地址]通过每个页面 [page-id,page-name] 的点击次数

我知道我们需要首先按 [page-id,page-name] 进行分组，并且在每个组中我想按 [clicks,impressions] desc 进行排序，然后仅发出前 5 名每个页面的 users[user-id, user-name, user-address] 但我发现构建查询很困难。

我们如何使用 HIVe UDF 来做到这一点？

Answer 1

从 Hive 0.11 开始，您可以使用 Hive 内置的rank()函数并使用更简单的语义来执行此操作 Hive 的内置分析和窗口功能。遗憾的是，我找不到我想要的那么多示例，但它们确实非常有用。使用这些，rank() 和WhereWithRankCond 都是内置的，因此您可以这样做：

SELECT page-id, user-id, clicks
FROM (
    SELECT page-id, user-id, rank() 
           over (PARTITION BY page-id ORDER BY clicks DESC) as rank, clicks 
    FROM my table
) ranked_mytable
WHERE ranked_mytable.rank < 5
ORDER BY page-id, rank

不需要UDF，并且只有一个子查询！此外，所有排名逻辑都是本地化的。

您可以在这个 Jira 中找到更多这些函数的示例（尽管还不足以满足我的喜好）< /a> 和此人的博客。

Answer 2

修订答案，修复@Himanshu Gahlot 提到的错误

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

请注意，rank() UDAF 应用于 page-id 列，其新值用于重置或增加排名计数器（例如，重置每个 page-id 分区的计数器） ）

Answer 3

您可以使用此处描述的rank() UDF来完成此操作：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

Answer 4

假设您的数据如下所示：

page-id   user-id   clicks
page1     user1     10
page1     user2     10
page1     user3     9
page1     user4     8
page1     user5     7
page1     user6     7
page1     user7     6
page1     user8     5
page2     user1     20
page2     user2     19
page2     user3     18

下面的查询将为您提供：

SELECT page-id, user-id, clicks, rank
FROM (
    SELECT page-id, user-id, rank() 
           over (PARTITION BY page-id ORDER BY clicks DESC) as rank, clicks 
    FROM your_table
) ranked_table
WHERE ranked_table.rank <= 5

结果：

page-id   user-id   clicks  rank
page1     user1     10      1
page1     user2     10      1 
page1     user3     9       3 
page1     user4     8       4
page1     user5     7       5 
page1     user6     7       5 
page2     user1     20      1
page2     user2     19      2  
page2     user3     18      3

因此，对于 page1，您将获得 6 个用户，因为具有相同点击次数的用户排名相同。

但是，如果您正在寻找 5 个用户，并随机选择，以防多个用户处于同一排名。您可以使用以下查询

SELECT page-id, user-id, clicks, rank
FROM (
    SELECT page-id, user-id, row_number() 
           over (PARTITION BY page-id ORDER BY clicks DESC) as rank, clicks 
    FROM your_table
) ranked_table
WHERE ranked_table.rank <= 5

结果：

page-id   user-id   clicks  rank
page1     user1     10      1
page1     user2     10      2 
page1     user3     9       3 
page1     user4     8       4
page1     user5     7       5 
page2     user1     20      1
page2     user2     19      2  
page2     user3     18      3

Answer 5

您可以使用 hivemall 的 >each_top_k 功能 Apache Hive 上的高效 top-k 计算。

select
  page-id, 
  user-id,
  clicks
from (
  select
    each_top_k(5, page-id, clicks, page-id, user-id)
      as (rank, clicks, page-id, user-id)
  from (
    select
      page-id, user-id, clicks
    from
      mytable
    DISTRIBUTE BY page-id SORT BY page-id
  ) t1
) t2
order by page-id ASC, clicks DESC

与在 Hive 中运行 top-k 查询（例如，distributed by/rank）的其他方法相比，each_top_k UDTF 非常快，因为它不保存中间结果。

Answer 6

select * from (select user_id,user-name,user-address,page,click,row_num() over (partition by page order by clicks desc) a where a.row_num<=5

在选择列时可能会发生变化，但是逻辑是正确的。