Hive 窗口函数

简介

本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析（在线分析处理）。

概念

我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。

数据准备

我们准备一张order表,字段分别为name,orderdate,cost.数据内容如下:

jack,2015-01-01,10
tony,2015-01-02,15
jack,2015-02-03,23
tony,2015-01-04,29
jack,2015-01-05,46
jack,2015-04-06,42
tony,2015-01-07,50
jack,2015-01-08,55
mart,2015-04-08,62
mart,2015-04-09,68
neil,2015-05-10,12
mart,2015-04-11,75
neil,2015-06-12,80
mart,2015-04-13,94

实例

聚合函数+over

假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

select name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'

得到的结果如下:

name    count_window_0
mart    5
mart    5
mart    5
mart    5
jack    5

可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式
第一种：distinct

select distinct name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'

第二种:group by

select name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'
group by name

执行后的结果如下:

name count_window_0 
mart 2 
jack 2

partition by子句

Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句，非常类似于Group By，都是将数据按照边界值分组，而Over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算.

实例

我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from t_window

执行结果如下:

name    orderdate   cost    sum_window_0
jack    2015-01-01  10  205
jack    2015-01-08  55  205
tony    2015-01-07  50  205
jack    2015-01-05  46  205
tony    2015-01-04  29  205
tony    2015-01-02  15  205
jack    2015-02-03  23  23
mart    2015-04-13  94  341
jack    2015-04-06  42  341
mart    2015-04-11  75  341
mart    2015-04-09  68  341
mart    2015-04-08  62  341
neil    2015-05-10  12  12
neil    2015-06-12  80  80

可以看出数据已经按照月进行汇总了.

order by子句

上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

order by子句会让输入的数据强制排序（文章前面提到过，窗口函数是SQL语句最后执行的函数，因此可以把SQL结果集想象成输入数据）。Order By子句对于诸如Row_Number()，Lead()，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。因此如果有了Order By子句，则Count()，Min()等计算出来的结果就没有任何意义。

我们在上面的代码中加入order by

select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
from t_window

得到的结果如下：(order by默认情况下聚合从起始行到当前行的数据)

name    orderdate   cost    sum_window_0
jack    20