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

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

В стандарте SQL:1999 иерархические запросы реализуются посредством рекурсивных общих табличных выражений (CTE). В отличие от более раннего предложения Oracle connect-by, рекурсивные CTE были разработаны с семантикой фиксированной точки с самого начала. [1] Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. [1] Рекурсивные CTE также поддерживаются Microsoft SQL Server (начиная с SQL Server 2008 R2), [2] Firebird 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 отсутствует семантика фиксированных точек.

Без общих табличных выражений или связанных предложений можно реализовать иерархические запросы с помощью определяемых пользователем рекурсивных функций. [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 release 2), PostgreSQL (начиная с версии 8.4), MariaDB (начиная с версии 10.2 [9] ), MySQL (начиная с версии 8.0), SQLite (начиная с версии 3.8.3), HyperSQL , Informix (начиная с версии 14.10), [10] Google BigQuery , Sybase (начиная с версии 9), Vertica , H2 (экспериментально), [11] и многими другими . Oracle называет CTE «subquery factoring». [12]

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

С [ РЕКУРСИВНЫМ ] с_запросом [, ...] ВЫБРАТЬ ...

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

имя_запроса [ ( имя_столбца [,...]) ] КАК ( ВЫБРАТЬ ...)

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

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

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

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

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

WITH recursive temp ( n , fact ) AS ( SELECT 0 , 1 — Начальный подзапрос UNION ALL SELECT n + 1 , ( n + 1 ) * fact FROM temp WHERE n < 9 — Рекурсивный подзапрос ) SELECT * FROM temp ;

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

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

CONNECT BYподдерживается Snowflake , EnterpriseDB , [21] базой данных Oracle , [22] CUBRID , [23] IBM Informix [24] и IBM Db2 , но только если он включен как режим совместимости. [25] Синтаксис следующий:

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 ... ] ...
Например,
ВЫБРАТЬ УРОВЕНЬ , LPAD ( ' ' , 2 * ( УРОВЕНЬ - 1 )) || ename "сотрудник" , empno , mgr "менеджер" ИЗ emp НАЧАТЬ С mgr ЕСТЬ NULL ПОДКЛЮЧИТЬ ПО ПРЕДЫДУЩЕЙ empno = mgr ;

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

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

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

  • УРОВЕНЬ
  • ПОДКЛЮЧИТЬСЯ_ПО_ISLEAF
  • ПОДКЛЮЧИТЬСЯ_ПО_ISCYCLE
  • ПОДКЛЮЧИТЬ_БИЗНЕС_КОРНЕМ

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

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

SELECT ename "Сотрудник" , CONNECT_BY_ROOT ename "Менеджер" , LEVEL - 1 "Pathlen" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Путь" FROM emp WHERE LEVEL > 1 AND deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Сотрудник" , "Менеджер" , "Pathlen" , "Путь" ;

Функции

  • SYS_CONNECT_BY_PATH

Смотрите также

Ссылки

  1. ^ ab Джим Мелтон; Алан Р. Саймон (2002). SQL:1999: Понимание реляционных языковых компонентов. Морган Кауфманн. ISBN 978-1-55860-456-8.
  2. ^ ab Microsoft. "Рекурсивные запросы с использованием общих табличных выражений" . Получено 2009-12-23 .
  3. ^ Хелен Борри (2008-07-15). "Firebird 2.1 Release Notes" . Получено 2015-11-24 .
  4. ^ «WITH Queries». 10 февраля 2022 г.PostgreSQL
  5. ^ «С пунктом».SQLite
  6. ^ "MySQL 8.0 Labs: [Рекурсивные] общие табличные выражения в MySQL (CTE)". Архивировано из оригинала 2019-08-16 . Получено 2017-12-20 .mysqlserverteam.com
  7. ^ Paragon corporation: Использование пользовательских функций PostgreSQL для решения проблемы дерева, 15 февраля 2004 г., дата обращения 19 сентября 2015 г.
  8. ^ https://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf [ пустой URL-адрес PDF ]
  9. ^ "MariaDB 10.2.0 Changelog". База знаний MariaDB . Получено 2024-12-22 .
  10. ^ возможно до 14.10 с временными таблицами https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix
  11. ^ "Расширенный".
  12. ^ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Про Oracle SQL. Апресс. п. 283. ИСБН 978-1-4302-3228-5.
  13. ^ «Документы IBM».
  14. ^ «Документы IBM».
  15. ^ Регина Обе; Лео Хсу (2012). PostgreSQL: Up and Running. O'Reilly Media. стр. 94. ISBN 978-1-4493-2633-3.
  16. ^ Джим Мелтон; Алан Р. Саймон (2002). SQL:1999: Понимание реляционных языковых компонентов. Морган Кауфманн. стр. 352. ISBN 978-1-55860-456-8.
  17. ^ Дон Чемберлин (1998). Полное руководство по универсальной базе данных DB2. Морган Кауфманн. С.  253–254 . ISBN 978-1-55860-482-7.
  18. ^ «Создать представление». 10 февраля 2022 г.
  19. ^ Бенедикт, М.; Сенелларт, П. (2011). «Базы данных». В Блюм, Эдвард К.; Ахо, Альфред В. (ред.). Компьютерные науки. Аппаратное обеспечение, программное обеспечение и его сердце . стр. 189. doi :10.1007/978-1-4614-1168-0_10. ISBN 978-1-4614-1167-3.
  20. ^ Санджай Мишра; Алан Болье (2004). Мастерство Oracle SQL. O'Reilly Media, Inc. стр. 227. ISBN 978-0-596-00632-7.
  21. ^ Иерархические запросы, архив 2008-06-21 в Wayback Machine , EnterpriseDB
  22. ^ Иерархические запросы, Oracle
  23. ^ "CUBRID Hierarchical Query". Архивировано из оригинала 14 февраля 2013 г. Получено 11 февраля 2013 г.
  24. ^ Иерархическое положение, IBM Informix
  25. ^ Джонатан Генник (2010). Карманный справочник SQL (3-е изд.). O'Reilly Media, Inc. стр. 8. ISBN 978-1-4493-9409-7.

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

  • CJ Date (2011). SQL и реляционная теория: как писать точный код SQL (2-е изд.). O'Reilly Media. С.  159–163 . ISBN 978-1-4493-1640-2.

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

  • Авраам Зильбершатц; Генри Корт; С. Сударшан (2010). Концепции систем баз данных (6-е изд.). McGraw-Hill. стр.  187–192 . ISBN 978-0-07-352332-3.
  • Рагху Рамакришнан; Йоханнес Герке (2003). Системы управления базами данных (3-е изд.). МакГроу-Хилл. ISBN 978-0-07-246563-1.Глава 24.
  • Гектор Гарсия-Молина; Джеффри Д. Ульман; Дженнифер Видом (2009). Системы баз данных: полная книга (2-е изд.). Pearson Prentice Hall. стр.  437–445 . ISBN 978-0-13-187325-4.
  • https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring
  • http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/
  • https://web.archive.org/web/20131114094211/http://gennick.com/with.html
  • http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf
  • http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html
Retrieved from "https://en.wikipedia.org/w/index.php?title=Hierarchical_and_recursive_queries_in_SQL&oldid=1265726824"