Hive的窗口函数

如果在一条Hive SQL涉及到多个窗口函数的引用方法如下：
select p_mfgr, p_name, p_size,

sum(p_size) over w1 as s1,

sum(p_size) over w2 as s2,

sum(p_size) over w3 as s3

from part

window w1 as (distribute by p_mfgr sort by p_name range between 2 preceding and 2 following), 
      w2 as w3,

       w3 as (distribute by p_mfgr sort by p_name range between unbounded preceding and current row);

over语句还可以独立出来，用window重写，但需要注意的是，如下sort by使用了多个字段，如果用range指定窗口的话会出错，需要用rows来指定窗口，因为range是对列的比较，不支持多列比较。

[sql]  view plain copy

1. select shop_id, stat_date, ordamt, sum(ordamt) over win as t  
2. from rt_data   
3. where dt = '2015-01-11' and shop_id = 10026  
4. window win as (distribute by shop_id sort by shop_id, ordamt desc rows between unbounded 

在Hive里面，可以把这一部分独立抽出来做声明。如：
select  p_mfgr,p_name, p_size,

sum(p_retailprice) over w1 as s,

min(p_retailprice) over w1 as mi,

max(p_retailprice) over w1 as ma,

avg(p_retailprice) over w1 as ag

from part

window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);

其中的window w1 则是抽出声明的窗口部分。

假如一个SQL如下：
select t.*, sum(pay) over(distribute by area sort by updated_date ) as rno
from table_name t;
是根据地区来汇总，那么我要取所有地区的金额呢？

select t.*, 
       sum(pay) over(sort by updated_date 
       rows between unbounded preceding and unbounded following) as rno
from table_name t;

rows between ... preceding and ... following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录

如果想计算当月累计的费用呢？
比如说，3月份的值=1月+2月+3月的费用。
如下：
select t.*, 
       sum(pay) over(sort by month
       rows between unbounded preceding and unbounded following) as current_sum_sales
from table_name t;
 
  
除了rows关键字，还有range关键字指定范围
select t.*, 
       sum(pay) over(sort by month
       range between 1 preceding and 1 following) as closeset_3_month_sales
from table_name t;
这里计算出来的值就是，当月+上一个月+下一个月的值。