1. Komplexe Abfragen

Komplexe Abfragen - Vertiefung der SQL-Kenntnisse

Was bedeutet “komplex” in diesem Zusammenhang? Es geht um Abfragen, die über einfache SELECT ... FROM ... WHERE ...-Anweisungen hinausgehen. Dazu zählen:

• Joins: Verknüpfungen zwischen mehreren Tabellen, um zusammenhängende Daten abzufragen.
• Unterabfragen: Abfragen innerhalb von Abfragen, um komplexe Bedingungen zu formulieren.
• Aggregation: Zusammenfassen von Daten (z. B. mit GROUP BY und HAVING).
• Fensterfunktionen: Analysieren von Daten über verschiedene “Fenster” hinweg, ohne die Daten zu aggregieren.
• CTEs (Common Table Expressions): Temporäre Ergebnismengen, die in einer Abfrage verwendet werden können, um die Lesbarkeit zu erhöhen.

”komplex” vs. “kompliziert”

Kurzfassung:

• komplex = “vielschichtig, umfassend; ineinandergreifend/zusammengesetzt”.
• kompliziert = “schwierig, verwickelt; [daher] schwer zu durchschauen/handhaben”.

Der Unterschied in einem Satz: Komplex beschreibt vor allem die Struktur (viele Teile & Beziehungen); kompliziert beschreibt vor allem die Handhabung/Verständlichkeit (es macht Mühe). Diese Begriffe werden im Alltag zwar oft vermischt, sind aber nicht synonym.

Beispiel zum Merken:

• Ein großes Ökosystem ist komplex (zahlreiche, vernetzte Wechselwirkungen), muss aber nicht zwingend kompliziert zu bedienen sein.
• Ein Formular mit 12 Pflichtfeldern kann kompliziert sein (umständlich), ohne strukturell komplex zu sein.

Hinweis

Komplex = viele verbundene Teile; kompliziert = schwer zu verstehen/benutzen.

Fachliche/strukturelle Komplexität (Cognitive Load)

Typische Merkmale:

• Viele Teilschritte: mehrere abhängige CTEs (s.u.), verschachtelte Subqueries.
• Über Zeilen hinweg denken: Window-Funktionen (LAG/LEAD, ROW_NUMBER, komplexe FRAME-Klauseln).
• Hierarchien/Graphen: rekursive CTEs (WITH RECURSIVE …).
• Aggregationen über Dimensionen: GROUPING SETS / ROLLUP / CUBE, Pivot/Unpivot, bedingte Aggregation.
• Mengenoperationen: UNION/INTERSECT/EXCEPT in Kombination mit obigem.
• Semistrukturiert & Spezialdomänen: JSON/Array-Operationen, Geodaten (GIS), Volltextsuche.

Operative/performanzbezogene Komplexität (Cost Load)

Typische Auslöser:

• Hohe Kardinalität & viele Joins, unklare Join-Reihenfolge.
• Nicht-sargable Prädikate (Funktionen auf Spalten), OR-Ketten, übermäßiges DISTINCT (SARGable = “Search ARGument-able”).
• Partitionierung/Statistiken nötig (Partition-Pruning, korrektes Sampling).
• Materialisierte Zwischenschritte / Hints erforderlich, um stabile Pläne zu bekommen.

Hinweis

Faustregel: Sobald man über Reihenfolge & Fenster, über Pfade/Hierarchien oder über mehrere Aggregationsebenen nachdenken muss und gleichzeitig auf Datenmengen/Pläne achten muss, ist die Query komplex.

Fensterfunktionen

Fensterfunktionen sind GROUP BY-ähnliche Funktionen, die jedoch über Zeilen hinweg operieren, ohne die Zeilen zu aggregieren. Sie ermöglichen es, Berechnungen durchzuführen, die auf einer “Fenster”-Ansicht der Daten basieren.

Video

YouTube: “SQL Window Functions Basics | Partition By, Order By, Frame | #SQL Course 22”

Abspielen

