Hive窗口函数理解

窗口函数

       一直对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;