Running balance of a ledger with aggregated balances



  • Imagine I have a ledger where I write id, event_id, quantity, datetime and I want to get up to date balances of the quantity grouped by event_id. The goal is to get the aggregated quantity per event_id quickly.

    id event_id quantity dateTime
    1 1000 3 timestamp
    2 1000 -2 timestamp
    3 2000 5 timestamp
    4 1000 3 timestamp
    5 2000 2 timestamp

    However if each event has a new record added every 1 second, and I want to regularly query to get the latest balance, how would you keep this performant? Summing potentially 36,000 records per day for a period of several weeks surely isn't a good option.

    I figure I could roll up the balances of each event_id on some schedule and delete those events based on date time. This poses a several issues in itself;

    1. I lose the full history of events (I could keep all events in an additional table for this purpose)
    2. I might introduce another set of performance issues with querying/delete records on an already busy table
    3. I need some scheduled job to maintain records periodically, I assume I could use a stored procedure to choose certain event_id's to tidy up.

    I know this is a highly abstract question, however I don't personally have the knowledge and experience with SQL to come up with a better design.



  • Maybe you are looking for an assignment operator query: I used table name item_data

    SELECT *, @running_qty:=@running_qty+quantity AS runningQty FROM (SELECT * FROM item_data i) a JOIN (SELECT @running_qty:=0)rn GROUP BY event_id




Suggested Topics

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