窗口函数
一直对Hive窗口函数半知半解,最近一直在学习相关知识,记录一下自己的理解。
首先是窗口函数出现的概念,指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化; 概念比较抽象,字都认识,放一块就不知道啥意思了。。。再来说说窗口函数应用的场景,因为之前我们接触过聚合函数,比如sum(),avg(),max()等,而且一般来讲聚合后的行数是小于聚合前的行数,而有时候我们即需要聚合前的结果也需要聚合后的结果,这时候就引入了窗口函数。
注意,窗口函数执行顺序是在SQL语句最后,仅位于Order By之前。
执行顺序:from–join on–where–group by–select/having–order by–limit
数据准备:name,orderdate,cost
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
创建表并导入数据
create table business( name string, orderdate string, cost int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/data/business.txt" into table business;
数据导入成功。
需求一:查询在2017年1月购买过得顾客以及总人数
首先从需求看,最终结果形式应该是 姓名 总人数,从图中可以知道显示出来应该是jack 6 tony 6 两行。
我一开始想的是使用下面这个语句:
select name, count(*)
from business
where substring(orderdate,1,7) = '2017-01'
group by(name);
但是这个语句结果求得的是一月份购物的顾客以及他的总次数。而我们求的是总人数,所以不符合。这时候就需要引入开窗函数。
select name, count(*) over()
from business
where substring(orderdate,1,7) = '2017-01'
group by(name);
结果和我们预想的一样,只是加了一个OVER(),括号内为空意思就是对全局进行开窗。
需求二:查询顾客的购买明细及购买总额
select name, orderdate, cost, sum(cost) over() from business;
CURRENT ROW:当前行;
n PRECEDING:往前 n 行数据;
n FOLLOWING:往后 n 行数据;
UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点,UNBOUNDED
FOLLOWING 表示到后面的终点; LAG(col,n):往前第 n 行数据; LEAD(col,n):往后第 n 行数据;
NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从 1 开始, 对于每一行,NTILE 返回此行所属的组的编号 注意:n 必须为 int 类型。
需求三:查看顾客上次的购买时间
select name, orderdate, lag(orderdate,1) over(distribute by(name) sort by(orderdate)) from business;
每个顾客第一行显示NULL,是因为在每个顾客在那一时期是第一次购买,没有上次购买记录,所以显示为NULL,不过我们可以指定值。比如这里我指定为NULL的地方用9999代替。
select name, orderdate, lag(orderdate,1,9999) over(distribute by(name) sort by(orderdate)) from business;