PostgreSQL PL/SQL 无需逐行遍历的复利值计算方案咨询

核心思路 #

复利计算本质是有序序列的累乘运算，PostgreSQL 无需自定义逐行遍历逻辑（如游标、递归 CTE、逐行PL/SQL函数），借助内置窗口聚合能力即可实现批量计算，性能远高于逐行方案。

实现方案 #

方案1：内置函数实现（无需自定义对象） #

利用累乘的对数等价转换规则：多个正数的累乘结果等于各因子自然对数求和后再取指数，即 a*b*c = exp(ln(a)+ln(b)+ln(c))，配合窗口函数实现滑动累乘：

SELECT
    "RowNum",
    "Date",
    "Some Index",
    CASE
        WHEN "RowNum" = 1 THEN 1000.0
        ELSE 1000.0 * EXP(
            SUM(
                CASE WHEN "RowNum" > 1 THEN LN(1 + "Some Index") ELSE 0 END
            ) OVER (ORDER BY "RowNum" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        )
    END AS Value
FROM index_data
ORDER BY "RowNum";

方案2：自定义累乘聚合函数（可读性更高） #

PostgreSQL 11及以上版本可以自定义通用累乘聚合函数，代码更直观，且避免对数转换的负数兼容问题：

-- 第一步：创建累乘聚合函数，仅需执行一次
CREATE AGGREGATE prod(numeric) (
    SFUNC = numeric_mul,
    STYPE = numeric,
    INITCOND = '1'
);

-- 第二步：查询计算
SELECT
    "RowNum",
    "Date",
    "Some Index",
    CASE
        WHEN "RowNum" = 1 THEN 1000.0
        ELSE 1000.0 * prod(
            CASE WHEN "RowNum" > 1 THEN 1 + "Some Index" ELSE 1 END
        ) OVER (ORDER BY "RowNum")
    END AS Value
FROM index_data
ORDER BY "RowNum";

性能说明 #

两种方案均使用数据库内核优化过的窗口聚合算子，采用批量计算逻辑，时间复杂度为O(n)，千万级数据量下的计算效率比逐行遍历方案高10~100倍，无需额外中间存储。

注意事项 #

• 若业务中存在1 + Some Index <= 0的情况，使用方案1时需要额外加判断避免对数计算报错，方案2无此问题。
• 对精度要求高的场景，建议将Some Index字段类型设置为numeric而非浮点数类型，减少运算误差。

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