如何通过前一行与当前行值相减计算balanceAmount？SQL查询求助

It looks like your query has two key issues causing unexpected results: a syntax error and incorrect balance calculation logic. Let's break this down and fix it step by step.

Issues in the Original Query #

1. Syntax Error: You used over by in the window function clause, which is invalid. The correct syntax to define the sequence for running totals is order by.
2. Incorrect Balance Logic: Your current calculation uses the current row's topup amount minus the sum of all charges up to that point. This doesn't track a running balance—it compares individual topups to cumulative charges, which isn't what you need.

Corrected Query #

To calculate a running balance where each row's balance builds on the previous one (adding topups, subtracting charges), we need to compute the cumulative sum of (topupAmount - chargeAmount) across the ordered rows. Here's the fixed query:

select 
    pp.*, 
    sum(pp.topupAmount - pp.chargeAmount) over (order by pp.ROW_NUM rows unbounded preceding) AS balanceAmount 
from (
    select 
        row_number() over (order by ppc.sortDate) ROW_NUM, 
        ppc.* 
    from (
        select 0 as topupAmount, t1.chargeAmount, t1.sortDate from t1 
        union all 
        select t2.topupAmount, 0 as chargeAmount, t2.sortDate from t2
    ) as ppc 
) as pp 
order by pp.ROW_NUM;

How This Works #

• The inner union combines all charge records (with topupAmount = 0) and topup records (with chargeAmount = 0) into a single list sorted by sortDate.
• We assign row numbers to maintain the correct chronological sequence of transactions.
• The window function sum(pp.topupAmount - pp.chargeAmount) over (...) calculates the cumulative net change (topup minus charge) from the first row up to the current row. This gives you the running balance:
  • For topup rows: adds the topup amount to the previous balance.
  • For charge rows: subtracts the charge amount from the previous balance.

Example Illustration #

Suppose we have these sample transactions:

The corrected query returns this expected result:

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