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:

    • Add a column to store the vault balance in a transaction, and if there is a change, all of its following transaction's vault balance should be updated accordingly.
    • Maybe there's a way to get the difference of credit and debit between all of its previous and the current transaction amount.

    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

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2e3312565b94473184dc14e4d5daefff to the Table fiddle.



  • Something like this, may be?

    sum(case transaction_type when 'debit' then -1 else 1 end * amount)
    over (partition by vault_id order by id rows unbounded preceding and current row)
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2