Улей, получающих первые n записей в группе по запросу

У меня есть следующая таблица в улье

идентификатор пользователя, имя пользователя, адрес пользователя, клики, показы, идентификатор страницы, название страницы

Мне нужно узнать 5 лучших пользователей [user-id, user-name, user-address], щелкнув по каждой странице [page-id, page-name]

Я понимаю, что нам нужно сначала сгруппировать по [page-id, page-name], и внутри каждой группы я хочу упорядочить [клики, показы] desc, а затем испустить только топ-5 пользователей [user-id, user-name, user-address] для каждой страницы, но мне сложно построить запрос.

Как это сделать, используя HIve UDF?

Ответ 1

Вы можете сделать это с помощью ранга() 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

Ответ 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

Обратите внимание, что ранг() UDAF применяется к столбцу идентификатора страницы, новое значение которого используется для reset или увеличивает счетчик ранга (например, счетчик reset для каждого раздела с идентификатором страницы)

Ответ 3

Как и в случае с Hive 0.11, вы можете сделать это, используя функцию Hive, встроенную в функцию rank(), и используя более простую семантику, используя Уличные встроенные функции Google Analytics и Windowing. К сожалению, я не мог найти столько примеров с ними, как мне бы хотелось, но они действительно очень полезны. Используя их, встраиваются как 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 и этот блог-блог.

Ответ 4

Вы можете использовать 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

each_top_k UDTF очень быстро по сравнению с другими методами, выполняющими top-k-запросы (например, distributed by/rank) в Hive, потому что он не удерживает весь рейтинг промежуточного результата.

Ответ 5

Скажем, ваши данные выглядят следующим образом:

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

Ответ 6

выберите * из (выберите user_id, user-name, user-address, page, click, row_num() over (разделить по порядку страниц по кликам desc) a, где a.row_num & lt; = 5

может быть изменение в выборе столбцов, но логика верна.