SQL递推填充银行账户表Income/Outcome字段（解决循环计算问题）

业务规则说明 #

• 需填充银行account流水表的Income（收入）、Outcome（支出）字段，计算逻辑如下：
  1. 单行支出计算公式：Outcome = 当前行Income + Debit（借方金额） - Credit（贷方金额）
  2. 非首行的Income取值为上一行的Outcome值
• 仅首行（交易日期2021-01-01）有已知初始值：Income=100、Outcome=400，其余行两个字段均为空值
• 直接使用lag()窗口函数会产生循环计算依赖，无法直接得到正确结果

测试表结构与样例数据 #

create table account(acc_date date,income int, debit int, credit int, outcome int);
insert into account values('2021-01-01', 100,800,500,400),
('2021-02-01', null,900,1500,null),
('2021-03-01', null,1700,2000,null),
('2021-04-01', null,2100,2800,null),
('2021-05-01', null,3500,4000,null);
select * from account;

实现方案 #

该场景属于典型的顺序递归计算场景，需使用递归CTE完成逐行推导，避免循环依赖：

1. 递归锚点取按交易日期排序后的首行数据，直接使用已知的初始Income、Outcome值
2. 递归部分按排序顺序逐行关联上一行的计算结果，将上一行Outcome赋值为当前行Income，再按公式计算当前行Outcome

通用SQL实现（支持MySQL8.0+、PostgreSQL、SparkSQL等兼容递归CTE的引擎） #

WITH RECURSIVE account_rn AS (
    -- 先给所有流水按日期排序编号，适配非固定日期间隔的场景
    SELECT *, ROW_NUMBER() OVER(ORDER BY acc_date) AS rn
    FROM account
),
account_calc AS (
    -- 递归起点：第一行初始值
    SELECT acc_date, income, debit, credit, outcome, rn
    FROM account_rn
    WHERE rn = 1

    UNION ALL

    -- 逐行递归计算
    SELECT 
        ar.acc_date,
        ac.outcome AS income,
        ar.debit,
        ar.credit,
        ac.outcome + ar.debit - ar.credit AS outcome,
        ar.rn
    FROM account_rn ar
    JOIN account_calc ac ON ar.rn = ac.rn + 1
)
-- 查询计算结果，如需更新原表可替换为对应UPDATE语句
SELECT acc_date, income, debit, credit, outcome
FROM account_calc
ORDER BY acc_date;

计算结果参考 #

执行后得到的正确值如下：

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