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

Hierarchical and recursive queries in SQL

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

В стандартной SQL: 1999 иерархические запросы реализуются посредством рекурсивных общие табличные выражения (CTE). В отличие от более ранних пункт подключения, рекурсивные 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+, Кубрид, MariaDB 10.2+ и MySQL 8.0.1+,[6]. Tableau имеет документацию описание того, как можно использовать CTE. TIBCO Spotfire не поддерживает CTE, а в реализации Oracle 11g Release 2 отсутствует семантика фиксированных точек.

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

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

Распространенное табличное выражение или CTE (в SQL) - это временный именованный набор результатов, полученный из простого запроса и определенный в рамках области выполнения ВЫБРАТЬ, ВСТАВЛЯТЬ, ОБНОВИТЬ, или же УДАЛИТЬ утверждение.

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

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

Синтаксис рекурсивного CTE следующий:

С [РЕКУРСИВНЫЙ] with_query [, ...]ВЫБРАТЬ...

куда with_queryСинтаксис:

query_name [ (имя_столбца [,...]) ] В КАЧЕСТВЕ (ВЫБРАТЬ ...)

Рекурсивные CTE (или «факторизация рекурсивных подзапросов»)[11] на жаргоне Oracle) может использоваться для обхода отношений (в виде графиков или деревьев), хотя синтаксис намного сложнее, потому что не создаются автоматические псевдостолбцы (например, УРОВЕНЬ ниже); если они желательны, они должны быть созданы в коде. См. Документацию MSDN[2] или документация IBM[12][13] для учебных примеров.

В РЕКУРСИВНЫЙ ключевое слово обычно не требуется после WITH в системах, отличных от PostgreSQL.[14]

В SQL: 1999 рекурсивный (CTE) запрос может появляться везде, где разрешен запрос. Например, результат можно назвать с помощью СОЗДАЙТЕ [РЕКУРСИВНЫЙ] ПОСМОТРЕТЬ.[15] Использование CTE внутри ВСТАВИТЬ Вможно заполнить таблицу данными, созданными в результате рекурсивного запроса; Генерация случайных данных возможна с использованием этого метода без использования каких-либо процедурных инструкций.[16]

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

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

С РЕКУРСИВНЫЙ темп (п, факт) В КАЧЕСТВЕ (ВЫБРАТЬ 0, 1 - Первоначальный подзапрос  СОЮЗ ВСЕ  ВЫБРАТЬ п+1, (п+1)*факт ИЗ темп - Рекурсивный подзапрос         КУДА п < 9)ВЫБРАТЬ * ИЗ темп;

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

Альтернативный синтаксис - нестандартный ПОДКЛЮЧИТЬСЯ построить; он был представлен Oracle в 1980-х годах.[18] До Oracle 10g конструкция была полезна только для обхода ациклических графов, потому что она возвращала ошибку при обнаружении любых циклов; в версии 10g Oracle представила функцию NOCYCLE (и ключевое слово), благодаря которой обход работает также и при наличии циклов.[19]

ПОДКЛЮЧИТЬСЯ поддерживается EnterpriseDB,[20] База данных Oracle,[21] Кубрид,[22] IBM Informix[23] и DB2 хотя только если он включен как режим совместимости.[24] Синтаксис следующий:

ВЫБРАТЬ select_listИЗ table_expression[ КУДА ... ][ НАЧНИТЕ С start_expression ]СОЕДИНЯТЬ К [НОЦИКЛ] { ПРЕЖНИЙ child_expr = parent_expr | parent_expr = ПРЕЖНИЙ child_expr }[ ПОРЯДОК БРАТЬЯ И СЕСТРЫ К column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ][ ГРУППА К ... ][ ИМЕЕТ ... ]...
Например,
ВЫБРАТЬ УРОВЕНЬ, LPAD (' ', 2 * (УРОВЕНЬ - 1)) || эмаль "наемный рабочий", empno, мгр "управляющий делами"ИЗ emp НАЧНИТЕ С мгр ЯВЛЯЕТСЯ НОЛЬСОЕДИНЯТЬ К ПРЕЖНИЙ empno = мгр;

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

 уровень | сотрудник | 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 рядов)

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

  • УРОВЕНЬ
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

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

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

