Hive Sum MAX MIN聚合函數(shù)

數(shù)據(jù)準(zhǔn)備
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
創(chuàng)建數(shù)據(jù)庫及表
create database if not exists cookie;
use cookie;
drop table if exists cookie1;
create table cookie1(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
load data local inpath "/home/hadoop/cookie1.txt" into table cookie1;
select * from cookie1;
SUM函數(shù)
select
cookieid,
createtime,
pv,
sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,
sum(pv) over (partition by cookieid order by createtime) as pv2,
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,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,
sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from cookie1;
解釋
pv1: 分組內(nèi)從起點(diǎn)到當(dāng)前行的pv累積，如，11號(hào)的pv1=10號(hào)的pv+11號(hào)的pv, 12號(hào)=10號(hào)+11號(hào)+12號(hào)
pv2: 同pv1
pv3: 分組內(nèi)(cookie1)所有的pv累加
pv4: 分組內(nèi)當(dāng)前行+往前3行，如，11號(hào)=10號(hào)+11號(hào)， 12號(hào)=10號(hào)+11號(hào)+12號(hào)， 13號(hào)=10號(hào)+11號(hào)+12號(hào)+13號(hào)， 14號(hào)=11號(hào)+12號(hào)+13號(hào)+14號(hào)
pv5: 分組內(nèi)當(dāng)前行+往前3行+往后1行，如，14號(hào)=11號(hào)+12號(hào)+13號(hào)+14號(hào)+15號(hào)=5+7+3+2+4=21
pv6: 分組內(nèi)當(dāng)前行+往后所有行，如，13號(hào)=13號(hào)+14號(hào)+15號(hào)+16號(hào)=3+2+4+4=13，14號(hào)=14號(hào)+15號(hào)+16號(hào)=2+4+4=10
關(guān)鍵字解釋
如果不指定ROWS BETWEEN,默認(rèn)為從起點(diǎn)到當(dāng)前行;
如果不指定ORDER BY，則將分組內(nèi)所有值累加;
關(guān)鍵是理解ROWS BETWEEN含義,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：當(dāng)前行
UNBOUNDED：起點(diǎn)，
UNBOUNDED PRECEDING 表示從前面的起點(diǎn)，
UNBOUNDED FOLLOWING：表示到后面的終點(diǎn)
–其他AVG，MIN，MAX，和SUM用法一樣。
AVG函數(shù)
select
cookieid,
createtime,
pv,
avg(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默認(rèn)為從起點(diǎn)到當(dāng)前行
avg(pv) over (partition by cookieid order by createtime) as pv2, --從起點(diǎn)到當(dāng)前行，結(jié)果同pv1
avg(pv) over (partition by cookieid) as pv3, --分組內(nèi)所有行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --當(dāng)前行+往前3行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --當(dāng)前行+往前3行+往后1行
avg(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6  --當(dāng)前行+往后所有行
from cookie1;