在此配置单元表的类别和限制 5 内排序

问题描述

我有一个具有以下列的配置单元表 A

I have a hive table A that has the following column

USER   ITEM    SCORE
U1      I1       S1
U1      I2       S2
...................

我要的是表B这样的格式

What I want is a table B such a format

USER    ITEMS    #ITEMS is an array
 U1     [I2,I3,...]   # items are sorted according to score in descending and limit 5

对于少于 5 个项目的用户，只需将包含项目的数组按降序排列即可.

for users have less than 5 items, just put the array with items in descending order.

推荐答案

应该是这样的:

select USER,collect_set(ITEM) from (
    select USER, ITEM,row_number () over (partition by USER order by SCORE desc) RN
    from A
) t1
where RN <= 5
group by USER;

这篇关于在此配置单元表的类别和限制 5 内排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！