如何用LAG函数生成表递归行并正确计算收支余额？

表结构与样本数据 #

现有表结构及测试数据如下：

CREATE TABLE base_table
(
  FUND_CODE varchar(255),
  OPENING_BALANCE float,
  TRANSACTION_DATE datetime,
  UNITS_ALLOCATED float,
  CLOSING_BALANCE float,
  ROW_ID int
)

INSERT INTO base_table
VALUES 
  ('A', 10000, '20230530', 300, 10300, 1),
  ('A', 10000, '20230531', 350, 10350, 2),
  ('A', 10000, '20230601', -150, 9850, 3),
  ('A', 10000, '20230605', -200, 9800, 4),
  ('A', 10000, '20230615', -300, 9700, 5),
  ('A', 10000, '20230620', 200, 10200, 6)

说明：表中第一行的OPENING_BALANCE为账户起始值，UNITS_ALLOCATED字段数据正确。需要通过SQL计算第一行的CLOSING_BALANCE，以及后续每行的OPENING_BALANCE和CLOSING_BALANCE（交易日期可能存在间隔）。

当前尝试的SQL #

SELECT DISTINCT
  bt.FUND_CODE,
  CASE
    WHEN bt.ROW_ID = 1 THEN bt.OPENING_BALANCE
    ELSE (LAG(bt.CLOSING_BALANCE, 1, 0) OVER (PARTITION BY bt.FUND_CODE ORDER BY bt.TRANSACTION_DATE))
  END AS OPENING_BALANCE,
  bt.TRANSACTION_DATE,
  bt.UNITS_ALLOCATED,
  (CASE
    WHEN bt.ROW_ID = 1 THEN bt.OPENING_BALANCE
    ELSE (LAG(bt.CLOSING_BALANCE, 1, 0) OVER (PARTITION BY bt.FUND_CODE ORDER BY bt.TRANSACTION_DATE))
  END) + bt.UNITS_ALLOCATED AS CLOSING_BALANCE,
  bt.ROW_ID
FROM base_table bt ORDER BY bt.ROW_ID ASC

问题所在 #

执行上述SQL后，从第三行开始OPENING_BALANCE计算错误，导致后续所有数据偏差。例如第三行的OPENING_BALANCE应为10650，CLOSING_BALANCE应为10500，但当前计算逻辑调用LAG(bt.CLOSING_BALANCE)取的是原表中存储的错误值，而非前一行计算后的正确余额。

解决方案 #

使用窗口累积求和函数基于起始值和历史交易数据计算余额，逻辑如下：

1. 第一行的期初余额为原表的起始值，期末余额 = 起始值 + 当日交易数
2. 后续行的期初余额 = 起始值 + 此前所有交易数的累积和，期末余额 = 期初余额 + 当日交易数

对应的SQL代码：

SELECT
  FUND_CODE,
  -- 计算期初余额：第一行用起始值，后续用起始值加此前所有交易的累积和
  CASE
    WHEN ROW_ID = 1 THEN OPENING_BALANCE
    ELSE OPENING_BALANCE + SUM(UNITS_ALLOCATED) OVER (
      PARTITION BY FUND_CODE 
      ORDER BY TRANSACTION_DATE 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    )
  END AS OPENING_BALANCE,
  TRANSACTION_DATE,
  UNITS_ALLOCATED,
  -- 计算期末余额：起始值加截至当日所有交易的累积和
  OPENING_BALANCE + SUM(UNITS_ALLOCATED) OVER (
    PARTITION BY FUND_CODE 
    ORDER BY TRANSACTION_DATE
  ) AS CLOSING_BALANCE,
  ROW_ID
FROM base_table
ORDER BY ROW_ID ASC;

执行结果 #

执行后将得到正确的余额数据：

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