Sql иерархические запросы: Иерархические (рекурсивные) запросы / Хабр

Рекурсивные запросы в Oracle

В Oracleсуществует свой
собственный механизм рекурсивного
вычисления запросов, который мы опишем
в этом разделе. В следующем разделе мы
представим подход рекурсивного вычисления
запросов, принятый в стандартеSQL.

Если таблица имеет древовидную структуру,
то рекурсивное вычисление с произвольной
глубиной вложенности поддерживается
с помощью следующих трех фраз предложения
SELECT:

START
WITH— указывает ту вершину или вершины
дерева, которые являются начальными
для организации рекурсивного вычисления.

CONNECT
BY— указывается, каким образом связаны
между собой родительские и дочерние
вершины (строки), В простейшем случае
указывается, какие именно столбцы
используются для установления этих
связей.

WHERE— стандартная фраза предложенияSELECT,
которая ограничивает выводимые строки
таблицы без какого–либо воздействия
на древовидную структуру строк в таблице.

Эта информация используется для
организации следующей процедуры
рекурсивного вычисления запроса:

1. Если во фразе WHEREприсутствует предикат соединения двух
или нескольких таблиц, то такое соединение
производится в первую очередь.

2. Производится отбор тех строк отношения,
которые удовлетворяют условию фразы
START
WITH.
Эти строки рассматриваются как корневые
для последующего рекурсивного вычисления.

2. По каждой из таким образом определенной
корневой строке производится поиск ее
дочерних строк. Дочерние вершины
определяются согласно условию, заданному
во фразе CONNECT
BY.

3. Затем производится поиск последующих
поколений дочерних строк. Для этого
выбираются дочерние строки тех строк,
которые были найдены на шаге 2, затем
дочерние строки этих дочерних строк и
т.д. до трех пор, пока не будут достигнуты
строки–листья. При этом на каждом шаге
поиск следующих дочерних строк
производится согласно условию фразы
CONNECT
BY,
и при этом текущая строка рассматривается,
как если бы она была корневая.

4. Если запрос содержит фразу WHERE,
в которой имеются предикаты отбора
строк, то устраняются строки, не
удовлетворяющие условию этой фразы.
При этом условие вычисляется для каждой
строки индивидуально. Это означает, что
не производится удаление дочерних строк
той строки, которая не удовлетворяет
условию этой фразы.

5. Полученные в результате рекурсивного
вычисления строки выдаются в порядке,
приведенном на рис. 10.3 ниже. На этой
диаграмме дочерние вершины расположены
под своими родительскими вершинами.
Таким образом, упорядоченность
определяется правилом обхода дерева
сверху–вниз и слева–направо. Эта
упорядоченность выдачи строк может
меняться фразой ORDER
BY.

Рис.10.3. Стандартный порядок выдачи строк
таблицы при рекурсивном вычислении
запроса

Обсудим вкратце фразы, которые используются
в рекурсивных запросах.

Фраза START
WITHпозволяет указать то поддерево всего
иерархического дерева, которое будет
участвовать в рекурсивном запросе. Это
определяется заданием условия, которому
должны удовлетворять строки таблицы,
выступающие в качестве корня поддерева.
Если эта фраза отсутствует, то все строки
таблицы рассматриваются в качестве
корневых. Условие фразыSTART
WITHможет содержать подзапрос.

Несколько примеров рекурсивных SQL-запросов для системы DIRECTUM | Статья

Чтобы понять рекурсию, нужно сначала понять рекурсию.

Рекурсия — определение, описание, изображение какого-либо объекта или процесса внутри самого этого объекта или процесса, то есть ситуация, когда объект является частью самого себя.

Пожалуй, одним определением и ограничусь, в сети масса информации на эту тему.

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

В стандартной поставке системы DIRECTUM есть ряд справочников, которые можно считать рекурсивными. Такие справочники содержат реквизит, сгенерированный по этому же справочнику (Подразделения, Поручения, РКК, Договоры и т.д.).

