Beispiel für rekursive CTEs zur Arbeit mit Hierarchien

Stell dir vor: Du hast einen Online-Shop mit tausenden Produkten, die ordentlich in Regale einsortiert sind – Kategorien, Unterkategorien, Unter-Unterkategorien. Auf der Website sieht das wie ein schickes Dropdown-Menü aus, aber in der Datenbank wird das schnell zum Kopfzerbrechen. Wie holst du mit nur einer Query den ganzen Zweig "Elektronik → Smartphones → Zubehör"? Wie zählst du, wie viele Ebenen jede Kategorie hat? Normale JOINs helfen hier nicht weiter – hier brauchst du Rekursion!

Aufbau einer Produktkategoriestruktur mit rekursiven CTEs

Eine der klassischen Aufgaben in relationalen Datenbanken ist die Arbeit mit hierarchischen Strukturen. Stell dir vor, du hast einen Kategoriebaum für Produkte: Hauptkategorien, Unterkategorien, Unter-Unterkategorien und so weiter. Zum Beispiel:

Elektronik
  └── Smartphones
      └── Zubehör
  └── Notebooks
      └── Gamer
  └── Foto und Video

Diese Struktur lässt sich im Shop-Interface easy darstellen, aber wie speicherst du sie in der Datenbank und holst sie wieder raus? Hier kommen rekursive CTEs ins Spiel!

Ausgangstabelle für Kategorien

Erstmal legen wir eine Tabelle categories an, die die Daten zu den Produktkategorien speichert:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Einzigartige Kategorie-ID
    category_name TEXT NOT NULL,          -- Name der Kategorie
    parent_category_id INT                -- Übergeordnete Kategorie (NULL für Hauptkategorien)
);

Hier ein Beispiel für Daten, die wir in die Tabelle einfügen:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Elektronik', NULL),
    ('Smartphones', 1),
    ('Zubehör', 2),
    ('Notebooks', 1),
    ('Gamer', 4),
    ('Foto und Video', 1);

Was passiert hier:

• Elektronik ist die Hauptkategorie (kein Parent, parent_category_id = NULL).
• Smartphones liegen in der Kategorie Elektronik.
• Zubehör gehört zur Kategorie Smartphones.
• Analog für die anderen Kategorien.

Die aktuelle Datenstruktur in der Tabelle categories sieht so aus:

Aufbau des Kategoriebaums mit rekursivem CTE

Jetzt wollen wir die komplette Hierarchie der Kategorien mit Angabe der Verschachtelungstiefe bekommen. Dafür nutzen wir einen rekursiven CTE.

WITH RECURSIVE category_tree AS (
    -- Basis-Query: Wähle alle Root-Kategorien (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Erste Ebene
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Rekursiver Teil: Finde Unterkategorien für jede Kategorie
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Erhöhe die Ebene
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Finale Query: Hole die Ergebnisse aus dem CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Ergebnis:

Was passiert hier?

1. Erstmal holt die Basis-Query (SELECT … FROM categories WHERE parent_category_id IS NULL) die Hauptkategorien. In unserem Fall ist das nur Elektronik mit depth = 1.
2. Dann fügt die rekursive Query mit INNER JOIN die Unterkategorien hinzu und erhöht die Ebene (depth + 1).
3. Das Ganze läuft so lange, bis alle Unterkategorien auf allen Ebenen gefunden sind.

Nützliche Erweiterungen

Das Basisbeispiel funktioniert, aber in echten Projekten brauchst du oft mehr. Sagen wir, du baust Breadcrumbs für die Website oder willst dem Manager zeigen, in welcher Kategorie es die meisten Unterbereiche gibt. Lass uns ein paar praktische Verbesserungen anschauen.

1. Vollständigen Kategoriepfad hinzufügen

Manchmal ist es praktisch, den kompletten Pfad zur Kategorie anzuzeigen, zum Beispiel: Elektronik > Smartphones > Zubehör. Das geht mit String-Aggregation:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- Strings zusammenbauen
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Ergebnis:

Jetzt hat jede Kategorie ihren kompletten Pfad, der die Verschachtelung zeigt.

2. Anzahl der Unterkategorien zählen

Was, wenn du wissen willst, wie viele Unterkategorien jede Kategorie hat?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

Ergebnis:

Die Tabelle zeigt, dass Elektronik 3 Unterkategorien hat (Smartphones, Notebooks, Foto und Video), und Smartphones und Notebooks jeweils eine.

Besonderheiten und typische Fehler bei rekursiven CTEs

Endlose Rekursion: Wenn die Daten Zyklen enthalten (z.B. eine Kategorie verweist auf sich selbst), kann die Query in eine Endlosschleife laufen. Um das zu verhindern, kannst du die Rekursionstiefe mit WHERE depth < N oder Limits einschränken.

Performance-Tuning: Rekursive CTEs können bei großen Datenmengen langsam sein. Setz einen Index auf parent_category_id, um das zu beschleunigen.

Fehler UNION statt UNION ALL: Nutze für rekursive CTEs immer UNION ALL, sonst versucht PostgreSQL, Duplikate zu entfernen, was die Query verlangsamt.

Dieses Beispiel zeigt, wie rekursive CTEs dir helfen, mit hierarchischen Strukturen zu arbeiten. Die Fähigkeit, Hierarchien aus der Datenbank zu holen, brauchst du in vielen echten Projekten – zum Beispiel beim Aufbau von Website-Menüs, bei der Analyse von Organisationsstrukturen oder beim Arbeiten mit Graphen. Jetzt bist du bereit für Aufgaben jeder Komplexität.

1

Umfrage/Quiz

Einführung in CTE, Level 27, Lektion 4

Nicht verfügbar

Einführung in CTE

Einführung in CTE

Öffnen