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

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

В стандарте SQL:1999 иерархические запросы реализуются посредством рекурсивных общих табличных выражений (CTE). В отличие от более раннего предложения Oracle Connect-by , рекурсивные CTE с самого начала разрабатывались с семантикой фиксированной точки. ^[1] Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. ^[1] Рекурсивные CTE также поддерживаются Microsoft SQL Server (начиная с SQL Server 2008 R2). ^[2] Жар-птица 2.1 , ^[3] PostgreSQL 8.4+ , ^[4] SQLite 3.8.3+ , ^[5] IBM Informix версии 11.50+, CUBRID , MariaDB 10.2+ и MySQL 8.0.1+ . ^[6] В Tableau есть документация, описывающая, как можно использовать CTE. TIBCO Spotfire не поддерживает CTE, а в реализации Oracle 11g Release 2 отсутствует семантика фиксированных точек.

Без общих табличных выражений или предложений linked-by можно создавать иерархические запросы с помощью определяемых пользователем рекурсивных функций. ^[7]

Общее табличное выражение

Общее табличное выражение, или CTE, (в SQL ) — это временный именованный набор результатов, полученный из простого запроса и определенный в области выполнения SELECT, INSERT, UPDATE, или DELETE заявление.

CTE можно рассматривать как альтернативу производным таблицам ( подзапросам ), представлениям и встроенным пользовательским функциям.

Общие табличные выражения поддерживаются Teradata (начиная с версии 14), IBM Db2 , Informix (начиная с версии 14.1), Firebird (начиная с версии 2.1), ^[8] Microsoft SQL Server (начиная с версии 2005), Oracle (с рекурсией, начиная с 11g версии 2), PostgreSQL (начиная с 8.4), MariaDB (начиная с 10.2), MySQL (начиная с 8.0), SQLite (начиная с 3.8.3), HyperSQL , Informix ( с 14.10), ^[9] Google BigQuery , Sybase (начиная с версии 9), Vertica , H2 (экспериментальная), ^[10] и многие другие . Oracle называет CTE «факторингом подзапросов». ^[11]

Синтаксис CTE (который может быть или не быть рекурсивным) следующий:

WITH [RECURSIVE] with_query [, ...]
SELECT ...

где with_queryсинтаксис:

query_name [ (column_name [,...]) ] AS (SELECT ...)

Рекурсивные CTE можно использовать для обхода отношений (в виде графиков или деревьев), хотя синтаксис гораздо сложнее, поскольку не создаются автоматические псевдостолбцы (например, LEVEL ниже ); если они желательны, их необходимо создать в коде. См. документацию MSDN. ^[2] или документация IBM ^[12]^[13] для учебных примеров.

The RECURSIVE Ключевое слово обычно не требуется после AND в системах, отличных от PostgreSQL. ^[14]

В SQL:1999 рекурсивный (CTE) запрос может появляться везде, где разрешен запрос. Например, можно назвать результат, используя CREATE [ RECURSIVE] VIEW. ^[15] Использование CTE внутри INSERT INTOможно заполнить таблицу данными, сгенерированными в результате рекурсивного запроса; С помощью этого метода возможна генерация случайных данных без использования каких-либо процедурных операторов. ^[16]

Некоторые базы данных, такие как PostgreSQL, поддерживают более короткий формат CREATE RECURSIVE VIEW, который внутренне преобразуется в кодирование With RECURSIVE. ^[17]

пример рекурсивного запроса, вычисляющего факториал Ниже приведен чисел от 0 до 9:

WITH recursive temp (n, fact) AS (
    SELECT 0,   1                                   -- Initial Subquery
  UNION ALL
    SELECT n+1, (n+1)*fact  FROM temp  WHERE n < 9  -- Recursive Subquery
)
SELECT * FROM temp;

ПОДКЛЮЧИТЬСЯ

Альтернативный синтаксис — нестандартный CONNECT BY построить; он был представлен Oracle в 1980-х годах. ^[18] До Oracle 10g эта конструкция была полезна только для обхода ациклических графов, поскольку возвращала ошибку при обнаружении каких-либо циклов; в версии 10g Oracle представила функцию NOCYCLE (и ключевое слово), благодаря которой обход работает и при наличии циклов. ^[19]

CONNECT BY поддерживается Snowflake , EnterpriseDB , ^[20] база данных Oracle , ^[21] КУБРИД , ^[22] IBM Информикс ^[23] и IBM Db2 , но только в том случае, если он включен в качестве режима совместимости. ^[24] Синтаксис следующий:

SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ]
[ GROUP BY ... ]
[ HAVING ... ]
...

Например,

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Вывод приведенного выше запроса будет выглядеть так:

 level |  employee   | empno | manager
