it-know-how/datenbanken/CTEs.md

#datenbank Kontext: Datenbanken

= ==Common Table Expression==

WITH-Block in SQL-Abfrage

➡️#Zusammenfassung

---

Im Folgenden bekommst du eine umfassende, aber einsteigerfreundliche Einführung in CTEs („Common Table Expressions“) im Kontext von Datenbanken – mit Definition, Abgrenzung, Nutzen, Herausforderungen und praxisnahen Beispielen.

---

1. Grundidee: Was ist eine CTE?

CTE steht für Common Table Expression.
Vereinfacht gesagt ist eine CTE:

Eine temporäre, benannte Ergebnismenge, die du in einer SQL-Abfrage definierst und im Anschluss in derselben Abfrage wie eine Tabelle verwenden kannst.

Man kann sich das vorstellen wie:

• „Ich speichere mir ein Zwischenergebnis unter einem Namen…“
• „…und benutze dieses Zwischenergebnis dann in der eigentlichen Anfrage.“

Grundform:

WITH name_der_cte AS (
    -- irgendeine SELECT-Abfrage
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM name_der_cte;

Wichtig:

• Die CTE existiert nur für diese eine Abfrage.
• Sie wird innerhalb der Abfrage definiert und direkt danach genutzt.
• Man kann CTEs mehrfach referenzieren, als wären es echte Tabellen.

---

2. Ein einfaches Beispiel

Stell dir vor, du hast eine Tabelle bestellungen:

• id
• kunde_id
• betrag
• datum

Du möchtest:

1. Alle Bestellungen des Jahres 2024 herausfiltern.
2. Dann auf Basis dieser gefilterten Daten den Gesamtumsatz pro Kunde berechnen.

Ohne CTE könnte man das mit einer verschachtelten Abfrage lösen; mit CTE sieht es lesbarer aus:

WITH bestellungen_2024 AS (
    SELECT *
    FROM bestellungen
    WHERE datum >= '2024-01-01'
      AND datum <  '2025-01-01'
)
SELECT
    kunde_id,
    SUM(betrag) AS umsatz_2024
FROM bestellungen_2024
GROUP BY kunde_id;

Die CTE bestellungen_2024 ist hier:
„Alle Bestellungen aus 2024“, und wird anschließend in der Hauptabfrage verwendet.

---

3. Abgrenzung zu ähnlichen oder verwandten Begriffen

3.1 CTE vs. Unterabfrage (Subquery / Derived Table)

Unterabfrage: Eine Abfrage innerhalb einer anderen Abfrage, z. B.:

SELECT
    kunde_id,
    SUM(betrag) AS umsatz_2024
FROM (
    SELECT *
    FROM bestellungen
    WHERE datum >= '2024-01-01'
      AND datum <  '2025-01-01'
) AS b2024
GROUP BY kunde_id;

Unterschiede:

• CTE: Wird am Anfang mit WITH definiert, trägt einen Namen und kann mehrfach benutzt werden.
• Subquery: Steht direkt im FROM oder WHERE, ist meist anonymer und schwerer zu lesen, vor allem bei komplexen Konstrukten.

Funktional können CTEs und Subqueries oft das Gleiche – CTEs verbessern eher Struktur und Lesbarkeit.

---

3.2 CTE vs. View (Sicht)

View (Sicht) ist wie eine gespeicherte Abfrage in der Datenbank:

CREATE VIEW bestellungen_2024 AS
SELECT *
FROM bestellungen
WHERE datum >= '2024-01-01'
  AND datum <  '2025-01-01';

Dann kannst du schreiben:

SELECT kunde_id, SUM(betrag)
FROM bestellungen_2024
GROUP BY kunde_id;

Unterschiede:

• View:
  • Wird dauerhaft in der Datenbank definiert.
  • Hat einen Namen, kann von vielen Abfragen und auch anderen Nutzer:innen verwendet werden.
  • Ändert sich nur über CREATE OR REPLACE VIEW / ALTER VIEW.
• CTE:
  • Gilt nur für eine einzige Abfrage.
  • Ist daher ideal für einmalige oder sehr spezialisierte Zwischenschritte.
  • Erfordert keine Rechte zum Erstellen von Objekten in der Datenbank (kein CREATE VIEW).

Kurz:
Views = dauerhafte, wiederverwendbare Bausteine.
CTEs = temporäre, einmalige Bausteine innerhalb einer Abfrage.

---

3.3 CTE vs. temporäre Tabelle

Viele Datenbanken kennen temporäre Tabellen, z. B. #temp_tab in SQL Server oder CREATE TEMP TABLE in PostgreSQL:

CREATE TEMP TABLE bestellungen_2024 AS
SELECT *
FROM bestellungen
WHERE datum >= '2024-01-01'
  AND datum < '2025-01-01';

SELECT kunde_id, SUM(betrag)
FROM bestellungen_2024
GROUP BY kunde_id;

Unterschiede:

• Temporäre Tabelle:
  • Wird physisch (zumindest logisch) in der Datenbank angelegt.
  • Existiert für die Dauer einer Session oder Transaktion.
  • Kann indiziert werden (Index hinzufügen) und so bei großen Datenmengen Performancevorteile bringen.
• CTE:
  • Keine echte Tabelle, eher eine „logische Abfragekomponente“.
  • Keine eigenen Indexe.
  • Gilt nur innerhalb einer Abfrage.

---

3.4 CTE vs. Stored Procedure / Function

Stored Procedures und Functions sind Programmierbausteine auf Datenbankseite, z. B.:

• Prozeduren: führen mehrere Schritte, ggf. mit Kontrollstrukturen aus (IF, WHILE,…).
• Funktionen: geben einen Wert oder eine Tabelle zurück.

Unterschiede:

• CTE ist Teil einer einzelnen SQL-Select/Insert/Update/Delete-Abfrage.
• Stored Proc / Function ist Code, den man speichert, versioniert und immer wieder aufrufen kann.

CTEs können innerhalb von Stored Procedures verwendet werden – sie sind eher Bausteine auf Abfrage-Ebene, nicht auf Programm-Ebene.

---

4. Welche Probleme werden durch CTEs gelöst?

4.1 Bessere Lesbarkeit und Struktur

Statt eine riesige, komplexe Abfrage mit vielen verschachtelten Unterabfragen zu schreiben, kann man sich die Abfrage in logische Schritte zerlegen:

1. WITH schritt1 AS (...)
2. , schritt2 AS (...)
3. SELECT ... FROM schritt2 ...

Beispiel:
Du möchtest erst „aktive Kunden“ bestimmen und dann nur deren Bestellungen summieren:

WITH aktive_kunden AS (
    SELECT id, name
    FROM kunden
    WHERE status = 'aktiv'
),
bestellungen_aktive_kunden AS (
    SELECT
        b.kunde_id,
        SUM(b.betrag) AS umsatz
    FROM bestellungen b
    JOIN aktive_kunden k ON k.id = b.kunde_id
    GROUP BY b.kunde_id
)
SELECT
    k.name,
    bak.umsatz
FROM bestellungen_aktive_kunden bak
JOIN aktive_kunden k ON k.id = bak.kunde_id;

Jeder CTE beschreibt einen klaren Teilschritt, das erleichtert Verstehen und Warten des Codes.

---

4.2 Wiederverwendung innerhalb einer Abfrage

Oft brauchst du ein bestimmtes Zwischenergebnis mehrmals in derselben Abfrage.
Ohne CTE müsstest du:

• Dieselbe Unterabfrage mehrfach schreiben (redundant, fehleranfällig)
• Oder du machst Temp-Tabellen / Views, die aber mehr Setup erfordern.

Mit CTE definierst du das einmal und verwendest es mehrfach:

WITH bestellungen_2024 AS (
    SELECT *
    FROM bestellungen
    WHERE datum >= '2024-01-01'
      AND datum < '2025-01-01'
)
SELECT
    (SELECT COUNT(*) FROM bestellungen_2024) AS anzahl_gesamt,
    (SELECT SUM(betrag) FROM bestellungen_2024) AS umsatz_gesamt;

---

4.3 Schrittweise Transformationen (ETL-artige Abläufe)

Man kann komplexe Datenverarbeitungen in mehrere CTE-Schritte aufteilen, z. B.:

1. Rohdaten bereinigen (ungültige Werte rausfiltern)
2. Daten anreichern (Join mit Lookup-Tabellen)
3. Aggregationen berechnen
4. Ergebnis selektieren

Schema:

WITH raw_data AS (
    SELECT * FROM import_tabelle
),
cleaned_data AS (
    SELECT *
    FROM raw_data
    WHERE wert IS NOT NULL
),
enriched_data AS (
    SELECT
        c.*,
        l.beschreibung
    FROM cleaned_data c
    LEFT JOIN lookup l ON c.code = l.code
),
aggregated AS (
    SELECT
        beschreibung,
        COUNT(*) AS anzahl,
        AVG(wert) AS durchschnitt
    FROM enriched_data
    GROUP BY beschreibung
)
SELECT *
FROM aggregated
ORDER BY anzahl DESC;

Solche „linearen“ CTE-Ketten sind sehr hilfreich, um komplexe ETL-Schritte innerhalb einer SQL-Abfrage klar zu definieren.

---

4.4 Rekursive Abfragen (Hierarchien, Bäume, Graphen)

Ein besonderer Typ von CTE ist die rekursive CTE. Damit kann man Strukturen abfragen, die hierarchisch sind, z. B.:

• Mitarbeiter und ihre Vorgesetzten (Organigramm)
• Kategorien und Unterkategorien (Baumstrukturen)
• Stücklisten (Bauteil besteht aus Unterteilen, die wiederum Unterteile haben)
• Graphen/Netzwerke mit Verbindungen

Grundidee:

• Es gibt eine Anker-Abfrage (Startpunkt).
• Und eine rekursive Abfrage, die sich immer wieder selbst referenziert.

Beispiel: Mitarbeiter-Hierarchie
Tabelle mitarbeiter:

• id
• name
• chef_id (Verweis auf id in derselben Tabelle, NULL für Chef ganz oben)

WITH RECURSIVE hierarchie AS (
    -- 1) Anker: Chef (oberste Ebene)
    SELECT
        id,
        name,
        chef_id,
        0 AS ebene
    FROM mitarbeiter
    WHERE chef_id IS NULL

    UNION ALL

    -- 2) Rekursiver Teil: alle Mitarbeiter, die einem bereits gefundenen Mitarbeiter unterstellt sind
    SELECT
        m.id,
        m.name,
        m.chef_id,
        h.ebene + 1 AS ebene
    FROM mitarbeiter m
    JOIN hierarchie h ON m.chef_id = h.id
)
SELECT *
FROM hierarchie
ORDER BY ebene, id;

