如何根据交易类型对SQL查询中的当前余额进行加减计算？

Alright, let's work through how to calculate the running balance based on your transaction types and initial opening balance. Your current query pulls the transaction details, but we need to extend it to compute the updated balance after each transaction step-by-step.

First, let's anchor the core logic: we’ll start with your specified openingbalance, then adjust it incrementally for each transaction—adding units for credit transactions, subtracting for debit transactions (we can tweak this to fit your three specific scenarios later).

Modified Query with Running Balance Calculation #

SELECT 
    track_no,
    -- Simplify getting transaction type with COALESCE (does the same as your original CASE)
    COALESCE(credit_type, debit_type) AS transaction_type,
    COALESCE(credit_units, debit_units) AS units,
    track_dt,
    -- Your fixed opening balance from the specified timestamp
    (SELECT prv_free_units 
     FROM benf_ewr_track 
     WHERE track_dt = '28-JUN-17 04.51.17.291000000 PM') AS openingbalance,
    -- Calculate the net change for each transaction (positive for credit, negative for debit)
    CASE 
        WHEN credit_type IS NOT NULL THEN COALESCE(credit_units, 0)
        WHEN debit_type IS NOT NULL THEN -COALESCE(debit_units, 0)
        ELSE 0 -- Fallback for edge cases where neither type exists
    END AS transaction_change,
    -- Compute current balance: opening balance + sum of all prior transaction changes
    (SELECT prv_free_units 
     FROM benf_ewr_track 
     WHERE track_dt = '28-JUN-17 04.51.17.291000000 PM') 
     + SUM(
         CASE 
             WHEN credit_type IS NOT NULL THEN COALESCE(credit_units, 0)
             WHEN debit_type IS NOT NULL THEN -COALESCE(debit_units, 0)
             ELSE 0
         END
     ) OVER (ORDER BY track_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_balance
FROM benf_ewr_track 
WHERE track_dt BETWEEN TO_DATE('2017/06/28', 'yyyy/mm/dd') 
                    AND TO_DATE('2017/06/28', 'yyyy/mm/dd') -- Fill in your end date here
ORDER BY track_dt;

Breakdown of Key Changes: #

• COALESCE for cleaner code: This replaces your nested CASE statements for transaction_type and units—it grabs the first non-null value from the two columns, which works exactly like your original logic but is more concise.
• transaction_change: Converts each transaction into a positive or negative value that we can sum up. This is where you’ll adjust for your three scenarios—just add more WHEN clauses if certain credit/debit types need different math (e.g., a specific credit type that subtracts units instead of adding).
• Window function for running balance: The SUM() OVER (...) clause calculates the total of all transaction_change values up to the current transaction. Adding this to your fixed opening balance gives you the updated current_balance after each step.
• Chronological ordering: We use ORDER BY track_dt to ensure transactions are processed in the right order—this is critical for accurate balance calculations (if multiple transactions share the same timestamp, add track_no to the ORDER BY to avoid inconsistencies).

Tweaking for Your Three Specific Scenarios #

If you have three distinct rules for when to add/subtract units (e.g., different credit types have opposite effects), expand the CASE statement in transaction_change like this:

CASE 
    WHEN credit_type = 'REWARD' THEN COALESCE(credit_units, 0) -- Add units for reward credits
    WHEN credit_type = 'ADJUSTMENT' THEN -COALESCE(credit_units, 0) -- Subtract for adjustment credits
    WHEN debit_type = 'PURCHASE' THEN -COALESCE(debit_units, 0) -- Subtract for purchase debits
    ELSE 0
END AS transaction_change

Just swap out the type values and signs to match your exact business rules.

Optimization Tip #

The subquery for openingbalance runs twice in the initial query. For better performance (especially with large tables), use a CTE to compute it once:

WITH opening_balance AS (
    SELECT prv_free_units AS initial_balance
    FROM benf_ewr_track 
    WHERE track_dt = '28-JUN-17 04.51.17.291000000 PM'
)
SELECT 
    bt.track_no,
    COALESCE(bt.credit_type, bt.debit_type) AS transaction_type,
    COALESCE(bt.credit_units, bt.debit_units) AS units,
    bt.track_dt,
    ob.initial_balance AS openingbalance,
    CASE 
        WHEN bt.credit_type IS NOT NULL THEN COALESCE(bt.credit_units, 0)
        WHEN bt.debit_type IS NOT NULL THEN -COALESCE(bt.debit_units, 0)
        ELSE 0
    END AS transaction_change,
    ob.initial_balance 
    + SUM(
        CASE 
            WHEN bt.credit_type IS NOT NULL THEN COALESCE(bt.credit_units, 0)
            WHEN bt.debit_type IS NOT NULL THEN -COALESCE(bt.debit_units, 0)
            ELSE 0
        END
    ) OVER (ORDER BY bt.track_dt) AS current_balance
FROM benf_ewr_track bt
CROSS JOIN opening_balance ob
WHERE bt.track_dt BETWEEN TO_DATE('2017/06/28', 'yyyy/mm/dd') 
                      AND TO_DATE('2017/06/28', 'yyyy/mm/dd')
ORDER BY bt.track_dt;

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