Перейти до вмісту

Ієрархічні та рекурсивні запити в SQL

Матеріал з Вікіпедії — вільної енциклопедії.

Ієрархічний запит — тип запиту SQL, що обробляє ієрархічну модель даних. Вони є особливим випадком загальніших рекурсивних нерухомих запитів, які обчислюють транзитивні замикання.

У стандарті SQL:1999 ієрархічні запити реалізовані шляхом рекурсивних загальних табличних виразів (ЗТВ). На відміну від більш ранніх умов connect-by в Oracle, рекурсивні ЗТВ було розроблено з нерухомою семантикою від початку[1]. Рекурсивні ЗТВ зі стандарту були відносно близькі до наявної реалізації в IBM DB2 версії 2[1]. Рекурсивні ЗТВ також підтримуються 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[en], MariaDB 10.2+ і MySQL 8.0.1+[6]. Tableau має документацію[7], що описує, як ЗТВ можуть використовуватися. TIBCO Spotfire не підтримує ЗТВ, тоді як реалізації Oracle 11g Release 2 бракує нерухомої семантики.

Без загальних табличних виразів або умов connected-by можливо досягти ієрархічних запитів за допомогою користувацьких рекурсивних функцій[8].

Загальний табличний вираз

[ред. | ред. код]

Загальний табличний вираз, або ЗТВ (в SQL) — тимчасовий іменований результатний набір, що походить із простого запиту та визначений усередині області виконання інструкції SELECT, INSERT, UPDATE чи DELETE.

ЗТВ можна вважати альтернативами похідним таблицям (підзапитам), розрізам і вбудованим користувацьким функціям.

Загальні табличні вирази підтримуються Teradata, DB2, Firebird[9], Microsoft SQL Server, Oracle (з рекурсією, починаючи з 11g release 2), PostgreSQL (починаючи з 8.4), MariaDB (починаючи з 10.2), MySQL (починаючи з 8.0), SQLite (починаючи з 3.8.3), HyperSQL і H2[en] (експериментально)[10]. Oracle називає ЗТВ «підзапитним факторингом» (англ. subquery factoring)[11].

Синтаксис для рекурсивного ЗТВ виглядає наступним чином: 

WITH [RECURSIVE] запит_with [, …]
SELECT …

де синтаксисом запит_with є: 

назва_запиту [ (назва_колонки [, …]) ] AS (SELECT …)

Рекурсивні ЗТВ (або «рекурсивний підзапитний факторинг»[12] у жаргоні Oracle) можуть використовуватися для обходу відношень (як графів або дерев), хоча синтаксис набагато більше залучений через відсутність створених автоматичних псевдо-колонок (як LEVEL нижче); якщо вони є бажаними, то їх слід створити в коді. Навчальні приклади див. у документації MSDN[2] або IBM[13][14].

Ключове слово RECURSIVE зазвичай не є необхідним після WITH у системах, крім PostgreSQL[15].

В SQL:1999 рекурсивний (ЗТВ) запит може з'являтися будь-де, де дозволено запит. Можливо, наприклад, назвати результат за допомогою CREATE [RECURSIVE] VIEW[1]. За допомогою ЗТВ усередині INSERT INTO можна наповнити таблицю даними, згенерованими з рекурсивного запиту; генерація випадкових даних можлива з використанням цієї техніки без використання жодних процедурних інструкцій[16].

Деякі бази даних на кшталт PostgreSQL підтримують скорочений формат CREATE RECURSIVE VIEW, який внутрішньо перекладається в кодування WITH RECURSIVE[17].

Прикладом рекурсивного запиту, що обчислює факторіал чисел від 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

[ред. | ред. код]

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

CONNECT BY підтримується EnterpriseDB[20], Oracle Database[21], CUBRID[en][22], IBM Informix[23] і DB2, хоча тільки, якщо його увімкнено як режим сумісності[24]. Синтаксис виглядає наступним чином: 

SELECT список_вибірки
FROM табличний_вираз
[ WHERE … ]
[ START WITH початковий_вираз ]
CONNECT BY [NOCYCLE] { PRIOR дочірній_вираз = батьківський_вираз | батьківський_вираз = PRIOR дочірній_вираз }
[ ORDER SIBLINGS BY колонка1 [ ASC | DESC ] [, колонка2 [ ASC | DESC ] ] …
[ GROUP BY … ]
[ HAVING … ]
…
Наприклад,

Виведення з вищенаведеного запиту виглядатиме як: 

 level |  працівник  | empno | менеджер
-------+-------------+-------+----------
     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 рядків)

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

[ред. | ред. код]
  • LEVEL
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Унарні оператори

[ред. | ред. код]

Наступний приклад повертає прізвище кожного працівника у відділі 10, кожного менеджера над цим працівником в ієрархії, кількість рівнів між менеджером і працівником і шлях між ними: 

SELECT ename "Працівник", CONNECT_BY_ROOT ename "Менеджер",
       LEVEL - 1 "Довжина шляху", SYS_CONNECT_BY_PATH(ename, '/') "Шлях"