Ergebnis:
Alle Mitarbeiter mit einer Spalte ebene, die angibt, wie weit sie vom obersten Chef entfernt sind.

Ohne rekursive CTEs wäre das sehr umständlich oder gar nicht (standardkonform) direkt in SQL möglich. Manche Systeme haben dafür eigene Syntax (z. B. CONNECT BY in Oracle), rekursive CTEs sind der SQL-Standard-Weg.

---

5. Weitere praxisnahe Beispiele

5.1 Alltagsszenario: „Top-Produkte pro Monat“

Gegeben:

• verkaeufe mit Spalten:
  • produkt_id
  • datum
  • menge
  • umsatz

Ziel:
Pro Monat die Top 3 Produkte nach Umsatz anzeigen.

Ein möglicher Weg mit CTE:

WITH monatliche_umsaetze AS (
    SELECT
        DATE_TRUNC('month', datum) AS monat,
        produkt_id,
        SUM(umsatz) AS umsatz_monat
    FROM verkaeufe
    GROUP BY DATE_TRUNC('month', datum), produkt_id
),
ranking AS (
    SELECT
        monat,
        produkt_id,
        umsatz_monat,
        ROW_NUMBER() OVER (
            PARTITION BY monat
            ORDER BY umsatz_monat DESC
        ) AS rang_im_monat
    FROM monatliche_umsaetze
)
SELECT
    monat,
    produkt_id,
    umsatz_monat,
    rang_im_monat
