Hive分组取Top K数据
我的知乎:DarrenChan陈驰
链接:https://www.jianshu.com/p/9802f3a035e1
正文
阿里交叉面试问到了这个题,当时感觉没有答好,主要是对Hive这块还是不熟悉,其实可以采用row_number()函数。
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
链接:https://www.jianshu.com/p/9802f3a035e1
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?