【Hive】累乘 和 累加

通过窗口函数在Hive中实现累加和累乘。如下：

1.依据date的顺序，对col分别进行累加和累乘；

2.在累乘过程中，由于进行了log转换，存在较小精度损失，用round（）进行处理四舍五入处理；

3.rows between unbounded preceding and current row 表示窗口的范围在当前行及前面所有行；

select 
    col
    , sum(col) over(order by date rows between unbounded preceding and current row) as 累加
    , power(10, sum(log(10, col))over(order by date rows between unbounded preceding and current row)) as 累乘 
    , round(power(10, sum(log(10, col))over(order by date rows between unbounded preceding and current row))) as 累乘处理
from (
    select 1 as col, '2018-12-01' as date
    union all 
    select 2 as col, '2018-12-02' as date
    union all 
    select 3 as col, '2018-12-03' as date
    union all 
    select 4 as col, '2018-12-04' as date
) as a