hive的开窗函数举例（SUM、AVG、MIN、MAX）

数据准备：

	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

建表语句：

	create database if not exists myhive;
	use myhive;
	drop table if exists cookie;
	create table cookie(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
	load data local inpath "/home/hadoop/cookie.txt" into table cookie;
	select * from cookie;
	desc cookie;

1.SQL语句： SUM

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 creat