FROM ranking
WHERE rang_im_monat <= 3
ORDER BY monat, rang_im_monat;

Die Logik wird nachvollziehbar in zwei Schritte aufgeteilt:

1. summieren pro Monat und Produkt
2. pro Monat ranken und Top 3 auswählen

---

5.2 Kalender-CTE: fehlende Tage auffüllen

Manchmal hat man z. B. nur Daten für Tage, an denen Verkäufe stattfanden, möchte aber jeden Tag im Zeitraum sehen (auch wenn Umsatz 0 ist).
Mit einer rekursiven CTE kann man einen Kalender erzeugen.

Beispiel (PostgreSQL-Syntax):

WITH RECURSIVE kalender AS (
    SELECT DATE '2024-01-01' AS tag
    UNION ALL
    SELECT tag + INTERVAL '1 day'
    FROM kalender
    WHERE tag < DATE '2024-01-31'
)
SELECT
    k.tag,
    COALESCE(SUM(v.umsatz), 0) AS umsatz
FROM kalender k
LEFT JOIN verkaeufe v ON DATE(v.datum) = k.tag
GROUP BY k.tag
ORDER BY k.tag;

So erhältst du für jeden Tag im Januar 2024 einen Umsatzwert – bei Tagen ohne Verkäufe eben 0.

---

6. Welche Herausforderungen und Stolpersteine gibt es?

6.1 Performance (Leistungsfähigkeit)

CTEs sind primär ein Lesbarkeits-Feature, aber sie können die Performance beeinflussen – je nach Datenbank:

• Einige Datenbanken „materialisieren“ CTEs:
  • Das Zwischenergebnis wird wirklich berechnet und zwischengespeichert.
  • Mehrfachzugriff ist dann ggf. schneller, aber die Initialberechnung kann teurer sein.
