CODEWITHSUNDEEP
databasepostgresql
Msw Tm
Msw Tm

Reputation: 528

Get the difference for a column value by all its previous rows data

Please consider these two tables:

  1. Vault

    • id BIGSERIAL PRIMARY KEY
    • name VARCHAR(30) NOT NULL

  2. Transaction

    • id BIGSERIAL PRIMARY KEY
    • transaction_type VARCHAR(6) NOT NULL -- Choice between "credit" or "debit"
    • amount NUMERIC(15,6) NOT NULL
    • vault_id BIGINT REFERENCES Vault (id)

When getting Transaction row/s, I would like to get an extra column for the remaining vault balance after the transaction is done.

Example of Transaction:

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
5       credit                  400         1               500
12      debit                   200         1               300
20      credit                  100         1               400

I can add a column to hold the vault balance and use that for the next transaction. But suppose the user deletes a row, for example row id 5 which has credit amount of 400 is deleted. Then its following transactions vault balance should change accordingly. So for row 12 the vault balance would be -100, and row id 20's vault balance would be 0.

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
12      debit                   200         1               -100
20      credit                  100         1               0

The way I see it I have two ways:

But I am hoping there is a much better way? Could you please help me what would be most efficient way to handle such situation?

Update

Link to the Table fiddle.

Upvotes: 1

Views: 37

Answers (1)

Belayer
Belayer

Reputation: 14934

There is a better way, at least IMHO. Do not change your table definition, instead create a view on transactions which has the running balance and that column is derived in the view.

    create view transactions_with_bal as 
    select id 
         , transaction_type
         , amount
         , vault_id
         , sum(amount * case when transaction_type = 'credit' then 1 else -1 end )
               over (partition by vault_id order by id) balance 
       from transactions;

Now you do not need any additional processing for DML (delete, insert, update) against the table; no trigger, no additional code, nothing. Also you avoid race conditions when multiple users issue DML against it at the same time. It even handles the case where the column vault_id changes.

Upvotes: 2

Related Questions