SQL如何计算期初余额OPENING_BALANCE与期末余额CLOSING_BALANCE

核心逻辑说明 #

• 期初余额（OPENING_BALANCE）：统计所有早于当前交易日期的金额累计和，支持自定义起始统计日期
• 期末余额（CLOSING_BALANCE）：当期期初余额 + 当日交易金额，后续日期的期初余额直接继承上一期期末余额

实现代码示例 #

以下示例适配SQLite环境，可根据实际使用的数据库（MySQL、PostgreSQL等）做少量语法调整：

-- 定义统计起始日期，可根据需求自行修改
WITH params AS (
    SELECT '2023-01-01' AS start_date
),
trans_with_balance AS (
    SELECT 
        t.*,
        -- 计算期末余额：累计到当前行的所有交易金额总和
        SUM(t.AMOUNT) OVER (ORDER BY t.TRANSACTION_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS closing_balance,
        -- 计算期初余额：累计到当前行前一行的所有交易金额总和，首行无前置记录默认置为0
        COALESCE(SUM(t.AMOUNT) OVER (ORDER BY t.TRANSACTION_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS opening_balance
    FROM your_transaction_table t
    JOIN params p ON t.TRANSACTION_DATE >= p.start_date
)
SELECT 
    TRANSACTION_DATE,
    AMOUNT,
    opening_balance AS OPENING_BALANCE,
    closing_balance AS CLOSING_BALANCE
FROM trans_with_balance
ORDER BY TRANSACTION_DATE;

代码说明 #

• your_transaction_table请替换为你实际使用的交易记录表名
• 用CTE统一管理统计起始日期，修改时无需调整核心计算逻辑
• 窗口函数的ROWS边界控制是核心实现逻辑，无需嵌套子查询或自定义变量即可实现滚动累计
• 如果需要按账户、交易类型等多维度分别计算余额，仅需在窗口函数的OVER子句中添加PARTITION BY 分区字段即可

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