如何使用Rank()等SQL函数按日期计算贷方借方的账户滚动余额

实现方案 #

你需要的按日期滚动计算账户余额属于累计求和场景，不需要使用RANK()函数，直接用SQL标准窗口聚合函数SUM()搭配排序规则即可实现，是目前性能最优、写法最简洁的方案。

前提假设 #

假设你的交易表名为account_trans，核心字段如下：

• trans_date：交易日期
• debit：借方发生额（支出类）
• credit：贷方发生额（收入类）
• account_id：账户ID（多账户场景使用）

单账户场景实现代码 #

对应你给出的逐行累计逻辑，写法如下：

SELECT 
  trans_date,
  debit,
  credit,
  -- 逐行累计 贷方-借方 的总和，得到滚动余额
  SUM(COALESCE(credit, 0) - COALESCE(debit, 0)) OVER (ORDER BY trans_date ASC) AS rolling_balance
FROM account_trans
ORDER BY trans_date ASC;

多账户场景实现代码 #

如果需要每个账户单独计算自己的滚动余额，增加窗口分区规则即可：

SELECT 
  account_id,
  trans_date,
  debit,
  credit,
  SUM(COALESCE(credit, 0) - COALESCE(debit, 0)) OVER (
    PARTITION BY account_id 
    ORDER BY trans_date ASC
  ) AS rolling_balance
FROM account_trans
ORDER BY account_id, trans_date ASC;

特殊场景适配 #

• 如果有初始余额，直接在SUM计算结果上叠加即可，比如初始余额为2000时，写为2000 + SUM(...) OVER (...)
• 同一天有多笔交易时，可以在ORDER BY后追加交易流水号这类唯一字段，明确同日期交易的计算顺序，避免结果错乱
• 如果你使用的是不支持窗口函数的旧版本数据库（如MySQL 5.7及以下），可以用自关联方式实现，性能会低于窗口函数方案，参考写法：

SELECT 
  t1.trans_date,
  t1.debit,
  t1.credit,
  SUM(COALESCE(t2.credit, 0) - COALESCE(t2.debit, 0)) AS rolling_balance
FROM account_trans t1
LEFT JOIN account_trans t2 ON t2.trans_date <= t1.trans_date
GROUP BY t1.trans_date, t1.debit, t1.credit
ORDER BY t1.trans_date ASC;

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