和同事一起探讨了个问题挺有意思,记录一下!
问题
每个月有数据如下:
需要计算截止到每个月,前月距离当月月数乘前月阅读数累计之和。
类似递归操作:本月到第一个月的累计和,前一个月到第一个月的累计和…一直到第二个月到第一个月的和 再加上第一个月 最终所有累积和的合计。
听起来比较绕,举个栗子:
图中每个月的阅读量是a,b,c,d,e。计算结果result如图公式。
- 截止到2014-01月:只有a,距离本月为1个月,即a*1;
- 截止到2014-02月:1月距离本月为2个月,即1月的a为a*2,2月距离本月为1,即b,求和为a*2+b;
- 截止到2014-03月:1月距离本月为3个月,及1月的a为a*3,2月距离本月为2,即b*2,3月距离本月为1,即c,求和为a*3+b*2+c;
以此类推计算截止到每个月的前月累计。
到这里可以先思考下怎么做。。。
最终同事用excel完成了计算。这里想用hiveSql看看能不能统计,想了下思路。
思路:
原本想的是横向拉平数据,在每个月的后面都计算好当月所需要的前月乘后的数据,再横向累加,如下图:
这个思路用sql实现有两个关键点:a.拉平数据 和 b.对应拉平数据后的乘数(前月距离本月的月数)
- 拉平数据
思路:即多行转多列操作,步骤为多行转一行,再一行转多列即可。
操作:按照createtime分组拼接每个月的数据为字符串,再拆分为多列
select
createtime
,cast(split(str,',')[0] as bigint) as month1
,cast(split(str,',')[1] as bigint) as month2
,cast(split(str,',')[2] as bigint) as month3
,cast(split(str,',')[3] as bigint) as month4
,cast(split(str,',')[4] as bigint) as month5
from
(select
createtime,wm_concat(avg_view,',') as str
from tablename
group by createtime
) tmp;
- 对应乘数
思路:观察这里的月差值,小于等于当月的月数直接减,大于当月的月数直接赋0值即可。
操作:利用row_number()产生自增序列,在利用leg() 操作自增序列的位置移动
select
createtime,
month1,lag(irank,1,0) over(order by irank) as lag1,
month2,lag(irank,2,0) over(order by irank) as lag2,
month2,lag(irank,3,0) over(order by irank) as lag3,
month2,lag(irank,4,0) over(order by irank) as lag4,
month2,lag(irank,5,0) over(order by irank) as lag5
from
(select
createtime
,cast(split(viewstr,',')[0] as bigint) as month1
,cast(split(viewstr,',')[1] as bigint) as month2
,cast(split(viewstr,',')[2] as bigint) as month3
,cast(split(viewstr,',')[3] as bigint) as month4
,cast(split(viewstr,',')[4] as bigint) as month5
,ROW_NUMBER() OVER(ORDER BY createtime) AS irank -- 多出一行自增序列
from
(select
createtime
,wm_concat(cast(avg_view as string),',') as viewstr
from tablename
group by createtime
) tmp
) tmp1
上述sql结果如图:
再将sql内的monthN*legN再横向累加即可
- 总结:这种思路可以实现但是缺点显而易见,太麻烦了。后来看到同事的数据有88个月,我裂开!要写多少行啊。放弃!
归结上述思路,发现繁琐的步骤就再横向拉平那块,88个月split要写88个,上面的leg又是88个。hive中对于这种繁琐的操作应该是有内置函数的。之前有写过内置函数的总结篇。HIVE窗口函数合集,发现果然有。
利用开窗函数的window子句其中的累加到当前行。只需要使用两次即可:
SELECT
createtime,avg_view,sample1,
sum(sample1) over(order by createtime rows between UNBOUNDED PRECEDING and CURRENT ROW ) as sample2
FROM
(select
createtime,avg_view,
sum(avg_view) over(order by createtime rows between UNBOUNDED PRECEDING and CURRENT ROW ) as sample1
from tablename
) t;
第一次使用得到第一行累加到当前行的统计值sample1。再次将sample1累加一次第一行到当前行即得到最终结果。如下图所示:
总结:同事使用excel完成的思路也是这样,累加两次。excel真香!