Косвенно рекурсивным можно считать справочники Работников (через Подразделения: в Работниках есть реквизит Подразделение, в Подразделении указан Руководитель — работник).

Кроме того, рекурсивной можно считать таблицу задач. Косвенно рекурсивной таблицу заданий (через задачи).

Построим несколько запросов на примере справочника Подразделения. Стандартный справочник Подразделения в DIRECTUM можно считать рекурсивным, так как в нем есть реквизит, который ссылается на сам справочник Подразделения: Головное подразделение. Фактически,
любой из примеров легко переделать для другого справочника или таблиц ЗЗУ.

Пример 1

В качестве первого примера хочу привести общий запрос, который строит иерархию всех подразделений компании. В качестве подразделения нулевого уровня берется то, у которого не указано головное, далее по уровню подчиненности.

-----------------------------------------------------------------
-- Иерархия подразделений
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение верхнего уровня (N = 0)
  select
    department.Analit,
    department. Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.Podr is null
    and department.vid = 
  union ALL
-- Подчиненное подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Podr = hightdepartment.ID
  where
    department.vid = 
)
-- Иерархия подразделений
select
  hierarchy.ID as [ИД],
  department.NameAn as [Подразделение],
  IsNull(hightdepartment.NameAn, '') as [Ведущее подразделение],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
   left join MBAnalit hightdepartment
      on hightdepartment.Analit = hierarchy.HightID

Результат этого запроса на тестовой базе: список подразделений с указанием ведущего и уровня в иерархии.

Пример 2

Запрос можно модифицировать и под конкретные задачи. Например, при разработке отчетов по определенному подразделению необходимо учитывать не только указанное, но и все подчиненные подразделения. В этом случае можно получить все дерево таким запросом:

-----------------------------------------------------------------
-- Иерархия подразделений вниз от указанного
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение верхнего уровня (N = 0)
  select
    department.Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.Analit = 
    and department.vid = 
  union ALL
-- Подчиненное подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Podr = hightdepartment.ID
  where
    department.vid = 
)
-- Иерархия подразделений вниз от указанного
select
  hierarchy.ID as [ИД],
  department.NameAn as [Подразделение],
  IsNull(hightdepartment.NameAn, '') as [Ведущее подразделение],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department. Analit = hierarchy.ID
   left join MBAnalit hightdepartment
      on hightdepartment.Analit = hierarchy.HightID

Пример 3

Также может возникнуть необходимость получить данные по всем подчиненным указанного работника:

-----------------------------------------------------------------
-- Список подчиненных указанного работника
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение верхнего уровня (N = 0)
  select
    department.Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.FIO = 
    and department.vid = 
  union ALL
-- Подчиненное подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Podr = hightdepartment.ID
  where
    department.vid = 
)
-- Список подчиненных указанного работника
select distinct
  department. NameAn as [Подразделение],
  employee.NameAn as [Работник],
  IsNull(chiefemployee.NameAn, '') as [Руководитель],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
   left join MBAnalit hightdepartment
      on hightdepartment.Analit = hierarchy.HightID
   left join MBAnalit employee
     on department.Analit = employee.Podr and employee.Vid = 
   left join MBAnalit chiefemployee
     on department.FIO = chiefemployee.Analit and chiefemployee.Vid = 
where
  employee.Analit is not null

Пример 4

Обратный к примеру 3: цепочка руководителей указанного работника по иерархии вверх (например, может понадобиться для построения списка согласующих):

-----------------------------------------------------------------
-- Список руководителей указанного работника
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение нижнего уровня (N = 0)
  select
    department. Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.Analit = 
    and department.vid = 
  union ALL
-- Ведущее подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Analit = hightdepartment.HightID
  where
    department.vid = 
)
-- Список руководителей указанного работника
select distinct
  department.NameAn as [Подразделение],
  IsNull(chiefemployee.NameAn, '') as [Руководитель],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
    left join MBAnalit chiefemployee
      on department.FIO = chiefemployee.Analit and chiefemployee.Vid = 

