Arbeiten mit CTEs (allgemeinen Tabellenausdrücken)¶

Potenzial für Endlosschleifen¶

Das fehlerhafte Konstruieren eines rekursiven CTE kann eine Endlosschleife verursachen. In diesen Fällen wird die Abfrage so lange ausgeführt, bis eine der folgenden Situationen eintritt: die Abfrage war erfolgreich, die Abfrage weist eine Zeitüberschreitung auf (z. B. wird die durch den Parameter STATEMENT_TIMEOUT_IN_SECONDS angegebene Anzahl von Sekunden überschritten), oder der Benutzer führt einen Abbruch der Abfrage aus.

Weitere Informationen zum Entstehen einer Endlosschleife und zu Richtlinien, mit deren Hilfe dieses Problem verhindert wird, finden Sie unter Problembehandlung bei rekursiven CTEs.

Nicht zusammenhängende Hierarchien¶

Unter diesem Thema wurden Hierarchien und die Frage beschrieben, wie rekursive CTEs Beziehungen zwischen übergeordneten/untergeordneten Elementen nutzen können. In allen Beispielen dieses Themas sind die Hierarchien zusammenhängend.

Informationen zu nicht zusammenhängenden Hierarchien finden Sie unter Abfragen hierarchischer Daten.

Ungeordnete Ausgabe¶

Hier ist das erste Beispiel.

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;

Die Abfrage umfasst die folgenden Abschnitte:

• ZeileContains2 enthält die Spaltennamen für die „Ansicht“ (CTE).

• Die Zeilen 4–16 enthalten den CTE.

• Die Zeilen 6–8 enthalten die Ankerklausel des CTE.

• Die Zeilen 12–15 enthalten die Rekursionsklausel des CTE.

• Die Zeilen 18–19 enthalten den Haupt-SELECT-Anweisung, in der der CTE als Ansicht verwendet wird. Diese SELECT-Anweisung referenziert:

  • Name des CTE (managers), definiert in Zeile 1

  • Spalten des CTE (indent, employee_id usw.), definiert in Zeile 2

Der CTE enthält zwei SELECT-Anweisungen.

• Die SELECT-Anweisung in der Ankerklausel wird einmal ausgeführt und liefert alle Zeilen der ersten (obersten) Ebene der Hierarchie.

• Die SELECT-Anweisung in der Rekursionsklausel kann auf den CTE verweisen. Sie können sich die Abfrage als iterierend vorstellen, wobei jede Iteration auf den Abfrageergebnissen der vorherigen Iteration aufbaut.

Im Beispiel Vorgesetzte/Mitarbeiter gibt die Ankerklausel die erste Zeile aus, die den Vorstandsvorsitzenden des Unternehmens beschreibt.

In der nächsten Iteration der rekursiven Klausel findet die rekursive Klausel alle Zeilen, deren Vorgesetzter der Vorstandsvorsitzende ist (d. h. sie findet alle stellvertretenden Vorstandsvorsitzenden). Die dritte Iteration findet alle Mitarbeiter, deren Vorgesetzter einer der stellvertretenden Vorstandsvorsitzenden ist. Die Iteration wird fortgesetzt, bis es eine Iteration gibt, in der alle abgerufenen Zeilen Zeilen von Mitarbeitern auf Blattebene sind, die für niemanden als Vorgesetzter fungieren. Die Anweisung führt eine weitere Iteration durch und sucht (findet aber keine) Mitarbeiter, deren Vorgesetzte Mitarbeiter auf Blattebene sind. Diese Iteration erzeugt 0 Zeilen, und die Iteration wird angehalten.

Im Laufe dieser Iterationen akkumuliert die UNION ALL-Klausel die Ergebnisse. Die Ergebnisse jeder Iteration werden zu den Ergebnissen der vorherigen Iterationen hinzugefügt. Nach Abschluss der letzten Iteration werden die akkumulierten Zeilen (wie alle in einer WITH-Klausel erzeugten Zeilen) der Haupt-SELECT-Klausel der Abfrage zur Verfügung gestellt. Diese Haupt-SELECT-Klausel kann dann die Zeilen abfragen.

