hive分组排序取top N

最新推荐文章于 2025-07-17 20:15:48 发布

longshenlmj

最新推荐文章于 2025-07-17 20:15:48 发布

阅读量2.3w

点赞数 2

CC 4.0 BY-SA版权

分类专栏： hive sql 文章标签： hive 分组排序 sql top-N

本文链接：https://blog.youkuaiyun.com/longshenlmj/article/details/50525385

hive 同时被 2 个专栏收录

26 篇文章

订阅专栏

sql

24 篇文章

订阅专栏

本文介绍了如何在Hive中实现TOPn查询，利用row_number()等窗口函数进行高效的数据排名处理。通过具体实例展示了如何统计各省份城市排名，并详细解释了row_number(), rank() 和 dense_rank() 的区别及用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pig可以轻松获取TOP n。书上有例子

hive中比较麻烦，没有直接实现的函数，可以写udf实现。还有个比较简单的实现方法：
用row_number，生成排名序列号。然后外部分组后按这个序列号多虑，样例代码如下

select a.*
from(   
    select 品牌,渠道,档期,count/sum/其它() as num row_number() over (partition by 品牌,渠道 order by num desc ) rank
    from table_name
    where 品牌,渠道 限制条件
    group by 品牌,渠道,档期
    )a
where a.rank<=10

其实排序有三个函数
（1）row_number：排序后，顺序下来，相同项按先后顺序排序，1，2，3，4，5
（2）rank:排序后，遇到数据相同项时序号一致，后面并留空一位，比如，1，2，2，4，4，6

dense_rank:在遇到数据相同项时,序号一致，不留空位，如 1，2，2，3,3,4,4,5

具体用例可以参见：http://www.cnblogs.com/dycg/p/4260283.html

我自己设计的代码

##统计国内，各省份的城市排名
select b.*
from
(select country,
    province,
    city,
    cnt,
    row_number() over (partition by country,province order by cnt desc) rank
from 
    (select country,
            province,
            city,
            count(1) as cnt
    from tb_pmp_region_report_hive_mapping
    where country = '中国'
    group by country,province,city
    ) a
)b
where b.rank<=3

表a统计出基本数据，从a中加排名项。然后，按排名项过滤。内部group后，外部不需要group by

需要注意的是，加排名项时，不应该使用group。
如果有group，那么row_number中的order by项必须是group内的字段，否则报错，如下段代码报错

select b.*
from
(select country,
    province,
    city,
    cnt,
    row_number() over (partition by country,province order by cnt desc) rank
from 
    (select country,
            province,
            city,
            count(1) as cnt
    from tb_pmp_region_report_hive_mapping
    where country = '中国'
    group by country,province,city
    ) a
    group by country,province,city
)b
where b.rank<=3

执行报错：
FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 7:62 Expression not in GROUP BY key ‘cnt’
hive>