В качестве P.S.

 Не стоит забывать о возможности существования бесконечной рекурсии. Во избежание используйте:

OPTION (MAXRECURSION )

Пошаговое руководство по созданию иерархических запросов SQL

1.

Введение

Иерархический запрос — это тип запроса SQL, который обычно используется для получения значимых результатов из иерархических данных. Иерархические данные определяются как набор элементов данных, связанных друг с другом иерархическими отношениями. Иерархические отношения существуют, когда один элемент данных является родителем другого элемента. Реальные примеры иерархических данных, которые обычно хранятся в базах данных, включают следующее:

• Иерархия сотрудников (отношения между сотрудником и менеджером)
• Организационная иерархия
• График ссылок между веб-страницами
• Подключенный граф социальных сетей
• Набор задач в проекте
• Файловая система

Если ваша база данных не является иерархической базой данных, такой как IBM Information Management System (IMS), вам потребуется написать иерархический SQL для извлечения реляционных данных в иерархическом формате, который устанавливает отношения родитель-потомок между объектами для лучшего понимания конечным пользователем.

Существует три типа иерархических синтаксисов SQL-запросов, которые можно использовать в различных коммерческих базах данных:

1. CONNECT BY: широко доступен в базе данных Oracle SQL
2. Common Table Expression (CTE): обычно используется с Microsoft SQL Server

.

3. АНСИ SQL

Теперь давайте рассмотрим особенности и примеры для каждого из типов.

2. CONNECT BY

Этот метод поддерживается Oracle, EnterpriseDB (PostgreSQL), CUBRID, IBM Informix и DB2. Стандартный синтаксис для запроса CONNECT BY подобен приведенному ниже. Как видите, SELECT, FROM и CONNECT BY — единственные обязательные предложения в операторе.

