Oracle中按年度计算百分比累计值的SQL实现问题

需求规则 #

• 源表tbl包含YEARS（年度）、PERCENTAGE（百分比值）字段
• 按年度升序排列，逐年计算累计复利值存入ACCUMULATIVE列
• 单期增长系数换算规则：PERCENTAGE / 100 + 1
• 累计规则为各期增长系数连乘后减1，转回百分比格式，而非简单百分比求和
• 预期结果参考：

YEARS,          PERCENTAGE,      ACCUMULATIVE
2010,           38.15%,           38.15%
2011,          -25.51%,           2.93%
2012,           -8.47%,           -5.80%
2013,           18.51%,           11.64%
2014,            -2.07%,           9.32%
2015,            16.27%,          27.11%
2016,           108.94%,         165.60%
2017,            29.67%,         244.41%

原有SQL错误点 #

• 字段别名前后不匹配：内层别名定义为YEAR，外层排序引用YR，字段无法识别
• 聚合逻辑错误：复利累计是系数连乘，不是百分比直接求和，不能用SUM(PERCENTAGE)实现
• 窗口分区错误：内层按YEAR分区会将每年数据独立切割，无法实现跨年度累计
• 缺失格式转换：没有将计算后的数值结果转回百分比展示格式

正确SQL写法 #

写法1：适用于支持乘积窗口函数的数据库（PostgreSQL 14+、BigQuery、Spark SQL等） #

SELECT
  YEARS,
  PERCENTAGE,
  CONCAT(ROUND((cumulative_factor - 1) * 100, 2), '%') AS ACCUMULATIVE
FROM (
  SELECT
    YEARS,
    PERCENTAGE,
    -- 按年度排序，逐行累计乘以前期增长系数
    PRODUCT(PERCENTAGE / 100 + 1) OVER (
      ORDER BY YEARS
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_factor
  FROM tbl
) AS t
ORDER BY YEARS;

写法2：适用于不支持PRODUCT窗口函数的数据库（MySQL、低版本PostgreSQL等） #

利用对数性质ln(a*b) = ln(a) + ln(b)，将乘积转换为求和计算，最后取指数还原结果：

SELECT
  YEARS,
  PERCENTAGE,
  CONCAT(ROUND((EXP(cumulative_ln_sum) - 1) * 100, 2), '%') AS ACCUMULATIVE
FROM (
  SELECT
    YEARS,
    PERCENTAGE,
    SUM(LN(PERCENTAGE / 100 + 1)) OVER (
      ORDER BY YEARS
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_ln_sum
  FROM tbl
) AS t
ORDER BY YEARS;

注：如果PERCENTAGE字段是带%的字符串格式，需要先替换掉%符号转为数值再计算，例如MySQL中用CAST(REPLACE(PERCENTAGE, '%', '') AS DECIMAL(10,2))做转换。

内容的提问来源于stack exchange，提问作者Мариян Цветков