Diese Beispielabfrage verwendet Einrückungen, um die hierarchische Struktur der Daten zu verdeutlichen. Wenn Sie sich die Ausgabe ansehen, werden Sie feststellen, dass die Daten eines Mitarbeiters umso weiter eingerückt sind, je niedriger seine Ebene ist.

Die Einrückung wird durch die Spalte indent gesteuert. Die Einrückung beginnt mit 0 Zeichen (eine leere Zeichenfolge in der Ankerklausel) und wird für jede Iteration, d. h. für jede Ebene in der Hierarchie, um 4 Zeichen (---) erhöht.

Es ist also sehr wichtig, dass die Verknüpfungen korrekt sind und die richtigen Spalten in der rekursiven Klausel ausgewählt sind. Die Spalten in der SELECT-Anweisung der rekursiven Klausel müssen den Spalten in der Ankerklausel korrekt entsprechen. Denken Sie daran, dass die Abfrage mit dem Vorstandsvorsitzenden beginnt, dann die stellvertretenden Vorstandsvorsitzenden und anschließend die Personen auswählt, die direkt den stellvertretenden Vorstandsvorsitzenden unterstellt sind usw. Bei jeder Iteration werden die Mitarbeiter ausgewählt, deren Feld manager_id einem der managers.employee_id-Werte der Vorgesetzten entspricht, die in der vorherigen Iteration erstellt wurden.

Anders ausgedrückt ist ID in der Vorgesetztenansicht die Vorgesetzten-ID für die nächste Ebene von Mitarbeitern. Die Mitarbeiter-IDs müssen bei jeder Iteration in der Hierarchie abwärts voranschreiten (Vorstandsvorsitzender, stellvertretender Vorgesetzter, oberes Management, mittleres Management usw.). Wenn die Mitarbeiter-IDs nicht fortgesetzt werden, kann die Abfrage in einer Endlosschleife enden (wenn dieselbe manager_ID in verschiedenen Iterationen immer wieder in der managers.employee_ID-Spalte des Vorgesetzten angezeigt wird) oder eine Ebene überspringen bzw. auf andere Weise fehlschlagen.

Geordnete Ausgabe¶

Das vorherige Beispiel enthielt keine ORDER BY-Klausel. Obwohl die Datensätze der einzelnen Mitarbeiter ordnungsgemäß eingerückt sind, wurde nicht unbedingt jeder Mitarbeiter direkt unter seinem Vorgesetzten angezeigt. Im folgenden Beispiel wird eine Ausgabe mit korrekter Einrückung generiert, sodass sich die Mitarbeiter unter ihren jeweiligen Vorgesetzten befinden.

Die ORDER BY-Klausel der Abfrage nutzt eine zusätzliche Spalte mit dem Namen sort_key. Bei Iteration der rekursiven Klausel wird der Sortierschlüssel akkumuliert. Sie können sich den Sortierschlüssel als eine Zeichenfolge vorstellen, die die gesamte Weisungskette der aus Ihrer Sicht höher gestellten Mitarbeiter enthält (Ihren Vorgesetzten, den Vorgesetzten Ihres Vorgesetzten usw.). Die höchste Person in dieser Weisungskette (der Vorstandsvorsitzende) steht am Anfang der Sortierschlüsselzeichenfolge. Obwohl Sie den Sortierschlüssel normalerweise nicht anzeigen würden, wird zum besseren Verständnis in der folgenden Abfrage der Sortierschlüssel zur Ausgabe hinzugefügt.

Jede Iteration sollte die Länge des Sortierschlüssels um denselben Betrag (dieselbe Anzahl von Zeichen) erhöhen. Die Abfrage verwendet also eine UDF (benutzerdefinierte Funktion) mit dem Namen skey und die folgende Definition, um Segmente des Sortierschlüssels mit konsistenter Länge zu generieren:

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;

Copy

Hier ist ein Beispiel für die Ausgabe der Funktion SKEY:

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+

Copy

