Рекурсивный запрос sql: Изучение SQL: рекурсивные запросы

sql server — рекурсивный запрос mssql

Вопрос задан 5 лет 1 месяц назад

Изменён 5 лет 1 месяц назад

Просмотрен 610 раз

Помогите с рекурсивным запросом к таблице, чтобы значение в столбце flag родителей был изменен 0 на 1 в случае если у всех его детей стоит 1

id  name    ID_PP   parentID    flag
1   Все NULL    NULL    0
2   Компания    NULL    1   0
3   Производство 1  NULL    2   0
4   Производство 2  NULL    2   0
5   Производство 3  NULL    2   0
6   Производство 4  NULL    2   0
7   Производство 5  NULL    2   0
8   Производство 6  NULL    2   0
9   Цех 1   NULL    3   0
10  Цех 2   NULL    4   0
11  Цех 3   NULL    4   0
12  Цех 4   NULL    4   0
13  Цех 5   NULL    5   0
14  Цех 6   NULL    5   0
15  Цех 7   NULL    6   0
16  Цех 8   NULL    6   0
17  Цех  9  NULL    6   0
18  Цех  10 NULL    7   0
19  Цех 11  NULL    7   0
20  Цех 12  NULL    8   1
21  Цех 13  8   1
23  Цех 14  NULL    8   1
24  Отделение 0101  1898    9   1
25  Отделение 0102  2205    9   0
26  Отделение 0103  2064    9   1
27  Отделение 0104  2121    9   0
28  Отделение 0105  2185    9   0
29  УСК             1983            9   0
30  Отделение 0106 (И-7)    5347    9   1
declare @in_bdt datetime = cast('13.  02.2018 00:00:00' as datetime)   
declare @in_edt datetime = cast('13.02.2018 23:59:00' as datetime) 
 declare @razn varchar(10) = DATEDIFF (HOUR,@in_bdt,@in_edt)+1
;with rec (  [id]
      ,[ID_PP]
      ,[name]
      ,[parentID]
      ,flag)
  as ( 
  select [id]
      ,t2.[ID_PP]
      ,[name]
      ,[parentID]
      ,flag
   from [dbo].[tab1] 
    left  join(
           SELECT IIF((COUNT([ID_PP])-@razn)=0,1,0) flag
           ,[ID_PP]
                 FROM [dbo].[tab2]
                 where dt between @in_bdt and @in_edt
                 group by  [ID_PP]
           ) as t2 on [tab1].ID_PP = t2.ID_PP
   union all
   select [tab1].[id]
      ,[tab1].[ID_PP]
      ,[tab1].[name]
      ,[tab1].[parentID]  
      ,flag
            from [tab1] 
      join rec on [tab1].id =rec.parentID
   )
select distinct rec.[id]
      ,[ID_PP]
      ,[name]
      ,[parentID]
      ,flag
       from rec
       order by rec.[id]
1   NULL    1
2   1   1
3   2   1
4   2   1
5   2   1
6   2   1
7   2   1
8   2   1
83  2   1
9   3   1
10  4   NULL
11  4   1
12  4   1
13  5   1
14  5   1
15  6   1
16  6   1
17  6   1
18  7   1
19  7   1
20  8   1
21  8   1
23  8   1
24  9   1
25  9   NULL

• sql-server
• запрос
• рекурсия

8

with RTable as(
     select [id],t2.  [ID_PP],[name],[parentID], coalesce(flag,0) flag
       from [dbo].[tab1] 
       left  join(
           SELECT IIF((COUNT([ID_PP])-@razn)=0,1,0) flag,[ID_PP]
             FROM [dbo].[tab2]
             where dt between @in_bdt and @in_edt
             group by  [ID_PP]
       ) as t2 on [tab1].ID_PP = t2.ID_PP
),
R1 as(
    select id, ParentId, flag
      from RTable R
     where not exists(select 1 from RTable N where N.parentId=R.Id)
    union all
    select T.id, T.ParentId,
           case when T.flag=1 then 1 else R.flag end
      from R1 R, RTable T
     where T.id=R.ParentId
)
select id, parentId, min(flag) flag
  from R1
group by id, parentId

