Hive分组取Top N数据

1、ROW_NUMBER,RANK(),DENSE_RANK()

语法格式:row_number() OVER (partition by COL1 order by COL2 desc ) rank
partition by:类似hive的建表,分区的意思;
order by :排序,默认是升序,加desc降序;
rank:表示别名
表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

1.1 案例

1.1.1 样本数据

浙江,杭州,300
浙江,宁波,150
浙江,温州,200
浙江,嘉兴,100
江苏,南京,270
江苏,苏州,299
江苏,某市,200
江苏,某某市,100

1.1.2导入数据

--执行下述语句
hive (temp)> hive -f 'HQL/loaddata.hql'>out/tmp;  
-----------------------------------------------
drop table datatable;
CREATE table datatable (
  province string,
  city string,
  people int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

load data local inpath 'home/loaddata'
overwrite into table temp.datatable;
----------------------------------------------
--查看结果
hive (temp)> select * from datatable;
OK
province      city  people
浙江    杭州    300
浙江    宁波    150
浙江    温州    200
浙江    嘉兴    100
江苏    南京    270
江苏    苏州    299
江苏    某市    200
江苏    某某市  100

1.2 按照人口降序排列,生成衍生变量

select province,city,
rank() over (order by people desc) rank,
dense_rank() over (order by people desc) dense_rank,
row_number() over(order by people desc) row_number
from datatable
group by province,city,people;

--结果
province        city    rank    dense_rank      row_number
浙江    杭州    1       1       1
江苏    苏州    2       2       2
江苏    南京    3       3       3
浙江    温州    4       4       4
江苏    某市    4       4       5
浙江    宁波    6       5       6
江苏    某某市  7       6       7
浙江    嘉兴    7       6       8

主要注意打圈的:
row_number:顺序下来
rank:在遇到数据相同项时,会留下空位5,(第一列4,4,6)
dense_rank:在遇到数据相同项时,不会留下空位,(红框内第一列,4,4,5)

1.3 分组按照省份分区,再按照人口降序排列,生成衍生变量

select province,city,
rank() over (partition by province order by people desc) rank,
dense_rank() over (partition by province order by people desc) dense_rank,
row_number() over(partition by province order by people desc) row_number
from datatable
group by province,city,people;

--结果
province        city    rank    dense_rank      row_number
江苏    苏州    1       1       1
江苏    南京    2       2       2
江苏    某市    3       3       3
江苏    某某市  4       4       4
浙江    杭州    1       1       1
浙江    温州    2       2       2
浙江    宁波    3       3       3
浙江    嘉兴    4       4       4

2取TOPN数据

2.1 按照国家提取TOP3

2.1.1 样本数据:

国家 城市 Visitors
阿联酋,阿布扎比,137
阿联酋,阿布扎比,146
阿联酋,阿布扎比,178
阿联酋,阿布扎比,337
阿联酋,阿布扎比,178
阿联酋,阿布扎比,227
阿联酋,阿布扎比,157
阿联酋,迪拜,144
阿联酋,迪拜,268
阿联酋,迪拜,103
阿联酋,迪拜,141
阿联酋,迪拜,108
阿联酋,迪拜,266
澳大利亚,悉尼,141
澳大利亚,悉尼,122
澳大利亚,悉尼,153
澳大利亚,悉尼,128
澳大利亚,墨尔本,294
澳大利亚,墨尔本,230
澳大利亚,墨尔本,159
澳大利亚,墨尔本,188
澳大利亚,堪培拉,249
澳大利亚,堪培拉,378
澳大利亚,堪培拉,255
澳大利亚,堪培拉,240

2.1.2导入数据

--执行下述语句
hive (temp)> hive -f 'HQL/loaddata.hql'>out/tmp;  
-----------------------------------------------
drop table temp.tripdata;
CREATE table datatable (
  country string,
  city string,
  Visitors int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

load data local inpath 'home/loaddata'
overwrite into table temp.tripdata;
----------------------------------------------

----------------------------------------------
--查看结果
hive (temp)> select * from tripdata;
country        city   visitors
阿联酋  阿布扎比        137
阿联酋  阿布扎比        146
阿联酋  阿布扎比        178
阿联酋  阿布扎比        337
阿联酋  阿布扎比        178
阿联酋  阿布扎比        227
阿联酋  阿布扎比        157
阿联酋  迪拜    144
阿联酋  迪拜    268
阿联酋  迪拜    103
阿联酋  迪拜    141
阿联酋  迪拜    108
阿联酋  迪拜    266
澳大利亚        悉尼    141
澳大利亚        悉尼    122
澳大利亚        悉尼    153
澳大利亚        悉尼    128
澳大利亚        墨尔本  294
澳大利亚        墨尔本  230
澳大利亚        墨尔本  159
澳大利亚        墨尔本  188
澳大利亚        堪培拉  249
澳大利亚        堪培拉  378
澳大利亚        堪培拉  255
澳大利亚        堪培拉  240
---格式:select 品牌,count/sum/其它() as num  from table_name order by num limit 10;


select country,city,visitors
from tripdata 
order by visitors desc 
limit 5;

country city    visitors
澳大利亚        堪培拉  378
阿联酋  阿布扎比        337
澳大利亚        墨尔本  294
阿联酋  迪拜    268
阿联酋  迪拜    266

2.2 按照国家、城市提取TOP3

--取top10品牌下各品牌的top10渠道 ,格式:       
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
select a.*
from (
select country,city,visitors, row_number() over (partition by country order by visitors desc ) rank  
from tripdata
order by country,visitors desc
) a
where a.rank<=3;

--结果
a.country       a.city  a.visitors      a.rank
澳大利亚        堪培拉  378     1
澳大利亚        墨尔本  294     2
澳大利亚        堪培拉  255     3
阿联酋  阿布扎比        337     1
阿联酋  迪拜    268     2
阿联酋  迪拜    266     3

2.3 按照国家提取TOP5

--取top10品牌下各品牌的top10渠道中各渠道的top10档期 ,格式:
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
select a.*
from (
select country,city,visitors, row_number() over (partition by city order by visitors desc ) rank  
from tripdata
order by country,city,visitors desc
) a
where a.rank<=3;

--结果
a.country       a.city  a.visitors      a.rank
澳大利亚        堪培拉  378     1
澳大利亚        堪培拉  255     2
澳大利亚        堪培拉  249     3
澳大利亚        墨尔本  294     1
澳大利亚        墨尔本  230     2
澳大利亚        墨尔本  188     3
澳大利亚        悉尼    153     1
澳大利亚        悉尼    141     2
澳大利亚        悉尼    128     3
阿联酋  迪拜    268     1
阿联酋  迪拜    266     2
阿联酋  迪拜    144     3
阿联酋  阿布扎比        337     1
阿联酋  阿布扎比        227     2
阿联酋  阿布扎比        178     3
===============================================================

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可以实现分页查询
 
实例:
 
  1. hive> create table t(name string, sub string, score int) row format delimited fields terminated by '\t';
 
数据在附件的a.txt里
  1. a chinese 98
  2. a english 90
  3. d chinese 88
  4. c english 82
  5. c math 98
  6. b math 89
  7. b chinese 79
  8. z english 90
  9. z math 89
  10. z chinese 80
  11. e math 99
  12. e english 87
  13. d english 90
 
 
1、row_number
  1. hive (test)> select *, row_number() over (partition by sub order by score) as od from t; 
 
2、rank
  1. hive (test)> select *, rank() over (partition by sub order by score) as od from t; 
 
3、dense_ran
  1. hive (test)> select *, dense_rank() over (partition by sub order by score desc) from t;
 
业务实例:
统计每个学科的前三名
  1. select * from (select *, row_number() over (partition by sub order by score desc) as od from t ) t where od<=3;
语文成绩是80分的排名是多少
  1. 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;
分页查询
  1. hive (test)> select * from (select *, row_number() over () as rn from t) t1 where rn between 1 and 5;

阅读更多

扫码向博主提问

我是你大哥大哥

非学,无以致疑;非问,无以广识
  • 擅长领域:
  • Hadoop
  • Spark
  • Java后端
  • HBase
去开通我的Chat快问
个人分类: 大数据~Hive
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