Postgres中基于id与reversed_by树形关联的行分组实现

现有一张表delete_me，其中amount字段表示收费或冲销金额，每行记录对应唯一id；可通过等额冲销撤销收费，此时reversed_by字段记录被冲销的目标id，且冲销操作支持嵌套，id与reversed_by形成树形关联结构。例如：

• id 8fa22925从未被冲销，需单独成组；
• id 4e35510a被c362b6c5冲销后无后续操作，二者同组；
• id beca097b被dc102220冲销，而dc102220又被84529ee0冲销，三者同组。

请使用PostgreSQL实现将所有关联记录分组，分组标识可为整数、字符串等任意类型。

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     )

可以通过**递归CTE（Common Table Expression）**追溯每条记录的最顶层根节点（即reversed_by为NULL的节点），再根据根节点对记录分组，最后生成组编号。具体SQL如下：

WITH RECURSIVE record_groups AS (
    -- 锚点：所有顶层节点（无被冲销记录），根节点为自身id
    SELECT 
        amount,
        id,
        reversed_by,
        id AS root_id
    FROM delete_me
    WHERE reversed_by IS NULL
    
    UNION ALL
    
    -- 递归：关联子节点，继承父节点的根id
    SELECT 
        dm.amount,
        dm.id,
        dm.reversed_by,
        rg.root_id
    FROM delete_me dm
    JOIN record_groups rg ON dm.reversed_by = rg.id
)
SELECT 
    amount,
    id,
    reversed_by,
    -- 根据根节点生成连续的组编号
    DENSE_RANK() OVER (ORDER BY root_id) AS "group"
FROM record_groups
ORDER BY "group", id;

逻辑说明 #

1. 递归CTE record_groups：
   • 锚点查询先找出所有没有被冲销的顶层记录（reversed_by IS NULL），将它们的root_id设为自身id。
   • 递归查询通过reversed_by关联子节点，把父节点的root_id传递给子节点，这样所有关联的记录都会共享同一个root_id。
2. 生成组编号：使用DENSE_RANK()函数，根据root_id排序后生成连续的整数组号，确保同一根节点下的记录属于同一组。

执行上述SQL后，即可得到符合期望的分组结果。

内容的提问来源于stack exchange，提问作者Tyler Rinker