• Andere optimieren CTEs ähnlich wie Unterabfragen:
  • Sie werden im Optimizer „eingefaltet“, d. h. wie eine direkte Teilabfrage behandelt.

Typische Punkte:

• Mehrfache Verwendung großer CTEs:
  • Wenn eine CTE sehr groß ist und du mehrfach darauf zugreifst, kann das teuer werden.
  • In manchen Systemen ist dann eine temporäre Tabelle mit Index effizienter.
• Rekursive CTEs:
  • Können viel CPU und Zeit brauchen, wenn die Hierarchie sehr tief oder stark verzweigt ist.
  • Es gibt oft eine maximale Rekursionstiefe (z. B. 100 oder 32767), die begrenzt, wie tief die Rekursion geht.

Praxis-Tipp:

• CTEs sind super für mittlere Komplexität und moderate Datenmengen.
• Bei sehr großen Datenmengen und Performanceproblemen:
  • Ausführungsplan anschauen.
  • Mit Temp-Tabellen, Indizes oder Views experimentieren.

---

6.2 Übermäßige Verschachtelung

Wenn man CTEs zu exzessiv nutzt, z. B. 20 oder mehr CTEs in einer Abfrage, wird es:

• schwer zu lesen
• schwierig zu debuggen
• komplex zu warten

Hier gilt:
Abstraktion, wo sinnvoll, aber nicht übertreiben.

Manchmal ist es besser:

• Teile der Logik in eine View zu legen,
• oder in eine Stored Procedure / Function,
• oder die Logik in mehrere kleinere Abfragen aufzuteilen.

---

6.3 Datenbankspezifische Unterschiede

Nicht jede Datenbank:

• unterstützt CTEs gleich
• oder hat genau dieselbe Syntax.

Beispiele:

• PostgreSQL: Unterstützt WITH und WITH RECURSIVE.
• SQL Server: Unterstützt CTEs, Rekursion ohne extra RECURSIVE-Keyword.
• MySQL: Ab Version 8.0 gibt es CTEs, rekursiv mit WITH RECURSIVE.
• Oracle: Unterstützt CTEs, hatte aber historisch eigene Hierarchie-Syntax (CONNECT BY).

Wenn du in mehreren Systemen arbeitest, lohnt ein Blick in die Dokumentation der jeweiligen Datenbank.

---

6.4 CTEs haben keine eigenen Indizes

Da CTEs keine physischen Tabellen sind, kannst du:

• keine Indexe darauf erstellen
• keine Statistiken direkt auf CTE-Ebene pflegen

D. h.:

• Der Optimizer muss die Abfrage so gut wie möglich auf Grundlage der zugrundeliegenden Tabellen optimieren.
• Bei Performanceproblemen kann eine echte (temporäre) Tabelle mit Index manchmal besser sein.

---

7. Wann sollte man CTEs einsetzen? – Orientierung

Geeignet für CTEs:

• Wenn eine Abfrage schwer lesbar ist, weil sie viele Unterabfragen enthält.
• Wenn du dasselbe Zwischenergebnis mehrfach in einer Abfrage benötigst.
• Wenn du schrittweise Transformationen ausdrücken möchtest („Step 1, Step 2, …“).
• Wenn du Hierarchien oder rekursive Strukturen mit SQL abbilden willst.

Eher nicht ideal:

• Für dauerhafte, von vielen Personen genutzte Logik → eher Views oder Stored Procedures.
• Für sehr große Zwischenergebnisse, die oft wiederverwendet werden → evtl. temporäre Tabellen mit Index.
• Wenn Performance schon kritisch ist → CTE-Einsatz testen und Ausführungspläne prüfen.

---

Zusammenfassung

• Definition: CTE = „Common Table Expression“ = eine temporäre, benannte Ergebnismenge innerhalb einer Abfrage, definiert mit WITH.
• Nutzen:
  • Klarere Struktur und bessere Lesbarkeit komplexer SQL-Abfragen.
  • Wiederverwendbarkeit eines Zwischenergebnisses innerhalb derselben Abfrage.
  • Möglichkeit, komplexe Transformationen sequenziell auszudrücken.
  • Unterstützung rekursiver Abfragen (Hierarchien/Bäume).
• Abgrenzung:
  • Gegenüber Subqueries: funktional ähnlich, aber meist lesbarer.
  • Gegenüber Views: CTEs sind temporär, Views dauerhaft.
  • Gegenüber Temp-Tabellen: CTEs sind logisch, Temp-Tabellen physisch und indexierbar.
• Herausforderungen:
  • Performanceabhängig vom Datenbanksystem und vom Aufbau der Abfrage.
  • Übermäßiger Gebrauch kann Abfragen unübersichtlich machen.
  • Rekursive CTEs können komplex und teuer werden, brauchen sorgfältige Bedingungen.