Group rows together by 2 column tree connection-CodePudding

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

CodePudding user response：

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