Вікіпедія

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

Цю статтю написано занадто професійним стилем зі специфічною термінологією, що може бути незрозумілим для більшості читачів. Ви можете допомогти вдосконалити цю статтю, зробивши її зрозумілою для неспеціалістів без втрат змісту. Можливо, сторінка обговорення містить зауваження щодо потрібних змін. (квітень 2018)

Ієрархічний запит — тип запиту 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.
  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.
  21. ↑ Hierarchical Queries. Oracle. Архів оригіналу за 8 листопада 2011. Процитовано 9 серпня 2019.
  22. ↑ CUBRID Hierarchical Query. Архів оригіналу за 14 лютого 2013. Процитовано 11 лютого 2013.
  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=36835957