Hier ist die endgültige Version der Abfrage. Damit werden die Mitarbeiter direkt unter ihren jeweiligen Vorgesetzten gesetzt und basierend auf der „Ebene“ des Mitarbeiters eingerückt:

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+

Copy

Die nächste Abfrage zeigt, wie auf ein Feld der vorherigen (höheren) Ebene in der Hierarchie verwiesen wird. Achten Sie besonders auf die Spalte mgr_title:

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_TITLE                  |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+

Copy

Ursache 1: Zyklische Datenhierarchie¶

Wenn Ihre Datenhierarchie einen Zyklus enthält (d. h. sie ist kein echter Strukturbaum), gibt es drei mögliche Lösungen:

Lösung 1.1

Wenn die Daten eigentlich keinen Zyklus enthalten sollen, dann korrigieren Sie die Daten.

Lösung 1.2

Begrenzen Sie die Abfrage auf irgendeine Weise (z. B. Begrenzen der Anzahl an Ausgabezeilen). Beispiel:

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;

Copy

Lösung 1.3

Verwenden Sie keine Abfrage, die einen rekursiven CTE enthält, der hierarchische Daten erwartet.

Ursache 2: Endlosschleife¶

Eine Endlosschleife kann auftreten, wenn die Projektionsklausel in recursive_clause einen Wert aus dem übergeordneten Element (der vorherigen Iteration) ausgibt und nicht aus dem untergeordneten Element (der aktuellen Iteration). Die nächste Iteration verwendet dann in der Join-Verknüpfung diesen Wert und nicht den Wert aus der aktuellen Iteration.

Der folgende Pseudocode zeigt ein ungefähres Beispiel dafür:

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...

Copy

In diesem Beispiel übergibt die rekursive Klausel ihren übergeordneten Wert (die manager_id) in der Spalte, die den aktuellen/untergeordneten Wert (die employee_id) enthalten sollte. Das übergeordnete Element wird in der nächsten Iteration als „aktueller“ Wert angezeigt und erneut als „aktueller“ Wert an die folgende Generation übergeben, sodass die Abfrage nie die Ebenen nach unten durchläuft, sondern jedes Mal die gleiche Ebene verarbeitet.

Schritt 1:

Angenommen, die Ankerklausel wählt die Werte employee_id = 1 und manager_id = NULL aus.

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL

Copy

Schritt 2:

Bei der ersten Iteration der rekursiven Klausel ist employee_id = 2 und manager_id = 1 in table1.

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL

Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Ergebnis der Verknüpfung (JOIN) in der rekursiven Klausel:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...

Copy

Projektion:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Da jedoch die Spalten employee_id und manager_id in der Projektion vertauscht sind, lautet die tatsächliche Ausgabe der Abfrage (und damit der Inhalt des CTE zu Beginn der nächsten Iteration):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

Copy

Schritt 3:

Bei der zweiten Iteration der rekursiven Klausel:

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2

Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Ergebnis der Verknüpfung (JOIN) in rekursiver Klausel:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...

Copy

Projektion:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Ergebnis der Abfrage (Inhalt von CTE zu Beginn der nächsten Iteration):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

Copy

Wie Sie sehen können, ist die Zeile im CTE am Ende der zweiten Iteration dieselbe wie zu Beginn der Iteration:

• employee_id ist 1.

• manager_id ist 2.

Daher ist das Ergebnis der Verknüpfung bei der nächsten Iteration das gleiche wie das Ergebnis der Verknüpfung bei der aktuellen Iteration und die Abfrage führt die Schleife endlos aus.

Wenn Sie eine Endlosschleife erstellt haben:

Lösung 2

Stellen Sie sicher, dass die rekursive Klausel die korrekten Variablen in der korrekten Reihenfolge übergibt.

Stellen Sie außerdem sicher, dass die JOIN-Bedingung in der rekursiven Klausel korrekt ist. In einem typischen Fall sollte das übergeordnete Element der „aktuellen“ Zeile mit dem untergeordneten/aktuellen Wert der übergeordneten Zeile verknüpft werden.