搜索 | 会员  
Hive窗口函数之累积值、平均值、首尾值的计算学习
来源: CSDN   作者:  日期:2018-5-10  类别:大数据  主题:Hadoop  编辑:Merlin
Hive窗口函数可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等;可以结合聚集函数SUM()、AVG()等使用;可以结合FIRST_VALUE()和LAST_VALUE(),返回窗口的第一个和最后

Hive窗口函数可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等;可以结合聚集函数SUM() 、AVG()等使用;可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值。
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合. 
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.
window子句: 
- PRECEDING:往前 
- FOLLOWING:往后 
- CURRENT ROW:当前行 
- UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点

1、计算累计和
统计1-12月的累积和,即1月为1月份的值,2月为1、2月份值的和,3月为123月份的和,12月为1-12月份值的和。
关键字解析:
SUM(SUM(amount)) 内部的SUM(amount)为需要累加的值;
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序;
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
ROWS UNBOUNDED PRECEDING
PRECEDING:在前 N 行的意思。
FOLLOWING:在后 N 行的意思。

1.1、计算所有月份的累计和


select pt_month,sum(amount) pay_amount,sum(sum(amount))over(order by pt_month) cumulative_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


select pt_month,sum(amount) pay_amount,sum(sum(amount))over(order by pt_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


1.2、计算前3个月和本月共4个月的累积和


select pt_month,sum(amount) pay_amount,sum(sum(amount))over(order by pt_month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) cumulative_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


select pt_month,sum(amount) pay_amount,sum(sum(amount))over(order by pt_month ROWS 3 PRECEDING) cumulative_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


1.3、计算前1月后1月和本月共3个月的累积和


select pt_month,sum(amount) pay_amount,sum(sum(amount))over(order by pt_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) cumulative_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


2、计算平均值
2.1、计算前1月后1月和本月共3个月各月总值的平均值


select pt_month,sum(amount) pay_amount,avg(sum(amount))over(order by pt_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) average_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


2.2、计算前3个月和本月共4个月各月总值的平均值


select pt_month,sum(amount) pay_amount,avg(sum(amount))over(order by pt_month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) cumulative_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


3、计算窗体第一条和最后一条的值


select pt_month,sum(amount) pay_amount,first_value(sum(amount))over(order by pt_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) first_amount,last_value(sum(amount))over(order by pt_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) last_amount

from data_chushou_pay_info

where pt_month between '2017-01' and '2017-11' and state=0

group by pt_month;


德仔网尊重行业规范,每篇文章都注明有明确的作者和来源;德仔网的原创文章,请转载时务必注明文章作者和来源:德仔网;
头条那些事
大家在关注
广告那些事
我们的推荐
也许感兴趣的
干货
了解一下吧