PostgreSQL中的层次求和

Question

这是我在 PostgreSQL 中遇到的一个问题的简化版本。

我有以下表格 A:

[ ID INTEGER | VALUE NUMERIC(10,2) | PARENT INTEGER ]

其中 'PARENT' 是一个指向列 ID 的自引用外键。

表格定义如下：

CREATE TABLE A(ID INTEGER IDENTITY, VALUE NUMERIC(10,2), PARENT INTEGER)                                    
ALTER  TABLE A ADD CONSTRAINT FK FOREIGN KEY (PARENT) REFERENCES A(ID) 

这个简单的表格可以定义任意深度的树形数据结构。现在我需要编写一条 SQL 语句（我不想使用服务器端 PL-SQL），以报告每个节点下“悬挂”子树的总值。例如，对于以下表格：

|  ID  | VALUE | PARENT |
-------------------------
|   1  | NULL  | NULL   |
|   2  | 3.50  |    1   |
|   3  | NULL  | NULL   |
|   4  | NULL  |    3   |
|   5  | 1.50  |    4   |
|   6  | 2.20  |    4   |

我应该得到以下结果集：

| ID  |  Total-Value-of-Subtree |
|  1  |                  3.50   |
|  2  |                  3.50   |
|  3  |                  3.70   |
|  4  |                  3.70   |
|  5  |                  1.50   |
|  6  |                  2.20   |

为简单起见，您可以假设只有叶节点具有值，在VALUE列中，非叶节点始终具有NULL值。是否有一种方法可以在SQL中实现这一点，甚至利用PostgreSQL特定的扩展功能？

- Marcus Junius Brutus

Answer 1

在PostgreSQL中，您可以使用递归CTE（公共表达式）在查询中遍历树。

以下是两个相关的链接到文档：

• 语法
• 示例

编辑 由于不需要子选择，因此在较大数据集上运行时可能比Arion的查询效果更好。

WITH RECURSIVE children AS (
    -- select leaf nodes
    SELECT id, value, parent
        FROM t
        WHERE value IS NOT NULL
    UNION ALL
    -- propagate values of leaf nodes up, adding rows 
    SELECT t.id, children.value, t.parent
        FROM children JOIN t ON children.parent = t.id
)
SELECT id, sum(value) 
    FROM children 
    GROUP BY id   -- sum up appropriate rows
    ORDER BY id;

- A.H.

Answer 2

也许像这样的东西：

WITH RECURSIVE CTE
AS
(
    SELECT
        t.ID,
        t.VALUE,
        t.PARENT
    FROM
        t
    WHERE NOT EXISTS
        (
            SELECT NULL FROM t AS t2 WHERE t2.PARENT=t.ID
        )
    UNION ALL
    SELECT
        t.ID,
        COALESCE(t.VALUE,CTE.VALUE),
        t.PARENT
    FROM
        t
        JOIN CTE
            ON CTE.PARENT=t.ID
)
SELECT
    CTE.ID,
    SUM(CTE.VALUE)
FROM
    CTE
GROUP BY
    CTE.ID
ORDER BY 
    ID;

这将从没有子节点的子级开始。然后向上遍历树到父级。结果会像这样：

1   3.50
2   3.50
3   3.70
4   3.70
5   1.50
6   2.20

- Arion