hive之分析窗函数

       hive提供很多的分析函数，用于完成统计分析，之前一直没学习，最近有用到，特意来学习一下。其中感谢很多博客，提供了很多知识和信息。

1.数据准备

          

drop table if exists table tmp.lxw1234;
CREATE EXTERNAL TABLE tmp.lxw1234 (  
cookieid string,  
createtime string,    
pv INT  
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile
;

desc lxw1234;

cookieid                string                                      
createtime              string                                      
pv                      int  

把txt文件的数据灌进去

 select * from lxw1234;
OK
cookie1 2015-04-16      4
cookie1 2015-04-15      4
cookie1 2015-04-14      2
cookie1 2015-04-13      3
cookie1 2015-04-12      7
cookie1 2015-04-11      5
cookie1 2015-04-10      1

2.简单的例子：先看一个大家常用的

select cookieid,sum(pv) from lxw1234 group by cookieid;     （1）

结果：

cookie1    26

计算sum，这里使用的是group by进行分组。

和上面有类似功能的语句：

select cookieid,sum(pv) over(partition by cookieid) from lxw1234;    （2）

结果：

cookie1 26
cookie1 26
cookie1 26
cookie1 26
cookie1 26
cookie1 26
cookie1 26

这两个语句用相同之处，也有不同之处

（1）对于group by进行分组的字段进行pv的求和，将所有pv相加返回结果

（2）使用partition by进行分组，分组内的所有字段进行求和，并返回结果。

不同呢？对于此表，cookie1会话，在不同时间的pv是不同的

如果我们这样写？

select cookieid,createtime,pv,sum(pv)  from lxw1234 group by cookie,createtime,pv;   (3)

结果：

cookie1 2015-04-10      1       1
cookie1 2015-04-11      5       5
cookie1 2015-04-12      7       7
cookie1 2015-04-13      3       3
cookie1 2015-04-14      2       2
cookie1 2015-04-15      4       4
cookie1 2015-04-16      4       4

分组条件变成了3个，不同cookieid,createtime,pv进行分组求和

select cookieid,createtime,pv,sum(pv) over(partition by cookieid) from lxw1234;           (4)

结果：

cookie1 2015-04-10      1       26
cookie1 2015-04-11      5       26
cookie1 2015-04-12      7       26
cookie1 2015-04-13      3       26
cookie1 2015-04-14      2       26
cookie1 2015-04-15      4       26
cookie1 2015-04-16      4       26

（4）如果想达到（3）的效果，可以这样写

select cookieid,createtime,pv,sum(pv) over(partition by cookieid,createtime,pv)
 from lxw1234;

cookie1 2015-04-10      1       1
cookie1 2015-04-11      5       5
cookie1 2015-04-12      7       7
cookie1 2015-04-13      3       3
cookie1 2015-04-14      2       2
cookie1 2015-04-15      4       4
cookie1 2015-04-16      4       4

但是如果（3）想达到（4）的效果，却没有办法

select cookieid,createtime,pv,sum(pv) 
from lxw1234 
group by  cookieid;  

这样的语句会报错！

所以，over()这样的窗口函数更灵活一点。具体我们再来看一下其他的例子。

3.sum

数据：

cookie1 2015-04-10      1  
cookie1 2015-04-11      5  
cookie1 2015-04-12      7  
cookie1 2015-04-13      3  
cookie1 2015-04-14      2  
cookie1 2015-04-15      4  
cookie1 2015-04-16      4 

SELECT 
cookieid,  
createtime,  
pv,  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1   
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    
FROM lxw1234;  

结果：

cookieid createtime     pv      pv1      pv2      pv3      pv4      pv5      pv6
cookie1 2015-04-10       1       1       1       26      1       6       26
cookie1 2015-04-11      5       6       6       26      6       13      25
cookie1 2015-04-12      7       13      13      26      13      16      20
cookie1 2015-04-13      3       16      16      26      16      18      13
cookie1 2015-04-14      2       18      18      26      17      21      10
cookie1 2015-04-15      4       22      22      26      16      20      8
cookie1 2015-04-16      4       26      26      26      13      13      4

partition by：分组     order by ：默认为升序

pv1:分组内所有行，order by的升序，默认从起点行到当前行

        11号的pv1=起点行10号pv+当前行11号的pv=1+5=6

        12号的pv1=起点行10号pv+11号的pv+当前行12号的pv=1+5+7=13

         同理。。。

pv2:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      与pv1同理，从起点到当前行

pv3:分组内（cookieid）所有的pv累加

       pv3=1+5+7+3+2+4+4=26

      注意与pv1的不同，差别在于少了order by createtime

pv4:ROWSBETWEEN 3 PRECEDING AND CURRENT ROW

       分组内当前行往前3行+当前行

       14号的pv4=11号的pv+12号的pv+13号的pv+当前行14号的pv=5+7+3+2=17

       15号的pv4=12号的pv+13号的pv+14号的pv+当前行15号的pv=7+3+2+4=16

       同理。。。

pv5:ROWSBETWEEN 3 PRECEDING AND 1 FOLLOWING

        分组内当前行往前3行+当前行+当前行往后1行

      14号的pv5=11号的pv+12号的pv+13号的pv+当前行14号的pv+15号的pv=5+7+3+2+4=21

      15号的pv5=12号的pv+13号的pv+14号的pv+当前行15号的pv+16号的pv=7+3+2+4+4=20

       同理。。。。

pv6:ROWSBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

        分组内当前行+往后的所有行

      10号的pv6=10号的pv+11号的pv+12号的pv+13号的pv+14号的pv+15号的pv+16号的pv=26

       11号的pv6=11号的pv+12号的pv+13号的pv+14号的pv+15号的pv+16号的pv=25

      14号的pv6=14号的pv+15号的pv+16号的pv=10

      同理。。。

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句 ：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

AVG,MIN,MAX和sum同理。。。