Group rows together by 2 column tree connection

user_1000343 0
Viewed 45

I have the table below where there is an amount that can be charged or credited. Each row Each one of these entries gets an id. Now you can undo a charge by giving a credit in exactly the same amount and reversing that/. If that's the case the reversed_by field will tell which id will flip it. But you can reverse a reverse as many times as you want. So for example, id 8fa22925 was never reversed and should be in a group by itself (4). 4e35510a was reversed by c362b6c5 and the reversing stopped so they make group 3. beca097b was reversed by dc102220 but in turn this was reversed by 84529ee0. I know that id and reversed_by form a simple tree structure in 2 column format.

How can I use Postgres to add grouping to all connections as shown in my desired output below?

I don't care what the group is...could be int, varchar, original/last id, etc.

Schema

CREATE TABLE delete_me (
    amount Int,
    id varchar(255),
    reversed_by varchar(255)
);

INSERT INTO delete_me
VALUES
( 12,'ea75921c', NULL     ),
(-12,'e72d92d9','ea75921c'),
(-12,'84529ee0', NULL     ),
( 12,'dc102220','84529ee0'),
(-12,'beca097b','dc102220'),
(  8,'c362b6c5', NULL     ),
( -8,'4e35510a','c362b6c5'),
( 10,'8fa22925', NULL     )

Query #1

SELECT * FROM delete_me;
amount id reversed_by
12 ea75921c
-12 e72d92d9 ea75921c
-12 84529ee0
12 dc102220 84529ee0
-12 beca097b dc102220
8 c362b6c5
-8 4e35510a c362b6c5
10 8fa22925

Desired Output

amount id reversed_by group
12 ea75921c 1
-12 e72d92d9 ea75921c 1
-12 84529ee0 2
12 dc102220 84529ee0 2
-12 beca097b dc102220 2
8 c362b6c5 3
-8 4e35510a c362b6c5 3
10 8fa22925 4

View on DB Fiddle

postgresql sql
1 Answers

What about using the root of the "reversing tree" as the "group indicator"?

with recursive cte as (
  select dm.*, id as root, 1 as level
  from delete_me dm
  where dm.reversed_by is null
  union all
  select ch.*, cte.root, cte.level + 1
  from delete_me ch
    join cte on cte.id = ch.reversed_by
)
select *
from cte
order by root, level

This returns the following result based on your sample data:

amount | id       | reversed_by | root     | level
-------+----------+-------------+----------+------
   -12 | 84529ee0 |             | 84529ee0 |     1
    12 | dc102220 | 84529ee0    | 84529ee0 |     2
   -12 | beca097b | dc102220    | 84529ee0 |     3
    10 | 8fa22925 |             | 8fa22925 |     1
     8 | c362b6c5 |             | c362b6c5 |     1
    -8 | 4e35510a | c362b6c5    | c362b6c5 |     2
    12 | ea75921c |             | ea75921c |     1
   -12 | e72d92d9 | ea75921c    | ea75921c |     2

Online example

user_3303150
Related