SELECT select_list
FROM table_expression
[ ГДЕ … ] [ НАЧАТЬ С начальным_выражением ] CONNECT BY [NOCYCLE] {PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [ столбец2 [ ASC | DESC ] ] …
[GROUP BY … ] [ HAVING … ]

Следует отметить несколько важных моментов, касающихся синтаксиса:

• START WITH указывает корневую или родительскую строку (строки) иерархии.
• CONNECT BY определяет связь между родительскими и дочерними строками иерархии.
• Параметр NOCYCLE указывает базе данных возвращать строки из запроса, даже если в данных существует циклическая связь (CONNECT BY LOOP). Вы можете использовать этот параметр вместе с псевдостолбцом CONNECT_BY_ISCYCLE, чтобы увидеть, какие строки содержат цикл.
• В иерархическом запросе одно выражение в условии должно быть дополнено оператором PRIOR, чтобы ссылаться на родительскую строку. Например,

… ПРЕДЫДУЩЕЕ выражение = выражение
или
… выражение = ПРЕДЫДУЩЕЕ выражение

2.1 CONNECT BY Working Mechanism

База данных оценивает иерархические запросы в следующем порядке:

• Соединение, если оно присутствует, оценивается первым, независимо от того, указано ли соединение в предложении FROM или с предикатами предложения WHERE
• Условие CONNECT BY оценивается
• Оцениваются все оставшиеся предикаты предложения WHERE

Затем ядро ​​базы данных использует информацию из этих оценок для формирования иерархии, используя следующие шаги (см. документацию Oracle 10g):

1. Выбирает корневую строку (строки) иерархии, то есть те строки, которые удовлетворяют условию НАЧАТЬ С.
2. Выбирает дочерние строки каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию CONNECT BY относительно одной из корневых строк.
3. Выбирает последующие поколения дочерних строк. Механизм базы данных сначала выбирает дочерние элементы строк, возвращенных на шаге 2, затем дочерние элементы этих дочерних элементов и так далее. База данных всегда выбирает потомков, оценивая условие CONNECT BY относительно текущей родительской строки.
4. Если запрос содержит предложение WHERE без объединения, то база данных удаляет из иерархии все строки, которые не удовлетворяют условию предложения WHERE. База данных оценивает это условие для каждой строки отдельно, а не удаляет все дочерние элементы строки, которая не удовлетворяет условию.
5. База данных возвращает строки в порядке, показанном на рисунке 1 ниже. На диаграмме дети появляются ниже своих родителей.

Рисунок 1. Иерархический порядок возврата строк запроса (из документации Oracle 10g)

Формат иерархического запроса CONNECT BY содержит операторы (особенно в Oracle), псевдостолбцы и функции для извлечения и представления иерархических данных в удобном для пользователя формате 7000 900: 900 положение в иерархии текущей строки по отношению к корневому узлу.

2.2 CONNECT BY & LEVEL Пример

Чтобы найти потомков родительской строки, база данных оценивает выражение PRIOR условия CONNECT BY для родительской строки и другое выражение для каждой строки в таблице. Строки, для которых условие истинно, являются дочерними элементами родителя. Условие CONNECT BY может содержать другие условия для дальнейшей фильтрации строк, выбранных запросом, однако оно не может содержать подзапрос.

Для примеров мы будем использовать знакомую иерархическую структуру данных «сотрудник-менеджер» из таблицы EMP. Вот пример иерархического SQL-запроса с CONNECT BY и LEVEL в его самой простой форме.

ВЫБЕРИТЕ EMPNO, ENAME, MGR, УРОВЕНЬ
ИЗ EMP
ПОДКЛЮЧИТЬСЯ ПО MGR = ПРЕДВАРИТЕЛЬНЫЙ EMPNO
ЗАКАЗАТЬ СЕСТРОВ ПО MGR;

Как вы заметили, здесь нет предложения START WITH; в результате мы не получаем хорошо организованных, значимых данных для эффективной отчетности. Итак, давайте введем START WITH в запрос и посмотрим на результаты.

2.3 START WITH Пример

Используйте предложение START WITH, чтобы указать корневую строку для иерархии, и предложение ORDER BY, используя ключевое слово SIBLINGS, чтобы сохранить порядок в иерархии. В иерархическом запросе используйте ORDER (или GROUP BY) SIBLINGS BY, чтобы отсортировать строки элементов одного и того же родителя; это сохранит иерархический порядок результатов CONNECT BY.

ВЫБЕРИТЕ EMPNO, ENAME, MGR, УРОВЕНЬ
ИЗ EMP
НАЧАТЬ С MGR IS NULL
ПОДКЛЮЧИТЬСЯ ПО MGR = ПРЕДЫДУЩИЙ EMPNO
ЗАКАЗАТЬ СЕСТРОВ ПО MGR;

2.4 NOCYCLE Пример

Иерархический набор данных может быть циклическим, что создаст проблему при запросе данных. Однако предложение NOCYCLE с CONNECT BY предписывает базе данных не проходить циклические иерархии данных. В этом случае функция CONNECT_BY_ISCYCLE показывает запись, отвечающую за цикл. Обратите внимание, что CONNECT_BY_ISCYCLE можно использовать, только если указано предложение NOCYCLE.

ВЫБЕРИТЕ EMPNO, ENAME, MGR, LEVEL, CONNECT_BY_ISCYCLE AS Cycle
FROM EMP
START WITH MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO
SIBLINGS BY MGR;

2.

5 CONNECT_BY_ROOT Пример

Это предложение возвращает корневые узлы, связанные с текущей строкой. В следующем примере возвращается имя каждого сотрудника в отделе 20, каждого менеджера выше этого сотрудника в иерархии, количество уровней между менеджером и сотрудником и путь между ними: 

Если вам нужно рассчитать общую заработную плату для каждого менеджера и всех его/ее подчиненных, используйте предложение CONNECT_BY_ROOT. Вот простой пример для сотрудников отдела 20 из таблицы EMP: 

2.6 Пример дополнительного иерархического запроса

Если вам нужно добавить вкладки для визуального представления иерархических данных, вы можете использовать функции заполнения, как показано в примере ниже. Значения в поле TREE имеют отступы, чтобы визуально показать иерархию отчетов (связь) сотрудников. Кроме того, PATH предоставляет связь между сотрудниками в формате с разделителями.

3. Common Table Expression (CTE)

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

Синтаксис CTE поддерживается базами данных Teradata, DB2, Firebird, Microsoft SQL Server, Oracle, PostgreSQL, SQLite, HyperSQL, h3. КТР называется « рекурсивный подзапрос с факторингом ” в базе данных Oracle.

3.1 Построение рекурсивного CTE

Рекурсивное CTE должно состоять из четырех элементов для правильной работы:

1. Якорный запрос — выполняется один раз, и результаты задают рекурсивный запрос
2. Рекурсивный запрос — выполняется несколько раз и является критерием для остальных результатов
3. UNION ALL — оператор для связывания Anchor и Recursive запросов вместе
4. INNER JOIN — оператор для привязки рекурсивного запроса к результатам CTE

Ниже приведен полный запрос CTE, который создает иерархические данные из таблицы EMP. Давайте идентифицируем каждый из четырех вышеперечисленных элементов запроса CTE.

WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, 1 EMPLEVEL – Initial Subquery
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E .MGR, CTE.EMPLEVEL + 1 – рекурсивный подзапрос
ИЗ EMP E
ВНУТРЕННЕЕ СОЕДИНЕНИЕ OURCTE CTE ON E.MGR = CTE.EMPNO
, ГДЕ E.MGR НЕ НУЛЕВОЕ)
ВЫБЕРИТЕ *
ИЗ OURCTE
ЗАКАЗАТЬ ПО EMPLEVEL;