Спускаемся от листьев к корню дерева (лист это такая запись у которой нет наследников). При этом берем флаг из текущей записи, если он 1 или, если он 0, то берем флаг пришедший по рекурсии от наследника. Если по всем возможным путям (от всех листьев) до данного узла дошли только 1, то и итогом данного узла будет 1. На выходе из рекурсии у нас как раз для каждого id есть записи всех его прямых наследников с теми флагами, которые были получены спуском от листьев.

Остается только сгруппировать до id и получить минимум (если был хотя бы один 0 — то итог 0)

Пример на sqlfiddle.com (только рекурсии, без начальной подготовки таблицы из вашего примера)

7

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

Простейший рекурсивный запрос

Простейший рекурсивный запрос

• 1. Введение

  • 1.1 Синтаксис SQL запроса

  • 1.2 Получение данных из таблицы

  • 1.3 Вызов функции

  • 1.4 Конкатенация строк

  • 1.5 Арифметические операции

  • 1.6 Исключение дубликатов

• 2. Отсечение строк и сортировка

  • 2.1 Выражение WHERE

  • 2.2 Логические операторы

  • 2.3 Порядок условий

  • 2.4 Операции сравнения

  • 2.5 BETWEEN

  • 2.6 IN

  • 2.7 Поиск по шаблону

  • 2.8 Обработка NULL значений

  • 2.9 Сортировка

  • 2.10 Ограничение количества строк LIMIT

  • 2.11 Пропуск первых строк результата

• 3. Соединения

  • 3.1 Соединение двух таблиц

  • 3.2 Псевдонимы таблиц

  • 3.3 Добавляем WHERE

  • 3.4 Несколько условий соединения

  • 3.5 Использование таблицы несколько раз

  • 3.6 Типы соединения

  • 3.

    7 RIGHT JOIN

  • 3.8 FULL JOIN

  • 3.9 Декартово произведение

  • 3.10 Синтаксис через WHERE

• 4. Агрегатные функции

  • 4.1 Агрегатные функции

  • 4.2 NULL значения в агрегатных функциях

  • 4.3 Количество уникальных значений

  • 4.4 Отсутствие строк

  • 4.5 GROUP BY

  • 4.6 Дополнительные столбцы в списке выборки с GROUP BY

  • 4.7 GROUP BY и WHERE

  • 4.8 GROUP BY по нескольким выражениям

  • 4.9 NULL значения в GROUP BY

  • 4.10 HAVING

  • 4.11 ROLLUP

  • 4.12 CUBE

  • 4.13 GROUPING SETS

• 5. Операции над множествами

  • 5.1 Доступные операции над множествами

  • 5.2 Из какого запроса строка?

  • 5.3 Пересечение строк

  • 5.4 Исключение строк

  • 5.5 Дубликаты строк

  • 5.6 Совпадение типов данных столбцов

  • 5. 7 Сортировка

  • 5.8 Несколько операций

• 6. Подзапросы

  • 6.1 Подзапрос одиночной строки

  • 6.2 Коррелированный подзапрос

  • 6.3 Подзапрос вернул более одной строки

  • 6.4 Подзапрос не вернул строк

  • 6.5 Попадание в список значений

  • 6.6 Отсутствие в списке значений

  • 6.7 NULL значения в NOT IN

  • 6.8 Проверка существования строки

  • 6.9 Проверка отсутствия строки

• 7. Строковые функции

  • 7.1 CONCAT — конкатенация строк

  • 7.2 Преобразование регистра букв

  • 7.3 LENGTH — определение длины строки

  • 7.4 Извлечение подстроки

  • 7.5 POSITION — поиск подстроки

  • 7.6 Дополнение до определенной длины

  • 7.7 TRIM — удаление символов с начала и конца строки

  • 7.8 REPLACE — замена подстроки

  • 7.9 TRANSLATE — замена набора символов

• 8. !)

• 8.5 Получение числа из строки

• 8.6 ROUND — округление числа

• 8.7 TRUNC — усечение числа

• 8.8 CEIL — следующее целое число

• 8.9 FLOOR — предыдущее целое число

• 8.10 GREATEST — определение большего числа

• 8.11 LEAST — определение меньшего числа

• 8.12 ABS — модуль числа

• 8.13 TO_CHAR — форматирование числа

9. Рекурсивные подзапросы

• 9.1 Подзапрос во фразе FROM

