在Oracle中，父节点递归地聚合值

Question

考虑以下示例结构:

<

部门/h3>

• ID
• PARENT_ID
<
• 名称/gh>

项目

• ID
<
• 名称/gh>
• DEPARTMENT_ID

一些数据，只是为了下面的例子:

| ID | PARENT_ID | NAME  | DEPTH |
|----|-----------|-------|-------|
|  1 |      NULL | DEPT1 |     1 |
|  2 |         1 | DEPT2 |     2 |
|  3 |         1 | DEPT3 |     2 |
|  4 |         2 | DEPT4 |     3 |
|  5 |         3 | DEPT5 |     3 |
|  6 |      NULL | DEPT6 |     1 |
|  7 |         6 | DEPT7 |     2 |

|  ID  |  NAME  | COST  | DEPARTMENT_ID |
|------|--------|-------|---------------|
|   1  |  PRJ1  |  100  |      1        |
|   2  |  PRJ2  |  200  |      2        |
|   3  |  PRJ3  |  300  |      3        |
|   4  |  PRJ4  |  400  |      4        |
|   5  |  PRJ5  |  500  |      5        |
|   6  |  PRJ6  |  600  |      6        |
|   7  |  PRJ7  |  700  |      7        |

现在，我需要以某种方式汇总一个部门和它的直接子部门的项目的成本。

如果选择的过滤器是DEPT1，预期的结果是:

| LINE | DEPARTMENT_ID  | PARENT_ID |  NAME  | AGGREGATE_COST |
|------|----------------|-----------|--------|----------------|
|  1   |       1        |   NULL    |  DEPT1 |           1500 |
|  2   |       2        |     1     |  DEPT2 |            600 |
|  3   |       3        |     1     |  DEPT3 |            800 |

地点:

• 第3行聚合为PRJ5 (DEPT5的子节点，DEPT3的子节点)+ PRJ3 (DEPT3的子节点)cost
• 第2行聚合为PRJ4 (DEPT4的子节点，DEPT2的子节点)+ PRJ2 (DEPT2的子节点)cost
• 第一行aggregate是其子的aggregate的总和。
• PRJ6和PRJ7的代价被忽略，因为它们来自DEPT6和DEPT7，并且它们不在DEPT1的层次结构中(DEPT6将是他的兄弟，而不是子)

编辑:

|  ID  |  NAME  | COST  | DEPARTMENT_ID |
|------|--------|-------|---------------|
|   1  |  PRJ1  |   1   |      1        |
|   2  |  PRJ2  |   1   |      1        |
|   3  |  PRJ3  |   1   |      2        |
|   4  |  PRJ4  |   1   |      2        |
|   5  |  PRJ5  |   1   |      4        |

在这种情况下，伊万茨提出的解决方案似乎不起作用。我得到的结果是高级别项目的两倍

如果我得到DEPT1的聚合，它返回类似于这样的内容:

| LINE | DEPARTMENT_ID  | PARENT_ID |  NAME  | AGGREGATE_COST |
|------|----------------|-----------|--------|----------------|
|  1   |       1        |   NULL    |  DEPT1 |              8 |
|  2   |       2        |   NULL    |  DEPT1 |              4 |

Answer 1

您可以使用CONNECT_BY_ROOT层次操作符在层次查询中标记行(以便稍后创建组)。在层次结构查询中，通过将所有行设置为根行来创建每个层次结构组合，以后只获取和聚合指定的组合。对于您的测试数据，这返回您指定的内容。

SELECT ROOT_DEPT AS DEPARTMENT_ID
      ,ROOT_PARENT AS PARENT_ID
      ,ROOT_NAME AS NAME
      ,SUM(COST) AS AGGREGATE_COST
FROM (SELECT COST 
            ,CONNECT_BY_ROOT DEPARTMENT_ID ROOT_DEPT 
            ,CONNECT_BY_ROOT PARENT_ID ROOT_PARENT 
            ,CONNECT_BY_ROOT NAME ROOT_NAME
      FROM (SELECT   B.DEPARTMENT_ID
                    ,NVL(A.PARENT_ID,'0') PARENT_ID
                    ,A.NAME
                    ,SUM(B.COST) COST
            FROM  DEPARTMENT A 
                JOIN PROJECT B
                    ON A.ID = B.DEPARTMENT_ID
            --> GROUP COST OF PROJECTS IN THE SAME DEPARTMENT IF THERE ARE ANY
            GROUP BY B.DEPARTMENT_ID
                    ,NVL(A.PARENT_ID,'0')
                    ,A.NAME
            )
        --> MAKE ALL ROWS ROOT ROWS
      CONNECT BY PRIOR DEPARTMENT_ID = PARENT_ID
    )
WHERE ROOT_DEPT = 1 OR ROOT_PARENT  = 1
GROUP BY  ROOT_DEPT
         ,ROOT_PARENT
         ,ROOT_NAME