3.1.1 Якорный запрос

Сотрудники, у которых нет менеджера (MGR IS NULL), например, генеральный директор компании, являются сотрудниками высшего уровня организации. (Генеральный директор, скорее всего, подчиняется председателю совета директоров, если только он или она не занимает совместные должности, но давайте пока не будем об этом беспокоиться). Эта группа представляет сотрудников уровня 1. Запрос Anchor идентифицирует этих сотрудников и передает эти данные в следующий рекурсивный запрос.

ВЫБЕРИТЕ EMPNO, ENAME, MGR – Начальный подзапрос
ИЗ EMP
ГДЕ MGR НУЛЬ;

3.

1.2 Рекурсивный запрос

Задача рекурсивного запроса состоит в том, чтобы найти всех сотрудников уровня 2 или ниже, для которых определен менеджер (MGR НЕ NULL). Приведенный ниже запрос является нашим рекурсивным запросом, который в конечном итоге будет иметь INNER JOIN для рекурсивной ссылки на результаты запроса Anchor.

SELECT E.EMPNO, E.ENAME, E.MGR – рекурсивный подзапрос
ИЗ EMP E
, ГДЕ E.MGR НЕ НУЛЬ;

3.1.3 Союз Все

Мы можем трансформировать якорь и рекурсивные запросы, заполнив союз всех заявлений между ними, разместив еще вокруг них, добавив объявление на нашем. с псевдонимами столбцов и, наконец, добавив SELECT * FROM OURCTE после закрывающих скобок.

С OURCTE (EMPNO, ENAME, MGR)
AS (ВЫБРАТЬ EMPNO, ENAME, MGR – начальный подзапрос
FROM EMP
WHERE MGR IS NULL
UNION ALL – Объединение всех объединенных запросов Anchor и Recursive
SELECT E. EMPNO, E.ENAME, E.MGR – Recursive Subquery
FROM EMP E
WHERE E.MGR НЕ NULL)
SELECT *
ОТ OURCTE;

