场景:hsql(hive sql)处理数据的时候会有取分组后topN的需求，这个时候们可能会用到同一检索条件下的组内排序，涉及到的函数有row_number(),rank(),dense_rank()
- row_number:不管排名是否有相同的，都按照顺序1，2，3…..n
- rank:排名相同的名次一样，同一排名有几个，后面排名就会跳过几次
- dense_rank:排名相同的名次一样，且后面名次不跳跃
数据准备
| # province, city, id 浙江,杭州,300 浙江,宁波,150 浙江,温州,200 浙江,嘉兴,100 江苏,南京,270 江苏,苏州,299 江苏,某市,200 江苏,某某市,100 |
普通排序
hive > select * from hive_table order by id desc;
浙江 杭州 300 浙江 宁波 150 浙江 温州 200 浙江 嘉兴 100 江苏 南京 270 江苏 苏州 299 江苏 某市 200 江苏 某某市 100
综合查询排序
hive > select province,city, rank() over (order by id desc) rank, dense_rank() over (order by id desc) dense_rank, row_number() over(order by id desc) row_number from hive_table group by province,city,people;
浙江 杭州 300 1 1 1 江苏 苏州 299 2 2 2 江苏 南京 270 3 3 3 江苏 某市 200 4 4 4 浙江 温州 200 4 4 5 浙江 宁波 150 6 5 6 江苏 某某市 100 7 6 7 浙江 嘉兴 100 7 6 8
主要说明讲解如下:
- row_number顺序下来, 顺序下来的序号不会有重复的
- rank:在遇到数据相同项时,会留下空位,(红框内第一列,4,4,6) ，相同的会占一个位置，接下来的数字变成 +2
- dense_rank:在遇到数据相同项时,不会留下空位,(红框内第一列,4,4,5) 相同的是同一个序号，但是接下来的还是 +1
分区统计查询排序
hive > select province,city, rank() over (partition by province order by id desc) rank, dense_rank() over (partition by province order by id desc) dense_rank, row_number() over(partition by province order by id desc) row_number from hive_table group by province,city,people;
江苏 苏州 299 1 1 1 江苏 南京 270 2 2 2 江苏 某市 200 3 3 3 江苏 某某市 100 4 4 4 浙江 杭州 300 1 1 1 浙江 温州 200 2 2 2 浙江 宁波 150 3 3 3 浙江 嘉兴 100 4 4 4