Hive получение n лучших записей в запросе group by
У меня есть следующая таблица в hive
user-id, user-name, user-address, clicks, impressions, page-id, page-name
Мне нужно узнать топ 5 пользователей[user-id, user-name, user-address] по кликам для каждой страницы [page-id, page-name]
Я понимаю, что сначала нужно сгруппировать по [page-id,page-name] и внутри каждой группы упорядочить по убыванию [clicks,impressions] и затем вывести только топ 5 пользователей[user-id,user-name,user-address] для каждой страницы, но мне сложно составить запрос.
Как мы можем сделать это с помощью HIve UDF?
3 ответа
Вы можете сделать это с помощью UDF для ранга (), описанного здесь: http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-group-in -хадоофив /
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
Вы можете использовать функцию each_top_k из hivemall для эффективного вычисления top-k в Apache Hive.
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
UDTF each_top_k является очень быстрым по сравнению с другими методами, выполняющими запросы top-k (например, distributed by/rank) в Hive, потому что он не содержит всего ранжирования для промежуточного результата.
Допустим, ваши данные выглядят следующим образом:
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
Итак, для На странице 1 вы получаете 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