7 SQL-Techniken für effektive Datenanalyse im Alltag

Einführung in fortgeschrittene SQL-Techniken

Die Anwendung grundlegender SQL-Befehle wie SELECT, WHERE und GROUP BY reicht für einfache Aggregationen aus. Viele analytische Aufgaben erfordern jedoch komplexere Muster, die über einfache Abfragen hinausgehen. Dazu zählen beispielsweise die Erkennung aufeinanderfolgender Aktivitätsphasen, die Segmentierung von Kunden nach Ausgabenkategorien, die Glättung von verrauschten Zeitreihendaten oder die Nachverfolgung von Plan-Upgrades über mehrere Zeilen.

Dieser Artikel stellt sieben praktische SQL-Muster vor, die über die Grundlagen hinausgehen und sich auf Techniken konzentrieren, die reale analytische Probleme lösen.

Vorbereitung des Datensatzes

Für die Demonstration verwenden wir eine Beispiel-Tabelle mit Kunden-Transaktionen eines fiktiven Unternehmens im Bereich Software-as-a-Service (SaaS):

CREATE TABLE transactions (
 transaction_id SERIAL PRIMARY KEY,
 customer_id INT,
 plan_type VARCHAR(20), -- 'starter', 'pro', 'enterprise'
 amount NUMERIC(10,2),
 status VARCHAR(20), -- 'completed', 'refunded', 'failed'
 created_at TIMESTAMP
);

Der vollständige Datensatz umfasst 36 Transaktionen über 7 Kunden und erstreckt sich von September 2023 bis Juni 2024. Führen Sie die Datei seed.sql aus, bevor Sie mit den Abfragen fortfahren.

1. Zeit zwischen Ereignissen mit LAG() messen

Die Funktionen LAG() und LEAD() ermöglichen den Zugriff auf Werte der vorherigen oder nächsten Zeile, ohne eine Selbstverknüpfung durchführen zu müssen. Sie sind besonders nützlich zur Berechnung von Zeitabständen zwischen Ereignissen wie Erneuerungszyklen, Abwanderungssignalen und Verzögerungen bei der Wiederengagement.

Aufgabe: Berechnen Sie, wie viele Tage zwischen den aufeinanderfolgenden abgeschlossenen Transaktionen jedes Kunden vergangen sind.

SELECT
 customer_id,
 created_at,
 LAG(created_at) OVER (
 PARTITION BY customer_id
 ORDER BY created_at
 ) AS previous_transaction_at,
 ROUND(
 EXTRACT(EPOCH FROM (
 created_at - LAG(created_at) OVER (
 PARTITION BY customer_id
 ORDER BY created_at
 )
 )) / 86400
 ) AS days_since_last
FROM transactions
WHERE status = 'completed'
ORDER BY customer_id, created_at;

Die erste Zeile pro Kunde zeigt immer NULL für beide Spalten an, da es kein vorheriges Ereignis gibt, auf das verwiesen werden kann. EXTRACT(EPOCH …) wandelt das Zeitstempelinternval in Sekunden um; die Division durch 86400 ergibt die Anzahl der Tage.

LEAD() funktioniert auf die gleiche Weise, schaut jedoch nach vorne, was nützlich ist, um die Zeit bis zur nächsten Erneuerung zu berechnen oder die letzte Transaktion vor einer Abwanderung zu kennzeichnen.

2. Vergleich einer Zeile mit anderen Zeilen in derselben Tabelle durch Selbstverknüpfung

Eine Selbstverknüpfung stellt eine Beziehung zwischen Zeilen innerhalb derselben Tabelle her. Dies ist das geeignete Werkzeug, wenn Sie zwei Ereignisse für dasselbe Objekt über die Zeit vergleichen müssen – beispielsweise Upgrades, Downgrades, Reaktivierungen oder andere Vorher/Nachher-Muster.

Aufgabe: Finden Sie Kunden, die zu irgendeinem Zeitpunkt von ’starter‘ auf ‚pro‘ (oder von ‚pro‘ auf ‚enterprise‘) gewechselt sind.

SELECT DISTINCT t1.customer_id
FROM transactions t1
JOIN transactions t2
 ON t1.customer_id = t2.customer_id
 AND t1.plan_type = 'starter'
 AND t2.plan_type = 'pro'
 AND t2.created_at > t1.created_at
WHERE t1.status = 'completed'
 AND t2.status = 'completed'
ORDER BY t1.customer_id;

Die Tabelle wird zweimal aliasiert (t1, t2), sodass jeder Alias einen anderen Zeitpunkt für denselben Kunden darstellen kann. Die Bedingung t2.created_at > t1.created_at stellt die zeitliche Reihenfolge sicher – ohne diese Bedingung würden Kunden übereinstimmen, die einfach beide Planarten in beliebiger Reihenfolge hatten, einschließlich der falschen. DISTINCT reduziert Fälle, in denen ein Kunde mehrere Starter-Transaktionen vor dem Upgrade hatte, was sonst zu doppelten Zeilen führen würde.

