如何用SQL为交易表计算客户每笔交易后的实时余额？

我们有一张名为Transactions的表，需要新增一列Balance，用于展示每个客户在完成每笔贷记（Credit）/借记（Debit）交易后的账户余额。

需求示例 #

• 示例1：客户A1初始余额为0，若发生一笔300的Credit交易，Balance列显示300；之后发生一笔100的Debit交易，Balance列显示200（300-100）。
• 示例2：客户B1初始余额为0，若发生一笔300的Debit交易，Balance列显示-300；之后发生一笔100的Credit交易，Balance列显示-200（-300+100）。

样本数据SQL脚本 #

CREATE TABLE transactions (
    transaction_id NUMBER,
    customer_id VARCHAR2(5),
    transaction_type VARCHAR2(15),
    amount NUMBER
);

INSERT INTO transactions VALUES(1,'A1', 'Credit',100);
INSERT INTO transactions VALUES(2,'A3', 'Credit',2000);
INSERT INTO transactions VALUES(3,'B1', 'Debit',300);
INSERT INTO transactions VALUES(4,'A1', 'Debit',1900);
INSERT INTO transactions VALUES(5,'A3', 'Debit',1200);
INSERT INTO transactions VALUES(6,'B1', 'Credit',1400);
INSERT INTO transactions VALUES(7,'C1', 'Credit',30000);
INSERT INTO transactions VALUES(8,'B1', 'Debit',2000);
INSERT INTO transactions VALUES(9,'A3', 'Credit',4000);
INSERT INTO transactions VALUES(10,'A1', 'Debit',200);
INSERT INTO transactions VALUES(11,'C1', 'Credit',300);

SELECT * FROM transactions;

实现方案 #

1. 查询时实时计算余额

通过窗口函数可直接在查询中计算每笔交易后的余额，无需修改表结构：

SELECT 
    transaction_id,
    customer_id,
    transaction_type,
    amount,
    SUM(CASE 
        WHEN transaction_type = 'Credit' THEN amount 
        ELSE -amount 
    END) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_id
    ) AS Balance
FROM transactions;

• 逻辑说明：用CASE语句将Credit交易金额转为正值，Debit转为负值；通过SUM() OVER (PARTITION BY customer_id ORDER BY transaction_id)按客户分组，按交易ID顺序累加每笔交易的净值，得到当前交易完成后的账户余额。

2. 持久化Balance列到表中

如果需要将余额永久保存到表中，可先添加列再更新数据：

-- 添加Balance列
ALTER TABLE transactions ADD Balance NUMBER;

-- 更新Balance值
MERGE INTO transactions t
USING (
    SELECT 
        transaction_id,
        SUM(CASE 
            WHEN transaction_type = 'Credit' THEN amount 
            ELSE -amount 
        END) OVER (
            PARTITION BY customer_id 
            ORDER BY transaction_id
        ) AS calc_balance
    FROM transactions
) s
ON (t.transaction_id = s.transaction_id)
WHEN MATCHED THEN 
    UPDATE SET t.Balance = s.calc_balance;

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