-------+-------------+-------+---------
     1 | KING        |  7839 |
     2 |   JONES     |  7566 |    7839
     3 |     SCOTT   |  7788 |    7566
     4 |       ADAMS |  7876 |    7788
     3 |     FORD    |  7902 |    7566
     4 |       SMITH |  7369 |    7902
     2 |   BLAKE     |  7698 |    7839
     3 |     ALLEN   |  7499 |    7698
     3 |     WARD    |  7521 |    7698
     3 |     MARTIN  |  7654 |    7698
     3 |     TURNER  |  7844 |    7698
     3 |     JAMES   |  7900 |    7698
     2 |   CLARK     |  7782 |    7839
     3 |     MILLER  |  7934 |    7782
(14 rows)

Псевдоколонны

УРОВЕНЬ
CONNECT_BY_ISLEAF
CONNECT_BY_ISCYCLE
CONNECT_BY_ROOT

Унарные операторы

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

SELECT
  ename                           "Employee",
  CONNECT_BY_ROOT ename           "Manager",
  LEVEL-1                         "Pathlen",
  SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
WHERE LEVEL > 1 AND deptno = 10
CONNECT BY PRIOR empno = mgr
ORDER BY "Employee", "Manager", "Pathlen", "Path";

Функции

SYS_CONNECT_BY_PATH

См. также

Datalog также реализует запросы фиксированных точек.
Запросы по обычному пути — это особый вид рекурсивных запросов в графовых базах данных.
Дедуктивные базы данных
Иерархическая модель
Доступность
Транзитивное замыкание
Древовидная структура

Ссылки

^ Перейти обратно: ^а ^б Джим Мелтон; Алан Р. Саймон (2002). SQL:1999: Понимание компонентов реляционного языка . Морган Кауфманн. ISBN 978-1-55860-456-8 .
^ Перейти обратно: ^а ^б Майкрософт. «Рекурсивные запросы с использованием общих табличных выражений» . Проверено 23 декабря 2009 г.
^ Хелен Борри (15 июля 2008 г.). «Примечания к выпуску Firebird 2.1» . Проверено 24 ноября 2015 г.
^ «С ЗАПРОСАМИ» . 10 февраля 2022. PostgreSQL
^ «С пунктом» . SQLite
^ «Лаборатория MySQL 8.0: [Рекурсивные] общие табличные выражения в MySQL (CTE)» . mysqlserverteam.com
^ Корпорация Paragon: Использование пользовательских функций PostgreSQL для решения проблемы дерева , 15 февраля 2004 г., по состоянию на 19 сентября 2015 г.
^ https://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf ^{[ только URL-адрес PDF ]}
^ возможно до 14.10 с временными таблицами
^ "Передовой" .
^ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Про Oracle SQL . Апресс. п. 283.ISBN 978-1-4302-3228-5 .
^ «Документация IBM» .
^ «Документация IBM» .
^ Регина Обе; Лео Сюй (2012). PostgreSQL: запуск и работа . О'Рейли Медиа. п. 94. ИСБН 978-1-4493-2633-3 .
^ Джим Мелтон; Алан Р. Саймон (2002). SQL:1999: Понимание компонентов реляционного языка . Морган Кауфманн. п. 352. ИСБН 978-1-55860-456-8 .
^ Дон Чемберлин (1998). Полное руководство по универсальной базе данных DB2 . Морган Кауфманн. стр. 253–254. ISBN 978-1-55860-482-7 .
^ «Создать представление» . 10 февраля 2022 г.
^ Бенедикт, М.; Сенелларт, П. (2011). «Базы данных». В Блюме, Эдвард К.; Ахо, Альфред В. (ред.). Информатика. Аппаратное обеспечение, программное обеспечение и его суть . п. 189. дои : 10.1007/978-1-4614-1168-0_10 . ISBN 978-1-4614-1167-3 .
^ Санджай Мишра; Алан Болье (2004). Владение Oracle SQL . О'Рейли Медиа, Инк. с. 227. ИСБН 978-0-596-00632-7 .
^ Иерархические запросы , заархивировано 21 июня 2008 г. в Wayback Machine , EnterpriseDB.
^ Иерархические запросы , Oracle
^ «Иерархический запрос CUBRID» . Проверено 11 февраля 2013 г.
^ Иерархический пункт , IBM Informix
^ Джонатан Генник (2010). Карманное руководство по SQL (3-е изд.). О'Рейли Медиа, Инк. с. 8. ISBN 978-1-4493-9409-7 .

Дальнейшее чтение

Си Джей Дэйт (2011). SQL и реляционная теория: как писать точный код SQL (2-е изд.). О'Рейли Медиа. стр. 159–163. ISBN 978-1-4493-1640-2 .

Академические учебники . Обратите внимание, что они охватывают только стандарт SQL:1999 (и журнал данных), но не расширение Oracle.

