计算付款后当前余额SQL查询异常求助：Balance字段无法正确取值

数据表结构与数据 #

表AG的记录如下：

buysell payment_date    balance     amount_paid 
175325  2023-07-01      1000.00     NULL             
175325  2023-07-02      0.00        500.00            
175325  2023-07-03      0.00        400.00            
175325  2023-07-04      0.00        100.00    

期望结果 #

需要得到付款后的当前余额，结果格式如下：

payment_date    balance  amount_paid    current_balance
2023-07-01      1000.00  NULL           1000.00
2023-07-02      0.00     500.00         500.00
2023-07-03      0.00     400.00         100.00
2023-07-04      0.00     100.00         0.00   

我的SQL查询语句 #

我尝试用CTE计算累计付款，但结果不正确：

;with ctec as
( 
    select payment_date, balance, amount_paid,
    sum(amount_paid) over (order by payment_date) as running_total
    from  AG
) 

select  payment_date, balance, amount_paid, 
        balance - coalesce(running_total, 0) as current_balance
from
    ctec
    group by  payment_date, balance, amount_paid, running_total
order by
    payment_date  

问题说明 #

上述查询返回结果错误，但把balance - coalesce(running_total, 0)改成1000 - coalesce(running_total, 0)就能得到正确结果。由于无法提前知晓初始余额的固定数值，必须使用表中的balance字段，求解决办法。

解决方案 #

问题根源：除了第一行记录，其余行的balance值都是0，直接用当前行的balance减累计付款额，自然会得到错误结果。正确的做法是获取同一buysell分组下的初始余额（即最早日期的balance值），再减去累计付款额。

修改后的SQL（两种写法任选其一） #

写法1：用MAX()窗口函数获取初始余额

;with ctec as
( 
    select 
        payment_date, 
        balance, 
        amount_paid,
        -- 按buysell分组计算累计付款
        sum(amount_paid) over (partition by buysell order by payment_date) as running_total,
        -- 取分组内最大的balance（即初始余额，其余为0）
        max(balance) over (partition by buysell) as initial_balance
    from  AG
) 

select  
    payment_date, 
    balance, 
    amount_paid, 
    initial_balance - coalesce(running_total, 0) as current_balance
from ctec
order by payment_date  

写法2：用FIRST_VALUE()窗口函数获取初始余额

;with ctec as
( 
    select 
        payment_date, 
        balance, 
        amount_paid,
        sum(amount_paid) over (partition by buysell order by payment_date) as running_total,
        -- 取分组内最早日期的balance值作为初始余额
        first_value(balance) over (partition by buysell order by payment_date) as initial_balance
    from  AG
) 

select  
    payment_date, 
    balance, 
    amount_paid, 
    initial_balance - coalesce(running_total, 0) as current_balance
from ctec
order by payment_date  

说明 #

• 增加partition by buysell确保累计计算和初始余额获取都在同一交易分组内进行，避免不同buysell的数据互相干扰。
• first_value(balance)会直接取分组内排序后的第一条记录的balance，也就是初始余额；max(balance)则利用初始余额是分组内唯一非0值的特性获取初始余额，两种方式都能得到正确结果。

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