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

редактировать

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

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

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

Содержание

  • 1 Общее табличное выражение
  • 2 CONNECT BY
    • 2.1 Псевдостолбцы
    • 2.2 Унарные операторы
    • 2.3 Функции
  • 3 См. Также
  • 4 Ссылки
  • 5 Дополнительная литература
  • 6 Внешние ссылки

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

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

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

Общие табличные выражения поддерживаются Teradata, DB2, Informix (начиная с версии 14.1), Firebird, Microsoft SQL Server, Oracle (с рекурсией, начиная с выпуска 2 11g), PostgreSQL (начиная с 8.4), MariaDB (с 10.2), MySQL (начиная с 8.0), SQLite (начиная с 3.8.3), HyperSQL и H2 (экспериментально). Oracle называет CTE «факторингом подзапроса».

Синтаксис рекурсивного CTE выглядит следующим образом:

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

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

имя_запроса [(имя_столбца [,...])] AS (SELECT...)

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

Ключевое слово RECURSIVEобычно не требуется после WITH в системах, отличных от PostgreSQL.

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

Некоторые базы данных, такие как PostgreSQL, поддерживают более короткий формат CREATE RECURSIVE VIEW, который внутренне переведен в кодирование WITH RECURSIVE.

Пример рекурсивного запроса, вычисляющего факториал чисел от 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) SELECT * FROM temp;

CONNECT BY

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

CONNECT BYподдерживается EnterpriseDB, Oracle database, CUBRID, IBM Informix и DB2, но только если он включен как режим совместимости. Синтаксис следующий:

SELECT select_list FROM table_expression [WHERE...] [НАЧАТЬ С начального_выражения] CONNECT BY [NOCYCLE] {PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr} [ЗАКАЗАТЬ SIBLINGS ПО столбцу1 [ASC | DESC] [, столбец2 [ASC | DESC]]...] [GROUP BY...] [HAVING...]...
Например,
SELECT LEVEL, LPAD ('', 2 * (LEVEL - 1)) || ename "сотрудник", empno, mgr "менеджер" ИЗ emp НАЧАТЬ С mgr НУЛЕНО ПОДКЛЮЧИТЬСЯ ПО ПРИОРУ empno = mgr;

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

level | сотрудник | empno | менеджер ------- + ------------- + ------- + --------- 1 | КОРОЛЬ | 7839 | 2 | ДЖОНС | 7566 | 7839 3 | СКОТТ | 7788 | 7566 4 | АДАМС | 7876 | 7788 3 | FORD | 7902 | 7566 4 | СМИТ | 7369 | 7902 2 | БЛЕЙК | 7698 | 7839 3 | АЛЛЕН | 7499 | 7698 3 | WARD | 7521 | 7698 3 | МАРТИН | 7654 | 7698 3 | ТЕРНЕР | 7844 | 7698 3 | ДЖЕЙМС | 7900 | 7698 2 | КЛАРК | 7782 | 7839 3 | МИЛЛЕР | 7934 | 7782 (14 строк)

Псевдостолбцы

  • LEVEL
  • 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, '/')" Путь "ОТ emp ГДЕ УРОВЕНЬ>1 и deptno = 10 ПОДКЛЮЧЕНИЕ ПО ПРИОРУ empno = mgr ЗАКАЗАТЬ ПО" Сотруднику "," Менеджеру "," Пути "," Пути ";

Функции

  • SYS_CONNECT_BY_PATH

См. Также

Ссылки

Дополнительная литература

  • C. Дж. Дэйт (2011). SQL и теория отношений: как писать точный код SQL (2-е изд.). O'Reilly Media. С. 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.

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

Последняя правка сделана 2021-05-23 11:21:07
Содержание доступно по лицензии CC BY-SA 3.0 (если не указано иное).
Обратная связь: support@alphapedia.ru
Соглашение
О проекте