Рекурсивно агрегировать значения по родителям в Oracle

Question

Рассмотрим следующую примерную структуру:

ОТДЕЛ

• Я БЫ
• PARENT_ID
• НАЗВАНИЕ
• ГЛУБИНА

ПРОЕКТ

• Я БЫ
• НАЗВАНИЕ
• СТОИМОСТЬ
• 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).
• Совокупность строки 2 - это PRJ4 (от DEPT4, которая является дочерней по отношению к DEPT2) + PRJ2 (от DEPT2).
• Строка 1 агрегат является суммой его детских агрегатов.
• Затраты на 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        |

В этом сценарии решение, представленное ivanzg, похоже, не работает. Я получаю удвоенные результаты для проектов в высших рангах

Если я получу агрегат для 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