Рекурсивная вставка с использованием предложения connect by

У меня есть иерархические данные (справа) в таблице следующим образом, который создает иерархию, как показано слева. Столы хранятся в оракуле 11г.

Таблица дерева иерархии дерева  
-------------- Элемент Родитель
                        ------ ------
P0 P0  
    P1 P1 P0
        P11 P2 P0
            C111 P11 P1
            C112 P12 P1
        P12 P21 P2
            C121 P22 P2
            C122 C111 P11
    P2 C112 P11
        P21 C121 P12
            C211 C122 P12
            C212 C211 P21
        P22 C212 P21
            C221 C221 P22
            C222 C222 P22

Моя таблица данных имеет следующие значения. Он содержит значения для всех конечных узлов.
Таблица данных

Значение элемента  
C111 3  
C112 3  
C121 3  
C122 3  
C211 3  
С212 3  
C221 3  
C222 3  
P11 6  

Мне нужно сгенерировать оператор вставки, предпочтительно один оператор вставки, который будет вставлять строки в таблицу данных на основе суммы значений дочерних элементов. Обратите внимание, что нам нужно рассчитать сумму только для тех родителей, чье значение отсутствует в таблице данных.

Таблица данных (ожидается после вставки)

Значение элемента
C111 3
C112 3
C121 3
C122 3
C211 3
С212 3
C221 3
C222 3
P11 6

- Ряды для вставки
P12 6
P21 6
P22 6
P1 12
P2 12
P0 24

2 ответа

Решение

Если все конечные узлы находятся на одной высоте (здесь lvl=4), вы можете написать простой запрос CONNECT BY с ROLLUP:

SQL> SELECT lvl0,
  2         regexp_substr(path, '[^/]+', 1, 2) lvl1,
  3         regexp_substr(path, '[^/]+', 1, 3) lvl2,
  4         SUM(VALUE) sum_value
  5    FROM (SELECT sys_connect_by_path(t.element, '/') path,
  6                 connect_by_root(t.element) lvl0,
  7                 t.element, d.VALUE, LEVEL lvl
  8             FROM tree t
  9             LEFT JOIN DATA d ON d.element = t.element
 10            START WITH t.PARENT IS NULL
 11           CONNECT BY t.PARENT = PRIOR t.element)
 12   WHERE VALUE IS NOT NULL
 13     AND lvl = 4
 14   GROUP BY lvl0, ROLLUP(regexp_substr(path, '[^/]+', 1, 2),
 15                         regexp_substr(path, '[^/]+', 1, 3));

LVL0 LVL1  LVL2   SUM_VALUE
---- ----- ----- ----------
P0   P1    P11            6
P0   P1    P12            6
P0   P1                  12
P0   P2    P21            6
P0   P2    P22            6
P0   P2                  12
P0                       24

Вставка будет выглядеть так:

INSERT INTO data (element, value) 
(SELECT coalesce(lvl2, lvl1, lvl0), sum_value
   FROM <query> d_out
  WHERE NOT EXISTS (SELECT NULL
                      FROM data d_in
                     WHERE d_in.element = coalesce(lvl2, lvl1, lvl0)));

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

В этом случае вы можете использовать древовидную структуру в самостоятельном соединении:

SQL> WITH HIERARCHY AS (
  2     SELECT t.element, path, VALUE
  3       FROM (SELECT sys_connect_by_path(t.element, '/') path,
  4                    connect_by_isleaf is_leaf, ELEMENT
  5                FROM tree t
  6               START WITH t.PARENT IS NULL
  7              CONNECT BY t.PARENT = PRIOR t.element) t
  8       LEFT JOIN DATA d ON d.element = t.element
  9                       AND t.is_leaf = 1
 10  )
 11  SELECT h.element, SUM(elements.value)
 12    FROM HIERARCHY h
 13    JOIN HIERARCHY elements ON elements.path LIKE h.path||'/%'
 14   WHERE h.VALUE IS NULL
 15   GROUP BY h.element
 16   ORDER BY 1;

ELEMENT SUM(ELEMENTS.VALUE)
------- -------------------
P0                       24
P1                       12
P11                       6
P12                       6
P2                       12
P21                       6
P22                       6

Вот еще один вариант с использованием предложения SQL MODEL. Я взял некоторые подсказки из того, что Винсент сделал в своем ответе (использование regexp_subsr), чтобы упростить мой код.

Первая часть предложения WITH просто перенастраивает данные и извлекает иерархию на каждом уровне.

Предложение модели в конце запроса выводит данные с самых низких уровней. Для этого потребуется добавить дополнительные столбцы, если имеется более четырех уровней, но они должны работать независимо от того, на каком уровне находятся значения.

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

with my_hierarchy_data as (
select 
    element,
    value, 
    path, 
    parent,
    lvl0,
    regexp_substr(path, '[^/]+', 1, 2) as lvl1,
    regexp_substr(path, '[^/]+', 1, 3) as lvl2,
    regexp_substr(path, '[^/]+', 1, 4) as lvl3
from ( 
  select 
    element,
    value, 
    parent,
    sys_connect_by_path(element, '/') as path, 
    connect_by_root element as lvl0
  from 
    tree
    left outer join data using (element)
  start with parent is null
  connect by prior element = parent
  order siblings by element
  )
)
select 
    element,
    value, 
    path, 
    parent,
    new_value,
    lvl0, 
    lvl1, 
    lvl2, 
    lvl3
from my_hierarchy_data
model
return all rows
partition by (lvl0)
dimension by (lvl1, lvl2, lvl3)
measures(element, parent, value, value as new_value, path)
rules sequential order (
    new_value[lvl1, lvl2, null] = sum(value)[cv(lvl1), cv(lvl2), lvl3 is not null],
    new_value[lvl1, null, null] = sum(new_value)[cv(lvl1), lvl2 is not null, null],
    new_value[null, null, null] = sum(new_value)[lvl1 is not null, null, null]
)

Вы можете использовать оператор вставки:

INSERT INTO data (elelment, value)
select element, newvalue
from <the_query>
where value is null;
Другие вопросы по тегам