I have a query that is taking transactions (buying and selling of items) to calculate the gain/loss when the running total resets back to 0.
The fiddle is here: https://www.db-fiddle.com/f/974UVvE6id2rEiBPR78CKx/0
The units of each item can be added and subtracted and each time they come back to 0 for a account and item combination we want to calculate the net result of those transactions.
You can see it working in the fiddle for the first few (when open = 0), however it fails if there are multiple transactions before getting to 0 (eg 1 increment, 2 separate decrements of units).
From this data:
INSERT INTO t
(account, item, units, price, created_at)
VALUES
(2, 'A', -1, '$120.00', '2022-09-23 17:33:07'),
(2, 'A', 1, '$110.00', '2022-09-23 17:34:31'),
(1, 'B', -1, '$70.00', '2022-09-23 17:38:31'),
(1, 'B', 1, '$50.00', '2022-09-23 17:36:31'),
(1, 'B', 2, '$50.00', '2022-09-23 17:40:31'),
(1, 'B', -1, '$60.00', '2022-09-23 17:41:31'),
(1, 'B', -1, '$70.00', '2022-09-23 17:42:31'),
(1, 'B', 1, '$50.00', '2022-09-23 17:35:31'),
(1, 'B', -1, '$60.00', '2022-09-23 17:33:31'),
(2, 'B', 1, '$70.00', '2022-09-23 17:43:31'),
(2, 'B', 1, '$75.00', '2022-09-23 17:45:31'),
(2, 'B', -2, '$80.00', '2022-09-23 17:46:31')
;
I need to produce this result (net is the relevant column which we cannot get right in the fiddle, it shows incorrect values for the last two net values):
account | item | units | price | created_at | open | cost | net |
---|---|---|---|---|---|---|---|
2 | A | -1 | $120.00 | 2022-09-23T17:33:07.000Z | -1 | $120.00 | |
1 | B | -1 | $60.00 | 2022-09-23T17:33:31.000Z | -1 | $60.00 | |
2 | A | 1 | $110.00 | 2022-09-23T17:34:31.000Z | 0 | -$110.00 | $10.00 |
1 | B | 1 | $50.00 | 2022-09-23T17:35:31.000Z | 0 | -$50.00 | $10.00 |
1 | B | 1 | $50.00 | 2022-09-23T17:36:31.000Z | 1 | -$50.00 | |
1 | B | -1 | $70.00 | 2022-09-23T17:38:31.000Z | 0 | $70.00 | $20.00 |
1 | B | 2 | $50.00 | 2022-09-23T17:40:31.000Z | 2 | -$100.00 | |
1 | B | -1 | $60.00 | 2022-09-23T17:41:31.000Z | 1 | $60.00 | |
1 | B | -1 | $70.00 | 2022-09-23T17:42:31.000Z | 0 | $70.00 | $30.00 |
2 | B | 1 | $70.00 | 2022-09-23T17:43:31.000Z | 1 | -$70.00 | |
2 | B | 1 | $75.00 | 2022-09-23T17:45:31.000Z | 2 | -$75.00 | |
2 | B | -2 | $80.00 | 2022-09-23T17:46:31.000Z | 0 | $160.00 | $15.00 |
CodePudding user response:
We start by establishing cost
and every time the running total is 0. By using lag
and count
we make groups out of every run that leads to zero divided by account
and item
. We use the groups we just created and find the running total of cost
, but only display the result when our original running_total
= 0.
select account
,item
,units
,price
,created_at
,running_total as open
,cost
,case running_total when 0 then sum(cost) over(partition by account, item, grp order by created_at) end as net
from
(
select *
,count(mark_0) over(partition by account, item order by created_at) as grp
from (
select *
,case when lag(running_total) over(partition by account, item order by created_at) = 0 then 1 when lag(running_total) over(partition by account, item order by created_at) is null then 1 end as mark_0
from (
select *
,sum(units) over(partition by account, item order by created_at) as running_total
,price*units*-1 as cost
from t
) t
) t
) t
order by created_at
account | item | units | price | created_at | open | cost | net |
---|---|---|---|---|---|---|---|
2 | A | -1 | 120.00 | 2022-09-23 17:33:07 01 | -1 | 120.00 | null |
1 | B | -1 | 60.00 | 2022-09-23 17:33:31 01 | -1 | 60.00 | null |
2 | A | 1 | 110.00 | 2022-09-23 17:34:31 01 | 0 | -110.00 | 10.00 |
1 | B | 1 | 50.00 | 2022-09-23 17:35:31 01 | 0 | -50.00 | 10.00 |
1 | B | 1 | 50.00 | 2022-09-23 17:36:31 01 | 1 | -50.00 | null |
1 | B | -1 | 70.00 | 2022-09-23 17:38:31 01 | 0 | 70.00 | 20.00 |
1 | B | 2 | 50.00 | 2022-09-23 17:40:31 01 | 2 | -100.00 | null |
1 | B | -1 | 60.00 | 2022-09-23 17:41:31 01 | 1 | 60.00 | null |
1 | B | -1 | 70.00 | 2022-09-23 17:42:31 01 | 0 | 70.00 | 30.00 |
2 | B | 1 | 70.00 | 2022-09-23 17:43:31 01 | 1 | -70.00 | null |
2 | B | 1 | 75.00 | 2022-09-23 17:45:31 01 | 2 | -75.00 | null |
2 | B | -2 | 80.00 | 2022-09-23 17:46:31 01 | 0 | 160.00 | 15.00 |
CodePudding user response:
You can use a recursive cte
, building up the results row by row, using a JSON
object to store running open
and cost
values for every unique item
:
with recursive transactions as (
select row_number() over (order by t1.created_at) id, t1.* from t t1
order by t1.created_at
),
cte(id, account, item, unit, price, created_at, open, cost, net, p) as (
select t.*, t.unit, -1*t.price*t.unit, 0, (select jsonb_object_agg(t1.item,
jsonb_build_object('u', 0, 'c', 0)) from transactions t1)||jsonb_build_object(t.item,
jsonb_build_object('u', t.unit, 'c', -1*t.price*t.unit))
from transactions t where t.id = 1
union all
select t.*, (c.p -> t.item -> 'u')::int t.unit, -1*t.price*t.unit,
case when (c.p -> t.item -> 'u')::int t.unit = 0
then (c.p -> t.item -> 'c')::int -1*t.price*t.unit else 0 end,
c.p || jsonb_build_object(t.item, jsonb_build_object('u', (c.p -> t.item -> 'u')::int t.unit, 'c',
case when (c.p -> t.item -> 'u')::int t.unit = 0 then 0
else (c.p -> t.item -> 'c')::int -1*t.price*t.unit end))
from cte c join transactions t on t.id = c.id 1
)
select account, item, unit, price, created_at,
open, cost, case when net > 0 then net end
from cte;