• 9.2 Введение в WITH

• 9.3 Несколько подзапросов в WITH

• 9.4 Простейший рекурсивный запрос

• 9.5 Рекурсивный запрос посложнее

• 9.6 Строим иерархию объектов

• 9.7 Путь до элемента

• 9.8 Сортировка (плохая)

• 9.9 Сортировка (надежная)

• 9.10 Форматирование иерархии

• 9.11 Нумерация вложенных списков

• 9.12 Листовые строки CONNECT_BY_ISLEAF

10. Оконные функции

• 10.1 Получение номера строки

• 10.2 Номер строки в рамках группы

• 10.3 Составляем рейтинг — RANK

• 10.4 Несколько человек на место — DENSE_RANK

• 10.5 Разделение на группы — NTILE

• 10.6 Агрегатные оконные функции

• 10.7 Обработка NULL значений

• 10.8 Нарастающий итог SUM + ORDER BY

• 10.9 Неуникальные значения в нарастающем итоге SUM + ORDER BY

• 10.10 Собираем строки через разделитель — STRING_AGG

• 10.11 WITHIN GROUP

• Оглавление
• Рекурсивные подзапросы

Рекурсивные запросы используют в подзапросе данные самого себя.

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

Простейший пример

Потренируемся на цифрах. Сгенерируем числа от одного до трех:

WITH RECURSIVE lv_recursive (num) as (
  SELECT 1 AS num
    
    UNION ALL
  
  SELECT p.num + 1
    FROM lv_recursive p
   WHERE p.num < 3
)
SELECT *
  FROM lv_recursive

Обрати внимание, что lv_recursive используется как для названия подзапроса в WITH, так и как таблица в этом же подзапросе.

Чтобы в запросе ссылаться на самого себя, необходимо указать ключевое слово RECURSIVE. В противном случае будет ошибка:

WITH lv_recursive (num) as (
  SELECT 1 AS num
    
    UNION ALL
  
  SELECT p.num + 1
    FROM lv_recursive p
   WHERE p.num < 3
)
SELECT *
  FROM lv_recursive

error: relation "lv_recursive" does not exist

Рекурсивный подзапрос состоит из двух частей: нерекурсивной, определяющей первоначальный набор данных, и рекурсивной части, выполняющейся итерационно (несколько раз). Нерекурсивная и рекурсивная части разделяются UNION или UNION ALL.

WITH RECURSIVE lv_recursive (num) as (
  -- Нерекурсивная часть
  SELECT 1 AS num
    
    UNION ALL
  -- Рекурсивная часть
  SELECT p.num + 1
    FROM lv_recursive p
   WHERE p.num < 3
)

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

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

2. Выполняется рекурсивная часть подзапроса. Вместо таблицы во фразе FROM с названием рекурсивного подзапроса (lv_recursive в нашем случае), используются строки из временной таблицы. Результат рекурсивной части добавляется в общий результат подзапроса и становится содержимым временной таблицы. Таким образом, на следующей итерации рекурсивный запрос выполняется для строк, полученных на текущей.

3. Если во временной таблице есть записи, то переходим к шагу 2. Если нет — то рекурсивный подзапрос выполнен.

Разберем на нашем подзапросе

Шаг 1. Выполняется нерекурсивная часть подзапроса.

Общий результат подзапроса:

Временная таблица:

Шаг 2. Рекурсивная часть выполняется 1-й раз

lv_recursive равен

Т.к. 1 < 3, то запрос

SELECT p.num + 1
  FROM lv_recursive p
 WHERE p.num < 3

вернет одну строку с num = 2.

После первого выполнения рекурсивной части:

Общий результат подзапроса:

Временная таблица:

Шаг 3. Рекурсивная часть выполняется 2-й раз

lv_recursive равен

Т.к. 2 < 3, то будет возвращена одна строка с num = 3.

После второго выполнения рекурсивной части:

Общий результат подзапроса:

Временная таблица:

Шаг 4. Рекурсивная часть выполняется 3-й раз

lv_recursive равен

Т.к. 3 не меньше 3, то результат рекурсивной части не возвращает ни одной строки.

После третьего выполнения рекурсивной части:

Общий результат подзапроса:

Временная таблица пуста.

