Postgresql - Sum over partition reset when running total is 0

MikeB
September 23, 2022
65 views
0 votes
2 Answers

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

View on DB Fiddle

Tags: postgresql sql

Answers

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;

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

Fiddle

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Sum over partition reset when running total is 0

Answers