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

martine
  • 2020-12-31
  • 1

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

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

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

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

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

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

Общие табличные выражения поддерживаются Teradata , DB2 , Firebird , Microsoft SQL Server , Oracle (с рекурсией начиная с версии 11g 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 : 

query_name [ (column_name [,...]) ] 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 -- Recursive Subquery WHERE n < 9) SELECT * FROM temp;

CONNECT BY

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

CONNECT BY поддерживается 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)

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

  • 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, '/') "Path" FROM emp WHERE LEVEL > 1 and deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Employee", "Manager", "Pathlen", "Path";

Функции

  • SYS_CONNECT_BY_PATH

См. также

Использованная литература

  1. ↑ Jim Melton, Alan R. Simon. . — Elsevier, 2001-05-30. — 930 с. — ISBN 9780080517605 .
  2. ↑ Archiveddocs. (англ.) . docs.microsoft.com. Дата обращения: 5 мая 2019. 5 мая 2019 года.
  3. (неопр.) . firebirdsql.org. Дата обращения: 5 мая 2019. 18 мая 2019 года.
  4. (неопр.) . www.postgresql.org. Дата обращения: 5 мая 2019. 5 мая 2019 года.
  5. (неопр.) . www.sqlite.org. Дата обращения: 5 мая 2019. 2 мая 2019 года.
  6. Guilhem Bichot. (англ.) . MySQL Server Blog (20 сентября 2016). Дата обращения: 5 мая 2019. 16 августа 2019 года.
  7. (неопр.) . www.paragoncorporation.com. Дата обращения: 5 мая 2019. 5 мая 2019 года.
  8. (англ.) // Wikipedia. — 2019-04-24.
  9. (неопр.) . www.h2database.com. Дата обращения: 5 мая 2019. 9 июля 2006 года.
  10. Karen Morton, Robyn Sands, Jared Still, Riyaj Shamsudeen, Kerry Osborne. . — Apress, 2010-12-15. — 601 с. — ISBN 9781430232285 .
  11. Karen Morton, Robyn Sands, Jared Still, Riyaj Shamsudeen, Kerry Osborne. . — Apress, 2010-12-15. — 601 с. — ISBN 9781430232285 .
  12. (англ.) . www.ibm.com. Дата обращения: 5 мая 2019. 5 мая 2019 года.
  13. Regina O. Obe, Leo S. Hsu. . — "O'Reilly Media, Inc.", 2012. — 167 с. — ISBN 9781449326333 .
  14. Don Chamberlin. . — Morgan Kaufmann, 1998-06-15. — 820 с. — ISBN 9781558604827 .
  15. (неопр.) . www.postgresql.org. Дата обращения: 5 мая 2019. 5 мая 2019 года.
  16. Sanjay Mishra, Alan Beaulieu. . — "O'Reilly Media, Inc.", 2004-06-22. — 496 с. — ISBN 9780596552473 .
  17. (англ.) . docs.oracle.com. Дата обращения: 5 мая 2019. 5 мая 2019 года.
  18. (англ.) . www.cubrid.org. Дата обращения: 5 мая 2019. 5 мая 2019 года.

Примечания

martine
  • 2020-12-31
  • 1
  • Tags:

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

The title for the last searches