Die 7 wichtigsten erweiterten SQL-Abfragen für die Datenanalyse

Entdecken Sie die wichtigsten fortgeschrittenen SQL-Abfragen für die Datenanalyse.

Structured Query Language, kurz SQL, ist ein unverzichtbares Werkzeug für Datenanalysten. Die meisten Menschen beherrschen die Sprache relativ schnell und können schon nach wenigen Lektionen mit einer tiefgreifenden Datenanalyse beginnen. Aus diesem Grund neigen viele Datenanalysten dazu, bei der Verwendung von SQL auf der Anfänger-/Mittelstufe zu bleiben. Mit diesen Kenntnissen kann man zwar "die Arbeit erledigen", aber es ist vielleicht nicht die effizienteste Art, eine Abfrage zu schreiben.

Die Beherrschung fortgeschrittener SQL-Techniken kann Ihre Datenanalysefähigkeiten erheblich verbessern und es Ihnen ermöglichen, tiefer in Ihre Datensätze einzudringen und wertvolle Erkenntnisse zu gewinnen. In diesem Artikel werden wir sieben fortgeschrittene SQL-Abfragen untersuchen, die für die Datenanalyse nützlich sein können. Für jedes Beispiel stellen wir die Abfrage und ihre Ergebnisse vor, erläutern die verwendete Syntax und geben eine kurze Erklärung, wie die Datenbank das Ergebnis errechnet.

Wenn Sie Ihre SQL-Kenntnisse verbessern möchten, sollten Sie sich unbedingt unseren SQL-Reporting-Kurs ansehen. Er ist eine gute Möglichkeit, fortgeschrittene SQL-Funktionen zu erlernen und zu üben.

7 Fortgeschrittenes SQL Abfragen, die Datenanalysten kennen sollten

1. Daten nach Zeitraum gruppieren

In diesem ersten Beispiel untersuchen wir die Daten in der Tabelle sales. Dieser Datensatz enthält eine ID für jedes Produkt, das Datum der Transaktion und den Gesamtbetrag der Transaktion.

Ein sehr häufiges Szenario für die Datenanalyse besteht darin, Rohdaten wie diese zu nehmen und sie nach bestimmten Datumsperioden, wie Monat oder Jahr, zu aggregieren. Lassen Sie uns genau das tun, indem wir die folgende Abfrage ausführen:

SELECT
  EXTRACT(YEAR FROM sale_date) AS year, 
  EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY year, month
ORDER BY year, month;

Diese Abfrage gibt diese Ergebnisse zurück:

In dieser Abfrage verwenden wir die Funktion EXTRACT(), um das Jahr und den Monat aus dem Feld sale_date zu ziehen. Anschließend gruppieren wir die Daten nach den neuen Feldern Jahr und Monat und berechnen den Gesamtumsatz für jede Gruppe mit der Funktion SUM().

Beachten Sie, dass wir sowohl nach Jahr als auch nach Monat gruppieren müssen, um genaue Ergebnisse zu erhalten. Wenn wir nur den Monat einbeziehen würden, würden die Ergebnisse die Werte für bestimmte Monate über die Jahre hinweg kombinieren (so würden alle Januare aus allen Jahren in einer Zeile kombiniert, alle Februare würden zusammen kombiniert usw.). Wenn wir die Ergebnisse sowohl nach Jahr als auch nach Monat gruppieren, werden die Monate aus verschiedenen Jahren in separate Zeilen gestellt.

Anschließend ordnen wir die Zeilen nach Jahr und Monat, um geordnete Ergebnisse zu erhalten.

Verschiedene Datenbank-Engines haben oft unterschiedliche und manchmal bessere Funktionen, um das gleiche Ergebnis zu erzielen, wie DATE_TRUNC() in PostgreSQL oder TRUNC() in Oracle.

2. Mehrere Gruppierungsebenen mit ROLLUP erstellen

