logo标签列表

Hive在分组查询中获取前n条记录

user-defined-functionshiverank
26

我在Hive中有以下表格：

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

我需要找出每个页面[page-id,page-name]的前5位用户[user-id，user-name，user-address]按点击量[clicks]排序。

我明白我们需要首先按[page-id，page-name]分组，然后在每个分组内按[clicks，impressions]倒序排序，然后仅发出每个页面的前5位用户[user-id，user-name，user-address]，但我发现很难构建查询。

如何使用Hive UDF实现此功能？

6个回答
17
从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和这个人的博客中找到更多（尽管我不太满意）的这些函数示例。
15

修订答案，根据 @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 分区重置计数器）。

太棒了，它保存了我的搜索 :) - minhas23
最后的按排名排序无法正常工作，因为在最外层没有选择排名。 - John Jiang
10
嗨Maxime，抱歉这样打扰你。我也遇到了类似的问题。我在SO上发过帖子，但是由于我正在使用Hive和HiveQL是新的对我来说，所以没有得到好的回应。https://dev59.com/Q2XWa4cB1Zd3GeqPRusM。如果有帮助，将不胜感激。 - arsenal
10
我刚刚花了好几个小时来让这个程序工作，但是它失败了。出错的原因是你先进行排名，然后再执行DISTRIBUTE BY和SORT BY。相反，你应该在外部查询中应用排名，并在内部查询中使用DISTRIBUTE BY和SORT BY。例如，SELECT page-id、user-id、clicks FROM（SELECT page-id、user-id、rank(user-id) as rank、clicks FROM（SELECT * FROM mytable DISTRIBUTE BY page-id、user-id SORT BY page-id、user-id、clicks DESC）a）b WHERE rank < 5 ORDER BY page-id、rank； - Himanshu Gahlot
2
确认@HimanshuGahlot是正确的。这个答案有一个BUG！你必须在外部查询中使用rank()，并在内部查询中使用DISTRIBUTE/SORT BY！ - Hai-Anh Trinh
2
假设您的数据如下所示：
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
2
您可以使用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
与其他运行top-k查询（例如distributed by/rank）的方法相比，each_top_k UDTF非常快速，因为它不会保留中间结果的整个排名。
-1

选择 * 从 (选择 user_id，user-name，user-address，page，click，row_num() over (partition by page order by clicks desc) a where a.row_num<=5

可能会更改选择的列，但逻辑是正确的。

2
请不要在多个问题中添加相同的答案。请回答最好的一个，并将其余的标记为重复。请参见是否可以在多个问题中添加重复答案？ - Dharman
请使用代码格式化您的查询（并使用大写等）。 - jasie
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接