Hive Grouping Sort, TopN

Hive Grouping Sort, TopN

Syntax format: row_number () over (partition by COL1 ORDER BY COL2 Desc) rank
Partition by: Hive-like build table, meaning of partition;
ORDER BY: Sort, default is ascending, plus desc descending;
Rank: Indicates an alias
Represents the sort based on COL2 within the grouping according to the COL1 grouping, and the value computed by this function represents the sequential number of each set of internally ordered (contiguous unique within the group)

--Grouping sort
--Ask for the maximum 3 days of a user's date
Select A.* from (
Select P_day,muuid,row_number () over (partition by Muuid ORDER by P_day) rank
From Test
Group by P_day,muuid) a
where A.rank <=3;
----Get the top 3 daily recharge numbers
SELECT * FROM (
Select P_day,muuid,c row_number over (partition by P_day ORDER BY p_day,c Desc) Ord
From
Select P_day,muuid,count (1) c from test where p_day> ' 2017-09-09 '
GROUP BY P_day,muuid
) T1
) T2
where Ord <= 3;

Hive Grouping Sort, TopN