PostgreSQL有序累计聚合：按当前累计值算百分比并分组求和

针对带顺序依赖的累计计算、按实体分组仅输出最终结果的需求，以下两种方案均可在PostgreSQL环境下直接运行，基于给出的示例数据计算结果均符合546的预期值。

方案1：改造递归CTE支持分组计算 #

原有递归CTE方案的核心问题是未适配分组逻辑，改造后不会冗余返回中间过程值，直接输出每个实体的最终累计结果：

WITH all_events AS (
    -- 合并三类事件，统一字段结构
    SELECT 
        entity_id,
        event_index,
        'added' AS event_type,
        amount AS calc_param,
        NULL::numeric(78,0) AS percent
    FROM added
    UNION ALL
    SELECT 
        entity_id,
        event_index,
        'reduce' AS event_type,
        amount AS calc_param,
        NULL::numeric(78,0) AS percent
    FROM reduce
    UNION ALL
    SELECT 
        entity_id,
        event_index,
        'interest' AS event_type,
        NULL::numeric(78,0) AS calc_param,
        percent AS percent
    FROM interest
),
ranked_events AS (
    -- 按实体分组，给每个实体下的事件按执行顺序打连续行号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_index ASC) AS rn,
        COUNT(*) OVER (PARTITION BY entity_id) AS total_events
    FROM all_events
),
recursive_calc AS (
    -- 递归锚点：取每个实体的第一个事件计算初始值
    SELECT 
        entity_id,
        rn,
        total_events,
        CASE event_type
            WHEN 'added' THEN calc_param
            WHEN 'reduce' THEN -calc_param
            WHEN 'interest' THEN 0 -- 若首事件为计息，初始值为0，有初始本金规则可直接在此处修改
        END AS balance
    FROM ranked_events
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：同实体下按行号顺序逐行计算累计值
    SELECT 
        e.entity_id,
        e.rn,
        e.total_events,
        CASE e.event_type
            WHEN 'added' THEN r.balance + e.calc_param
            WHEN 'reduce' THEN r.balance - e.calc_param
            WHEN 'interest' THEN r.balance * (1 + e.percent/100)
        END AS balance
    FROM recursive_calc r
    JOIN ranked_events e 
        ON r.entity_id = e.entity_id 
        AND e.rn = r.rn + 1
)
-- 仅返回每个实体最后一步计算完成的最终累计值
SELECT entity_id, balance AS final_balance
FROM recursive_calc
WHERE rn = total_events;

方案2：自定义聚合函数（大数据量下性能更优） #

递归CTE在单实体事件量过万时性能会明显下降，使用PostgreSQL自定义聚合可以实现更高的计算效率，写法也更简洁：

-- 定义累计计算的状态转换函数
CREATE OR REPLACE FUNCTION calc_balance_state(
    current_balance numeric,
    event_type text,
    calc_param numeric,
    percent numeric
) RETURNS numeric AS $$
BEGIN
    CASE event_type
        WHEN 'added' THEN RETURN current_balance + calc_param;
        WHEN 'reduce' THEN RETURN current_balance - calc_param;
        WHEN 'interest' THEN RETURN current_balance * (1 + percent/100);
        ELSE RETURN current_balance;
    END CASE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 定义顺序聚合函数
CREATE AGGREGATE balance_calc(text, numeric, numeric) (
    SFUNC = calc_balance_state,
    STYPE = numeric,
    INITCOND = '0' -- 初始累计值为0，有初始本金规则可在此处修改
);

-- 直接分组查询得到每个实体的最终累计值
SELECT 
    entity_id,
    balance_calc(
        event_type, 
        COALESCE(amount, 0), 
        COALESCE(percent, 0) 
        ORDER BY event_index ASC -- 聚合内严格按事件顺序计算
    ) AS final_balance
FROM (
    SELECT entity_id, event_index, 'added' AS event_type, amount, NULL AS percent FROM added
    UNION ALL
    SELECT entity_id, event_index, 'reduce' AS event_type, amount, NULL AS percent FROM reduce
    UNION ALL
    SELECT entity_id, event_index, 'interest' AS event_type, NULL AS amount, percent FROM interest
) all_events
GROUP BY entity_id;

注：如果计息后存在非整数结果，上述代码使用无精度限制的numeric类型避免截断，若业务要求保留整数位可在外层套ROUND函数即可。

内容的提问来源于stack exchange，提问作者Ярослав Коробейников