窗口函数累计余额计算

示例说明

逐行自联结算累计是性能灾难，窗口函数让引擎在单次扫描内完成聚合。PARTITION BY 账户隔离计算范围，ORDER BY 时间加主键构成确定序——同秒多笔交易不会随机排列。ROWS 帧显式声明从首行到当前行，语义精确到不会被 RANGE 的同值行模糊化。

技术要点

• PARTITION 隔离各账户的累计区间
• 主键进 ORDER BY 保证确定序
• ROWS 帧显式优于默认 RANGE 语义

代码解读

CTE 里 SUM 开窗得到每行的累计余额；外层再用 LAG 取前一行余额形成对照列，余额变化幅度可直观核算。WHERE 筛选负余额行——账户首次跌入透支的时刻被直接定位，排查异常扣款从这里入手。注释给出配套的复合索引。

使用建议

帧语义按业务选择：包含同行合计用 RANGE。大表开窗务必核对执行计划的窗口计算成本。累计口径要先冻结定义再写查询。

代码示例

-- 按账户分区、按时间排序的累计余额
WITH ledger AS (
  SELECT
    account_id,
    occurred_at,
    amount_cents,
    SUM(amount_cents) OVER (
      PARTITION BY account_id
      ORDER BY occurred_at, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
  FROM transactions
  WHERE status = 'settled'
)
SELECT
  account_id,
  occurred_at,
  amount_cents,
  running_balance,
  LAG(running_balance, 1, 0) OVER (
    PARTITION BY account_id ORDER BY occurred_at, id
  ) AS previous_balance
FROM ledger
WHERE running_balance < 0  -- 首次透支的时刻一目了然
ORDER BY account_id, occurred_at;

-- 配套索引：窗口的 PARTITION + ORDER 键
-- CREATE INDEX idx_tx_account_time
--   ON transactions (account_id, occurred_at, id);