使用窗口函数计算账户滚动余额的SQL问题排查

咱们来梳理下可能导致C3账户2020-01-03余额显示100而非0的常见SQL逻辑问题：

1. 窗口函数未按账户分区 #

这是滚动余额计算里最容易踩的坑！如果你用SUM()窗口函数时没加PARTITION BY account_id（或你的账户标识字段），所有账户的交易金额会被混在一起累加，而不是单独计算每个账户的余额。
比如错误写法：

SELECT transaction_date, account_id, amount,
       SUM(amount) OVER (ORDER BY transaction_date) AS rolling_balance
FROM transactions;

正确姿势必须加上分区，确保每个账户的滚动总和独立计算：

SELECT transaction_date, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id ORDER BY transaction_date) AS rolling_balance
FROM transactions;

2. 交易金额的正负号处理错误 #

通常咱们会把支出（比如借记）记为负数，收入（贷记）记为正数。如果C3在2020-01-03有一笔100的支出，但你没把它转为负数，滚动余额就会只加不减——比如原本余额是0，加100变成100，但预期是0的话，应该是原余额100减去100才对。
检查下交易金额的符号，或者在计算时做调整：

-- 假设DEBIT是支出，需要转为负数
SELECT transaction_date, account_id,
       CASE WHEN transaction_type = 'DEBIT' THEN -amount ELSE amount END AS adjusted_amount,
       SUM(CASE WHEN transaction_type = 'DEBIT' THEN -amount ELSE amount END) OVER (PARTITION BY account_id ORDER BY transaction_date) AS rolling_balance
FROM transactions;

3. 窗口排序仅按日期，未考虑同日多笔交易的顺序 #

如果C3在2020-01-03有两笔交易：先收入100，再支出100，但你的窗口函数只按transaction_date排序，数据库可能会随机调整这两笔交易的计算顺序——如果先算支出再算收入，最终余额就会是100，而不是预期的0。
解决方法是加上更精确的排序字段，比如transaction_id或交易时间戳：

SUM(adjusted_amount) OVER (PARTITION BY account_id ORDER BY transaction_date, transaction_id) AS rolling_balance

4. 未正确纳入账户初始余额 #

如果你的accounts表存储了账户的初始余额，那得把初始余额作为“第一笔交易”加入计算。比如C3的初始余额是100，但你没把它和后续交易的滚动总和结合，或者初始余额对应的逻辑有误，就会导致余额多100。
可以用CTE把初始余额和交易数据合并：

WITH combined_data AS (
    -- 把初始余额作为初始交易
    SELECT account_id, 'INITIAL' AS transaction_type, creation_date AS transaction_date, initial_balance AS amount
    FROM accounts
    UNION ALL
    -- 合并交易数据
    SELECT account_id, transaction_type, transaction_date, amount
    FROM transactions
)
SELECT transaction_date, account_id, amount,
       SUM(CASE WHEN transaction_type = 'DEBIT' THEN -amount ELSE amount END) OVER (PARTITION BY account_id ORDER BY transaction_date) AS rolling_balance
FROM combined_data;

5. 过滤条件错误导致交易遗漏 #

检查你的WHERE子句，是不是不小心把C3在2020-01-03的支出交易排除了？比如如果交易日期是2020-01-03但你写了transaction_date < '2020-01-03'，那这笔支出就没被计算，余额自然还是100。

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