SQL/Presto基于历史行可变增长率的复利值计算实现方法问询

你说的场景本质是要计算按时间排序的增长因子的累积乘积，再乘以初始值即可，有两种常用实现方式：

方法1：对数转换法（兼容大部分标准SQL） #

利用对数的数学特性：ln(a*b) = ln(a) + ln(b)，先对所有增长因子取对数求累积和，再取指数还原为乘积结果。

代码示例 #

SELECT 
  time,
  growth_factor,
  -- 初始值默认为1，要调整初始值直接在结果前乘对应数值即可
  EXP(SUM(LN(growth_factor)) OVER (ORDER BY time)) AS value
FROM growth_data
ORDER BY time;

注意事项 #

• 仅适用于所有growth_factor都为正数的场景，如果存在0或负数，对数计算会报错，需要提前做数据清洗。
• 如果需要按不同维度（比如不同商品、不同区域）分开计算，在窗口函数中增加PARTITION BY 维度字段即可。

方法2：array_agg+reduce函数（Presto专属，更稳定） #

Presto支持自定义聚合逻辑的reduce函数，可以直接实现累积乘积，不会有对数转换的精度问题，也能兼容0值场景。

代码示例 #

SELECT 
  time,
  growth_factor,
  reduce(
    array_agg(growth_factor) OVER (ORDER BY time),
    1.0, -- 此处直接填你需要的初始值即可
    (acc, current) -> acc * current,
    res -> res
  ) AS value
FROM growth_data
ORDER BY time;

用你给出的示例数据测试两种方法，计算得到的value和示例完全一致。

内容的提问来源于stack exchange，提问作者user2105632