PostgreSQL中的层次求和

admin

PostgreSQL中的层次求和

sql

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

我有下表 A ：

[ID INTEGER | 数值 NUMERIC（10,2） | 父 整数 ]

其中“ PARENT”是列ID的自引用FK。

该表的定义是：

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   |

对于simplicitly，你可以假设，只有叶节点有值，非叶节点总是有一个值 NULL 的值
列。有没有办法在SQL中做到这一点，甚至利用PostgreSQL特定的扩展？

阅读 290

2021-05-10

共1个答案

admin

在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;

2021-05-10