主页 / dba / 问题 / 267808
Accepted
lurf jurv
Asked: 2020-05-25 11:17:31 +0800 CST

如何在postgres中以递归方式聚合“多源”？

  • 772

想象一下，我有一个像这样的表“部门”：

name TEXT
parent_department TEXT (nullable)

我也有一个像这样的“预算”表：

department_name TEXT
budget INTEGER

我需要在父部门列之后从“叶到根”“遍历”这个部门表。最终结果是我需要总结每个部门的预算，但问题是我将在任何给定时间使用的“预算”表非常稀疏（10 到 100 行），而部门是非常密集（100m 行）。

通常，我会通过创建一个“递归”CTE 来解决这个问题，该 CTE 将部门关系“扁平化”为一个只有“根部门”、“叶部门”的临时表。然后我会将叶子部门的那个表加入预算，并按根分组。问题是“部门”表现在太大了（我承认，与部门的类比在这里有点分解，没有公司拥有超过 1 亿个部门）。

具体来说，如果我有这样的部门（孩子->父母）：

C -> B
B -> A
A -> NULL
E -> D
D -> NULL
F -> G
G -> NULL

和这样的预算：

A 1
C 3
E 5

我想得到输出：

A 4
D 5

理想情况下，例如，F 和 G 永远不会被触及，因为像 F 和 G 这样的数亿行不应该出现。但在我当前的查询中，他们确实如此。我该如何解决这个问题，以便我只“遍历”我实际需要的部门？

postgresql recursive

1 个回答

  1. Best Answer

    花了一段时间，但我想通了：

    WITH RECURSIVE
initial AS (
    SELECT
        department_name,
        department_name,
        FALSE
    FROM
        budgets
    GROUP BY department_name
),
tmp (child, parent, root) AS (
    SELECT
        *
    FROM
        initial
    UNION ALL
        SELECT
            tmp.child,
            CASE WHEN
                departments.parent_department IS NULL THEN tmp.parent
                ELSE departments.parent_department
            END,
            departments.parent_department IS NULL
        FROM
            tmp
            INNER JOIN departments
                ON tmp.parent = departments.name
        WHERE
            NOT tmp.root
)
SELECT
    tmp.parent,
    SUM(budget)
FROM
    tmp
    INNER JOIN budgets
        ON tmp.child = budgets.department_name
WHERE
    tmp.root
GROUP BY tmp.parent
    • 0

相关问题