Es kann vorkommen, dass Sie Daten in einer einzigen Abfrage nach mehreren Ebenen gruppieren möchten. Nehmen wir an, Sie möchten den Gesamtumsatz (über alle Jahre hinweg) sowie den Gesamtumsatz in jedem Jahr zum vorherigen Beispiel hinzufügen. Mit dem Schlüsselwort ROLLUP können Sie zusätzliche Zeilen für die Gesamtsumme und die Summen für jedes Jahr hinzufügen.

Mit demselben Datensatz führen wir diese Abfrage aus:

SELECT
 EXTRACT(YEAR FROM sale_date) AS year,
 EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(year,month)
ORDER BY year, month;

Ergebnis:

In unserem Beispiel fügt ROLLUP drei zusätzliche Zeilen hinzu: eine für die Gesamtsumme aller Verkäufe und zwei für die Gesamtverkäufe der einzelnen Jahre. In unserer Ergebnismenge ist die dritte Zeile die Jahressumme für 2021: Der Wert in der Spalte year ist 2021 und der Wert in der Spalte month ist NULL. Die sechste Zeile ist die Gesamtsumme für 2022: Der Wert in der Spalte Jahr ist 2022 und der Wert in der Spalte month ist NULL. Die letzte Zeile ist die Gesamtsumme für beide Jahre: Sie hat NULL in den Spalten Jahr und month. Diese Zeilen wurden von ROLLUP addiert. Die Werte NULL in den Spalten Jahr und month zeigen die aggregierten Zeilen an.

3. Ranglistendaten verwenden Fensterfunktionen

Das Einordnen von Daten ist eine häufige Anforderung in der fortgeschrittenen Datenanalyse. Einige Geschäftsfälle wären z. B. das Ranking Ihrer Produkte nach den höchsten Umsätzen, um zu verstehen, welche Produkte den größten Umsatz bringen, oder das Ranking der Filialen nach den niedrigsten Umsätzen, um zu verstehen, welche Filialen die schlechtesten Ergebnisse liefern.

Sie können Fensterfunktionen wie RANK() oder DENSE_RANK() verwenden, um Zeilen auf der Grundlage des Werts einer bestimmten Spalte eine Rangfolge zuzuweisen. Werfen wir zunächst einen Blick auf die Funktion RANK():