Как видите, результаты запроса CTE точно такие же, как объединенные результаты запроса Anchor.

Запрос Anchor внутри CTE представляет всех на уровне 1, а рекурсивный запрос представляет всех на уровне 2 и выше. Чтобы визуализировать каждый уровень в наборе результатов, вам нужно будет добавить поле выражения к каждому запросу, как показано ниже:

WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, ‘1’ EMPLEVEL – выражение для уровня дорожки
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E .ENAME, E.MGR, ‘2 или выше’ EMPLEVEL – Выражение для отслеживания уровня (здесь просто заполнитель)
FROM EMP E
– INNER JOIN OURCTE CTE ON E.MGR = CTE.EMPNO
ГДЕ E.MGR НЕ NULL )
SELECT *
FROM OURCTE
ORDER BY EMPLEVEL;

3.

1.4 ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Как видите, мне пришлось поместить жестко заданное значение для поля EMPLEVEL в секции Recursive запроса выше. Это произошло потому, что я не присоединил рекурсивный раздел запроса к нашему рекурсивному CTE. Теперь, чтобы заставить весь иерархический запрос работать с фактическим иерархическим уровнем назначенных записей, нам нужно выполнить ВНУТРЕННЕЕ СОЕДИНЕНИЕ, как в приведенном ниже запросе. Обратите внимание на разницу в значении поля EMPLEVEL. Это полный иерархический запрос в формате CTE. Обратите внимание, что ORDER BY здесь необязателен.

WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, 1 EMPLEVEL – Initial Subquery
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E .MGR, CTE.EMPLEVEL + 1 – Рекурсивный подзапрос
ИЗ EMP E
ВНУТРЕННЕЕ СОЕДИНЕНИЕ OURCTE CTE ON E.MGR = CTE.EMPNO – Внутреннее соединение
, ГДЕ E.MGR НЕ NULL)
SELECT *
FROM OURCTE
ORDER BY EMPLEVEL ;

Если вам нужно отобразить имя менеджера рядом с идентификатором менеджера, обратитесь к следующему запросу:

ВЫБЕРИТЕ CTE. EMPNO, CTE.ENAME, CTE.MGR, E.ENAME «ИМЯ MGR», CTE.EMPLEVEL
ИЗ (
С OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (ВЫБЕРИТЕ EMPNO, ENAME, MGR , 1 EMPLEVEL – исходный подзапрос
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E.MGR, CTE.EMPLEVEL + 1 – рекурсивный подзапрос
FROM EMP E
INNER JOIN OURCTE CTE ON E .MGR = CTE.EMPNO
, ГДЕ E.MGR НЕ НУЛЕВОЕ)
SELECT *
FROM OURCTE
ЗАКАЗАТЬ ПО EMPLEVEL) CTE
LEFT OUTER JOIN EMP E ON CTE.MGR = E.EMPNO
ЗАКАЗАТЬ CTE.EMPLEVEL,E.ENAME;

4. ANSI SQL

CONNECT_BY и Common Table Expression (CTE) — два наиболее широко используемых метода построения иерархических результатов из реляционных данных. Однако функции CONNECT_BY, которые обсуждались выше, в основном доступны для базы данных Oracle SQL, и не все они совместимы с ANSI SQL. С другой стороны, хотя синтаксис CTE соответствует стандарту ANSI SQL 99, он не очень гибкий и не полностью совместим со всеми базами данных, такими как HP Vertica. В результате вам может потребоваться получить иерархические результаты из реляционных структур данных с использованием ANSI SQL в системах баз данных, которые не полностью поддерживают синтаксис CONNECT_BY и CTE.

