hadoop - 配置单元表达式不按键大小分组

Question

我的表架构是(州字符串，城市字符串，大小整数)

这是我的输入数据

Karnataka,Bangalore,200
Karnataka,Mysore,50
Karnataka,Bellary,100
Karnataka,Mangalore,10
Andhra pradesh,Chittoor, 25
Andhra pradesh,nellore, 15
Andhra pradesh,guntur, 20
Andhra pradesh,tirupathi, 30
Andhra pradesh,vizag, 35
Andhra pradesh,kadapa, 45

我想检索该州排名前 2 的城市及其规模，我想要如下输出。

(Andhra pradesh,{(35),(30)},{(vizag),(tirupathi)})
(Karnataka,{(200),(100)},{(Bangalore),(Bellary)})

我已经按如下方式编写了查询，但是我收到错误消息，因为表达式不是按大小分组的，请帮助我。

select * from statefile groyp by state,city order by size limit 2;

提前致谢。

Answer 1

您将使用 row_number():

select sf.*
from (select sf.*,
             row_number() over (partition by state order by size desc) as seqnum
      from statefile sf
     ) sf
where seqnum <= 2;