SQL fuer Datenanalysten: Fensterfunktionen, CTEs und fortgeschrittene Abfragen

SQL-Fensterfunktionen, CTEs (Common Table Expressions) und fortgeschrittene Abfragemuster bilden das Rueckgrat analytischer Datenbankarbeit. Ob bei der Vorbereitung auf ein technisches Interview als Datenanalyst oder beim Aufbau komplexer Reporting-Abfragen — diese Techniken verwandeln verschachtelte, schwer lesbare Subqueries in sauberes, performantes SQL. Wer diese Konzepte sicher beherrscht, loest Analyseaufgaben in einem Bruchteil der Zeit und liefert dabei wartbaren, nachvollziehbaren Code.

SQL-Fensterfunktionen und die OVER-Klausel verstehen

Eine Fensterfunktion wendet eine Berechnung auf ein definiertes "Fenster" von Zeilen an. Die OVER-Klausel bestimmt, welche Zeilen zum Fenster gehoeren und in welcher Reihenfolge sie verarbeitet werden. Im Gegensatz zu Aggregatfunktionen mit GROUP BY bleiben bei Fensterfunktionen alle einzelnen Zeilen im Ergebnis erhalten.

Die allgemeine Syntax folgt einem einheitlichen Muster, das in allen gaengigen Datenbanksystemen funktioniert — PostgreSQL, MySQL 8+, BigQuery, SQL Server und Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  -- Ranks employees within each department by salary
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  -- Running total of salaries within department
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY teilt die Daten in Gruppen auf — aehnlich wie GROUP BY, aber ohne Zeilen zusammenzufassen. ORDER BY legt die Reihenfolge innerhalb jeder Partition fest. Die optionale Frame-Klausel (ROWS BETWEEN ...) grenzt das Fenster auf einen bestimmten Zeilenbereich ein. Dieses Zusammenspiel aus Partitionierung, Sortierung und Frame-Definition bildet die Grundlage fuer alle weiteren Fensterfunktionen.

ROW_NUMBER, RANK und DENSE_RANK im Vergleich

Diese drei Ranking-Funktionen sehen auf den ersten Blick aehnlich aus, verhalten sich aber bei Gleichstaenden (Ties) unterschiedlich. Die Wahl der falschen Funktion ist eine haeufige Fehlerquelle in analytischen Abfragen und ein beliebtes Thema in SQL-Interviews.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER vergibt immer eine eindeutige, aufsteigende Ganzzahl — auch bei identischen Werten erhaelt eine Zeile willkuerlich die niedrigere Nummer. RANK vergibt bei Gleichstaenden dieselbe Nummer, ueberspringt aber die nachfolgenden Plaetze (1, 1, 3). DENSE_RANK behandelt Gleichstaende ebenfalls, ueberspringt jedoch keine Nummern (1, 1, 2). Fuer Top-N-Abfragen, bei denen Duplikate denselben Rang teilen sollen, ist DENSE_RANK in der Regel die richtige Wahl.

LAG und LEAD fuer Periodenvergleiche

LAG und LEAD greifen auf Daten vorheriger oder nachfolgender Zeilen zu — ohne Self-Joins. Diese Funktionen sind unverzichtbar fuer die Berechnung von Wachstumsraten, die Erkennung von Trends und die Identifikation von Veraenderungen zwischen Zeitraeumen.

-- period_over_period.sql
SELECT
  month,
  revenue,
  -- Previous month revenue
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  -- Month-over-month growth rate
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  -- Next month revenue (forward-looking)
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Das zweite Argument von LAG/LEAD gibt den Offset an (Standard ist 1). Ein optionales drittes Argument definiert einen Standardwert, wenn an der gewuenschten Position keine Zeile existiert — das ist nuetzlich, um NULL-Werte in der ersten bzw. letzten Zeile zu vermeiden. NULLIF in der Wachstumsberechnung verhindert Division-durch-Null-Fehler, die andernfalls das gesamte Ergebnis verfaelschen wuerden.

NTILE und Perzentil-Bucketing

NTILE verteilt Zeilen in eine festgelegte Anzahl moeglichst gleich grosser Gruppen. Datenanalysten verwenden diese Funktion fuer Quartilanalysen, Dezil-Scoring und Kundensegmentierung — Anwendungsfaelle, die in der Praxis staendig vorkommen.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  -- Split customers into 4 quartiles by spend
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  -- Decile scoring for finer granularity
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Quartil 1 enthaelt die umsatzstaerksten Kunden, Quartil 4 die umsatzschwachsten. Dieses Muster laesst sich direkt auf die RFM-Analyse (Recency, Frequency, Monetary) uebertragen, die im Marketing-Analytics zur Kundensegmentierung eingesetzt wird.

Common Table Expressions: Verschachtelte Subqueries ersetzen

CTEs (WITH-Klauseln) zerlegen komplexe Abfragen in benannte, lesbare Schritte. Jede CTE wirkt als temporaere, benannte Ergebnismenge, die nur fuer die Dauer der Abfrage existiert. Neben der verbesserten Lesbarkeit erleichtern CTEs auch das Debugging erheblich — jeder Schritt laesst sich einzeln testen und verifizieren.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  -- Step 1: Aggregate raw orders into monthly totals
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  -- Step 2: Rank categories within each month
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
-- Step 3: Final output — top 3 categories per month
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Der entscheidende Vorteil gegenueber verschachtelten Subqueries liegt in der linearen Struktur: Die Abfrage liest sich von oben nach unten wie eine Schritt-fuer-Schritt-Anleitung. Jede CTE baut auf den Ergebnissen der vorherigen auf, was die Fehlersuche drastisch vereinfacht.