SELECT
  product_id,
  SUM(amount) AS total_sales,
  RANK() OVER(ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY id;

Ergebnis:

Diese Abfrage berechnet zunächst die Gesamtumsätze für jede product_id und ordnet sie dann in absteigender Reihenfolge an. Die Funktion RANK() ist eine Fensterfunktion, die zur Berechnung der Rangfolge verwendet wird. Die OVER() Klausel ist die für Fensterfunktionen verwendete Syntax. Die Funktion RANK() weist jeder Zeile innerhalb einer Ergebnismenge einen eindeutigen Rang zu, der auf einer bestimmten Reihenfolge basiert. Die ORDER BY -Klausel in der OVER() -Klausel gibt die Reihenfolge an, in der der Rang zugewiesen wird, basierend auf einer oder mehreren Spalten. In unserem Beispiel ordnen wir die Zeilen nach dem Gesamtverkaufswert.

Die gleiche Syntax kann für die Funktion DENSE_RANK() verwendet werden. Der Unterschied zwischen den beiden Funktionen besteht in dem Szenario, dass zwei Werte gleich sind. Wenn zwei Werte gleichauf auf dem ersten Platz liegen, überspringt RANK() die dritte Zeile auf 3, während DENSE_RANK() die dritte Zeile als 2 zuordnen würde.

Weitere Informationen zu Ranking-Funktionen finden Sie in unserem ausführlichen Artikel Überblick über Ranking-Funktionen in SQL.

Sie können auch die obersten X Zeilen filtern, indem Sie einen gemeinsamen Tabellenausdruck (CTE) und eine WHERE Klausel verwenden. Wir können zum Beispiel die folgende Abfrage ausführen, um nur die am niedrigsten eingestufte Zeile zu erhalten:

WITH sales_cte AS (
  SELECT
    product_id,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
  FROM sales
  GROUP BY id 
) 
SELECT * 
FROM sales_cte
WHERE rank = 1;

In dieser Abfrage definieren wir eine CTE namens sales_ctedie den Rang der einzelnen product_id auf der Grundlage der Gesamtverkäufe angibt. Dann wird diese CTE abgefragt, wobei nur die Zeilen abgerufen werden, deren Rang = 1 ist. So können wir nur die oberste Zeile zurückgeben.

4. Berechnen der Differenz (Delta) zwischen Zeilen

Manchmal müssen Sie die Werte von aufeinander folgenden Zeilen vergleichen. Ein Beispiel wäre, die Umsätze des Vormonats mit denen des aktuellen Monats zu vergleichen. Wenn Sie sowohl die Umsätze des aktuellen Monats als auch die Umsätze des Vormonats haben, können Sie die Differenz zwischen den beiden Werten berechnen.

Dazu können Sie Fensterfunktionen wie LEAD() und LAG() verwenden. Diesmal werden wir eine andere Tabelle verwenden: sales_agg. Diese Tabelle enthält die aggregierten Umsätze für jedes Jahr und jeden Monat.

Berechnen wir die Umsatzdifferenz von Monat zu Monat mit LAG():

SELECT
  year, 
  month,
  total_sales,
  LAG(total_sales) OVER(ORDER BY year, month) AS previous_month,
  total_sales – LAG(total_sales) OVER(ORDER BY year, month) AS            sales_difference
FROM sales_agg
ORDER BY year, month;

Diese Abfrage verwendet die Funktion LAG(), um die Differenz zwischen dem Umsatz des aktuellen Monats und dem des Vormonats zu berechnen:

Die Funktion LAG() ist eine Fensterfunktion. Sie zieht den Wert total_sales aus der vorherigen Zeile, geordnet nach Jahr und Monat. Wir aliasieren diese Spalte als previous_month.

Dann wird die Spalte sales_difference berechnet, indem der Wert total_sales für jede Zeile genommen und dann der Wert previous_month abgezogen wird. Die erste Zeile hat NULL Werte, da es keine vorherige Zeile gibt.

Wenn Sie die Umsätze des nächsten Monats abrufen müssten, würden Sie die Fensterfunktion LEAD() anstelle von LAG() verwenden. LAG() vergleicht die aktuelle Zeile mit den vorherigen Werten; LEAD() vergleicht die aktuelle Zeile mit den nachfolgenden Werten.

How to Calculate the Difference Between Two Rows in SQL (Wie man die Differenz zwischen zwei Zeilen in SQL berechnet ) enthält mehrere Beispiele aus der Praxis für die Verwendung dieser Syntax zur Berechnung der Differenz zwischen zwei Zeilen.

5. Berechnen einer laufenden Summe

Eine laufende Summe berechnet die Summe einer Folge von Zahlen. Sie wird auch als kumulative Summe oder kumulative Summe bezeichnet; sie addiert jeden neuen Wert zur vorherigen Summe.

Laufende Summen sind nützlich, um den kumulativen Effekt von Datenpunkten über die Zeit zu berechnen. Sie könnten zum Beispiel die kumulative Anzahl der Nutzer berechnen, die Ihre Website bis zu einem bestimmten Zeitpunkt besucht haben, um das Nutzerwachstum Ihrer Website zu verstehen.

Schauen wir uns noch einmal die Funktion SUM() an und sehen wir uns an, wie wir sie zur Berechnung einer laufenden Summe der Verkäufe verwenden können. Wie im vorherigen Beispiel müssen wir SUM() als Fensterfunktion verwenden, um die gewünschten Ergebnisse zu erzielen.

SELECT
  year,
  month,
  total_sales,
  SUM(total_sales) OVER(ORDER BY year, month) AS running_total
FROM sales_agg
ORDER BY year, month;

Diese laufende Summe wird mit der Funktion SUM() und der Klausel OVER() berechnet. Dabei wird die total_sales aus der aktuellen Zeile zu allen vorherigen Zeilen in der angegebenen Reihenfolge addiert.

In der zweiten Zeile wird die laufende Summe berechnet, indem der Wert total_sales aus der ersten und zweiten Zeile aggregiert wird. In unserem Beispiel ist die zweite Zeile für Februar 2021. Der Wert running_total ist die Summe aus der ersten Zeile (für Januar 2021) und der zweiten Zeile (für Februar 2021).

In der dritten Zeile wird die laufende Summe durch Aggregation der Werte aus der ersten bis dritten Zeile berechnet. Das gleiche Muster setzt sich in jeder Zeile fort.

Weitere Informationen zur Berechnung von laufenden Summen in SQL finden Sie in unserem Artikel Was ist eine laufende Summe in SQL und wie wird sie berechnet?

6. Berechnen eines gleitenden Durchschnitts

Bei der Untersuchung von Verkaufstrends in der Datenanalyse ist es oft hilfreich, einen gleitenden Durchschnitt anstelle jedes einzelnen Datenpunkts zu verwenden. Ein gleitender Durchschnitt (auch als rollender Durchschnitt bezeichnet) berechnet den Durchschnitt des aktuellen Werts und einer bestimmten Anzahl unmittelbar vorangegangener Werte.

Diese Technik hilft, Daten zu glätten und Trends zu erkennen, insbesondere wenn Ihre Daten eine hohe Volatilität aufweisen. Die Hauptidee besteht darin, zu untersuchen, wie sich diese Durchschnittswerte im Laufe der Zeit verhalten, anstatt das Verhalten der ursprünglichen Datenpunkte zu untersuchen.

Ein Beispiel: Sie möchten die täglichen Umsätze eines Restaurants analysieren, das am Wochenende hohe, von Montag bis Mittwoch jedoch niedrige Umsätze verzeichnet. Wenn Sie jeden einzelnen Datenpunkt aufzeichnen würden, würden Sie sehr hohe und sehr niedrige Werte nebeneinander sehen, wodurch langfristige Trends schwieriger zu erkennen sind. Bei der Verwendung eines gleitenden 3-Tage-Durchschnitts würde man den Durchschnitt der letzten 3 Tage nehmen, was die Hochs und Tiefs ausgleicht.

Für dieses Beispiel werden wir unsere Tabelle sales_agg ändern.

Berechnen wir nun einen gleitenden 3-Monats-Durchschnitt der Verkäufe:

SELECT
  year, 
  month, 
  total_sales, 
  AVG(total_sales) OVER(ORDER BY year, month ROWS BETWEEN 2 PRECEDING and CURRENT ROW) AS moving_average
FROM aales_agg
ORDER BY year, month;

Diese Abfrage berechnet den gleitenden Durchschnitt unter Verwendung der Funktion AVG() mit der Klausel OVER(). Die Klausel ROWS BETWEEN gibt den Bereich der Zeilen an, die in die Berechnung des gleitenden Durchschnitts einbezogen werden sollen - in diesem Fall die aktuelle Zeile und die beiden vorangegangenen Zeilen.

Das Ergebnis:

Da es keine vorherigen Zeilen gibt, ist der gleitende Durchschnitt der ersten Zeile nur der Gesamtverkaufswert. Der gleitende Durchschnitt der zweiten Zeile ist der Durchschnitt aus 300 und 700. Für die dritte Zeile haben wir nun die 2 vorangegangenen Zeilen, wie in unserer Abfrage definiert; der gleitende Durchschnitt wird durch den Mittelwert von 300, 700 und 500 berechnet. Dieses Muster setzt sich für die restlichen Zeilen fort.

Was ein gleitender Durchschnitt ist und wie man ihn in SQL berechnet, ist eine gute Quelle für weitere Informationen zu diesem Thema.

7. Zählen von Elementen in benutzerdefinierten Kategorien mit SUM() und CASE WHEN

Sie können Elemente in benutzerdefinierten Kategorien zählen, indem Sie SUM() mit CASE WHEN kombinieren. Sie verwenden dies, wenn Sie eine Geschäftslogik erstellen müssen, die in Ihren Daten nicht vorhanden ist. Sie könnten zum Beispiel bestimmte Standorte nach benutzerdefinierten Regionen gruppieren und dann Metriken auf der Grundlage dieser Regionen berechnen wollen.

CASE WHEN Anweisungen ermöglichen es Ihnen, bedingte Logik in Abfragen auszuführen. Die Syntax ist wie folgt aufgebaut:

CASE 
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
END

Die Bedingungen werden von oben nach unten ausgewertet. Die erste Bedingung, die als wahr bewertet wird, bestimmt, welches Ergebnis zurückgegeben wird.

In unserem Beispiel wollen wir eine Aufschlüsselung nach Verkaufskategorien auf der Grundlage von Umsatzbeträgen erstellen:

SELECT
  SUM(CASE WHEN total_sales < 400 THEN 1 ELSE 0 END) AS low_sales,  
  SUM(CASE WHEN total_sales >= 400 AND total_sales < 700 THEN 1 ELSE 0 END) AS medium_sales,
  SUM(CASE WHEN total_sales >= 700 THEN 1 ELSE 0 END) AS high_sales
FROM sales_agg

In der obigen Abfrage verwenden wir den Ausdruck CASE WHEN, um jeden Monat in die Kategorien low_sales, medium_sales oder high_sales einzuteilen. Wenn die Bedingung im Ausdruck CASE WHEN erfüllt ist, wird der Wert 1 zurückgegeben; andernfalls wird 0 zurückgegeben.

Die Funktion SUM() wird dann verwendet, um die 1en und 0en für jede Kategorie zu addieren und somit die Anzahl der Monate zu zählen, die in jede Kategorie fallen. Das Ergebnis ist eine einzige Zeile mit den Zählungen der Monate low_sales, medium_sales und high_sales.

Mit diesem Ansatz können Sie benutzerdefinierte Kategorien erstellen und die Elemente in jeder Kategorie mit einer einzigen Abfrage zählen. Die Kombination von SUM() und CASE WHEN ist vielseitig und kann an verschiedene Anwendungsfälle angepasst werden.

Wenn Sie diese Konstruktion in Ihrer Abfrage verwenden möchten, finden Sie in How to Use CASE WHEN with SUM() in SQL weitere Einzelheiten.

Optimieren Sie Ihre Datenanalyse mit Fortgeschrittenes SQL Abfragen

Die Beherrschung fortgeschrittener SQL-Abfragen ist für eine effiziente und genaue Datenanalyse unerlässlich. In diesem Artikel stellen wir Ihnen sieben leistungsstarke SQL-Techniken vor, mit denen Sie tiefere Einblicke in Ihre Daten gewinnen können.

Wenn Sie lernen, Daten nach Zeiträumen zu gruppieren, ROLLUP für mehrere Gruppierungsebenen zu verwenden, Daten mit Fensterfunktionen in eine Rangfolge zu bringen, Unterschiede zwischen Zeilen zu berechnen, laufende Summen und gleitende Durchschnitte zu berechnen und Elemente in benutzerdefinierten Kategorien zu zählen, sind Sie gut gerüstet, um komplexe Datenanalyseaufgaben zu bewältigen.

Wenn Sie Ihre SQL-Kenntnisse weiter ausbauen, werden Sie noch mehr Möglichkeiten finden, dieses leistungsstarke Tool zu nutzen, um das volle Potenzial Ihrer Datensätze zu erschließen und die Entscheidungsfindung in Ihrem Unternehmen zu verbessern.

Wenn Sie noch mehr üben und ein noch tieferes Verständnis für diese Themen erlangen möchten, melden Sie sich für unseren SQL Reporting Track an. Er ist eine hervorragende Möglichkeit, diese fortgeschrittenen SQL-Funktionen zu beherrschen.