我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，这个时候就需要使用窗口分析函数。
本文章主要介绍hive的开窗函数和自定义函数。
Quick Guide
分析函数用于计算基于组的某种聚合值，它和单纯聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
1.基础结构
1
分析函数（如:sum(),max(),row_number()...） + 窗口子句（over函数）
2.over函数写法
1 | # 先根据cookieid字段分区，相同的cookieid分为一区，每个分区内根据createtime字段排序（默认升序） |
基础函数
基础函数：SUM、AVG、MIN、MAX 用于实现分组内所有和连续累积的统计。
- 1.测试数据
1 | CREATE EXTERNAL TABLE lxw1234 ( |
- 2.设置开窗的窗口范围
1 | SELECT cookieid, |
结果解析：
1 | pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号 |
ROWS BETWEEN 叫做window子句，如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行
：
- PRECEDING：往前
- FOLLOWING：往后
- CURRENT ROW：当前行
- UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED – FOLLOWING：表示到最后面的终点
– 3.AVG，MIN，MAX，和SUM用法一样
1 | -- AVG |
1 | -- MIN |
1 | -- MAX |
序列函数
序列函数:NTILE、ROW_NUMBER、RANK、DENSE_RANK、CUME_DIST和PERCENT_RANK，不支持WINDOW子句.
- 1.数据准备
1 | cookie1,2015-04-10,1 |
- 2.NTILE: NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
- 如果切片不均匀，默认增加第一个切片的分布
- NTILE不支持ROWS BETWEEN
1 | SELECT |
- 3.ROW_NUMBER：从1开始，按照顺序，生成分组内记录的序列
1 | -- 按照pv降序排列，生成分组内每天的pv名次 |
- 4.RANK 和 DENSE_RANK
- RANK() :生成数据项在分组中的排名，排名相等会在名次中留下空位
- DENSE_RANK() :生成数据项在分组中的排名，排名相等会在名次中不会留下空位
1 | SELECT |
5.数据准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
CREATE EXTERNAL TABLE lxw1234 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile location '/tmp/lxw11/';
hive> select * from lxw1234;
OK
d1 user1 1000
d1 user2 2000
d1 user3 3000
d2 user4 4000
d2 user5 50006.CUME_DIST: 小于等于当前值的行数/分组内总行数
1 | SELECT |
- 7.PERCENT_RANK:分组内当前行的RANK值-1/分组内总行数-1
1 | SELECT |
位置函数
位置函数:LAG 、LEAD、FIRST_VALUE和LAST_VALUE,不支持WINDOW子句
- 1.数据准备
1 | cookie1,2015-04-10 10:00:02,url2 |
- 2.LAG:LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
- 第一个参数为列名
- 第二个参数为往上第n行（可选，默认为1）
- 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
1 | SELECT cookieid, |
- 3.LEAD:LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
- 第一个参数为列名
- 第二个参数为往下第n行（可选，默认为1）
- 第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
1 | SELECT cookieid, |
- 4.FIRST_VALUE:取分组内排序后，截止到当前行，第一个值
1 | SELECT cookieid, |
- 5.LAST_VALUE:取分组内排序后，截止到当前行，最后一个值
1 | SELECT cookieid, |
聚合函数
聚合函数：GROUPING SETS、CUBE和ROLLUP
- 1.数据准备
1 | 2015-03,2015-03-10,cookie1 |
- 2.GROUPING SETS:在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
1 | SELECT |
注：GROUPING__ID，表示结果属于哪一个分组集合。
- 3.CUBE:根据GROUP BY的维度的所有组合进行聚合
1 | SELECT |
- 4.ROLLUP:是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。
1 | -- 以month维度进行层级聚合： |
自定义函数
- 1.创建文件set_message.sql
- 2.在文件中定位存储过程
1 | -- 创建一个存储过程set_message，它接受一个STRING类型的IN参数，输出一个STRING类型的OUT参数。 |
- 3.自定义函数
1 | INCLUDE /home/hadoop/set_message.sql |