通过窗口函数在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