hive的窗口函数

1．相关函数说明

over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；group by不可以用在over中，可以用distribut by 和 sort by 连用

CURRENT ROW：当前行； n PRECEDING：往前 n 行数据； n FOLLOWING：往后 n 行数据；

UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED

FOLLOWING 表示到后面的终点；

所上函数在over中使用

--------------------------------------------------------------------------------

下面函数在over外使用，后面必须跟over

lag(col,n,’默认值’)：往前第 n 行数据；

lead(col,n,’默认值’)：往后第 n 行数据；

col表示查找哪一列；第三个参数为null时，我们也可传默认值，不传也可以。

ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

2．数据准备：name，orderdate，cost ，创建hive表为business

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

3.需求：

（1）查询在 2017 年 4 月份购买过的顾客及总人数

select name,count(*) over() from business where substring(orderdate,0,7) = '2017-04' group by name;

（2）查询顾客的购买明细及购买总额

         如果over函数没有值，则是对所有数据集进行操作

select *,sum(cost) over() from business;

（3）上述的场景,要将 cost 按照日期进行累加

select *,sum(cost) over(order by orderdate) from business;

（4）查询每位顾客详情，将cost按照日期进行累加

         此时我们不可以用group by，而是用distribut by 和 sort by来进行处理

select *,sum(cost) over(distribute by name sort by orderdate) from business;

（5）查询顾客上次的购买时间

select *,lag(orderdate,1) over(distribute by name sort by orderdate) from business;

NULL说明这是第一次消费，为了使数据保持一致，我们可以在lag中添加第三个参数，也就是为NULL的默认值。

 select *,lag(orderdate,1,'1970-01-01') over(distribute by name sort by orderdate) from business;

这样我们的NULL值就被替换成默认值了

（6）查询前 20%时间的订单信息

select
name,
orderdate,
cost
from
(select
 name,
 orderdate,
 cost,
 ntile(5) over(order by orderdate) ntile
from
 business)t1
where
ntile = 1;