如何编写SQL实现sample表observations字段的累积乘法计算

假设sample表存在排序字段（如自增主键id）用于确定行的计算顺序，observations字段的原始值依次为2、3、5、10，可使用以下方案实现需求：

通用兼容写法（适配绝大多数支持对数运算的数据库） #

利用数学特性转换：a*b*c = exp(ln(a) + ln(b) + ln(c))，配合窗口函数实现累积求和后还原乘积：

SELECT
  EXP(SUM(LN(observations)) OVER (ORDER BY id)) AS cumulative_multiplication
FROM sample;

常见数据库专属写法 #

• PostgreSQL：可直接调用乘积聚合函数

  SELECT
    product(observations) OVER (ORDER BY id) AS cumulative_multiplication
  FROM sample;
  

• MySQL 8.0及以上版本：可用会话变量累积计算

  SET @cum_product = 1;
  SELECT
    (@cum_product := @cum_product * observations) AS cumulative_multiplication
  FROM sample
  ORDER BY id;
  

注意：如果表没有显式的排序字段，需要先定义排序规则，否则无法保证累积计算的顺序符合预期。

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