Т.к. временная таблица пуста, то выполнение рекурсивного подзапроса на этом завершается.

P.S.: Сгенерировать числа в PostgreSQL можно гораздо проще:

SELECT num
  FROM generate_series(1, 5) as num

• Практическое задание

  Простейший рекурсивный запрос

9.3 Несколько подзапросов в WITH

9.5 Рекурсивный запрос посложнее

Сделано ребятами из Сибири

© 2023 LearnDB

Написание рекурсивных запросов в SQL Server | Джейми Бернс

Как эффективно получить доступ к вашим данным в иерархической структуре

Фото Майкла Дзидзича на Unsplash

Нередко в ваших данных есть иерархические отношения.

Хранение этих данных может быть относительно простым, но иногда бывает сложно получить их снова, особенно если данные содержат различные уровни иерархии.

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

Мы также будем использовать SQL Server 2019, но это будет работать во всех версиях SQL Server, начиная с SQL Server2008.

Начнем с того, как моделируются данные.

Допустим, у нас есть несколько пользователей, и каждый пользователь может управлять другими пользователями. Мы можем представить это так:

Иерархическая структура пользователей, управляющих другими пользователями

Итак, прямо вверху у нас есть Джон, который управляет Яном и Элли. Элли никем не управляет, но Йен управляет Аланом и Тимом. Алан управляет Робертом.

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

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

А данные для менеджеров выглядят так:

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

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

Пока все хорошо. Но что, если бы мы захотели поднять всех менеджеров вверх по иерархии для любого заданного пользователя? Итак, если бы мы посмотрели на Алана, мы бы получили список Яна и Джона. Если бы мы посмотрели на Элли, то получили бы только Джона.

Здесь мы переходим к нашим рекурсивным запросам.

Написание запроса для получения всех менеджеров для одного пользователя

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

Это возвращает единственное значение «Ian»:

Результат запроса

Пока все в порядке.

Теперь мы хотим, чтобы этот набор результатов включал «Джон». У нас есть возможность добавить дополнительные JOIN в этот запрос, но тогда мы будем ограничены добавляемым количеством. Мы могли бы заставить его работать для двух уровней менеджеров, но что, если у нас вдруг будет три уровня? Или 30? Здесь нам нужен рекурсивный запрос, чтобы найти всех возможных менеджеров, независимо от количества уровней.

Основным элементом рекурсивного запроса в SQL Server является Common Table Expression (CTE). В CTE есть удобная функция, позволяющая легко обрабатывать рекурсию, поэтому мы будем использовать ее здесь.

Давайте обновим наш существующий запрос, чтобы использовать CTE, чтобы мы могли выполнить необходимые изменения:

Посмотрите, как мы переместили часть запроса в блок WITH — это CTE. По сути, это тот же запрос, что и раньше (он по-прежнему просто возвращает «Ian»), но теперь у нас есть CTE, и проще добавить рекурсию.

Ключевой частью рекурсивного CTE является UNION . Мы пишем CTE, чтобы сначала выбрать базовые данные (в нашем случае найти непосредственного руководителя пользователя), а затем UNION полученные данные обратно в CTE, чтобы найти менеджеров этого нового набора результатов.

Что-то вроде этого:

Посмотрите, как у нас появился новый UNION , который объединяет CTE (называемый AllManagers ) с таблицей dbo. Managers , используя столбец StaffUserId , чтобы соответствовать предыдущему — избранные менеджеры.

Теперь у нас есть список «Ян» и «Джон», как и следовало ожидать:

Результаты запроса

Если мы изменим наши Параметр UserId в Id 6 и выполняем снова, теперь мы получаем всех менеджеров Роберта:

Результат запроса

И все! У нас есть рекурсивный запрос, который возвращает всех менеджеров для любого заданного пользователя.

Добавление номеров на уровне иерархии

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

Посмотрите, как мы добавили столбец «Позиция» в CTE. В базовом запросе мы устанавливаем это как 1, которое будет использоваться для непосредственного менеджера, а затем 9Часть 0025 UNION каждый раз добавляет 1 для подсчета уровней. Запустив это для «Роберта», мы получим следующие результаты:

Результаты запроса

Итак, мы видим, что непосредственному менеджеру («Алану») присвоена позиция 1, а каждому последующему менеджеру — возрастающая позиция.