Gemeinsamkeiten: Beide helfen, aus vielen Zeilen „etwas Zusammengefasstes“ zu berechnen - z. B. SUM, AVG, MIN/MAX. Bei beiden denkt man in Gruppen: GROUP BY bildet echte Gruppen; Fensterfunktionen benutzen hingegen PARTITION BY im OVER(...), was logisch ähnlich ist. Ziel in beiden Fällen: Antworten wie “Durchschnitt pro Klasse”, “Gesamtumsatz pro Kunde” oder “Rangfolge nach Punkten”.

Unterschied (GROUP BY + Aggregat): GROUP BY verdichtet die Tabelle: Aus vielen Zeilen wird eine Zeile pro Gruppe. Nicht gruppierte Spalten verschwinden; du siehst nur noch die Gruppenschlüssel und die Aggregatwerte. Das ist ideal für Berichte/Tabellen mit wenigen Zeilen, z. B. “Umsatz je Kunde” oder “Anzahl Schüler je Schule”.

Unterschied (Fensterfunktionen): Fensterfunktionen lassen alle Zeilen stehen und rechnen zusätzlich über ein “Fenster” der Daten (OVER(...)) - bsp. pro Kunde (PARTITION BY) in einer sinnvollen Reihenfolge (ORDER BY). So bekommst du pro Zeile extra Infos wie Rang (ROW_NUMBER()), laufende Summe (SUM() OVER(...)) oder Vergleich zur Vorzeile (LAG()), ohne die Detailzeilen zu verlieren.

Rang

Rang (Ranking): “Rang” ist die Position einer Zeile innerhalb einer Gruppe (PARTITION) nach einer definierten Sortierung (ORDER BY). Typische Ranking-Funktionen sind ROW_NUMBER() (zählte strikt 1, 2, 3, …- auch bei Gleichstand), RANK() (gleiche Werte bekommen denselben Rang; danach entstehen Lücken: 1, 2, 2, 4, …), und DENSE_RANK() (wie RANK(), aber ohne Lücken: 1, 2, 2, 3, …). Diese Funktionen sind Fensterfunktionen und arbeiten mit OVER (PARTITION BY ... ORDER BY ...).

LAG

LAG (zur “Vorzeile” schauen): LAG(expr [, offset [, default]]) OVER (PARTITION BY … ORDER BY...) liest den Wert aus einer vorherigen Zeile - ohne Self-Join. Standardmäßig ist offset = 1. Typisch nutzt man LAG, um Differenzen zur Vorzeile oder Trendwechsel zu berechnen (z. B. Preisänderung, Puls beim letzten Messzeitpunkt).

Kurzdefinition

• Syntax: Fensterfunktionen wie Aggregatsfunktionen (AVG(), SUM(), …), Rangfunktionen (RANK(), ROW_NUMBER(), ...) oder analytische Funktionen (LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), ...) über ein “Fenster” der Daten OVER (PARTITION BY ... ORDER BY ... [FRAME])
• Lebensdauer: nur innerhalb der laufenden Query
• Nutzen: Berechnungen über Fenster von Daten, ohne Zeilen zu aggregieren.

Diese Anweisung führt in der aktuellen Tabelle eine Fensterfunktion aus, die über ein definiertes “Fenster” von Zeilen operiert. Das Fenster wird durch die PARTITION BY-Klausel (Gruppierung) und die ORDER BY-Klausel (Sortierung innerhalb der Gruppe) definiert. Optional kann ein FRAME angegeben werden, um das genaue Fenster weiter einzuschränken.

FRAME-Beispiele:

• Alles von der aktuellen Zeile bis zum Anfang der Partition: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Die letzten 10 Zeilen: RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
• Die nächsten 5 Zeilen: RANGE BETWEEN CURRENT ROW AND 5 FOLLOWING
• …

Beispiel zu GROUP BY vs. Fensterfunktionen

Ausgangsdaten (vereinfacht)

department	employee_name	salary
IT	Alice	4000
IT	Bob	4500
HR	Carol	3000
HR	Dave	3200

Aufgabe

Berechne das Durchschnittsgehalt jeder Abteilung mit GROUP BY.

SQL GROUP BY

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Diese Query gibt pro Abteilung das Durchschnittsgehalt zurück. Insgesamt gibt es nur eine Zeile pro Abteilung.

Ergebnis:

department	avg_salary
HR	3100
IT	4250

Kurz und knackig, aber die einzelnen Mitarbeiter sind verschwunden – wir haben nur noch 1 Zeile pro Abteilung.