3. Die oberste Zeile pro Gruppe mit ROW_NUMBER() auswählen

Wenn Sie die obersten N-Zeilen pro Kategorie benötigen – die höchste Transaktion pro Kunde, das aktuellste Ereignis pro Konto, den ersten Kauf pro Kohorte – ist ROW_NUMBER() innerhalb einer gemeinsamen Tabellenausdruck (CTE) der Standardansatz.

Aufgabe: Holen Sie sich die höchste abgeschlossene Transaktion jedes Kunden.

WITH ranked AS (
 SELECT
 customer_id,
 transaction_id,
 amount,
 plan_type,
 ROW_NUMBER() OVER (
 PARTITION BY customer_id
 ORDER BY amount DESC, created_at DESC
 ) AS rn
 FROM transactions
 WHERE status = 'completed'
)
SELECT customer_id, transaction_id, amount, plan_type
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

ROW_NUMBER() weist der Zeile, die innerhalb jeder Partition zuerst sortiert, die 1 zu. Die äußere Abfrage filtert dann nur diese Zeilen. Die sekundäre Sortierung nach created_at DESC dient als Tiebreaker; wenn zwei Transaktionen den gleichen Betrag haben, gewinnt die aktuellere.

4. Kunden nach Ausgaben mit NTILE(n) segmentieren

NTILE(n) teilt geordnete Zeilen in n ungefähr gleich große Gruppen und weist jeder Zeile eine Gruppennummer zu. Dies ist das geeignete Werkzeug für die Kundenklassifizierung, Ausgabenquartile oder den Aufbau von Kohorten für A/B-Analysen, ohne Schwellenwerte hart zu kodieren.

Aufgabe: Bewerten Sie Kunden in Ausgabenquartilen basierend auf ihrem gesamten Wert abgeschlossener Transaktionen.

WITH customer_spend AS (
 SELECT
 customer_id,
 SUM(amount) AS total_spend,
 COUNT(*) AS total_transactions
 FROM transactions
 WHERE status = 'completed'
 GROUP BY customer_id
)
SELECT
 customer_id,
 total_spend,
 total_transactions,
 NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY total_spend DESC;

Quartil 4 sind die höchsten Ausgeber; Quartil 1 sind die niedrigsten. NTILE() kodiert keine Ausgabenschwellen, sodass die Gruppen automatisch neu kalibriert werden, wenn neue Kunden hinzukommen. Dies macht es robuster als statische Schwellenwerte wie CASE WHEN total_spend > 500.

5. Rauschen in Daten mit einem gleitenden Fenster glätten

Ein gleitender (oder beweglicher) Durchschnitt glättet die monatlichen Schwankungen und macht Trends in Zeitreihendaten viel leichter lesbar. Fensterfunktionen mit einem expliziten ROWS BETWEEN-Rahmen geben Ihnen präzise Kontrolle darüber, wie viele Perioden einbezogen werden sollen.

Aufgabe: Berechnen Sie einen 3-Monats-Gleitdurchschnitt des monatlichen Umsatzes, um Rauschen zu glätten.