Это полезно, если вас интересует только определенное количество менеджеров (например, найти первых двух менеджеров пользователя).

Как избежать бесконечных циклов

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

Давайте добавим дополнительную строку в нашу таблицу dbo.Managers , чтобы сделать «Роберт» менеджером «Джона». Визуально иерархия будет выглядеть так:

Обновленная иерархия менеджеров

И обновленные данные будут такими :

Если бы мы запустили последнюю версию нашего запроса, мы бы получили ошибку:

Ошибка, которую мы получаем для бесконечного цикла

Однако, если мы посмотрим на то, что возвращено, мы увидим, что эти первые 100 строк все еще были возвращены. :

Результат запроса

Это неправильно, и решение не в том, чтобы увеличивать максимальный предел рекурсии, если вам это не нужно!

Есть несколько способов исправить это, но один из самых простых (хотя и не обязательно самый быстрый) — создать «путь» менеджеров и проверить, что следующий менеджер, который вы добавляете в CTE, еще не существует. . По сути, вы можете создать CSV-значение идентификаторов менеджеров, которые вы уже обработали, и сверить их с ним. Вот как это выглядит:

Посмотрите, как мы установили начальное значение 9.0025 ManagerPath в качестве идентификатора менеджера с запятой до и после него, а в UNION мы добавляем это значение к идентификатору следующего менеджера. Проверяем, что мы еще не видели этого менеджера в предложении WHERE , проверяем, что id (обведенный запятыми) там не существует.

Это приводит к правильному списку:

Результат запроса

Однако для вас может не иметь смысла включать в список менеджеров «Роберт» также и «Роберт». Если вы хотите исключить исходного пользователя из результатов, вы можете добавить его в WHERE пункт тоже. Вот код:

Это приводит к тому же набору результатов, только без исходного пользователя в нем:

Результаты запроса

Мы видели, что мы можем запрашивать иерархические данные в SQL Server, используя рекурсивное выражение Common Table Expression (CTE). , чтобы вернуть всех родителей/предков любой данной строки. Мы также увидели, как можно включить позиционное значение для каждого результата и избежать бесконечных циклов.

рекурсивных SQL-запросов с PostgreSQL | Мартин Хайнц

Common Table Expression — менее известная функция SQL, позволяющая писать рекурсивные запросы. Давайте изучим это с помощью PostgreSQL!

Photo by Ludde Lorentz on Unsplash

При работе с базами данных в большинстве случаев все, что вам нужно, это SELECT , UPDATE (CRUD-операции), несколько JOIN s и WHERE предложений и на этом все. Но иногда вы будете сталкиваться с наборами данных и задачами, которые потребуют от вас использования гораздо более продвинутых функций SQL. Один из них CTE или общее табличное выражение и, более конкретно, Recursive CTE , которое мы можем использовать для выполнения рекурсивных запросов SQL. Давайте посмотрим, как это работает и что мы можем с этим сделать!

Во-первых, зачем вообще это нужно? Для чего нужны рекурсивные запросы? — В общем, рекурсивные запросы удобны при работе с самореферентными данными или графическими/древовидными структурами данных. Вот лишь несколько примеров таких вариантов использования:

Самореферентные данные:

• Менеджер -> Подчиненный (сотрудник) Отношения
• Категория -> Подкатегория -> Товар Отношения
• Графики — Полет (Путешествие на самолете) карта

Деревья:

• Любые системы таксономии — животные, таксономия …
• Ссылки между статьями — например, в Википедии
• Раздел комментариев — например, темы на Reddit

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

Рекурсивные запросы используют то, что называется CTE — Common Table expression , которое представляет собой предложение SQL WITH , чаще используемое для упрощения очень сложных запросов (подзапросов). Давайте посмотрим на пример этого:

Это очень простой пример, но вы, конечно, можете себе представить, как его можно использовать, чтобы сделать очень сложные запросы, содержащие несколько подзапросов, более читабельными.

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

Таблица сотрудников

Вот SQL для создания такой таблицы, включая некоторые данные для игры: запросы!

Итерировать — человечно, повторять — божественно — Л. Питер Дойч

Давайте начнем с формальной структуры рекурсивного SQL-запроса:

Это выглядит довольно просто, но мало что нам говорит, поэтому давайте см. удобочитаемый пример:

И вывод этого запроса:

В этом примере наш нерекурсивный базовый случай — это просто SELECT 1 , а рекурсивная часть — SELECT n+1 FROM nums WHERE n+1 <= 10 . Эта часть создает рекурсию, ссылаясь на имя CTE ( nums ) и объединяя все результаты. В конце у нас есть предложение WHERE для завершения рекурсии, чтобы наш запрос не выполнялся бесконечно.

В предыдущем разделе показан очень простой пример, который объясняет, как это работает, но не показывает, как рекурсивный CTE может нам помочь. Итак, давайте рассмотрим несколько примеров, используя данные иерархии Менеджер — Подчиненный сверху: по сути это поддерево, начинающееся с какого-то конкретного сотрудника. В качестве базового случая здесь мы выбираем одного сотрудника (менеджера) по идентификатору, а также включаем уровень , чтобы указать, на сколько уровней вниз мы спустились в дереве. В рекурсивной части мы ПРИСОЕДИНЯЙТЕ таблицу сотрудников к CTE ( менеджеров ), используя идентификаторы менеджеров. Опять же, мы включаем уровня , увеличивая уровень результатов предыдущего шага рекурсии. Вот результат:

Чтобы лучше представить, что там происходит, посмотрите на следующее дерево. Выделенная часть — это то, что возвращает запрос:

Другой практический пример с использованием тех же данных — вычисление степеней разделения между двумя сотрудниками :

Рекурсивный CTE очень похож на тот, что был в предыдущем примере. Для простоты мы выбираем только ID и градусов (вместо уровня). Затем нам нужен запрос, который ищет и сообщает нам степени разделения для 2 сотрудников — для этого мы присоединяем таблицу сотрудников к нашей ранее созданной таблице rec , содержащей идентификаторы, и градусов с использованием идентификаторов в соответствующих таблицах. В последней части SELECT мы немного форматируем, чтобы получить более приятный результат, а в WHERE 9В пункте 0026 мы дополнительно указываем другого (второго) сотрудника, для которого мы хотим получить степени разделения. Вывод:

Опять же, играя с теми же данными, давайте выясним, какой может быть прогресс работы в гипотетической компании:

На этот раз мы запускаем рекурсию снизу вверх. Это достигается за счет перестановки пункта ON по сравнению с предыдущими примерами. Чтобы создать читаемый вывод, мы используем функцию STRING_AGG , которая берет строки сверху SELECT и объединяет их, используя > в качестве разделителя. Вот что дает нам запрос:

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

График

В качестве примера мы сделаем очевидную вещь — найдем пути в виде графика. Для этого нам понадобится PostgreSQL 9.0150 специальный — МАССИВ тип данных. Массивы не являются обычной функцией реляционных баз данных, но здесь они очень удобны для хранения путей. Если вы не знакомы с этим типом данных, то для понимания SQL вам понадобятся следующие вещи:

• Создать массив — МАССИВ[значение_1, значение_2]
• Объединить массив — МАССИВ[значение_1, значение_2] || МАССИВ[значение_3]
• ВСЕ функция - "x" != ВСЕ(МАССИВ["a", "b", "c"]) - сравнивает "x" для каждого значения в массиве, результат равен true , если все сравнения приводят к true

Хорошо, теперь для запроса:

Приведенный выше запрос сначала выбирает всех прямых соседей в нерекурсивном случай. Затем в рекурсивной части мы основываемся на базовом случае (и во время последующих итераций на предыдущих рекурсивных случаях) и добавляем доступное ребро к существующим путям и заменяем пункт назначения тем же ребром. Мы делаем это для каждой строки, где конец существующего пути ( dest ) совпадает с началом выбранного ребра и где новый пункт назначения еще не находится на пути (для предотвращения циклов). И это полный вывод:

Помимо полезных вещей выше, вы также можете использовать рекурсивную CTE с бесконечными потоками данных:

Рекурсивная таблица строится итеративно с каждым шагом, и каждый шаг производит конечное количество строк , поэтому можно использовать LIMIT для получения первых n строк из бесконечного запроса!

В качестве небольшого бонуса я также хочу показать, что возможно (начиная с PostgreSQL 9.3) даже создавать рекурсивные представления:

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

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