PostgreSQL中的层次求和 [英] hierarchical sum in PostgreSQL

问题描述

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

This is a simplified version of a problem I am encountering in PostgreSQL.

我有下表 A ：

[ID （ID） INTEGER | VALUE NUMERIC（10,2） | PARENT INTEGER ]

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

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

Where 'PARENT' is a self-referencing FK to column 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），该SQL为每个节点报告其下挂的子树的总值。例如，具有下表：

This simple table allows one to define tree data structures of arbitrary depth. Now I need to write a SQL (I prefer not to use server-side PL-SQL) that reports for each node, the total value of the sub-tree "hanging" under it. For instance, with the following table:

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

我应该得到以下结果集：

I should get the following result set:

| 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 的值。即使在利用PostgreSQL特定扩展的情况下，也可以在SQL中执行此操作吗？

For simplicitly, you can assume that only leaf nodes have values, non-leaf nodes always have a value of NULL in the VALUE column. Is there a way to do this in SQL, even utilizing PostgreSQL-specific extensions?

推荐答案

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

In PostgreSQL you can use recursive CTEs (Common Table Expression) to walk trees in your queries.

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

Here are two relevant links into the docs:

• 语法


• < a href = http://www.postgresql.org/docs/current/interactive/queries-with.html rel = noreferrer>示例

• Syntax
• Examples

编辑

由于不需要子选择，因此在更大的数据集上运行可能要好一些Arion的查询。

Since there is no subselect required it might run a little better on a larger dataset than Arion's query.

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;

这篇关于PostgreSQL中的层次求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！