Hierarchické a rekurzivní dotazy v SQL - Hierarchical and recursive queries in SQL

A hierarchický dotaz je typ SQL dotaz který zpracovává hierarchický model data. Jsou to speciální případy obecnějších rekurzivních dotazů na fixní body, které se počítají tranzitivní uzávěry.

Standardně SQL: 1999 hierarchické dotazy jsou implementovány rekurzivně běžné tabulkové výrazy (CTE). Na rozdíl od Oracle dříve klauzule o připojení, rekurzivní CTE byly navrženy s fixní bod sémantika od začátku.[1] Rekurzivní CTE ze standardu byly relativně blízké stávající implementaci v IBM DB2 verze 2.[1] Rekurzivní CTE podporuje také Microsoft SQL Server (od SQL Server 2008 R2),[2] Firebird 2.1,[3] PostgreSQL 8.4+,[4] SQLite 3.8.3+,[5] IBM Informix verze 11.50+, CUBRID, MariaDB 10.2+ a MySQL 8.0.1+,[6]. Tableau má dokumentaci popisující, jak lze použít CTE. TIBCO Spotfire nepodporuje CTE, zatímco implementace Oracle 11g Release 2 postrádá sémantiku fixpointů.

Bez běžných tabulkových výrazů nebo klauzulí propojeného je možné dosáhnout hierarchických dotazů pomocí uživatelem definovaných rekurzivních funkcí.[7]

Společný tabulkový výraz

Běžný tabulkový výraz nebo CTE (v SQL ) je dočasná pojmenovaná sada výsledků, odvozená od jednoduchého dotazu a definovaná v rozsahu provádění a VYBRAT, VLOŽIT, AKTUALIZACEnebo VYMAZAT prohlášení.

CTE lze považovat za alternativu k odvozeným tabulkám (poddotaz ), pohledy a vložené uživatelem definované funkce.

Běžné tabulkové výrazy podporuje Teradata, DB2, Informix (počínaje verzí 14.1), Fénix,[8] Microsoft SQL Server, Věštec (s rekurzí od vydání 11g 2), PostgreSQL (od 8.4), MariaDB (od 10.2), MySQL (od 8.0), SQLite (od 3.8.3), HyperSQL a H2 (experimentální).[9] Oracle nazývá CTE „subquery factoring“.[10]

Syntaxe rekurzivního CTE je následující:

S [OPAKUJTE] with_query [, ...]VYBRAT...

kde with_querySyntaxe je:

název_ dotazu [ (název_sloupce [,...]) ] TAK JAKO (VYBRAT ...)

Rekurzivní CTE (neboli „rekurzivní poddotazový factoring“)[11] v žargonu Oracle) lze použít k procházení vztahů (jako grafy nebo stromy), ačkoli syntaxe je mnohem více zapojena, protože nejsou vytvořeny žádné automatické pseudosloupce (jako ÚROVEŇ níže ); pokud jsou požadovány, musí být vytvořeny v kódu. Viz dokumentace MSDN[2] nebo dokumentace IBM[12][13] pro příklady cvičení.

The OPAKUJTE klíčové slovo není obvykle po WITH potřeba v jiných systémech než PostgreSQL.[14]

V SQL: 1999 se může rekurzivní (CTE) dotaz objevit kdekoli, kde je povolen dotaz. Je možné například pojmenovat výsledek pomocí VYTVOŘIT [OPAKUJTE] POHLED.[15] Použití CTE uvnitř VLOŽ DO, lze tabulku naplnit daty generovanými z rekurzivního dotazu; generování náhodných dat je možné pomocí této techniky bez použití jakýchkoli procedurálních příkazů.[16]

Některé databáze, jako je PostgreSQL, podporují kratší formát CREATE RECURSIVE VIEW, který je interně přeložen do WITH RECURSIVE kódování.[17]

Příklad rekurzivního dotazu počítajícího faktoriál čísel od 0 do 9 je následující:

S OPAKUJTE tepl (n, skutečnost) TAK JAKO (VYBRAT 0, 1 - Počáteční poddotaz  UNIE VŠECHNO  VYBRAT n+1, (n+1)*skutečnost Z tepl - Rekurzivní poddotaz         KDE n < 9)VYBRAT * Z tepl;

PŘIPOJIT SE

Alternativní syntaxe je nestandardní PŘIPOJIT SE postavit; byl představen společností Oracle v 80. letech.[18] Před Oracle 10g byl konstrukt užitečný pouze pro procházení acyklickými grafy, protože vrátil chybu při detekci jakýchkoli cyklů; ve verzi 10g společnost Oracle představila funkci NOCYCLE (a klíčové slovo), díky níž funguje i přechod v přítomnosti cyklů.[19]

PŘIPOJIT SE je podporován EnterpriseDB,[20] Databáze Oracle,[21] CUBRID,[22] IBM Informix[23] a DB2 i když pouze v případě, že je povolen jako režim kompatibility.[24] Syntaxe je následující:

VYBRAT select_listZ výraz_tabulky[ KDE ... ][ START S start_expression ]PŘIPOJIT PODLE [NOCYKL] { PŘEDCHOZÍ child_expr = parent_expr | parent_expr = PŘEDCHOZÍ child_expr }[ OBJEDNAT Sourozenci PODLE sloupec1 [ ASC | POP ] [, sloupec2 [ ASC | POP ] ] ... ][ SKUPINA PODLE ... ][ MÁM ... ]...
Například,
VYBRAT ÚROVEŇ, LPAD (' ', 2 * (ÚROVEŇ - 1)) || ename "zaměstnanec", empno, mgr "manažer"Z emp START S mgr JE NULAPŘIPOJIT PODLE PŘEDCHOZÍ empno = mgr;

Výstup z výše uvedeného dotazu by vypadal takto:

 úroveň | zaměstnanec | empno | manažer ------- + ------------- + ------- + --------- 1 | KRÁL | 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 | OTOČKA | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 řádků)

Pseudosloupce

  • ÚROVEŇ
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Unární operátoři

Následující příklad vrátí příjmení každého zaměstnance v oddělení 10, každého správce nad tímto zaměstnancem v hierarchii, počet úrovní mezi správcem a zaměstnancem a cestu mezi těmito dvěma:

VYBRAT ename "Zaměstnanec", CONNECT_BY_ROOT ename "Manažer",ÚROVEŇ-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Cesta"Z empKDE ÚROVEŇ > 1 a odd = 10PŘIPOJIT PODLE PŘEDCHOZÍ empno = mgrOBJEDNAT PODLE "Zaměstnanec", "Manažer", "Pathlen", "Cesta";

Funkce

  • SYS_CONNECT_BY_PATH

Viz také

Reference

  1. ^ A b Jim Melton; Alan R. Simon (2002). SQL: 1999: Understanding Relational Language Components. Morgan Kaufmann. ISBN  978-1-55860-456-8.
  2. ^ A b Microsoft. "Rekurzivní dotazy pomocí běžných tabulkových výrazů". Citováno 2009-12-23.
  3. ^ Helen Borrie (2008-07-15). „Poznámky k verzi Firebird 2.1“. Citováno 2015-11-24.
  4. ^ „S dotazy“. PostgreSQL
  5. ^ „S Doložkou“. SQLite
  6. ^ „Laboratoře MySQL 8.0: [rekurzivní] běžné tabulkové výrazy v MySQL (CTE)“. mysqlserverteam.com
  7. ^ Společnost Paragon: Používání uživatelsky definovaných funkcí PostgreSQL k řešení problému stromu, 15. února 2004, zpřístupněno 19. září 2015
  8. ^ Porovnání systémů správy relačních databází # Možnosti databáze
  9. ^ http://www.h2database.com/html/advanced.html#recursive_queries
  10. ^ Karen Morton; Robyn Sands; Jared Still; Riyaj Shamsudeen; Kerry Osborne (2010). Pro Oracle SQL. Apress. p. 283. ISBN  978-1-4302-3228-5.
  11. ^ Karen Morton; Robyn Sands; Jared Still; Riyaj Shamsudeen; Kerry Osborne (2010). Pro Oracle SQL. Apress. p. 304. ISBN  978-1-4302-3228-5.
  12. ^ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
  13. ^ http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
  14. ^ Regina Obe; Leo Hsu (2012). PostgreSQL: Up and Running. O'Reilly Media. p. 94. ISBN  978-1-4493-2633-3.
  15. ^ Jim Melton; Alan R. Simon (2002). SQL: 1999: Understanding Relational Language Components. Morgan Kaufmann. p. 352. ISBN  978-1-55860-456-8.
  16. ^ Don Chamberlin (1998). Kompletní průvodce produktem DB2 Universal Database. Morgan Kaufmann. str. 253–254. ISBN  978-1-55860-482-7.
  17. ^ https://www.postgresql.org/docs/10/static/sql-createview.html
  18. ^ Benedikt, M .; Senellart, P. (2011). "Databáze". In Blum, Edward K .; Aho, Alfred V. (eds.). Počítačová věda. Hardware, software a jádro toho. p. 189. doi:10.1007/978-1-4614-1168-0_10. ISBN  978-1-4614-1167-3.
  19. ^ Sanjay Mishra; Alan Beaulieu (2004). Zvládnutí Oracle SQL. O'Reilly Media, Inc. str. 227. ISBN  978-0-596-00632-7.
  20. ^ Hierarchické dotazy Archivováno 2008-06-21 na Wayback Machine, EnterpriseDB
  21. ^ Hierarchické dotazy, Oracle
  22. ^ „CUBRID Hierarchical Query“. Citováno 11. února 2013.
  23. ^ Hierarchická doložka, IBM Informix
  24. ^ Jonathan Gennick (2010). Průvodce Pocket SQL (3. vyd.). O'Reilly Media, Inc. str. 8. ISBN  978-1-4493-9409-7.

Další čtení

Akademické učebnice. Všimněte si, že tyto pokrývají pouze standard SQL: 1999 (a Datalog), ale ne rozšíření Oracle.

externí odkazy