ВЫБРАТЬ эмаль "Наемный рабочий", CONNECT_BY_ROOT эмаль "Управляющий делами",УРОВЕНЬ-1 «Патлен», SYS_CONNECT_BY_PATH(эмаль, '/') "Дорожка"ИЗ empКУДА УРОВЕНЬ > 1 и deptno = 10СОЕДИНЯТЬ К ПРЕЖНИЙ empno = мгрПОРЯДОК К "Наемный рабочий", "Управляющий делами", «Патлен», "Дорожка";

Функции

  • SYS_CONNECT_BY_PATH

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

Рекомендации

  1. ^ а б Джим Мелтон; Алан Р. Саймон (2002). SQL: 1999: понимание компонентов реляционного языка. Морган Кауфманн. ISBN 978-1-55860-456-8.
  2. ^ а б Microsoft. «Рекурсивные запросы с использованием общих табличных выражений». Получено 2009-12-23.
  3. ^ Хелен Борри (2008-07-15). «Примечания к выпуску Firebird 2.1». Получено 2015-11-24.
  4. ^ «С запросами». PostgreSQL
  5. ^ "С пунктом". SQLite
  6. ^ "MySQL 8.0 Labs: [Рекурсивные] общие табличные выражения в MySQL (CTE)". mysqlserverteam.com
  7. ^ Корпорация Paragon: Использование пользовательских функций PostgreSQL для решения проблемы дерева, 15 февраля 2004 г., по состоянию на 19 сентября 2015 г.
  8. ^ Сравнение систем управления реляционными базами данных # Возможности базы данных
  9. ^ http://www.h2database.com/html/advanced.html#recursive_queries
  10. ^ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Профессиональный Oracle SQL. Апресс. п. 283. ISBN 978-1-4302-3228-5.
  11. ^ Карен Мортон; Робин Сэндс; Джаред Стилл; Риядж Шамсудин; Керри Осборн (2010). Профессиональный Oracle SQL. Апресс. п. 304. ISBN 978-1-4302-3228-5.
  12. ^ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
  13. ^ http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
  14. ^ Регина Обе; Лео Сюй (2012). PostgreSQL: готово и работает. O'Reilly Media. п. 94. ISBN 978-1-4493-2633-3.
  15. ^ Джим Мелтон; Алан Р. Саймон (2002). SQL: 1999: понимание компонентов реляционного языка. Морган Кауфманн. п. 352. ISBN 978-1-55860-456-8.
  16. ^ Дон Чемберлин (1998). Полное руководство по DB2 Universal Database. Морган Кауфманн. С. 253–254. ISBN 978-1-55860-482-7.
  17. ^ https://www.postgresql.org/docs/10/static/sql-createview.html
  18. ^ Бенедикт, М .; Сенелларт, П. (2011). «Базы данных». В Blum, Edward K .; Ахо, Альфред В. (ред.). Информатика. Аппаратное и программное обеспечение и его суть. п. 189. Дои:10.1007/978-1-4614-1168-0_10. ISBN 978-1-4614-1167-3.
  19. ^ Санджай Мишра; Алан Больё (2004). Освоение Oracle SQL. O'Reilly Media, Inc. стр. 227. ISBN 978-0-596-00632-7.
  20. ^ Иерархические запросы В архиве 2008-06-21 на Wayback Machine, EnterpriseDB
  21. ^ Иерархические запросы, Oracle
  22. ^ «Иерархический запрос CUBRID». Получено 11 февраля 2013.
  23. ^ Иерархическая оговорка, IBM Informix
  24. ^ Джонатан Генник (2010). Карманное руководство по SQL (3-е изд.). O'Reilly Media, Inc. стр. 8. ISBN 978-1-4493-9409-7.

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

  • C. J. Date (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.

внешняя ссылка