PostgreSQL – How to Obtain History Ledger with SQL Query

postgresql

Not sure if this is the place to ask but here it goes. I am using a Postgres server and to make things simpler, I'll try to show a brief scenario of what I need.

CREATE TABLE operations(id,type,item,price,amount)
 AS VALUES
  ( 1::int, 'buy' , 'item1',  2, 10::int ),
  ( 2::int, 'buy' , 'item1',  3,  4::int ),
  ( 3::int, 'sell', 'item1',  4, 12::int )
;

  • If I go through a FIFO cost basis the ledger or balances after each order should look like:

    id  |  price   | amount |

1   |      2   |     10 |

2   |      2   |     10 |
    |      3   |      4 |

3   |      3   |      2 |

One thing that I've tried is to have a table like operation_balances in which for each processed operation I insert the corresponding balance. This works however there's a problem with the huge amount of balances that get inserted for large number of operations so it's not a valid solution. I was wondering if it's possible to obtain the balance part for each order as part of a sql query. It may take a little while to run but it would save a lot of database space.

Best Answer

To start, and this is just to get you somewhere,

SELECT *,
  jsonb_agg(jsonb_build_object('p',price,'a',amount))
    FILTER (WHERE type='buy')
    OVER (PARTITION BY item ORDER BY id)
    AS cumpurchased
FROM operations;
 id | type | item  | price | amount |             cumpurchased              
----+------+-------+-------+--------+---------------------------------------
  1 | buy  | item1 |     2 |     10 | [{"a": 10, "p": 2}]
  2 | buy  | item1 |     3 |      4 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]
  3 | sell | item1 |     4 |     12 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]

Here, we serialize the purchases into cumpurchased. This seems simple, now we can blow this up by expanding cumpurchased to the individual transactions.

SELECT id, type, item, price, amount, cumsale, e, eid
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
ORDER BY id, eid;
 id | type | item  | price | amount | cumsale |         e         | eid 
----+------+-------+-------+--------+---------+-------------------+-----
  1 | buy  | item1 |     2 |     10 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 4, "p": 3}  |   2
  3 | sell | item1 |     4 |     12 |      12 | {"a": 10, "p": 2} |   1
  3 | sell | item1 |     4 |     12 |      12 | {"a": 4, "p": 3}  |   2
(5 rows)

You should be ale to see where I'm going with this...

SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
  AS qty_remaining
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid);
 id | type | item  |         e         | eid | qty_remaining 
----+------+-------+-------------------+-----+---------------
  1 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 4, "p": 3}  |   2 |            14
  3 | sell | item1 | {"a": 10, "p": 2} |   1 |            -2
  3 | sell | item1 | {"a": 4, "p": 3}  |   2 |             2
(5 rows)

From this point, you need to filter out the -2, we wrap the query again and put that conditional in there. Then we just select from that.

SELECT id, e->>'p' AS price, least(qty_remaining,(e->>'a')::int) AS amount
FROM (
      SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
      ) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
        AS qty_remaining
      FROM (
        SELECT *,
          sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
          jsonb_agg(jsonb_build_object('p',price,'a',amount))
            FILTER (WHERE type='buy')
            OVER (PARTITION BY item ORDER BY id)
            AS cumpurchased
        FROM operations
      ) AS with_agg
      CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
      ) AS t
WHERE qty_remaining > 0;
 id | price | amount 
----+-------+--------
  1 | 2     |     10
  2 | 2     |     10
  2 | 3     |      4
  3 | 3     |      2
(4 rows)
Related Question