# Einführung
Konzentrieren Sie sich nur auf SELECT, WHEREUnd GROUP BY reicht für die grundlegende Aggregation aus, aber viele echte Analyseaufgaben erfordern Muster, die über einfache Abfragen hinausgehen. Beispiele hierfür sind die Erkennung aufeinanderfolgender Aktivitätsstränge, die Segmentierung von Kunden nach Ausgabenstufen, die Glättung verrauschter Zeitreihendaten oder die zeilenübergreifende Verfolgung von Plan-Improve-Pfaden.
Dieser Artikel geht über die Grundlagen hinaus durch sieben praktische SQL-Muster und konzentriert sich auf Techniken, die echte analytische Probleme lösen.
# Einrichten des Datensatzes
Wir verwenden eine Beispieltabelle für Kundentransaktionen eines fiktiven Software program-as-a-Service-Unternehmens (SaaS):
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
customer_id INT,
plan_type VARCHAR(20), -- 'starter', 'professional', 'enterprise'
quantity NUMERIC(10,2),
standing VARCHAR(20), -- 'accomplished', 'refunded', 'failed'
created_at TIMESTAMP
);Der vollständige Datensatz von 36 Transaktionen bei 7 Kunden im Zeitraum September 2023 bis Juni 2024 ist verfügbar in seed.sql. Führen Sie es aus, bevor Sie mit den Abfragen fortfahren.
# 1. Messung der Zeit zwischen Ereignissen mit LAG()
LAG() Und LEAD() ermöglicht Ihnen den Zugriff auf den Wert einer vorherigen oder nächsten Zeile ohne Selbstverknüpfung. Sie sind besonders nützlich für die Berechnung von Lücken zwischen Ereignissen wie Verlängerungsrhythmen, Abwanderungssignalen und Verzögerungen bei der Wiederaufnahme.
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 standing="accomplished"
ORDER BY customer_id, created_at;Ausgabe (gekürzt):
customer_id | created_at | previous_transaction_at | days_since_last
-------------+---------------------+-------------------------+-----------------
3317 | 2024-01-03 11:02:00 | |
3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00 | 72
3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00 | 68
4482 | 2023-09-10 09:00:00 | |
4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00 | 30
4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00 | 31
4482 | 2024-01-03 09:14:00 | 2023-11-10 09:14:00 | 54
4482 | 2024-03-03 08:20:00 | 2024-01-03 09:14:00 | 60
4482 | 2024-04-03 10:00:00 | 2024-03-03 08:20:00 | 31
4482 | 2024-05-01 11:00:00 | 2024-04-03 10:00:00 | 28
...
7891 | 2024-02-01 09:00:00 | |
7891 | 2024-04-01 09:00:00 | 2024-02-01 09:00:00 | 60
7891 | 2024-05-15 09:00:00 | 2024-04-01 09:00:00 | 44
8810 | 2024-01-05 12:00:00 | |
8810 | 2024-02-05 12:00:00 | 2024-01-05 12:00:00 | 31
8810 | 2024-04-05 12:00:00 | 2024-02-05 12:00:00 | 60
(29 rows)Die erste Reihe professional Kunde hat immer NULL Für beide Spalten gibt es kein vorheriges Ereignis, auf das verwiesen werden kann. EXTRACT(EPOCH ...) wandelt das Zeitstempelintervall in Sekunden um; dividieren durch 86400 gibt Tage.
LEAD() funktioniert auf die gleiche Weise, blickt jedoch vorwärts statt rückwärts, was es nützlich macht, die Zeit bis zur nächsten Verlängerung zu berechnen oder die letzte Transaktion vor der Abwanderung zu kennzeichnen.
# 2. Vergleichen einer Zeile mit anderen Zeilen in derselben Tabelle mit einem Self-Be part of
A selbst beitreten verknüpft Zeilen innerhalb derselben Tabelle miteinander. Es ist das richtige Device, wenn Sie zwei Ereignisse für dieselbe Entität im Zeitverlauf vergleichen müssen – Upgrades, Downgrades, Reaktivierungen oder ein beliebiges Vorher/Nachher-Muster.
Aufgabe: Finden Sie Kunden, die zu einem beliebigen Zeitpunkt vom Starter zum Professional (oder vom Professional zum 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="professional"
AND t2.created_at > t1.created_at
WHERE t1.standing="accomplished"
AND t2.standing="accomplished"
ORDER BY t1.customer_id;Ausgabe:
customer_id
-------------
4482
6204
7891
(3 rows)Die Tabelle wird zweimal mit einem Alias versehen (t1, t2), sodass jeder Alias einen anderen Zeitpunkt für denselben Kunden darstellen kann. Der Zustand t2.created_at > t1.created_at erzwingt die zeitliche Reihenfolge – ohne sie würden Sie Kunden zuordnen, die einfach beide Plantypen in beliebiger Reihenfolge hatten, auch den falschen. DISTINCT Reduziert Fälle, in denen ein Kunde vor dem Improve mehrere Starttransaktionen hatte, die andernfalls zu doppelten Zeilen führen würden.
Dieselbe Struktur eignet sich zum Erkennen von Herabstufungen, zum Finden von Kunden, die abgewandert sind und zurückgekommen sind, oder zum Vergleichen zweier Zustände, die nach Zeit sortiert werden müssen.
# 3. Auswählen der obersten Reihe professional Gruppe mit ROW_NUMBER()
Wenn Sie die Prime-N-Zeilen professional Kategorie benötigen – höchste Transaktion professional Kunde, jüngstes Ereignis professional Konto, erster Kauf professional Kohorte – ROW_NUMBER() innerhalb eines gemeinsamen Tabellenausdrucks (CTE) ist der Standardansatz.
Aufgabe: Erhalten Sie die höchste abgeschlossene Transaktion jedes Kunden.
WITH ranked AS (
SELECT
customer_id,
transaction_id,
quantity,
plan_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY quantity DESC, created_at DESC
) AS rn
FROM transactions
WHERE standing="accomplished"
)
SELECT customer_id, transaction_id, quantity, plan_type
FROM ranked
WHERE rn = 1
ORDER BY customer_id;Ausgabe:
customer_id | transaction_id | quantity | plan_type
-------------+----------------+--------+------------
3317 | 12 | 19.00 | starter
4482 | 8 | 299.00 | enterprise
5901 | 19 | 299.00 | enterprise
6103 | 25 | 299.00 | enterprise
6204 | 28 | 79.00 | professional
7891 | 32 | 79.00 | professional
8810 | 36 | 79.00 | professional
(7 rows)ROW_NUMBER() weist der Zeile, die innerhalb jeder Partition zuerst sortiert wird, 1 zu. Die äußere Abfrage filtert dann nur auf diese Zeilen. Die sekundäre Sortierung created_at DESC fungiert als Tiebreaker; Wenn zwei Transaktionen den gleichen Betrag haben, gewinnt die neuere.
Wenn Sie möchten, dass die Kabelbinder enthalten und nicht kaputt sind, tauschen Sie sie aus ROW_NUMBER() für RANK(). RANK() weist gebundenen Reihen die gleiche Nummer zu und überspringt die nächste Reihe (1, 1, 3), whereas DENSE_RANK() macht dasselbe, ohne (1, 1, 2) zu überspringen.
# 4. Segmentierung der Kunden nach Ausgaben mit NTILE(n)
NTILE(n) unterteilt geordnete Zeilen in n ungefähr gleiche Buckets und weist jeder Zeile eine Bucket-Nummer zu. Es ist das richtige Device für die Kundenstaffelung, Ausgabenquartile oder den Aufbau von Kohorten für A/B-Analysen ohne feste Schwellenwerte.
Aufgabe: Ordnen Sie Kunden anhand ihres gesamten abgeschlossenen Transaktionswerts in Ausgabenquartile ein.
WITH customer_spend AS (
SELECT
customer_id,
SUM(quantity) AS total_spend,
COUNT(*) AS total_transactions
FROM transactions
WHERE standing="accomplished"
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;Ausgabe:
customer_id | total_spend | total_transactions | spend_quartile
-------------+-------------+--------------------+----------------
5901 | 1495.00 | 5 | 4
6103 | 835.00 | 5 | 3
4482 | 653.00 | 7 | 3
8810 | 237.00 | 3 | 2
6204 | 177.00 | 3 | 2
7891 | 177.00 | 3 | 1
3317 | 57.00 | 3 | 1
(7 rows)Quartil 4 ist Ihr höchstes Ausgabevolumen; Quartil 1 ist Ihr niedrigstes. NTILE() Kodiert die Ausgabenschwellenwerte nicht fest, sodass die Buckets automatisch neu kalibriert werden, wenn neue Kunden hinzugefügt werden. Dies macht es robuster als statische Cutoffs CASE WHEN total_spend > 500.
# 5. Glätten verrauschter Daten mit einem rollenden Fenster
Ein gleitender (oder gleitender) Durchschnitt glättet die monatliche Volatilität und macht Traits in Zeitreihendaten viel einfacher zu lesen. Fensterfunktionen mit einem expliziten ROWS BETWEEN Mit dem Body können Sie genau steuern, wie viele Perioden einbezogen werden sollen.
Aufgabe: Berechnen Sie einen gleitenden 3-Monats-Durchschnitt des monatlichen Umsatzes, um Lärm auszugleichen.
WITH month-to-month AS (
SELECT
DATE_TRUNC('month', created_at)::DATE AS month,
SUM(quantity) AS monthly_revenue
FROM transactions
WHERE standing="accomplished"
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 month-to-month
ORDER BY month;Ausgabe:
month | monthly_revenue | revenue_3mo_avg
-------------+-----------------+-----------------
2023-09-01 | 19.00 | 19.00
2023-10-01 | 19.00 | 19.00
2023-11-01 | 79.00 | 39.00
2024-01-01 | 275.00 | 124.33
2024-02-01 | 476.00 | 276.67
2024-03-01 | 555.00 | 435.33
2024-04-01 | 835.00 | 622.00
2024-05-01 | 775.00 | 721.67
2024-06-01 | 598.00 | 736.00
(9 rows)ROWS BETWEEN 2 PRECEDING AND CURRENT ROW weist die Fensterfunktion an, sich die aktuelle Zeile und die beiden Zeilen davor anzusehen. In den ersten beiden Zeilen werden weniger Eingaben benötigt, da kein Vorverlauf vorhanden ist. Daher dienen sie als 1-Monats- bzw. 2-Monats-Durchschnitt.
Tauschen ROWS für RANGE wenn Sie alle Zeilen mit demselben einschließen möchten ORDER BY Wert (nützlich, wenn mehrere Zeilen einen Zeitstempel teilen). Für eine längere Glättung wechseln 2 PRECEDING Zu 5 PRECEDING für ein 6-Monats-Fenster.
# 6. Bedingtes Aggregieren mit FILTER
FILTER Hier können Sie a anwenden WHERE Bedingung einem bestimmten Aggregat zuordnen, ohne die Abfrage in mehrere Unterabfragen aufzuteilen. Das Ergebnis sind mehrere bedingte Aggregationen in einem einzigen Durchgang über die Daten.
Aufgabe: Erhalten Sie den Gesamtumsatz, die Rückerstattungen und die Anzahl fehlgeschlagener Transaktionen, aufgeschlüsselt nach Monat – alles in einer Zeile professional Monat.
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(quantity) FILTER (WHERE standing="accomplished") AS revenue_completed,
SUM(quantity) FILTER (WHERE standing="refunded") AS revenue_refunded,
COUNT(*) FILTER (WHERE standing="failed") AS failed_count
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;Ausgabe:
month | revenue_completed | revenue_refunded | failed_count
------------------------+-------------------+------------------+--------------
2023-09-01 00:00:00+00 | 19.00 | | 0
2023-10-01 00:00:00+00 | 19.00 | | 0
2023-11-01 00:00:00+00 | 79.00 | | 0
2024-01-01 00:00:00+00 | 275.00 | | 0
2024-02-01 00:00:00+00 | 476.00 | 79.00 | 1
2024-03-01 00:00:00+00 | 555.00 | 79.00 | 0
2024-04-01 00:00:00+00 | 835.00 | 299.00 | 0
2024-05-01 00:00:00+00 | 775.00 | | 1
2024-06-01 00:00:00+00 | 598.00 | | 2
(9 rows)Die Different zu FILTER besteht aus drei separaten Unterabfragen, die miteinander verbunden sind – mehr Code, schwerer zu lesen und oft langsamer. Beachten Sie, dass SUM mit FILTER kehrt zurück NULL (nicht Null), wenn in einem bestimmten Monat keine Zeilen übereinstimmen, was genau ist: In diesen Monaten gab es tatsächlich keine Rückerstattungen. Einpacken COALESCE(..., 0) wenn Sie Nullen bevorzugen.
FILTER ist Commonplace-SQL und funktioniert in PostgreSQL und BigQuery. Verwenden Sie in Snowflake und einigen anderen SUM(CASE WHEN standing="accomplished" THEN quantity END) stattdessen.
# 7. Erkennen aufeinanderfolgender Aktivitätsstreifen mit Fensterfunktionen
Das Finden ununterbrochener Sequenzen – aktive Monate ohne Lücke, aufeinanderfolgende Tage mit Transaktionen, Abonnementsstreaks – ist eines der schwierigeren SQL-Probleme. Die klassische Lösung verwendet eine Fensterfunktion, um Zeilen ohne rekursiven CTE in Streifen zu gruppieren.
Die Technik: Weisen Sie jedem aktiven Monat eine fortlaufende Zeilennummer innerhalb seiner Kundenpartition zu. Wenn die Monate wirklich aufeinanderfolgend sind, ergibt das Subtrahieren dieser Zeilennummer vom Monatsdatum für jeden Monat im Streifen denselben konstanten Wert. Eine Lücke durchbricht die Konstante.
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 standing="accomplished"
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;Ausgabe:
customer_id | streak_start | streak_end | streak_length_months
-------------+--------------+------------+----------------------
3317 | 2024-01-01 | 2024-01-01 | 1
3317 | 2024-03-01 | 2024-03-01 | 1
3317 | 2024-05-01 | 2024-05-01 | 1
4482 | 2023-09-01 | 2023-11-01 | 3
4482 | 2024-01-01 | 2024-01-01 | 1
4482 | 2024-03-01 | 2024-05-01 | 3
5901 | 2024-02-01 | 2024-06-01 | 5
6103 | 2024-01-01 | 2024-04-01 | 4
6103 | 2024-06-01 | 2024-06-01 | 1
6204 | 2024-01-01 | 2024-01-01 | 1
6204 | 2024-03-01 | 2024-03-01 | 1
6204 | 2024-05-01 | 2024-05-01 | 1
7891 | 2024-02-01 | 2024-02-01 | 1
7891 | 2024-04-01 | 2024-05-01 | 2
8810 | 2024-01-01 | 2024-02-01 | 2
8810 | 2024-04-01 | 2024-04-01 | 1
(16 rows)# Kurzreferenz
Diese Muster funktionieren in Commonplace-SQL, ohne auf datenbankspezifische Funktionen angewiesen zu sein, und sie erscheinen häufig in analytischen Arbeitsabläufen wie Aufbewahrungsanalysen, Improve-Trichterverfolgung und Umsatzberichten.
| Tipp | Wann man es verwendet |
|---|---|
LAG() / LEAD()
|
Zeit zwischen Ereignissen, Vorher/Nachher-Vergleiche professional Entität |
|
Selbst beitreten |
Übergänge zwischen Zuständen erkennen (Upgrades, Reaktivierungen) |
ROW_NUMBER()
|
Prime-N Zeilen professional Gruppe, Deduplizierung |
NTILE(n)
|
Kundensegmentierung in Ausgaben-/Aktivitätsstufen |
|
Rollfenster ( |
Glatte, verrauschte Zeitreihen, gleitende Durchschnitte |
FILTER
|
Mehrere bedingte Aggregationen in einem Abfragedurchlauf |
|
Aufeinanderfolgende Streifenerkennung |
Abonnementstreaks, Retention-Analyse, Sitzungslücken |
Sobald Sie damit vertraut sind, können viele mehrstufige Datentransformationen, die häufig in Python durchgeführt werden, sauberer und effizienter in einer einzigen SQL-Abfrage ausgedrückt werden.
Bala Priya C ist ein Entwickler und technischer Redakteur aus Indien. Sie arbeitet gerne an der Schnittstelle von Mathematik, Programmierung, Datenwissenschaft und Inhaltserstellung. Zu ihren Interessen- und Fachgebieten gehören DevOps, Datenwissenschaft und Verarbeitung natürlicher Sprache. Sie liebt es zu lesen, zu schreiben, zu programmieren und Kaffee zu trinken! Derzeit arbeitet sie daran, zu lernen und ihr Wissen mit der Entwickler-Group zu teilen, indem sie Tutorials, Anleitungen, Meinungsbeiträge und mehr verfasst. Bala erstellt außerdem ansprechende Ressourcenübersichten und Programmier-Tutorials.