FROM emp
WHERE LEVEL > 1 and deptno = 10
CONNECT BY PRIOR empno = mgr
ORDER BY "Працівник", "Менеджер", "Довжина шляху", "Шлях";

Функції

[ред. | ред. код]
  • SYS_CONNECT_BY_PATH

Див. також

[ред. | ред. код]

Примітки

[ред. | ред. код]
  1. ↑ а б в Melton, Jim; Simon, Alan R. (2002). SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. с. 352. ISBN 978-1-55860-456-8. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  2. ↑ а б Microsoft. Recursive Queries Using Common Table Expressions. MSDN. Архів оригіналу за 8 грудня 2009. Процитовано 23 грудня 2009.
  3. ↑ Borrie, Helen (15 липня 2008). Firebird 2.1 Release Notes. Архів оригіналу за 22 квітня 2017. Процитовано 24 листопада 2015.
  4. ↑ WITH Queries. PostgreSQL. Архів оригіналу за 1 травня 2016. Процитовано 9 серпня 2019.
  5. ↑ WITH Clause. SQLite. Архів оригіналу за 5 липня 2019. Процитовано 9 серпня 2019.
  6. ↑ MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs). mysqlserverteam.com. Архів оригіналу за 16 серпня 2019. Процитовано 9 серпня 2019. [Архівовано 2019-08-16 у Wayback Machine.]
  7. ↑ Using Common Table Expressions. kb.tableau.com (англійською) . 8 січня 2019 [2016]. Архів оригіналу за 1 серпня 2019. Процитовано 9 серпня 2019.
  8. ↑ Paragon corporation: Using PostgreSQL User-Defined Functions to solve the Tree Problem. 15 лютого 2004. Архів оригіналу за 23 вересня 2015. Процитовано 19 вересня 2015.
  9. ↑ Порівняння реляційних систем керування базами даних[en]
  10. ↑ Advanced. h2database.com (англійською) . Архів оригіналу за 9 липня 2006. Процитовано 9 серпня 2019.
  11. ↑ Morton, Karen; Sands, Robyn; Still, Jared; Shamsudeen, Riyaj; Osborne, Kerry (2010). Pro Oracle SQL. Apress. с. 283. ISBN 978-1-4302-3228-5. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  12. ↑ Morton, Karen; Sands, Robyn; Still, Jared; Shamsudeen, Riyaj; Osborne, Kerry (2010). Pro Oracle SQL. Apress. с. 304. ISBN 978-1-4302-3228-5. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  13. ↑ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
  14. ↑ http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
  15. ↑ Obe, Regina; Hsu, Leo (2012). PostgreSQL: Up and Running. O'Reilly Media. с. 94. ISBN 978-1-4493-2633-3. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  16. ↑ Chamberlin, Don (1998). A Complete Guide to DB2 Universal Database. Morgan Kaufmann. с. 253—254. ISBN 978-1-55860-482-7. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
  17. ↑ Архівована копія. Архів оригіналу за 4 жовтня 2018. Процитовано 9 серпня 2019.{{cite web}}: Обслуговування CS1: Сторінки з текстом «archived copy» як значення параметру title (посилання)
  18. ↑ Benedikt, M.; Senellart, P. (2011). Databases. У Blum, Edward K.; Aho, Alfred V. (ред.). Computer Science. The Hardware, Software and Heart of It. с. 189. doi:10.1007/978-1-4614-1168-0_10. ISBN 978-1-4614-1167-3.
  19. ↑ Beaulieu, Alan (2004). Mastering Oracle SQL. O'Reilly Media, Inc. с. 227. ISBN 978-0-596-00632-7. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019. {{cite book}}: |ім'я1= з пропущеним |прізвище1= (довідка)
  20. ↑ Hierarchical Queries. EnterpriseDB. Архів оригіналу за 21 червня 2008. Процитовано 9 серпня 2019. [Архівовано 2008-06-21 у Wayback Machine.]
  21. ↑ Hierarchical Queries. Oracle. Архів оригіналу за 8 листопада 2011. Процитовано 9 серпня 2019.
  22. ↑ CUBRID Hierarchical Query. Архів оригіналу за 14 лютого 2013. Процитовано 11 лютого 2013. [Архівовано 2013-02-14 у Wayback Machine.]
  23. ↑ Hierarchical Clause. IBM Informix. Архів оригіналу за 4 березня 2016. Процитовано 9 серпня 2019.
  24. ↑ Gennick, Jonathan (2010). SQL Pocket Guide (вид. 3-є). O'Reilly Media, Inc. с. 8. ISBN 978-1-4493-9409-7. Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.

Література

[ред. | ред. код]

Академічні підручники. Зверніть увагу, що вони покривають тільки SQL:1999 (та Datalog), але не розширення Oracle.

Посилання

[ред. | ред. код]
Отримано з https://uk.wikipedia.org/w/index.php?title=Ієрархічні_та_рекурсивні_запити_в_SQL&oldid=44056229