Hierarchische und rekursive Abfragen in SQL - Hierarchical and recursive queries in SQL

Eine hierarchische Abfrage ist eine Art von SQL-Abfrage , die hierarchische Modelldaten verarbeitet. Sie sind Spezialfälle von allgemeineren rekursiven Fixpunktabfragen, die transitive Abschlüsse berechnen .

Im Standard SQL:1999 werden hierarchische Abfragen durch rekursive allgemeine Tabellenausdrücke ( Common Table Expressions, CTEs) implementiert. Im Gegensatz zu Oracles früherer connect-by-Klausel wurden rekursive CTEs von Anfang an mit Fixpoint- Semantik entworfen . Rekursive CTEs aus dem Standard kamen der bestehenden Implementierung in IBM DB2 Version 2 relativ nahe . Rekursive CTEs werden auch von Microsoft SQL Server (seit SQL Server 2008 R2), Firebird 2.1 , PostgreSQL 8.4+ , SQLite 3.8.3+ , IBM Informix unterstützt Version 11.50+, CUBRID , MariaDB 10.2+ und MySQL 8.0.1+ . Tableau verfügt über eine Dokumentation, in der beschrieben wird, wie CTEs verwendet werden können. TIBCO Spotfire unterstützt keine CTEs, während der Implementierung von Oracle 11g Release 2 die Fixpoint-Semantik fehlt.

Ohne gemeinsame Tabellenausdrücke oder Connected-by-Klauseln können hierarchische Abfragen mit benutzerdefinierten rekursiven Funktionen durchgeführt werden.

Gemeinsamer Tabellenausdruck

Ein gemeinsamer Tabellenausdruck oder CTE (in SQL ) ist eine temporäre Ergebnismenge genannt, von einer einfachen Abfrage abgeleitet und definierte innerhalb des Ausführungs Rahmen einen SELECT, INSERT, UPDATEoder DELETEErklärung.

CTEs können als Alternativen zu abgeleiteten Tabellen ( Unterabfragen ), Ansichten und benutzerdefinierten Inline-Funktionen betrachtet werden.

Gängige Tabellenausdrücke werden unterstützt von Teradata (ab Version 14), DB2 , Informix (ab Version 14.1), Firebird (ab Version 2.1), Microsoft SQL Server (ab Version 2005), Oracle (mit Rekursion seit 11g Release 2 ), PostgreSQL (seit 8.4), MariaDB (seit 10.2), MySQL (seit 8.0), SQLite (seit 3.8.3), HyperSQL , Informix (seit 14.10), Google BigQuery , Sybase (ab Version 9), Vertica , H2 (experimentell) und viele andere . Oracle nennt CTEs "Subquery Factoring".

Die Syntax für einen CTE (der rekursiv sein kann oder nicht) lautet wie folgt: 

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

wobei with_querydie Syntax von : 

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

Rekursive CTEs können verwendet werden, um Beziehungen (als Graphen oder Bäume) zu durchlaufen, obwohl die Syntax viel komplizierter ist, da keine automatischen Pseudospalten erstellt werden (wie LEVEL unten ); wenn diese gewünscht sind, müssen sie im Code angelegt werden. Tutorial-Beispiele finden Sie in der MSDN-Dokumentation oder der IBM-Dokumentation.

Das RECURSIVESchlüsselwort wird in anderen Systemen als PostgreSQL normalerweise nicht nach WITH benötigt.

In SQL:1999 kann eine rekursive (CTE) Abfrage überall dort erscheinen, wo eine Abfrage erlaubt ist. Es ist beispielsweise möglich, das Ergebnis mit CREATE[ RECURSIVE] zu benennen VIEW. Mit einem CTE in einem INSERT INTOkann man eine Tabelle mit Daten füllen, die aus einer rekursiven Abfrage generiert wurden; Mit dieser Technik ist eine zufällige Datengenerierung ohne Verwendung von Verfahrensanweisungen möglich.

Einige Datenbanken, wie PostgreSQL, unterstützen ein kürzeres CREATE RECURSIVE VIEW-Format, das intern in WITH RECURSIVE-Codierung übersetzt wird.

Ein Beispiel für eine rekursive Abfrage, die die Fakultät von Zahlen von 0 bis 9 berechnet, ist folgendes: 

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;

VERBINDEN DURCH

Eine alternative Syntax ist das Nicht-Standard- CONNECT BYKonstrukt; Es wurde von Oracle in den 1980er Jahren eingeführt. Vor Oracle 10g war das Konstrukt nur zum Durchlaufen azyklischer Graphen nützlich, da es beim Erkennen von Zyklen einen Fehler zurückgab. in Version 10g hat Oracle die Funktion NOCYCLE (und das Schlüsselwort) eingeführt, wodurch die Traversierung auch bei Vorhandensein von Zyklen funktioniert.

CONNECT BYwird von Snowflake , EnterpriseDB , Oracle-Datenbank , CUBRID , IBM Informix und DB2 unterstützt, allerdings nur, wenn es als Kompatibilitätsmodus aktiviert ist. Die Syntax lautet wie folgt: 

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 ... ]
...
Beispielsweise,
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Die Ausgabe der obigen Abfrage würde so aussehen: 

 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)

Pseudospalten

  • NIVEAU
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Unäre Operatoren

Das folgende Beispiel gibt den Nachnamen jedes Mitarbeiters in Abteilung 10, jeden Managers über diesem Mitarbeiter in der Hierarchie, die Anzahl der Ebenen zwischen Manager und Mitarbeiter und den Pfad zwischen den beiden zurück: 

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";

Funktionen

  • SYS_CONNECT_BY_PATH

Siehe auch

Verweise

Weiterlesen

  • CJ-Datum (2011). SQL und relationale Theorie: Schreiben von genauem SQL-Code (2. Aufl.). O'Reilly-Medien. S. 159–163. ISBN 978-1-4493-1640-2.

Akademische Lehrbücher . Beachten Sie, dass diese nur den SQL: 1999-Standard (und Datalog) abdecken, nicht jedoch die Oracle-Erweiterung.

  • Abraham Silberschatz; Henry Korth; S. Sudarshan (2010). Datenbanksystemkonzepte (6. Aufl.). McGraw-Hill. S. 187–192. ISBN 978-0-07-352332-3.
  • Raghu Ramakrishnan; Johannes Gehrke (2003). Datenbankverwaltungssysteme (3. Aufl.). McGraw-Hill. ISBN 978-0-07-246563-1. Kapitel 24.
  • Hector Garcia-Molina; Jeffrey D. Ullman; Jennifer Widom (2009). Datenbanksysteme: das komplette Buch (2. Aufl.). Pearson Prentice Hall. S. 437–445. ISBN 978-0-13-187325-4.

Externe Links