sql - sql - 获取每个ID的前3个值

是否有可能使用HIVEQL根据每个id的count来提取前三个值。在输入和输出中如下所示:


Input


[id] [word] [count]


B000JMLBHU book 89


B000JMLBHU read 83


B000JMLBHU was 76


B000JMLBHU story 54


B000R93D4Y with 69


B000R93D4Y book 61


B000R93D4Y story 60


B000R93D4Y was 57


B000R93D4Y have 53


B001892DGG was 68


B001892DWA was 73


B001BXNQ2O was 119


B001BXNQ2O book 59


B001H55R8M was 56


B001HQHCBQ was 93


B001HQHCBQ story 75


B001HQHCBQ bella 61


B001HQHCBQ with 59


B001HQHCBQ love 58


B001HQHCBQ zsadist 53



Output


[id] [word] [count]


B000JMLBHU book 89


B000JMLBHU read 83


B000JMLBHU was 76


B000R93D4Y with 69


B000R93D4Y book 61


B000R93D4Y story 60


B001892DGG was 68


B001892DWA was 73


B001BXNQ2O was 119


B001BXNQ2O book 59


B001H55R8M was 56


B001HQHCBQ was 93


B001HQHCBQ story 75


B001HQHCBQ bella 61



时间:

你可以使用row_number()函数:


select t.*


from (select *, row_number() over (partition by id order by count desc) as seq


 from table 


 ) t


where seq <= 3;



这个答案是特定于Hiveql的,并且对Mysql 8有效

你可以使用公用表表达式和window函数rank来获取每个id的最大的3个结果


WITH cte AS(


 SELECT *, 


 RANK() OVER (PARTITION BY id ORDER BY count DESC ) rnk


 FROM your_table


 ORDER BY id


)



SELECT *


FROM cte


WHERE rnk <= 3;



你可以尝试使用row_NUMBER函数,并包含在where子句ROW_NUMBER

...