Hive分组取Top N
Hive在0.11.0版本开始加入了row_number、rank、dense_rank分析函数,可以查询分组排序后的top值
说明:
row_number() over ([partition col1] [order by col2])
rank() over ([partition col1] [order by col2])
dense_rank() over ([partition col1] [order by col2])
它们都是根据col1字段分组,然后对col2字段进行排序,对排序后的每行生成一个行号,这个行号从1开始递增
col1、col2都可以是多个字段,用','分隔
区别:
1)row_number:不管col2字段的值是否相等,行号一直递增,比如:有两条记录的值相等,但一个是第一,一个是第二
2)rank:上下两条记录的col2相等时,记录的行号是一样的,但下一个col2值的行号递增N(N是重复的次数),比如:有两条并列第一,下一个是第三,没有第二
3)dense_rank:上下两条记录的col2相等时,下一个col2值的行号递增1,比如:有两条并列第一,下一个是第二
row_number可以实现分页查询
实例:
hive> create table t(name string, sub string, score int) row format delimited fields terminated by '\t';
数据在附件的a.txt里
a chinese 98
a english 90
d chinese 88
c english 82
c math 98
b math 89
b chinese 79
z english 90
z math 89
z chinese 80
e math 99
e english 87
d english 90
1、row_number
hive (test)> select *, row_number() over (partition by sub order by score) as od from t;
![](https://note.wiz.cn/api/document/files/unzip/dcec1f50-de6d-4955-a388-f513712aea71/881d094f-fb98-4c93-869b-df76ce4abd13.2712/index_files/2357801f-7c96-4d7d-8e0c-bfaed070f6e5.png)
2、rank
hive (test)> select *, rank() over (partition by sub order by score) as od from t;
![](https://note.wiz.cn/api/document/files/unzip/dcec1f50-de6d-4955-a388-f513712aea71/881d094f-fb98-4c93-869b-df76ce4abd13.2712/index_files/62be0c79-ec25-4530-82b1-e9577d1c5da8.png)
3、dense_ran
hive (test)> select *, dense_rank() over (partition by sub order by score desc) from t;
![](https://note.wiz.cn/api/document/files/unzip/dcec1f50-de6d-4955-a388-f513712aea71/881d094f-fb98-4c93-869b-df76ce4abd13.2712/index_files/f624d6d5-3425-40d1-b731-a64d5c51fff6.png)
业务实例:
统计每个学科的前三名
select * from (select *, row_number() over (partition by sub order by score desc) as od from t ) t where od<=3;
语文成绩是80分的排名是多少
hive (test)> select od from (select *, row_number() over (partition by sub order by score desc) as od from t ) t where sub='chinese' and score=80;
分页查询
hive (test)> select * from (select *, row_number() over () as rn from t) t1 where rn between 1 and 5;
附件列表