hive常用的窗口函数

0. 窗口函数的定义

首先, 在列举窗口函数之前, 我们需要知道, 什么是窗口函数, 这里只是谈一谈我的理解, 通俗的理解方式。
先附上规范的定义:

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

再来说说我的理解:

窗口函数
格式: xxx over (partition by xxx order by xxx)
解释: 在数据库查询中的select后使用, 在这后面再加一列
举例: 假如我们有一个学生成绩表, 字段有 sid, cid, sc, 此时我们可以使用group by分组得到这个学生的平均成绩或者总分是多少, 但是如果我还需要这个学生单独的分数呢

我们构造一个表和人为添加一些数据便于讲解(这里使用的是hive数据库)

-- 创建数据库
create database testForOver charset 'utf8';
use testForOver;

-- 建表
create table testForOver.sc(
	sid int commit '学生id', 
	cid int commit '课程id',
	sc int commit '学生该课程得分'
) commit '学生成绩表'
 row format delimited fields terminated by '\t'
 stored as orc ;
 
 -- 添加表数据
 insert into table testForOver.sc
 values(1, 1, 90),
 (1, 2, 80),
 (1, 3, 95),
 (2, 1, 100),
 (2, 3, 80);

至此, 我们能够得到, 表的情况为:

sid	cid	sc
1	1	90
1	2	80
1	3	95
2	1	100
2	3	80

如果我们使用group by算学生的平均分得到的结果就是

sid	avg_sc
1	88.33
2	90

可是我并不想这样, 我需要在后面加一列用来存储avg_sc, 这样我可以在一张表中看到这个学生的每门课程, 也可以看到这个学生的平均分, 效果为:

sid	cid	sc	avg_sc
1	1	90	88.33
1	2	80	88.33
1	3	95	88.33
2	1	100	90
2	3	80	90

如果我们不适用窗口函数, 我们也可以实现:

with avg_sc as ( 
select
	sid,
	avg(sc) as avg_sc
from sc 
group by sid 
)
select
	t1.*,
	t2.avg_sc
from sc t1
left join avg_sc t2
on t1.sid = t2.sid;

但是, 当我们需要更复杂的运算的时候, 这并不简单, 而且这并不是窗口函数的全部用法, 常用的我会在后面讲解, 如果使用窗口函数:

select
	*,
	avg(sc) over (partition by sid) as avg_sc
from sc;

1. 聚合函数配合窗口函数使用

如上述窗口函数中的avg, 还有sum, count等聚合函数使用上述格式进行使用, 这里我不多赘述, 需要注意的是, 如果使用了order by, 默认的是从头到当前行进行聚合, 就比如说 1, 2, 3, 4如果使用order by聚合sum, 那么新的一列会是1, 3, 6, 10, 而不是10, 10, 10, 10, 详细的我在窗口函数的范围中讲解

2. 使用窗口函数排序

在窗口函数中, 用来排序的方法常用的有: row_number, rank, dense_rank
还有sc表, 但是字段稍微变简单一点:

sid	cid	sc
1	1	90
2	1	90
3	1	80

我们需要对学生成绩进行排名, 排序学生的成绩, 那么我们可以使用这三个函数进行排序, 它们的作用为, 加一列为排序列, 拿row_number举例, 我们的查询语句为

select
*,
row_number() over(partition by cid order by sc desc) as row_number, 
rank() over(partition by cid order by sc desc) as rank, 
dense_rank() over(partition by cid order by sc desc) as dense_rank
from sc;

可是我们发现, 1和2号学生考试成绩是一样的, 这就会体现出这三个函数的区别:

sid	cid	sc	row_number	rank	dense_rank
1	1	90	1	1	1
2	1	90	2	1	1
3	1	80	3	2	3

由此我们可以看出, row_number在遇到相同的成绩的时候, 会根据表中原本的顺序排序, 而rank和dense_rank在遇到相同的成绩的时候, 会将这两行都定义为同一排名, 但是rank在后续排名还接着排, 但是dense_rank会跳过2(如果有多个成绩相同, 则有多少个相同的就跳过多少条, 比如说, 有19条数据并列第一, 那么第11名使用rank就是2, 而用dense_rank就是11)

3. lag和lead函数

当我们需要错位查看的时候, 我们可以使用这两个函数实现, 比方说, 我们有一个表为 身份信息表

create table testForOver.identificationInformation(
	id int commit '身份证id',
	gender string commit '性别',
	address string commit '所在地',
	start_time string commit '起始有效期',
	end_time string commit '结束有效期'
) commit '身份信息表'
row format delimited fields terminated by '\t'
stored as orc;

这里就不添加信息了, 偷个懒…
但是如果我想要实现的是, 查询这个表, 并在后面加一列为上次所在地, 那么应该怎么做呢?

select
	*,
	lag(address, 1, '无') over (partition by id order by start_time) as last_address
from testForOver.identificationInformation;

这样就可以取向上一行的address值, 而lag有三个参数, 分别为:
取哪一行
向上取多少行, 默认为1
当没有上n行时, 使用什么进行填充, 这里使用的是 字符串 无 填充, 默认为NULL
lead与lag类似, 只不过lag是向上取, lead是向下取, 这里不做过多解释了。