hive的分组和组内排序

背景

hive的分组和组内排序—语法

语法：

row_number() over (partition by 字段a order by 计算项b desc ) rank
1

rank是排序的别名
partition by：用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。；
order by ：排序，默认是升序，加desc降序；
这里按字段a分区，对计算项b进行降序排序

应用

测试数据

下面有一份测试数据id,dept,salary,然后我们就使用这份测试数据学习我们的窗口排序函数

1,销售,10000
2,销售,14000
3,销售,10000
4,后端,20000
5,后端,25000
6,后端,32000
7,AI,40000
8,AI,35000
9,AI,60000
10,数仓,20000
11,数仓,30000
12,数仓,32000
13,数仓,42000
create table ods_num_window(
    id string,
    dept string,
    salary int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
LOAD DATA LOCAL INPATH '/Users/liuwenqiang/workspace/hive/number.txt' 
OVERWRITE INTO TABLE ods_num_window;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

每个部门的员工按照工资降序排序

每个部门都有自己的第一名，排序是发生在每个部门内部的

select
    *,row_number() over(partition by dept order by salary desc) as rank
from
    ods_num_window
;
1
2
3
4
5

全部的员工按照工资降序排序

当我们没有定义partition by 子句的时候，我们的所有数据都放在一个窗口里面，这个时候我们的排序就是全局排序，其实如果你仔细看过我们的Hive语法之窗口函数初识这一节的话，你就知道partition by 其实是定义了子窗口，如果没有子窗口的话，那就就是一个窗口，如果所有的数据都放在一个窗口的话那就是全局排序

select
    *,row_number() over(order by salary desc) as rank
from
    ods_num_window
;
1
2
3
4
5

取每个部门的工资前两名

这个是row_number() 函数非常常见的使用场景top-N,其实如果你仔细看过我们的Hive语法之窗口函数初识这一节的话，你就知道partition by 其实是定义了子窗口，那其实这里的top-N,本质上是子窗口的的top-N

select
    *
from(
   select
       *,row_number() over(partition by dept order by salary desc) as rank
   from
       ods_num_window
) tmp
where
    rank <=2
;
1
2
3
4
5
6
7
8
9
10
11

其实这个的实现方式就是我们对数据在子窗口内进行排序，然后选择出我们我们需要的数据，也就是这里的rn <=2

rank() over()

rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

select
    *,rank() over(partition by dept order by salary) as rn
from
    ods_num_window
;
1
2
3
4
5

dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．

select
    *,dense_rank() over(partition by dept order by salary) as rn
from
    ods_num_window
;
1
2
3
4
5

总结

rank() 排序相同时会重复，总数不会变(会有间隙跳跃，数据不连续)
dense_rank() 排序相同时会重复，总数会减少(不会有间隙，数据连续的)
row_number() 会根据顺序计算，不会重复不会减少
Row_number 函数常用的三种场景Top-N，计算连续，分组抽样

参考

1、Hive窗口函数row number的用法， 你肯定都会吧！
2、Hive row_number() 等用法
3、hive的分组和组内排序