hive窗口函数/分析函数
在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。
窗口函数最重要的关键字是 partition by 和 order by。
具体语法如下：over (partition by xxx order by xxx)
sum,avg,min,max 函数
准备数据
建表语句: create table bigdata_t1( cookieid string, createtime string, --day pv int ) row format delimited fields terminated by ','; 加载数据： load data local inpath '/root/hivedata/bigdata_t1.dat' into table bigdata_t1; cookie1,2018-04-10,1 cookie1,2018-04-11,5 cookie1,2018-04-12,7 cookie1,2018-04-13,3 cookie1,2018-04-14,2 cookie1,2018-04-15,4 cookie1,2018-04-16,4 开启智能本地模式 SET hive.exec.mode.local.auto=true;
SUM函数和窗口函数的配合使用：结果和ORDER BY相关,默认为升序。
#pv1 select cookieid,createtime,pv, sum(pv) over(partition by cookieid order by createtime) as pv1 from bigdata_t1; #pv2 select cookieid,createtime,pv, sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2 from bigdata_t1; #pv3 select cookieid,createtime,pv, sum(pv) over(partition by cookieid) as pv3 from bigdata_t1; #pv4 select cookieid,createtime,pv, sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4 from bigdata_t1; #pv5 select cookieid,createtime,pv, sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5 from bigdata_t1; #pv6 select cookieid,createtime,pv, sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6 from bigdata_t1; pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号 pv2: 同pv1 pv3: 分组内(cookie1)所有的pv累加 pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号 pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21 pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13， 14号=14号+15号+16号=2+4+4=10
如果不指定rows between,默认为从起点到当前行;
如果不指定order by，则将分组内所有值累加;
关键是理解rows between含义,也叫做window子句：
preceding：往前
following：往后
current row：当前行
unbounded：起点
unbounded preceding 表示从前面的起点
unbounded following：表示到后面的终点
AVG，MIN，MAX，和SUM用法一样。
row_number,rank,dense_rank,ntile 函数
准备数据
cookie1,2018-04-10,1 cookie1,2018-04-11,5 cookie1,2018-04-12,7 cookie1,2018-04-13,3 cookie1,2018-04-14,2 cookie1,2018-04-15,4 cookie1,2018-04-16,4 cookie2,2018-04-10,2 cookie2,2018-04-11,3 cookie2,2018-04-12,5 cookie2,2018-04-13,6 cookie2,2018-04-14,3 cookie2,2018-04-15,9 cookie2,2018-04-16,7 CREATE TABLE bigdata_t2 ( cookieid string, createtime string, --day pv INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile; 加载数据： load data local inpath '/root/hivedata/bigdata_t2.dat' into table bigdata_t2;
- ROW_NUMBER()使用
ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。
SELECT cookieid, createtime, pv, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM bigdata_t2;
- RANK 和 DENSE_RANK使用
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位 。
DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。
SELECT cookieid, createtime, pv, RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1, DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 FROM bigdata_t2 WHERE cookieid = 'cookie1';
