i_mycode的博客：hiveSql完成类递归计算

当前位置博文首页 > i_mycode的博客：hiveSql完成类递归计算

i_mycode的博客：hiveSql完成类递归计算

作者：[db:作者] 时间：2021-08-05 15:57

和同事一起探讨了个问题挺有意思，记录一下！
问题
每个月有数据如下：
在这里插入图片描述
需要计算截止到每个月，前月距离当月月数乘前月阅读数累计之和。
类似递归操作：本月到第一个月的累计和，前一个月到第一个月的累计和…一直到第二个月到第一个月的和再加上第一个月最终所有累积和的合计。

听起来比较绕，举个栗子：
在这里插入图片描述
图中每个月的阅读量是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看看能不能统计，想了下思路。

思路：

原本想的是横向拉平数据，在每个月的后面都计算好当月所需要的前月乘后的数据，再横向累加，如下图：
123
这个思路用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
    group by createtime,avg_view
    ) t
group by createtime,avg_view,sample1;