hive udf 分组取top1_Hive分组取Top K数据

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