Иерархические и рекурсивные запросы в SQL - Hierarchical and recursive queries in SQL
Иерархический запрос представляет собой тип SQL запроса , который обрабатывает иерархическую модель данных. Это частные случаи более общих рекурсивных запросов фиксированной точки, которые вычисляют транзитивные замыкания .
В стандартном SQL: 1999 иерархические запросы реализованы посредством рекурсивных общих табличных выражений (CTE). В отличие от более раннего предложения Oracle connect-by , рекурсивные CTE с самого начала разрабатывались с семантикой фиксированных точек . Рекурсивные 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 отсутствует семантика фиксированных точек.
Без общих табличных выражений или связанных предложений можно выполнять иерархические запросы с определяемыми пользователем рекурсивными функциями.
Общее табличное выражение
Общее выражение таблицы или КТР, (в SQL ) является временным назван результирующий набор, полученный из простого запроса и определяется в пределах выполнения сферы действия SELECT
, INSERT
, UPDATE
или DELETE
заявления.
CTE можно рассматривать как альтернативу производным таблицам ( подзапросам ), представлениям и встроенным пользовательским функциям.
Общие табличные выражения поддерживаются Teradata (начиная с версии 14), DB2 , Informix (начиная с версии 14.1), Firebird (начиная с версии 2.1), Microsoft SQL Server (начиная с версии 2005), Oracle (с рекурсией, начиная с версии 2 11g). ), PostgreSQL (начиная с 8.4), MariaDB (с 10.2), MySQL (с 8.0), SQLite (с 3.8.3), HyperSQL , Informix (с 14.10), Google BigQuery , Sybase (начиная с версии 9), Vertica , H2 (экспериментальный) и многие другие . Oracle называет CTE «факторингом подзапросов».
Синтаксис CTE (который может быть рекурсивным, а может и не быть) выглядит следующим образом:
WITH [RECURSIVE] with_query [, ...]
SELECT ...
где with_query
синтаксис:
query_name [ (column_name [,...]) ] AS (SELECT ...)
Рекурсивные CTE могут использоваться для обхода отношений (в виде графиков или деревьев), хотя синтаксис намного сложнее, потому что не создаются автоматические псевдостолбцы (как LEVEL
показано ниже ); если они желательны, они должны быть созданы в коде. См. Документацию MSDN или документацию IBM для примеров руководств.
RECURSIVE
Ключевое слово обычно не требуется после того, как в рамках других , чем 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 -- Recursive Subquery
WHERE n < 9)
SELECT * FROM temp;
ПОДКЛЮЧИТЬСЯ
Альтернативный синтаксис - нестандартная CONNECT BY
конструкция; он был представлен Oracle в 1980-х годах. До Oracle 10g эта конструкция была полезна только для обхода ациклических графов, потому что она возвращала ошибку при обнаружении любых циклов; в версии 10g Oracle представила функцию NOCYCLE (и ключевое слово), благодаря которой обход работает также и при наличии циклов.
CONNECT BY
поддерживается Snowflake , EnterpriseDB , базой данных Oracle , CUBRID , IBM Informix и DB2, но только если он включен в качестве режима совместимости. Синтаксис следующий:
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 также реализует запросы на фиксированные точки.
- Дедуктивные базы данных
- Иерархическая модель
- Достижимость
- Переходное закрытие
- Древовидная структура
Рекомендации
дальнейшее чтение
- CJ 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.
Внешние ссылки
- 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