Иерархические и рекурсивные запросы в SQL (Nyjgj]ncyvtny n jytrjvnfudy [ghjkvd f SQL)

Перейти к навигации Перейти к поиску

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

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

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

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

[править | править код]

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

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

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

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

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

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

query_name [ (column_name [,...]) ] AS (SELECT ...)

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

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

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

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

Примером рекурсивного запроса, вычисляющего факториал чисел от 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 (и ключевое слово), благодаря чему обход работает и при наличии циклов.[16]

CONNECT BY поддерживается EnterpriseDB, базой данных Oracle,[17] CUBRID,[18] 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. 1 2 Jim Melton, Alan R. Simon. SQL: 1999: Understanding Relational Language Components. — Elsevier, 2001-05-30. — 930 с. — ISBN 9780080517605.
  2. 1 2 Archiveddocs. Recursive Queries Using Common Table Expressions (англ.). docs.microsoft.com. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.
  3. Firebird 2.1 Release Notes. firebirdsql.org. Дата обращения: 5 мая 2019. Архивировано 18 мая 2019 года.
  4. PostgreSQL: Documentation: 11: 7.8. WITH Queries (Common Table Expressions). www.postgresql.org. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.
  5. SQLite Query Language: WITH clause. www.sqlite.org. Дата обращения: 5 мая 2019. Архивировано 2 мая 2019 года.
  6. Guilhem Bichot. MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs) (англ.). MySQL Server Blog (20 сентября 2016). Дата обращения: 5 мая 2019. Архивировано 16 августа 2019 года.
  7. Using PostgreSQL User-Defined Functions to solve the Tree Problem. www.paragoncorporation.com. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.
  8. Comparison of relational database management systems (англ.) // Wikipedia. — 2019-04-24.
  9. Advanced. www.h2database.com. Дата обращения: 5 мая 2019. Архивировано 9 июля 2006 года.
  10. Karen Morton, Robyn Sands, Jared Still, Riyaj Shamsudeen, Kerry Osborne. Pro Oracle SQL. — Apress, 2010-12-15. — 601 с. — ISBN 9781430232285.
  11. Karen Morton, Robyn Sands, Jared Still, Riyaj Shamsudeen, Kerry Osborne. Pro Oracle SQL. — Apress, 2010-12-15. — 601 с. — ISBN 9781430232285.
  12. IBM Knowledge Center (англ.). www.ibm.com. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.
  13. Regina O. Obe, Leo S. Hsu. PostgreSQL: Up and Running. — "O'Reilly Media, Inc.", 2012. — 167 с. — ISBN 9781449326333.
  14. Don Chamberlin. A Complete Guide to DB2 Universal Database. — Morgan Kaufmann, 1998-06-15. — 820 с. — ISBN 9781558604827.
  15. PostgreSQL: Documentation: 10: CREATE VIEW. www.postgresql.org. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.
  16. Sanjay Mishra, Alan Beaulieu. Mastering Oracle SQL: Putting Oracle SQL to Work. — "O'Reilly Media, Inc.", 2004-06-22. — 496 с. — ISBN 9780596552473.
  17. Database SQL Reference (англ.). docs.oracle.com. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.
  18. Learn CUBRID: Manuals, Get Started Tutorial and FAQ (англ.). www.cubrid.org. Дата обращения: 5 мая 2019. Архивировано 5 мая 2019 года.

Примечания

[править | править код]