Rekursive CTEs fuer hierarchische Daten

Rekursive CTEs loesen Probleme mit hierarchischen oder graphaehnlichen Datenstrukturen — Organigramme, Kategoriebaeume, Stuecklisten und Pfadsuch-Abfragen. Sie bestehen aus einem Basisfall (Ankerbedingung) und einem rekursiven Teil, der sich selbst referenziert.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  -- Base case: top-level managers (no manager above them)
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: join employees to their managers
  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Fortgeschrittene Analysemuster: Gaps, Islands und laufende Summen

Das Gaps-and-Islands-Muster identifiziert aufeinanderfolgende Sequenzen in Daten — aktive Abonnementzeitraeume, aufeinanderfolgende Login-Tage oder ununterbrochene Produktionslaeufe. Diese Technik kombiniert ROW_NUMBER mit Datumsarithmetik und gehoert zum Standardrepertoire fortgeschrittener SQL-Analysten.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    -- Subtracting row_number from date creates a constant for consecutive days
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Der Trick besteht darin, dass bei aufeinanderfolgenden Tagen die Differenz zwischen Datum und ROW_NUMBER konstant bleibt. Sobald eine Luecke auftritt, aendert sich dieser Wert, wodurch eine neue Gruppe entsteht. Dieses Muster wird in Interviews regelmaessig abgefragt und hat direkten Praxisbezug in der Nutzeranalyse und Retentionsmessung.

Fensterfunktionen mit CASE fuer bedingte Analysen kombinieren

Die Kombination von Fensterfunktionen mit CASE-Ausdruecken ermoeglicht komplexe bedingte Berechnungen innerhalb einer einzigen Abfrage. Typische Anwendungsfaelle sind gleitende Durchschnitte, quartalsbezogene kumulative Summen und die automatische Erkennung von Ausreissern.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  -- 7-day moving average
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  -- Cumulative sum resetting each quarter
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  -- Flag if current row exceeds 2x the category average
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Der gleitende 7-Tage-Durchschnitt verwendet eine explizite Frame-Klausel (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), um genau die letzten sieben Tage einzubeziehen. Die quartalsweise kumulative Summe nutzt DATE_TRUNC in der PARTITION-BY-Klausel, sodass sich die Summe zu Quartalsbeginn automatisch zuruecksetzt. Der Ausreisser-Flag vergleicht jeden einzelnen Wert mit dem Gesamtdurchschnitt der Kategorie.

Performance-Optimierung fuer analytische Abfragen

Bei umfangreichen Datensaetzen ist die Performance von Fensterfunktionen ein entscheidender Faktor. Eine besonders nuetzliche Technik ist die Verwendung benannter Fenster (WINDOW-Klausel), die Codeduplizierung reduziert und dem Abfrageoptimierer zusaetzliche Hinweise gibt.

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

Statt die OVER-Klausel dreimal identisch zu wiederholen, wird das Fenster einmal definiert und mehrfach referenziert. Dies verbessert nicht nur die Lesbarkeit, sondern ermoeglicht es dem Datenbank-Optimizer in vielen Faellen, die Daten nur einmal zu sortieren statt mehrfach. Zusaetzlich sollten Daten immer so frueh wie moeglich in der WHERE-Klausel gefiltert werden, bevor Fensterfunktionen greifen — das reduziert die Menge der zu verarbeitenden Zeilen erheblich.

Weitere bewaeahrte Optimierungsstrategien umfassen die Indizierung von Spalten, die in PARTITION BY und ORDER BY verwendet werden, sowie die Vermeidung unnoetig breiter Fenster-Frames. Bei sehr grossen Tabellen kann es sinnvoll sein, die Daten vorab in einer CTE zu aggregieren oder zu filtern, bevor Fensterfunktionen angewendet werden.

Fazit

• Fensterfunktionen (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operieren ueber Zeilenmengen, ohne diese zusammenzufassen — im Gegensatz zu GROUP-BY-Aggregaten
• CTEs zerlegen komplexe Abfragen in benannte, einzeln testbare Schritte und ersetzen tief verschachtelte Subqueries
• Rekursive CTEs verarbeiten hierarchische Daten wie Organigramme und Kategoriebaeume, erfordern aber Tiefenlimits und indizierte Join-Spalten
• Das Gaps-and-Islands-Muster (ROW_NUMBER + Datumsarithmetik) identifiziert aufeinanderfolgende Sequenzen in Zeitreihendaten
• Benannte Fenster (WINDOW-Klausel) reduzieren Codeduplizierung und koennen die Optimierung des Abfrageplans verbessern
• Daten sollten in WHERE-Klauseln gefiltert werden, bevor Fensterfunktionen ausgewertet werden, um die Performance bei grossen Tabellen zu erhalten
• Diese Muster tauchen regelmaessig in Interviews fuer Datenanalysten auf und sind direkt anwendbar auf taegliche Reporting-, Segmentierungs- und Trendanalysen