HIVE-计算累计和

原创
2019/06/19 21:58
阅读数 2.6K

eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和

SELECT  
month,SUM(amount) month_amount,  
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount  
FROM table_name  
GROUP BY month  
ORDER BY month;  

其中: SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:

ROWS UNBOUNDED PRECEDING

PRECEDING:在前 N 行的意思。

FOLLOWING:在后 N 行的意思。

计算前3个月之间的和

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount 

或者

SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount  

前后一个月之间的和

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount 

统计7.23到7.26的累计和,实现结果为:

2018-07-23      10527150773     10527150773
2018-07-24      11799445150     22326595923
2018-07-25      11238537810     33565133733
2018-07-26      10917352755     44482486488
select datemion,sum(loan_amount),sum(sum(loan_amount)) OVER (ORDER BY datemion ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from(
select datemion,strdeptcode,loan_amount,b.busiarea_code
from  a
inner b
on a.strdeptcode = b.dept_code
where a.datemion between '2018-07-23' and '2018-07-26') a
group by datemion

​ 如果按照每个月内去累加,添加分区字段即可,表示按月分区,每个月内进行累加求和,从当月1号到月末:

select datemion,sum(loan_amount),sum(sum(loan_amount)) OVER (distribute by date_format(datemion,'yyyy-MM') sort BY datemion ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from(
select datemion,strdeptcode,loan_amount,b.busiarea_code
from app.app_total_busioverview_aggre a
inner join dim.dim_department_v b
on a.strdeptcode = b.dept_code
where a.datemion between '2018-07-23' and '2018-09-26') a
group by datemion
order by datemion;

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
2 评论
0 收藏
0
分享
返回顶部
顶部