Существует довольно простой способ сделать это с помощью SQL LEFT OUTER JOIN. В этом методе мы сглаживаем иерархические данные, которые имеют отношение родитель-потомок. Результатом этого запроса является обширный и редко заполняемый набор данных с дополнительными столбцами для хранения идентификаторов узлов и значений на разных уровнях иерархии. Количество дополнительных столбцов с непустыми значениями напрямую связано с глубиной иерархии.

Еще раз, мы будем использовать таблицу EMP для построения иерархического запроса ANSI SQL для создания плоского набора данных с помощью предложения LEFT OUTER JOIN. Это простейшая форма запроса, однако разработанный здесь принцип может быть применен к источнику(ам) данных с несколькими встроенными в них иерархиями. С учетом сказанного, вот шаги, которые выполняются для разработки иерархического запроса ниже:

1. Определите корень иерархии. В случае таблицы EMP запись с MGR IS NULL представляет собой корень иерархии данных менеджер-сотрудник. Это уровень 1 или самый верхний узел всей иерархии. Давайте назначим псевдоним T1 этому встроенному набору результатов.

2. Затем рекурсивно перемещайтесь на один уровень вниз, используя LEFT OUTER JOIN, в котором левое внешнее соединение T1 с таблицей EMP (T2) в полях EMPNO и MGR соответственно. Этот запрос возвращает сотрудников, которые подчиняются сотруднику(ам) уровня 1. Теперь у нас есть список сотрудников уровня 2 вместе с сотрудниками уровня 1. Обратите внимание, что каждая новая рекурсия добавляет в результирующий набор дополнительные столбцы, делая его более плоским и разреженным. Присвоим этому результирующему набору псевдоним T2.

3. Продолжайте повторять шаг 2, пока мы не найдем уровень со всеми пустыми значениями полей. Это когда мы знаем, что уровней больше нет; мы прошли даже на один уровень ниже самого глубокого листа иерархии. В нашем примере последним является уровень 4, поскольку все поля уровня 5 пусты.

4. В этот момент вы будете думать: «Как я узнаю, где остановиться и/или сколько уровней мне нужно определить в моем запросе, чтобы убедиться, что мой запрос не сломается, если появятся новые узлы?» добавлены в иерархию?». В большинстве случаев практического использования глубина иерархии будет определена или находится в определенных пределах. Однако в определенных ситуациях, таких как организационные изменения или слияния и поглощения, иерархия может измениться. Поэтому рекомендуется определить несколько дополнительных уровней, хотя это добавит в набор результатов больше столбцов с пустыми значениями. Но дополнительные поля не изменят конечный результат, поскольку они пусты и, следовательно, не изменяют эффективную длину ветви дерева иерархии.

Ниже приведен наш запрос и набор результатов. Как видите, уровень 5 добавлен для избыточности, но это не меняет окончательный результат запроса в реальном смысле.

 5. Заключение

Каждый из этих иерархических форматов запросов имеет свое место и варианты использования. Если вы используете базу данных Oracle версии 10g или более поздней, вам следует использовать метод CONNECT BY, поскольку иерархические функции SQL Oracle просты в использовании, зрелы и универсальны. Однако если вы используете базу данных (например, Microsoft SQL Server), которая не поддерживает метод CONNECT BY, вам необходимо использовать синтаксис CTE для построения иерархического набора результатов. Наконец, если вы работаете с одной из платформ Big Data SQL, такой как HP Vertica, вам может потребоваться использовать ANSI SQL для получения иерархических данных в линейном формате (например, Excel) из реляционных данных. В Clarity Solution Group наши аналитики данных решают эту и многие другие сложные задачи в нашей повседневной работе. Узнайте больше о службах данных и аналитики Clarity.

Первоначально эта статья была опубликована в блоге Clarity Solution Group по адресу http://clarity-us. com/guide-sql-hierarchical-queries/

Иерархические запросы в Oracle SQL

Лоран
Шнайдер считается одним из лучших экспертов по Oracle SQL.
он является автором книги «Advanced SQL Programming» издательства Rampant TechPress. Следующее
это отрывок из книги.

