Уровень иерархии таблиц Oracle и группировка

У меня есть база данных оракула, в которой включен внешний ключ, и у меня есть таблицы A, B, C, D, E, F, AB, ABC, EF. Прикрепление изображения для иерархии «B» имеет родительский «D», «AB» имеет родительский «A», а также «B», «ABC» имеет родительские элементы «AB» и «C», «EF» имеет родительские элементы «E» и «F»,

введите здесь описание изображения

Теперь {A,B,C,D,AB, ABC} не имеет связи с {E,F,EF}. у меня 2 требования

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

введите здесь описание изображения

Как я могу сделать это, используя sql/pl/sql в базе данных оракула?


sql oracle hierarchical-query
person user3016635    schedule 05.03.2021    source источник
comment
Прежде чем мы зайдем слишком далеко — в первой части требуется найти связанные компоненты в графе. Узлы графа в вашем случае — это таблицы, а ребра графа (стрелки, ссылки) — реляционные ограничения. Это распространенная проблема; SQL довольно быстро задыхается от этого (в большинстве случаев он не может работать с графами с более чем 30-40 узлами за любое разумное время). Oracle предлагает пакеты, решающие эту проблему. Я предложил решение, которое работает намного быстрее, чем решение Oracle, здесь: community.oracle.com/tech/developers/discussion/4344746/   -  person mathguy    schedule 05.03.2021
comment
В самом общем случае вторая часть вашего вопроса не имеет смысла. Ссылочные ограничения могут иметь циклы (например: в таблице городов есть столбец страна, в таблице стран есть столбец столица, ссылающийся на таблицу городов. Итак, в этом случае, какой уровень является первым, а какой вторым? И ссылки может быть немного сложнее. Например: таблица A ссылается как на B, так и на C, а также на таблицу B ссылается на C. Таблица C — это уровень 1, а таблица B — это уровень 2, но какой это уровень A? потому что он ссылается на C) или 3 (потому что он также ссылается на B)?   -  person mathguy    schedule 05.03.2021

Ответы (1)


arrow_upward
0
arrow_downward

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

WITH groups ( child, parent, grp ) AS (
  SELECT child,
         parent,
         DENSE_RANK() OVER ( ORDER BY CONNECT_BY_ROOT( child ) )
  FROM   table_name t
  START WITH child NOT IN ( SELECT parent FROM table_name )
  CONNECT BY PRIOR parent = child
),
depths ( child, parent, grp, depth ) AS (
  SELECT child, parent, grp, LEVEL
  FROM   groups
  START WITH parent NOT IN ( select child FROM table_name )
  CONNECT BY PRIOR child = parent
)
SELECT table_name,
       grp,
       MAX( depth + depth_modifier ) AS depth
FROM   depths
UNPIVOT ( table_name FOR depth_modifier IN ( child AS 1, parent AS 0 ) )
GROUP BY grp, table_name
ORDER BY grp, depth, table_name

Что для ваших образцов данных:

CREATE TABLE table_name ( child, parent ) AS
SELECT 'B',   'D'  FROM DUAL UNION ALL
SELECT 'AB',  'A'  FROM DUAL UNION ALL
SELECT 'AB',  'B'  FROM DUAL UNION ALL
SELECT 'ABC', 'AB' FROM DUAL UNION ALL
SELECT 'ABC', 'C'  FROM DUAL UNION ALL
SELECT 'EF',  'E'  FROM DUAL UNION ALL
SELECT 'EF',  'F'  FROM DUAL;

Выходы:

TABLE_NAME | GRP | DEPTH
:--------- | --: | ----:
A          |   1 |     1
C          |   1 |     1
D          |   1 |     1
B          |   1 |     2
AB         |   1 |     3
ABC        |   1 |     4
E          |   2 |     1
F          |   2 |     1
EF         |   2 |     2

Если у вас не всегда есть один конечный узел для каждой группы, вам понадобится НАМНОГО более сложное решение (вероятно, написанное на PL/SQL).

db‹›fiddle здесь

person MT0    schedule 05.03.2021
comment
У меня есть 200 таблиц, но после использования запроса я получил 700 строк, я ожидал 200 строк. не могли бы вы поделиться персоналом pl/sql, о котором вы упоминали? - person user3016635; 05.03.2021
comment
@user3016635 user3016635 У меня нет под рукой решения PL/SQL. Вам просто нужно решить, как вы можете сделать это в Java/C/Python/и т. д. (без необходимости иметь дело с базой данных), а затем просто перевести его на PL/SQL. Это просто поиск в глубину вверх и вниз по древовидной структуре. - person MT0; 05.03.2021