简介
本文主要介绍hive中的窗口函数，hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于OLAP分析（在线分析处理）。
概念
窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起，我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。
窗口函数可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括：
1 | Function() Over (Partition By Column1，Column2，Order By Column3) |
窗口函数又分为以下三类：
- 聚合型窗口函数
- 分析型窗口函数
- 取值型窗口函数
over()子句
我们可以形象的把over()子句理解成开窗子句，即打开一个窗口，窗口内包含多条记录，over()会给每一行开一个窗口。如下图，总共有5条记录，每一行代表一条记录，over()在每一条记录的基础上打开一个窗口，给r1记录打开w1窗口，窗口内只包含自己，给r2打开w2窗口，窗口内包含r1、r2，给r3打开w3窗口，窗口内包含r1、r2、r3，以此类推…..
over()子句的开窗范围
先看一张图：
current row代表查询的当前行，1 preceding代表前一行，1 following代表后一行，unbounded preceding代表第一行，unbounded following代表最后一行。（注意这里的第一行和最后一行并不是严格的第一行和最后一行，根据具体情况而定）
由上我们不难发现，在使用over()子句进行查询的时候， 不仅可以查询到每条记录的信息，还可以查询到这条记录对应窗口内的所有记录的聚合信息，所以我们通常结合聚合函数和over()子句一起使用。
那么over()是如何进行开窗的呢？即每条记录对应的窗口内应该包含哪些记录呢？这些都是在over()子句的括号内进行定义。
order by
如果over()子句中接order by，例如：over(order by date)，则默认的开窗范围为根据date排序后的rows between unbounded preceding and current row，即第一行到当前行，意思是over(order by date)和over(order by date rows rows between unbounded preceding and current row)的效果是一样的。
partition by
如果over子句中接partition by（和group by类似，都是根据列值对行进行分组），例如over(partition by month(date))，则每一行的默认的开窗范围为当前行所在分组的所有记录。注意partition by子句不能单独和window clause子句一起使用，必须结合order by子句，下面会讨论。
partition by + order by
先分组，再排序，即组内排序。同样的，如果 order by后不接window clause，则每一行的默认的开窗范围为：当前行所在分组的第一行到当前行，即over(partition by (month(date)) order by orderdate)和over(partition by (month(date)) order by orderdate rows between undounded preceding and current row)是一样的。
窗口大小
over()子句的开窗范围可以通过window 子句（window clause）在over()的括号中定义，window clause的规范如下：
1 | (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING) |
例如 select ,sum(column_name) over( rows between unbounded preceding and unbounded following) from table_name 表示查询每一行的所有列值，同时给每一行打开一个从第一行到最后一行的窗口，并统计窗口内所有记录的column_name列值的和。最后给每一行输出该行的所有属性以及该行对应窗口内记录的聚合值。
如果over()子句中什么都不写的话，默认开窗范围是：rows between unbounded preceding and unbounded following
*在深入研究Over子句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。
实践准备
创建Hive表
1 | CREATE TABLE user_match_temp ( |
数据量较少，就直接手动插入了
1 | INSERT INTO TABLE user_match_temp values |
数据包含4列，分别为 user_name，opponent，result，create_time。 我们将基于这些数据来介绍下窗口函数的一些使用场景。
原始数据
**
user_name opponent result create_time
A B 1 2019-07-18 23:19:00
B A 0 2019-07-18 23:19:00
A C 0 2019-07-18 23:20:00
C A 1 2019-07-18 23:20:00
A D 1 2019-07-19 22:19:00
D A 0 2019-07-19 22:19:00
C B 0 2019-07-19 23:19:00
B C 1 2019-07-19 23:19:00
聚合型窗口函数
聚合型即SUM(), MIN(),MAX(),AVG(),COUNT()这些常见的聚合函数。 聚合函数配合窗口函数使用可以使计算更加灵活，例如以下场景：
至今累计分数
1
2
3
4
5
6
7
8
9
10
11select *, sum(result) over(partition by user_name order by create_time) result_sum from user_match_temp;
user_name opponent result create_time result_sum
A B 1 2019-07-18 23:19:00 1
A C 0 2019-07-18 23:20:00 1
A D 1 2019-07-19 22:19:00 2
B A 0 2019-07-18 23:19:00 0
B C 1 2019-07-19 23:19:00 1
C A 1 2019-07-18 23:20:00 1
C B 0 2019-07-19 23:19:00 1
D A 0 2019-07-19 22:19:00 0之前3场平均胜场
1
2
3
4
5
6
7
8
9
10
11hive (test)> SELECT *,avg(result) over (partition by user_name order by create_time rows between 3 preceding and current row) as recently_wins from user_match_temp;
user_match_temp.user_name user_match_temp.opponent user_match_temp.result user_match_temp.create_time recently_wins
A B 1 2019-07-18 23:19:00 1.0
A C 0 2019-07-18 23:20:00 0.5
A D 1 2019-07-19 22:19:00 0.6666666666666666
B A 0 2019-07-18 23:19:00 0.0
B C 1 2019-07-19 23:19:00 0.5
C A 1 2019-07-18 23:20:00 1.0
C B 0 2019-07-19 23:19:00 0.5
D A 0 2019-07-19 22:19:00 0.0
我们通过rows between 即可定义窗口的范围，这里我们定义了窗口的范围为之前3行到该行。
- 累计遇到的对手数量
需要注意的是count(distinct xxx)在窗口函数里是不允许使用的，不过我们也可以用size(collect_set() over(partition by order by))来替代实现我们的需求
1 | hive (test)> SELECT *,size(collect_set(opponent) over (partition by user_name order by create_time)) as recently_wins from user_match_temp; |
collect_set()也是一个聚合函数，作用是将多行聚合进一行的某个set内，再用size()统计集合内的元素个数，即可实现我们的需求。
分析型窗口函数
分析型即RANk(),ROW_NUMBER(),DENSE_RANK()等常见的排序用的窗口函数，不过他们也是有区别的。
排名函数不支持window子句，即不支持自定义窗口大小**
1 | hive (test)> SELECT *,rank() over (order by create_time) as user_rank,row_number() over (order by create_time) as user_row_number,dense_rank() over (order by create_time) as user_dense_rank FROM user_match_temp; |
如上所示： row_number函数：生成连续的序号（相同元素序号相同）；
rank函数：如两元素排序相同则序号相同，并且会跳过下一个序号；
dense_rank函数：如两元素排序相同则序号相同，不会跳过下一个序号；
除了这三个排序用的函数，还有 _CUME_DIST函数 ：小于等于当前值的行在所有行中的占比 _PERCENT_RANK() ：小于当前值的行在所有行中的占比 * NTILE() ：如果把数据按行数分为n份，那么该行所属的份数是第几份 这三种窗口函数 sql如下：
1 | SELECT *, CUME_DIST() over (order by create_time) as user_CUME_DIST, PERCENT_RANK() over (order by create_time) as user_PERCENT_RANK, NTILE(3) over (order by create_time) as user_NTILE FROM user_match_temp; |
## 取值型窗口函数 这几个函数可以通过字面意思记得，LAG是迟滞的意思，也就是对某一列进行往后错行；LEAD是LAG的反义词，也就是对某一列进行提前几行；FIRST_VALUE是对该列到目前为止的首个值，而LAST_VALUE是到目前行为止的最后一个值。
LAG()和LEAD() 可以带3个参数，第一个是返回的值，第二个是前置或者后置的行数，第三个是默认值。
下一个对手，上一个对手，最近3局的第一个对手及最后一个对手，如下：
1 | SELECT *, |
参考文章：[https://blog.csdn.net/czr11616/article/details/101645693](https://blog.csdn.net/czr11616/article/details/101645693)
[https://zhuanlan.zhihu.com/p/77705681](https://zhuanlan.zhihu.com/p/77705681)
[https://blog.csdn.net/qq_37296285/article/details/90940591](https://blog.csdn.net/qq_37296285/article/details/90940591)
