hive求TopN语句

ROW_NUMBER，RANK()，DENSE_RANK()

先了解这三个之间的区别：

Rank()：1,2,2,4,5（一般用这个较多，不会影响总排名）

Dense_rank()：1,2,2,3,4,5（会影响最终排名）

Row_number()：1,2,3,4,5,6（按照行数显示）

 

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

取TOPN数据

按照国家提取TOP3

 样本数据：

国家 城市 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

导入数据

--执行下述语句
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 country,city,visitors
from tripdata 
order by visitors desc 
limit 5;

country city    visitors

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

按照国家、提取城市TOP3

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

按照城市提取TOP3

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