配置单元-配置单元子查询的问题

xa9qqrwz  于 2021-06-27  发布在  Hive
关注(0)|答案(1)|浏览(516)

我的问题陈述是
“找出每个州人口最多的前2个地区”
数据就像
输入
我的预期产出是
输出
我尝试了很多查询和子查询,但结果是子查询出现sql错误
有人能帮我得到这个结果吗?
提前谢谢。
我试过的问题
选择state_name,(select concat_ws(',','collect_set(dist_name as string))from population where state_name=state_name group by state order by population desc 2)
按州/市/自治区名称列出的人口组
选择
state\u name,concat\u ws(',',collect\u set(cast(dist\u name as string)))
from population where population.dist\u name in(select dist\u name from(select dist\u name,max(b.population)as total from population b where state\u name=b.state\u name group by b.dist\u name,b.dist\u name order by total desc limit 2)as dist\u name)按州分组

6ju8rftf

6ju8rftf1#

下面是问题-

select A.state, collect_set(A.dist)[0], collect_set(A.dist)[1] from 
(select state, dist, row_number() over (partition by state order by population 
 desc) as rnk from <tableName>) A
where A.rnk<=2 group by A.state;

下面是样本数据的结果-

hive> select * from hier;
OK
C1      C11
C11     C12
C12     123
P1      C1
P2      C2

hive> select parent, collect_set(child)[0], collect_set(child)[1] from hier group by parent;
OK
C1      C11     NULL
C11     C12     NULL
C12     123     NULL
P1      C1      NULL
P2      C2      NULL
Time taken: 19.212 seconds, Fetched: 5 row(s)

相关问题