如何在PostgreSQL中为树形结构表计算子节点累计成本？

现有通过递归查询生成的树形结构工作类型表，需新增CUMULATIVE_COST列，用于计算当前节点所有嵌套子节点的cost总和。

原始递归查询（获取树形结构） #

WITH RECURSIVE rel_rec AS (
    SELECT
            1 AS depth,
            *,
            ARRAY[lvl] AS child_path
        FROM types_of_work
        WHERE parent_id IS NULL AND project_id = 14

        UNION ALL
        SELECT
            nlevel(r.path) + 1,
            n.*,
            r.child_path || n.lvl
        FROM rel_rec AS r
        JOIN types_of_work AS n ON n.parent_id = r.tow_id
        WHERE r.project_id = 14
)
SELECT
    t0.tow_id,
    t0.project_id,
    t0.path,
    t0.child_path,
    t0.lvl,
    t0.parent_id,
    t0.depth,
    t2.cost AS tow_cost
FROM rel_rec t0
LEFT JOIN (
    SELECT
        tow_id,
        cost
    FROM tows_contract
    WHERE contract_id = 10
) AS t2 ON t0.tow_id = t2.tow_id
ORDER BY t0.child_path, t0.lvl;

最小可复现示例 #

建表语句 #

CREATE TABLE types_of_work (
    tow_id integer,
    path ltree,
    lvl integer,
    project_id integer,
    parent_id  integer
);
CREATE TABLE tows_contract (
    tow_id integer,
    cost integer,
    contract_id integer
);

插入测试数据 #

INSERT INTO types_of_work (tow_id, path, project_id, lvl, parent_id)
VALUES 
    (39, 'root', 14, 1, null),
    (40, 'root.39', 14, 2, 39),
    (131, 'root.39', 14, 3, 39),
    (41, 'root.39', 14, 5, 39),
    (46, 'root.39', 14, 5, 39),
    (47, 'root.39.46', 14, 6, 46),
    (48, 'root.39.46.47', 14, 7, 47),
    (134, 'root.39.46.47.48',  14, 8, 48),
    (49, 'root.39.46.47', 14, 9, 47),
    (125, 'root.39.46.47', 14, 10, 47),
    (132, 'root.39.46', 14, 11, 46),
    (133, 'root.39.46.132', 14, 12, 132),
    (135, 'root.39.46.132', 14, 13, 132),
    (136, 'root.39.46', 14, 14, 46),
    (657, 'root.39.46.132', 14, 15, 136),
    (142, 'root', 14, 16, null),
    (143, 'root.142', 14, 17, 142),
    (178, 'root.142.143', 14, 18, 143),
    (146, 'root.142.143', 14, 19, 143),
    (147, 'root.142.143', 14, 20, 143),
    (42, 'root', 14, 21, null),
    (43, 'root.42', 14, 22, 42),
    (45, 'root.42.43',  14, 23, 43),
    (671, 'root.42.43.45', 14, 24, 45),
    (672, 'root.42.43.45.671', 14, 25, 671);

INSERT INTO tows_contract (tow_id, cost, contract_id)
VALUES 
    (40, 10, 10),
    (131, 10, 10),
    (41, 10, 10),
    (134, 10, 10),
    (49, 10, 10),
    (125, 10, 10),
    (133, 10, 10),
    (135, 10, 10),
    (657, 10, 10),
    (178, 10, 10),
    (146, 10, 10),
    (147, 10, 10),
    (672, 10, 10);

解决方案：添加累计成本列 #

方法1：利用ltree路径关联计算 #

借助PostgreSQL的ltree类型特性，直接匹配当前节点的所有后代子节点并求和：

WITH RECURSIVE rel_rec AS (
    SELECT
            1 AS depth,
            *,
            ARRAY[lvl] AS child_path
        FROM types_of_work
        WHERE parent_id IS NULL AND project_id = 14

        UNION ALL
        SELECT
            nlevel(r.path) + 1,
            n.*,
            r.child_path || n.lvl
        FROM rel_rec AS r
        JOIN types_of_work AS n ON n.parent_id = r.tow_id
        WHERE r.project_id = 14
),
tree_with_cost AS (
    SELECT
        t0.tow_id,
        t0.project_id,
        t0.path,
        t0.child_path,
        t0.lvl,
        t0.parent_id,
        t0.depth,
        COALESCE(t2.cost, 0) AS tow_cost
    FROM rel_rec t0
    LEFT JOIN (
        SELECT tow_id, cost
        FROM tows_contract
        WHERE contract_id = 10
    ) AS t2 ON t0.tow_id = t2.tow_id
)
SELECT
    *,
    (SELECT SUM(tow_cost) 
     FROM tree_with_cost child
     WHERE child.path <@ parent.path 
       AND child.tow_id != parent.tow_id) AS cumulative_cost
FROM tree_with_cost parent
ORDER BY parent.child_path, parent.lvl;

逻辑说明：

• 用COALESCE将空成本转为0，避免求和时出现NULL
• 通过child.path <@ parent.path判断子节点是否为当前节点的后代
• 排除当前节点自身，对所有子节点的成本求和

方法2：反向递归累加（大数据量更高效） #

从叶子节点向上递归，逐层累加子节点的成本：

WITH RECURSIVE rel_rec AS (
    SELECT
            1 AS depth,
            *,
            ARRAY[lvl] AS child_path
        FROM types_of_work
        WHERE parent_id IS NULL AND project_id = 14

        UNION ALL
        SELECT
            nlevel(r.path) + 1,
            n.*,
            r.child_path || n.lvl
        FROM rel_rec AS r
        JOIN types_of_work AS n ON n.parent_id = r.tow_id
        WHERE r.project_id = 14
),
tree_with_cost AS (
    SELECT
        t0.tow_id,
        t0.project_id,
        t0.path,
        t0.child_path,
        t0.lvl,
        t0.parent_id,
        t0.depth,
        COALESCE(t2.cost, 0) AS tow_cost
    FROM rel_rec t0
    LEFT JOIN (
        SELECT tow_id, cost
        FROM tows_contract
        WHERE contract_id = 10
    ) AS t2 ON t0.tow_id = t2.tow_id
),
cumulative_cost_rec AS (
    -- 初始：叶子节点无嵌套子节点，累计成本为0
    SELECT 
        twc.*,
        0::integer AS cumulative_cost
    FROM tree_with_cost twc
    WHERE NOT EXISTS (
        SELECT 1 FROM types_of_work tow WHERE tow.parent_id = twc.tow_id
    )

    UNION ALL

    -- 向上递归：父节点累计成本 = 所有子节点的自身成本 + 子节点的累计成本之和
    SELECT
        parent.*,
        SUM(child.tow_cost + child.cumulative_cost) AS cumulative_cost
    FROM tree_with_cost parent
    JOIN cumulative_cost_rec child ON parent.tow_id = child.parent_id
    GROUP BY parent.tow_id, parent.project_id, parent.path, parent.child_path, parent.lvl, parent.parent_id, parent.depth, parent.tow_cost
)
SELECT *
FROM cumulative_cost_rec
ORDER BY child_path, lvl;

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