HIVE how to limit the number of entries in a group
I am studying HIV these days and am facing some challenges ...
I have a table called SAMPLE:
USER_ID PRODUCT_ID NUMBER
1 3 20
1 4 30
1 2 25
1 6 50
1 5 40
2 1 10
2 3 15
2 2 40
2 5 30
2 3 35
How can I use HIVE to group a table by user_id and in each group order records in DESC order from NUMBER, and in each group I want to store up to 3 records.
As a result, I want:
USER_ID PRODUCT_ID NUMBER(optional column)
1 6 50
1 5 40
1 4 30
2 2 40
2 3 35
2 5 30
or
USER_ID PRODUCT_IDs
1 [6,5,4]
2 [2,3,5]
Can anyone help me? .. Thanks a lot !!!!!!!!!!!!!!!!
+3
source to share
1 answer
try it,
select user_id,product_id,number
from(
select user_id,product_id,number, ROW_NUMBER() over (Partition BY user_id) as RNUM
from (
select user_id, number,product_id
from SAMPLE
order by number desc
) t) t2
where RNUM <=3
Output
1 6 50
1 5 40
1 4 30
2 2 40
2 3 35
2 5 30
hive version must be 0.11 or greater, can I know if your version is lower
+4
source to share