Gleiche Fragestellung – aber wir wollen nun jeden Mitarbeiter sehen und zusätzlich das Durchschnittsgehalt seiner Abteilung anzeigen:

Aufgabe

Zeige jeden Mitarbeiter und das Durchschnittsgehalt seiner Abteilung mit einer Fensterfunktion.

SQL Fensterfunktion

SELECT employee_name,
       department,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

Hier wird das Durchschnittsgehalt jeder Abteilung berechnet, ohne die Detailzeilen zu verlieren. Jede Zeile zeigt das Gehalt des Mitarbeiters sowie das Durchschnittsgehalt der Abteilung.

Ergebnis:

employee_name	department	salary	avg_salary
Alice	IT	4000	4250
Bob	IT	4500	4250
Carol	HR	3000	3100
Dave	HR	3200	3100

Vorteil von diesem Ergebnis: Man behält die Detailzeilen und hat gleichzeitig die Aggregation. Fensterfunktionen sind somit ideal für Analysen, bei denen man Vergleiche anstellen möchte (z. B. „Wie viel über/unter Durchschnitt verdient jemand?“).

Schauen wir uns das beim nächsten Beispiel an:

Aufgabe

Berechne das Minimum-, Durchschnitts- und Maximumgehalt jeder Abteilung mit einer Fensterfunktion, gib zusätzlichen jeden Mitarbeiter aus, ohne die Detailzeilen zu verlieren. Berechne außerdem die Gehaltsdifferenz des Mitarbeiters zum Abteilungsdurchschnitt und zum Gesamtdurchschnitt. Füge außerdem den Rang des Mitarbeiters innerhalb der Abteilung und im gesamtem Unternehmen hinzu (höchstes Gehalt = Rang 1).

SQL Fensterfunktion (komplex)

SELECT
  e.employee_no                                                  AS employee_no,
  e.employee_name                                                AS employee_name,
  e.department                                                   AS department,
  e.salary                                                       AS salary,
  MIN(e.salary) OVER (PARTITION BY e.department)                 AS dept_min_salary,
  AVG(e.salary) OVER (PARTITION BY e.department)                 AS dept_avg_salary,
  MAX(e.salary) OVER (PARTITION BY e.department)                 AS dept_max_salary,
  e.salary - AVG(e.salary) OVER (PARTITION BY e.department)      AS diff_from_dept_avg,
  AVG(e.salary) OVER ()                                          AS overall_avg_salary,
  e.salary - AVG(e.salary) OVER ()                               AS diff_from_overall_avg,
  RANK() OVER (PARTITION BY e.department ORDER BY e.salary DESC) AS dept_rank,
  RANK() OVER (ORDER BY e.salary DESC)                           AS overall_rank
FROM employees e
ORDER BY e.department, dept_rank, e.employee_no;

Ergebnis: Beispielausgabe (vereinfacht)

No	Name	Dept	Gehalt	Dept Min	Dept Avg	Dept Max	diff dept	Overall Avg	diff overall	Dept Rank	Overall Rank
1	Alice	IT	4000	4000	4250	4500	-250	3675	+325	2	2
2	Bob	IT	4500	4000	4250	4500	+250	3675	+825	1	1
3	Carol	HR	3000	3000	3100	3200	-100	3675	-675	2	4
4	Dave	HR	3200	3000	3100	3200	+100	3675	-475	1	3

Diese Query läßt sich mit GROUP BY nicht leicht realisieren, da wir die Detailzeilen behalten wollen und gleichzeitig mehrere Aggregationen und Rangberechnungen benötigen.

Erklärung:

MIN(e.salary) OVER (PARTITION BY e.department) AS dept_min_salary,
AVG(e.salary) OVER (PARTITION BY e.department) AS dept_avg_salary,
MAX(e.salary) OVER (PARTITION BY e.department) AS dept_max_salary

• PARTITION BY e.department bedeutet: Berechnung wird pro Abteilung gemacht.

• Jeder Mitarbeiter in der gleichen Abteilung bekommt die gleichen Werte.

• Somit steht in jeder Zeile zusätzlich das Minimum, der Durchschnitt und das Maximum der Gehälter in dieser Abteilung.

