Antwort

Die Anweisung WITH (oder CTE – Common Table Expression) erstellt temporär eine Ergebnismenge, die in der Hauptabfrage SELECT oder sogar rekursiv verwendet werden kann. CTE machen Abfragen einfacher zu verstehen, erhöhen die Lesbarkeit und ermöglichen die Wiederverwendung von Zwischenresultaten.

Vorteile gegenüber normalen Unterabfragen:

Verbesserung der Lesbarkeit komplexer Abfragen (leichter zu debuggen und zu warten).
Rekursive Abfragen können verwendet werden.
Man kann mehrmals auf CTE mit Namen zugreifen, anstatt die gleiche Unterabfrage zu duplizieren.

Beispiel:

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Trickfrage

„Ist die Angabe von MATERIALIZED/NOT MATERIALIZED bei der Deklaration von CTE wichtig? Wie kann dies die Leistung beeinflussen?“

Antwort: In PostgreSQL kann man ausdrücklich MATERIALIZED (CTE immer einmal berechnen und speichern, selbst wenn sie mehrmals verwendet werden) oder NOT MATERIALIZED (CTE wird in die Hauptabfrage inline eingefügt) angeben. Normalerweise entscheidet der Optimierer selbst, ob CTE materialisiert werden sollen, aber eine erzwungene Angabe kann manchmal erhebliche Auswirkungen auf die Geschwindigkeit haben.

Beispiel:

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Geschichte

In einem großen Projekt ersetzte ein Analyst alle Unterabfragen durch CTE, ohne zu wissen, dass in PostgreSQL bis Version 12 CTE immer materialisiert werden. Das Ergebnis – die Abfragen verlangsamen sich um das 2-3-fache. Nach einer Rückkehr zu den Unterabfragen oder dem Wechsel zu neuen Versionen war das Problem verschwunden.

Geschichte

Ein Bericht mit mehreren Ebenen von verschachtelten Unterabfragen wurde für einen Kollegen unleserlich. Wir schrieben es in CTE mit sinnvollen Namen um – Diskussion und Wartung der Abfragen wurden erheblich vereinfacht.

Geschichte

Zwei CTE mit den gleichen Namen führten in einem großen Skript zu Verwirrung: CTE wurde bereits vor seiner Deklaration verwendet. Infolgedessen ein Kompilierungsfehler und ein erheblicher Zeitaufwand zur Fehlersuche.

Was macht die Anweisung WITH (Common Table Expressions, CTE)? Was sind die Vorteile der Verwendung von CTE im Vergleich zu Unterabfragen? Geben Sie ein Beispiel an.

Antwort

Trickfrage