hive分组排序 取top N

转自：http://blog.csdn.net/longshenlmj/article/details/50525385

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
2
3
4
5
6
7
8

其实 排序有三个函数 
（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
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

表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
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

执行报错： 
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>