e.salary - AVG(e.salary) OVER (PARTITION BY e.department) AS diff_from_dept_avg

• Nimmt das Gehalt des Mitarbeiters minus den Durchschnitt seiner Abteilung.

• Positive Zahl = verdient mehr als der Abteilungsdurchschnitt.

• Negative Zahl = verdient weniger

AVG(e.salary) OVER () AS overall_avg_salary,
e.salary - AVG(e.salary) OVER () AS diff_from_overall_avg

• OVER () ohne Partition → ein Wert für das gesamte Unternehmen.

• Jeder Mitarbeiter sieht also den Gesamtdurchschnitt und seine Abweichung davon.

RANK() OVER (PARTITION BY e.department ORDER BY e.salary DESC) AS dept_rank,
RANK() OVER (ORDER BY e.salary DESC)                           AS overall_rank

• dept_rank: Sortierung innerhalb der Abteilung nach Gehalt (höchstes Gehalt = Rang 1).

• overall_rank: Gleiche Sortierung über alle Mitarbeiter hinweg.

• RANK() vergibt gleiche Ränge bei gleichem Gehalt (mit Lücken: z. B. 1, 2, 2, 4).

ORDER BY e.department, dept_rank, e.employee_no;

• Ergebnis wird zuerst nach Abteilung sortiert, dann nach Rang, dann nach Mitarbeiternummer.

CTE - Common Table Expression

Das ist eine benannte Zwischenabfrage direkt vor dem eigentlichen SELECT - eingeführt mit der WITH-Klausel. Man definierst damit “temporäre Tabellen”, die nur für diese Abfrage sichtbar sind, ohne etwas dauerhaft zu speichern.

Kurzdefinition

• Syntax: WITH name AS ( ... ) SELECT ... FROM name ...
• Lebensdauer: nur innerhalb der folgenden Abfrage
• Nutzen:
  • Lesbarer Code: Lange, verschachtelte Subqueries werden aufgeteilt in benannte Zwischenschritte.
  • Wiederverwendbarkeit: Du kannst dieselbe Berechnung mehrfach in der Abfrage nutzen.
  • Rekursion möglich: Mit WITH RECURSIVE lassen sich Hierarchien und Pfade (z. B. Baumstrukturen) auflösen.

Beispiele

Beispiel 1: Wir berechnen Monatsumsätze pro Kunde und vergleichen sie mit dem Vormonat:

Nicht-rekursiv (Zwischenschritt benennen & mehrfach verwenden):

SQL

WITH monthly_sales AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS mon, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT m.customer_id, m.mon, m.total,
       m.total / NULLIF(prev.total, 0) AS growth
FROM monthly_sales m
LEFT JOIN monthly_sales prev
  ON prev.customer_id = m.customer_id
 AND prev.mon = m.mon - INTERVAL '1 month';

• monthly_sales ist eine CTE.

• Sie wird oben einmal definiert und dann unten mehrfach benutzt (m und prev).

• Ohne CTE müsste man diese Aggregation zweimal als Subquery hineinschreiben → viel unübersichtlicher.

Beispiel 2: Wir wollen eine Baumstruktur auflösen (z. B. Kategorien mit Unterkategorien):

Beispiel-Daten (Tabelle nodes)

id	parent_id	name
10	NULL	Elektronik
25	10	Computer
31	25	Laptops
42	10	Fernseher
77	25	Desktops
88	31	Gaming-Laps

Rekursiv (Hierarchie/Pfad auflösen):

SQL