Иерархические запросы

Иерархия строится на отношениях родитель-потомок внутри
ту же таблицу или представление. Иерархический запрос — одна из самых первых возможностей
Oracle Database и была представлена ​​более двадцати лет назад!

В традиционном запросе Oracle ищет хорошее выполнение
planand извлекает строки одну за другой, в произвольном порядке.
В иерархии строки организованы в виде дерева:

Рисунок 6. 1:
Иерархия
Дерево

В таблице известных сотрудников (EMP) король является
президент. Ниже президента у каждого менеджера есть свой отдел. в
бухгалтерии, у Кларка есть один клерк, Миллер. Отдел продаж
под контролем Блейка. У Блейка четыре продавца — Аллен, Уорд, Мартин и
Тернер — и один клерк, Джеймс. Джонс возглавляет исследовательский отдел. Аналитик
У Форда есть клерк по имени Смит, а у аналитика Скотта также есть клерк по имени Адамс.

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

Одной из наиболее распространенных проблем в иерархиях является цикличность.
То есть, если топ-менеджер сам себе начальник, Oracle обнаружит петлю и
вернуть ошибку. Эта проблема частично исправлена ​​в 10g новым механизмом, который устанавливает
флаг и прекращает обработку ветки-нарушителя.

До версии 9i доступные поля находились либо на текущей
строке или в родительской строке. В 10g поля в верхней части иерархии
также доступны, что позволяет анализировать и агрегировать по всему
иерархия. Еще одно дополнение в 10g — это флаг для нижней части иерархии,
называются листовыми рядами.

ПОДКЛЮЧИТЬСЯ, ДО и НАЧАТЬ С

Есть два обязательных ключевых слова для построения иерархии,
ПОДКЛЮЧАЙТЕСЬ ПО и ПРЕД. Иерархия строится, когда одна строка является родительской для другой строки.
START WITH определяет первого предка.

ВЫБЕРИТЕ
   ENAME
FROM
   EMP
ПОДКЛЮЧИТЬ
   ПРЕДЫДУЩИЙ EMPNO = MGR
НАЧАТЬ С
   ENAME = ‘JONES’;

ENAME
———-
ДЖОНС
СКОТТ
АДАМС
ФОРД
СМИТ

Джонс и его сотрудники возвращены. Адамс — сотрудник
Скотта, а Скотт — сотрудник Джонса, поэтому Адамс также вернулся.

УРОВЕНЬ

Уровень псевдостолбца возвращает глубину иерархии.
Первый уровень — корень:

ВЫБЕРИТЕ
   ENAME
FROM
   EMP
ГДЕ
   УРОВЕНЬ=2
ПОДКЛЮЧИТЬСЯ ПО
   ПРЕДЫДУЩИЙ EMPNO = MGR
НАЧАТЬ С
   ENAME = ‘JONES’;

ENAME
———
SCOTT
FORD

Возвращаются только непосредственные сотрудники Jones. Джонс
первый предок и имеет уровень 1. Адамс и Смит на один уровень ниже
прямых сотрудников и относятся к третьему уровню, начатому Джонсом.

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

SELECT
CONCAT
(
LPAD
(
»,
Уровень*3-3
),
ENAME
) ENAME
из
EMP
CONNECT с
Предыдущим EMPNO = MGR
СТАРЬ С
MGR IS NOULL;

Ename
———————-
King
Jones
Scott
Adams
Ford
Smith
Blake
Allen
Ward
Martin
Turner
      ДЖЕЙМС
   КЛАРК
      МИЛЛЕР

Начиная с топ-менеджера, имена сотрудников
дополняются пробелами в соответствии с их уровнем.

ORDER SIBLINGS BY

Строки в иерархическом запросе возвращаются в виде дерева,
дети следуют за родителем. ЗАКАЗАТЬ SIBLINGS BYpreserves
иерархия и упорядочивает потомков каждого родителя.