Hive 窗口函数
1. 什么是窗口函数
窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。 大家都知道聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括：
Function() Over (Partition By Column1，Column2，Order By Column3)
窗口函数又分为以下三类：
-
聚合型窗口函数
-
分析型窗口函数
-
取值型窗口函数
接下来我们将通过几个实际的例子来介绍下窗口函数。
2. 准备数据
首先我们准备如下数据：
touch /datas/business.txt
vi business.txt
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
#创建hive表
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
# 加载数据
load data local inpath "/datas/business.txt" into table business;
3. 聚合型窗口函数
聚合型即SUM(), MIN(),MAX(),AVG(),COUNT()这些常见的聚合函数。 聚合函数配合窗口函数使用可以使计算更加灵活,比如:
（1）查询在2017年4月份购买过的顾客及总人数
select
name,
count(*) over() as num
from business
where substring(orderdate,1,7)='2017-04'
group by name;
查询结果如下:
-----------------------------------------
name num
mart 2
jack 2
-----------------------------------------
（2）查询顾客的购买明细及每月所有顾客的购买总额
select
name,orderdate,cost,
sum(cost) over(partition by month(orderdate)) as month_cost
from business;
查询结果如下:
-----------------------------------------
name orderdate cost month_cost
jack 2017-01-01 10 205
jack 2017-01-08 55 205
tony 2017-01-07 50 205
jack 2017-01-05 46 205
tony 2017-01-04 29 205
tony 2017-01-02 15 205
jack 2017-02-03 23 23
mart 2017-04-13 94 341
jack 2017-04-06 42 341
mart 2017-04-11 75 341
mart 2017-04-09 68 341
mart 2017-04-08 62 341
neil 2017-05-10 12 12
neil 2017-06-12 80 80
-----------------------------------------
ps: 如果要统计 顾客的购买明细 以及顾客每月的购买总额
select
name,orderdate,cost,
sum(cost) over(partition by name,month(orderdate)) as cm_cost
from business;
查询结果如下:
-----------------------------------------
name orderdate cost cm_cost
jack 2017-01-05 46 111
jack 2017-01-08 55 111
jack 2017-01-01 10 111
jack 2017-02-03 23 23
jack 2017-04-06 42 42
mart 2017-04-13 94 299
mart 2017-04-11 75 299
mart 2017-04-09 68 299
mart 2017-04-08 62 299
neil 2017-05-10 12 12
neil 2017-06-12 80 80
tony 2017-01-04 29 94
tony 2017-01-02 15 94
tony 2017-01-07 50 94
-----------------------------------------
上述窗口均是整个窗口的统计,实际还可以通过row between 定义窗口的范围,比如指定窗口的起点,终点;
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
（3）上述的场景,要将cost按照日期进行累加
select
name,orderdate,cost,
sum(cost) over(partition by name order by orderdate) as s1,
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row) as s2, -- 从起点到当前行
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as s3, -- 从前一行到当前行
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) as s4, -- 从前一行到后一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING) as s5 -- 从当前行到后面所有行
from business;
查询结果如下:
-----------------------------------------
name orderdate cost s1 s2 s3 s4 s5
jack 2017-01-01 10 10 10 10 56 176
jack 2017-01-05 46 56 56 56 111 166
jack 2017-01-08 55 111 111 101 124 120
jack 2017-02-03 23 134 134 78 120 65
jack 2017-04-06 42 176 176 65 65 42
mart 2017-04-08 62 62 62 62 130 299
mart 2017-04-09 68 130 130 130 205 237
mart 2017-04-11 75 205 205 143 237 169
mart 2017-04-13 94 299 299 169 169 94
neil 2017-05-10 12 12 12 12 92 92
neil 2017-06-12 80 92 92 92 92 80
tony 2017-01-02 15 15 15 15 44 94
tony 2017-01-04 29 44 44 44 94 79
tony 2017-01-07 50 94 94 79 79 50
-----------------------------------------
ps: s1默认是整个窗口,和s2指定窗口范围是原始起点和最终终点,是等价的,故结论完全一致;
count(distinct xxx)在窗口函数里是不允许使用的，不过我们也可以用size(collect_set() over(partition by order by))来替代实现我们的需求;
hive (test)> select *,count(distinct name) over(order by orderdate) from business;
FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key name
select
name,orderdate,cost,
size(collect_set(name) over()) as num
from business order by orderdate;
查询结果如下:
-----------------------------------------
name orderdate cost num
jack 2017-01-01 10 4
tony 2017-01-02 15 4
tony 2017-01-04 29 4
jack 2017-01-05 46 4
tony 2017-01-07 50 4
jack 2017-01-08 55 4
jack 2017-02-03 23 4
jack 2017-04-06 42 4
mart 2017-04-08 62 4
mart 2017-04-09 68 4
mart 2017-04-11 75 4
mart 2017-04-13 94 4
neil 2017-05-10 12 4
neil 2017-06-12 80 4
-----------------------------------------
4. 分析型窗口函数
分析型即 RANK(), ROW_NUMBER(), DENSE_RANK()等常见的排序用的窗口函数，不过他们也是有区别的。
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
除了这三个排序用的函数，还有:
CUME_DIST函数 ：小于等于当前值的行在所有行中的占比
PERCENT_RANK() ：小于当前值的行在所有行中的占比
NTILE() ：如果把数据按行数分为n份，那么该行所属的份数是第几份
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。每份的数量分配规则是: 先整除得到平均数,把余数再轮询从前往后分配; 比如共有14条数据,准备分成5组,那么 14/5 = 2 余 4 余数4会被 加入到前4组 所以结果是 3 3 3 3 2
select
name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;
查询结果如下:
-----------------------------------------
name subject score rp drp rmp
孙悟空 数学 95 1 1 1
宋宋 数学 86 2 2 2
婷婷 数学 85 3 3 3
大海 数学 56 4 4 4
宋宋 英语 84 1 1 1
大海 英语 84 1 1 2
婷婷 英语 78 3 2 3
孙悟空 英语 68 4 3 4
大海 语文 94 1 1 1
孙悟空 语文 87 2 2 2
婷婷 语文 65 3 3 3
宋宋 语文 64 4 4 4
-----------------------------------------
select *,ntile(5) over(order by orderdate) as sorted from business;
查询结果如下:
-----------------------------------------
business.name business.orderdate business.cost sorted
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
jack 2017-01-05 46 2
tony 2017-01-07 50 2
jack 2017-01-08 55 2
jack 2017-02-03 23 3
jack 2017-04-06 42 3
mart 2017-04-08 62 3
mart 2017-04-09 68 4
mart 2017-04-11 75 4
mart 2017-04-13 94 4
neil 2017-05-10 12 5
neil 2017-06-12 80 5
-----------------------------------------
5. 取值型窗口函数
这几个函数可以通过字面意思记得，LAG是迟滞的意思，也就是对某一列进行往后错行；LEAD是LAG的反义词，也就是对某一列进行提前几行；FIRST_VALUE是对该列到目前为止的首个值，而LAST_VALUE是到目前行为止的最后一个值。
LAG()和LEAD() 可以带3个参数，第一个是返回的值，第二个是前置或者后置的行数，第三个是默认值。
LAG(col,n,defaultValue)：往前第n行数据
LEAD(col,n,defaultValue)：往后第n行数据
select
name,orderdate,cost,
lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) as up_time, --顾客上一次购买时间
lead(orderdate,1,'9999-01-01') over(partition by name order by orderdate) as down_time --顾客下一次购买时间
from business;
查询结果如下:
---------------------------------------------
name orderdate cost up_time down_time
jack 2017-01-01 10 1970-01-01 2017-01-05
jack 2017-01-05 46 2017-01-01 2017-01-08
jack 2017-01-08 55 2017-01-05 2017-02-03
jack 2017-02-03 23 2017-01-08 2017-04-06
jack 2017-04-06 42 2017-02-03 9999-01-01
mart 2017-04-08 62 1970-01-01 2017-04-09
mart 2017-04-09 68 2017-04-08 2017-04-11
mart 2017-04-11 75 2017-04-09 2017-04-13
mart 2017-04-13 94 2017-04-11 9999-01-01
neil 2017-05-10 12 1970-01-01 2017-06-12
neil 2017-06-12 80 2017-05-10 9999-01-01
tony 2017-01-02 15 1970-01-01 2017-01-04
tony 2017-01-04 29 2017-01-02 2017-01-07
tony 2017-01-07 50 2017-01-04 9999-01-01
----------------------------------------------
6. 总结
通过窗口函数,可以在当前行得到多行数据聚合分析的结果数据,便于后续进一步分析处理。
<人追求理想之时,便是坠入孤独之际.> By 史泰龙