WITH RECURSIVE tree AS (

  SELECT id, parent_id, name, 0 AS depth
  FROM nodes
  WHERE id = :root

  UNION ALL

  SELECT n.id, n.parent_id, n.name, t.depth + 1
  FROM nodes n
  JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

• Startpunkt ist :root

• Mit jedem UNION-Schritt werden die Kinder (Unterknoten) gefunden.

• Das wiederholt sich, bis keine Kinder mehr da sind.

• Ergebnis: kompletter Baum von oben nach unten.

Ergebnis: Wir bekommen die gesamte Baumstruktur unterhalb von „Elektronik“ aufgelöst.

id	parent_id	name	depth
10	NULL	Elektronik	0
25	10	Computer	1
42	10	Fernseher	1
31	25	Laptops	2
77	25	Desktops	2
88	31	Gaming-Laps	3

Grafisch würde der Baum so aussehen:

CTE vs. Subquery vs. Temp-Table

• CTE: benannt, oben definiert, kann mehrfach referenziert werden; gut lesbar; keine dauerhafte Speicherung.
• Subquery: “inline” im FROM/WHERE; oft einmalig; kann unübersichtlich werden.
• Temporäre Tabelle: wird angelegt (z. B. CREATE TEMP TABLE ...), lebt über mehrere Statements; man kann indizieren, aber hat mehr Overhead.

Wie man Komplexität beherrscht (Praxis-Checkliste)

1. Pipeline statt Monster-Query: Sinnvoll benannte CTE-Schritte; jeder Schritt beantwortet eine Frage.
2. Lesbarkeit > Cleverness: Explizite Aliase, Kommentare, konsistente Formatierung.
3. Sargability: Filter links “roh”, Funktionen rechts; vermeide WHERE DATE(col)=..., nutze col >= ... AND col < ....
4. Explain/Analyse regelmäßig auf repräsentativen Daten; prüfe Kardinalitäts-Schätzungen.
5. Indizes/Partitionen an die Prädikate & Join-Keys anpassen; DISTINCT nur, wenn fachlich nötig.
6. Vorberechnungen: Views/Materialized Views, wenn derselbe teure Schritt öfter gebraucht wird.

1. Pipeline statt Monster-Query

Statt eine einzige riesige Abfrage zu schreiben, lieber mehrere CTEs (Zwischenschritte) mit sprechenden Namen.

• Jeder CTE beantwortet eine Teilfrage.
• Am Ende setzt man die Schritte zusammen.

Beispiel:

WITH orders_per_customer AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
),
top_customers AS (
  SELECT customer_id
  FROM orders_per_customer
  WHERE order_count > 5
)
SELECT * FROM top_customers;

So ist es viel lesbarer als alles in einer verschachtelten Query.

2. Lesbarkeit > Cleverness

Abfragen sollen verständlich sein, nicht „möglichst clever und kryptisch“.

• Benutze Aliase für Spalten und Tabellen.
• Kommentiere komplizierte Stellen.
• Nutze eine konsistente Formatierung (z. B. Schlüsselwörter in Großbuchstaben).

3. Sargability (Search ARGument-able)

Filter so schreiben, dass die Datenbank Indizes nutzen kann - also „Spalte links roh, Funktion rechts“.

Schlecht:

WHERE DATE(order_date) = '2025-09-25'

(Datenbank muss jede Zeile durch DATE() jagen → Index unbrauchbar)

Besser:

WHERE order_date >= '2025-09-25'
  AND order_date <  '2025-09-26'

(Index auf order_date wird benutzt).

4. Explain/Analyse nutzen

Regelmäßig EXPLAIN (MySQL, PostgreSQL) oder EXPLAIN ANALYZE verwenden.

• Damit sieht man, ob die Abfrage Indizes nutzt oder ob ein „Full Table Scan“ passiert.
• Prüfe die Kardinalität (Schätzung der Zeilenanzahl) – falsche Schätzungen führen oft zu langsamen Plänen.

5. Indizes und Partitionen

Indizes auf die Spalten legen, die in WHERE, JOIN oder GROUP BY vorkommen.

• Ohne Index sucht die DB zeilenweise → langsam.

  Bei sehr großen Tabellen Partitionen nutzen. Zum Beispiel Daten nach Jahr partitionieren, dann scannt die DB nur die relevanten Partitionen.

• DISTINCT nur wenn nötig

  DISTINCT klingt praktisch, ist aber teuer. Es zwingt die DB, Duplikate zu vergleichen und zu eliminieren. Oft ist DISTINCT nur ein Workaround für eine falsche Join-Logik.

6. Vorberechnungen

Wenn ein Teilschritt oft gebraucht wird:

• Als View abspeichern (einfacher Zugriff, aber immer neu berechnet).
• Oder als Materialized View (Ergebnis wird zwischengespeichert, schneller, muss aber aktualisiert werden).

→ Mehr dazu gibt’s im Kapitel “3. Performance”