配置单元中的列(count)from table

ubby3x7f  于 2021-06-27  发布在  Hive
关注(0)|答案(2)|浏览(327)

如果我想选择 count(user_name), country 从 hive 的table上。我应该使用什么命令来获得最多用户名的前2个国家的结果?
如何使用秩函数?

id | user_name | country
 1 | a         | UK
 2 | b         | US
 3 | c         | AUS
 4 | d         | ITA
 5 | e         | UK
 6 | f         | US

结果应该是:

rank| num_user_name | country
 1  | 2             | US
 1  | 2             | UK
 2  | 1             | ITA
 2  | 1             | AUS
doinxwow

doinxwow1#

不需要子查询:

select dense_rank() over (order by count(*)) as rank,
       country, 
       count(*) as num_user_name
from t
group by country
order by count(*) desc, country;
bvjxkvbb

bvjxkvbb2#

你可以用这个 dense_rank 分析函数:

with cte as (
    select   country, 
             count(user_name) as num_user_name
    from     tbl
    group by country
), cte2 as (
    select   dense_rank() over (order by num_user_name desc) as ranked,
             num_user_name,
             country
    from     cte
)
select   ranked,
         num_user_name,
         country
from     cte2
where    ranked <= 2
order by 1

相关问题