Авраам Зильбершац; Генри Корт; С. Сударшан (2010). Концепции системы баз данных (6-е изд.). МакГроу-Хилл. стр. 187–192. ISBN 978-0-07-352332-3 .
Рагху Рамакришнан; Йоханнес Герке (2003). Системы управления базами данных (3-е изд.). МакГроу Хилл. ISBN 978-0-07-246563-1 . Глава 24.
Эктор Гарсиа-Молина; Джеффри Д. Уллман; Дженнифер Видом (2009). Системы баз данных: полная книга (2-е изд.). Пирсон Прентис Холл. стр. 437–445. ISBN 978-0-13-187325-4 .

Внешние ссылки

[JimMelton1-1] Перейти обратно: ^а ^б Джим Мелтон; Алан Р. Саймон (2002). SQL:1999: Понимание компонентов реляционного языка . Морган Кауфманн. ISBN 978-1-55860-456-8 .

[msdnRCTEs-2] Перейти обратно: ^а ^б Майкрософт. «Рекурсивные запросы с использованием общих табличных выражений» . Проверено 23 декабря 2009 г.

[3] Хелен Борри (15 июля 2008 г.). «Примечания к выпуску Firebird 2.1» . Проверено 24 ноября 2015 г.

[4] «С ЗАПРОСАМИ» . 10 февраля 2022. PostgreSQL

[5] «С пунктом» . SQLite

[6] «Лаборатория MySQL 8.0: [Рекурсивные] общие табличные выражения в MySQL (CTE)» . mysqlserverteam.com

[7] Корпорация Paragon: Использование пользовательских функций PostgreSQL для решения проблемы дерева , 15 февраля 2004 г., по состоянию на 19 сентября 2015 г.

[8] ttps://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf ^{[ только URL-адрес PDF ]}

[9] возможно до 14.10 с временными таблицами

[10] "Передовой" .

[MortonSands2010-11] Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Про Oracle SQL . Апресс. п. 283.ISBN 978-1-4302-3228-5 .

[12] «Документация IBM» .

[13] «Документация IBM» .

[ObeHsu2012-14] Регина Обе; Лео Сюй (2012). PostgreSQL: запуск и работа . О'Рейли Медиа. п. 94. ИСБН 978-1-4493-2633-3 .

[15] Джим Мелтон; Алан Р. Саймон (2002). SQL:1999: Понимание компонентов реляционного языка . Морган Кауфманн. п. 352. ИСБН 978-1-55860-456-8 .

[ChamberlinChamberlin1998-16] Дон Чемберлин (1998). Полное руководство по универсальной базе данных DB2 . Морган Кауфманн. стр. 253–254. ISBN 978-1-55860-482-7 .

[17] «Создать представление» . 10 февраля 2022 г.

[18] Бенедикт, М.; Сенелларт, П. (2011). «Базы данных». В Блюме, Эдвард К.; Ахо, Альфред В. (ред.). Информатика. Аппаратное обеспечение, программное обеспечение и его суть . п. 189. дои : 10.1007/978-1-4614-1168-0_10 . ISBN 978-1-4614-1167-3 .

[MishraBeaulieu2004-19] Санджай Мишра; Алан Болье (2004). Владение Oracle SQL . О'Рейли Медиа, Инк. с. 227. ИСБН 978-0-596-00632-7 .

[20] Иерархические запросы , заархивировано 21 июня 2008 г. в Wayback Machine , EnterpriseDB.

[21] Иерархические запросы , Oracle

[22] «Иерархический запрос CUBRID» . Проверено 11 февраля 2013 г.

[23] Иерархический пункт , IBM Informix

[Gennick2010-24] Джонатан Генник (2010). Карманное руководство по SQL (3-е изд.). О'Рейли Медиа, Инк. с. 8. ISBN 978-1-4493-9409-7 .

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

[17]

[18]

[19]

[20]

[21]

[22]

[23]

[24]

v т и Системы управления базами данных
Типы	Объектно-ориентированный сравнение Реляционный список сравнение Ключ-значение Столбцово-ориентированный список Документоориентированный Ширококолоночный магазин График NoSQL НьюSQL В памяти список Мультимодель сравнение Облако База данных на основе блокчейна
Концепции	База данных КИСЛОТА Аксиомы Армстронга 12 правил Кодда Теорема CAP CRUD Нулевой Кандидатский ключ Внешний ключ Superkey Суррогатный ключ Уникальный ключ
Объекты	Связь стол столбец ряд Вид Сделка Журнал транзакций Курок Индекс Хранимая процедура Курсор Раздел
Компоненты	Управление параллелизмом Словарь данных JDBC XQJ ОДБК Язык запросов Оптимизатор запросов Система переписывания запросов План запроса
Функции	Администрация Оптимизация запросов Репликация Шардинг
Связанные темы	Модели баз данных Нормализация базы данных Хранение базы данных Распределенная база данных Система федеративных баз данных Ссылочная целостность Реляционная алгебра Реляционное исчисление Реляционная модель Объектно-реляционная база данных Обработка транзакций
Категория Контур ВикиПроект