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;OKcookie1 2015-04-16      4cookie1 2015-04-15      4cookie1 2015-04-14      2cookie1 2015-04-13      3cookie1 2015-04-12      7cookie1 2015-04-11      5cookie1 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       1cookie1 2015-04-11      5       5cookie1 2015-04-12      7       7cookie1 2015-04-13      3       3cookie1 2015-04-14      2       2cookie1 2015-04-15      4       4cookie1 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       26cookie1 2015-04-11      5       26cookie1 2015-04-12      7       26cookie1 2015-04-13      3       26cookie1 2015-04-14      2       26cookie1 2015-04-15      4       26cookie1 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       1cookie1 2015-04-11      5       5cookie1 2015-04-12      7       7cookie1 2015-04-13      3       3cookie1 2015-04-14      2       2cookie1 2015-04-15      4       4cookie1 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      pv6cookie1 2015-04-10       1       1       1       26      1       6       26cookie1 2015-04-11      5       6       6       26      6       13      25cookie1 2015-04-12      7       13      13      26      13      16      20cookie1 2015-04-13      3       16      16      26      16      18      13cookie1 2015-04-14      2       18      18      26      17      21      10cookie1 2015-04-15      4       22      22      26      16      20      8cookie1 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 PRECEDINGAND 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:ROWSBETWEEN3 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 ROWAND 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同理。。。