WITH monthly AS (
 SELECT
 DATE_TRUNC('month', created_at)::DATE AS month,
 SUM(amount) AS monthly_revenue
 FROM transactions
 WHERE status = 'completed'
 GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
 month,
 monthly_revenue,
 ROUND(AVG(monthly_revenue) OVER (
 ORDER BY month
 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 ), 2) AS revenue_3mo_avg
FROM monthly
ORDER BY month;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW weist die Fensterfunktion an, die aktuelle Zeile und die zwei vorhergehenden Zeilen zu betrachten. Die ersten beiden Zeilen verwenden weniger Eingaben, da es keine vorherige Historie gibt, sodass sie als 1-Monats- und 2-Monats-Durchschnitt fungieren.

6. Bedingte Aggregation mit FILTER durchführen

FILTER ermöglicht es Ihnen, eine WHERE-Bedingung auf eine spezifische Aggregation anzuwenden, ohne die Abfrage in mehrere Unterabfragen aufteilen zu müssen. Das Ergebnis sind mehrere bedingte Aggregationen in einem einzigen Durchlauf über die Daten.

Aufgabe: Erhalten Sie den Gesamtumsatz, Rückerstattungen und die Anzahl fehlgeschlagener Transaktionen, aufgeschlüsselt nach Monat – alles in einer Zeile pro Monat.

SELECT
 DATE_TRUNC('month', created_at) AS month,
 SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed,
 SUM(amount) FILTER (WHERE status = 'refunded') AS revenue_refunded,
 COUNT(*) FILTER (WHERE status = 'failed') AS failed_count
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

Die Alternative zu FILTER wären drei separate Unterabfragen, die zusammengeführt werden – mehr Code, schwerer zu lesen und oft langsamer. Beachten Sie, dass SUM mit FILTER NULL (nicht null) zurückgibt, wenn in einem bestimmten Monat keine Zeilen übereinstimmen, was genau ist: Es gab tatsächlich keine Rückerstattungen in diesen Monaten. Wickeln Sie es in COALESCE(…, 0) ein, wenn Sie lieber Nullen hätten.

7. Erkennung aufeinanderfolgender Aktivitätsphasen mit Fensterfunktionen

Das Finden ununterbrochener Sequenzen – aktive Monate ohne Unterbrechung, aufeinanderfolgende Tage mit Transaktionen, Abonnementsphasen – ist eines der kniffligeren SQL-Probleme. Die klassische Lösung verwendet eine Fensterfunktion, um Zeilen in Phasen zu gruppieren, ohne eine rekursive CTE zu benötigen.

Aufgabe: Finden Sie die aufeinanderfolgenden aktiven Monate jedes Kunden (Monate mit mindestens einer abgeschlossenen Transaktion).

WITH monthly_activity AS (
 SELECT
 customer_id,
 DATE_TRUNC('month', created_at)::DATE AS active_month
 FROM transactions
 WHERE status = 'completed'
 GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
with_prev AS (
 SELECT
 customer_id,
 active_month,
 LAG(active_month) OVER (
 PARTITION BY customer_id
 ORDER BY active_month
 ) AS prev_month
 FROM monthly_activity
),
streak_groups AS (
 SELECT
 customer_id,
 active_month,
 SUM(CASE WHEN active_month = prev_month + INTERVAL '1 month' THEN 0 ELSE 1 END)
 OVER (PARTITION BY customer_id ORDER BY active_month) AS streak_id
 FROM with_prev
),
streaks AS (
 SELECT
 customer_id,
 streak_id,
 MIN(active_month) AS streak_start,
 MAX(active_month) AS streak_end,
 COUNT(*) AS streak_length_months
 FROM streak_groups
 GROUP BY customer_id, streak_id
)
SELECT customer_id, streak_start, streak_end, streak_length_months
FROM streaks
ORDER BY customer_id, streak_start;

Die Ausgabe zeigt die Kunden-ID, den Start- und Endzeitpunkt der Phasen sowie die Länge der Phasen in Monaten. Diese Technik ermöglicht es, die Aktivitätsmuster der Kunden zu analysieren und gezielte Marketingstrategien zu entwickeln.

Schnellübersicht der SQL-Muster

• LAG() / LEAD(): Zeit zwischen Ereignissen, Vorher/Nachher-Vergleiche pro Entität
• Selbstverknüpfung: Übergänge zwischen Zuständen erkennen (Upgrades, Reaktivierungen)
• ROW_NUMBER(): Top-N-Zeilen pro Gruppe, Duplikate entfernen
• NTILE(n): Kundensegmentierung in Ausgaben-/Aktivitätskategorien
• Gleitendes Fenster (ROWS BETWEEN): Rauschen in Zeitreihen glätten, gleitende Durchschnitte
• FILTER: Mehrere bedingte Aggregationen in einem Abfrage-Durchlauf
• Erkennung aufeinanderfolgender Phasen: Abonnementsphasen, Retentionsanalyse, Sitzungsunterbrechungen

Wenn Sie mit diesen Mustern vertraut sind, können viele mehrstufige Datenumwandlungen, die häufig in Python durchgeführt werden, klarer und effizienter in einer einzigen SQL-Abfrage ausgedrückt werden. Weitere Informationen finden Sie in unserem Artikel über Wie die Struktur von Datensätzen den Programmierstil beeinflusst oder lernen Sie, Effiziente Python-Skripte zur Automatisierung der explorativen Datenanalyse.

Bala Priya C ist Entwicklerin und technische Autorin aus Indien. Sie arbeitet an der Schnittstelle von Mathematik, Programmierung, Datenwissenschaft und Inhaltserstellung. Ihre Interessensgebiete und Fachkenntnisse umfassen DevOps, Datenwissenschaft und natürliche Sprachverarbeitung. Sie genießt das Lesen, Schreiben, Programmieren und Kaffee! Derzeit arbeitet sie daran, ihr Wissen mit der Entwicklergemeinschaft zu teilen, indem sie Tutorials, Anleitungen, Meinungsartikel und mehr verfasst.

„`

Quellen: kdnuggets

Bildquelle: KI generiert

KI Snack