分析函数hive计算均值_Hive分析窗口函数(一) SUM,AVG,MIN,MAX

Hive中提供了越来越多的分析函数，用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍，将陆续发布。

今天先看几个基础的，SUM、AVG、MIN、MAX。

用于实现分组内所有和连续累积的统计。

Hive版本为 apache-hive-0.13.1

数据准备

CREATE EXTERNAL TABLE lxw1234 (

cookieid string,

createtime string, --day

pv INT

) ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

stored as textfile location '/tmp/lxw11/';

DESC lxw1234;

cookieid STRING

createtime STRING

pv INT

hive> select * from lxw1234;

OK

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

SUM — 注意,结果和ORDER BY相关,默认为升序

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