5 fortgeschrittene SQL-Konstruktionen, die jeder Datenanalyst kennen sollte

Gehört die Datenanalyse zu Ihren Aufgaben? Hier sind fünf Beispiele dafür, wie fortgeschrittenes SQL Ihnen bei Ihrer täglichen Arbeit helfen kann.

Ich bin Datenanalyst und ich muss sagen, dass Datenanalysten ziemlich seltsam sein können. Andere Menschen haben in der Regel Angst vor riesigen Datenmengen, wir hingegen genießen sie. Je tiefer wir in Daten eindringen, desto glücklicher sind wir. Ein wichtiges Werkzeug, um in Daten einzudringen (und somit ein glücklicherer Datenanalyst zu sein), ist SQL.

Erinnern Sie sich noch daran, als Sie zum ersten Mal einfache SQL-Funktionen entdeckten? Erinnern Sie sich an die Freude, die Sie empfanden, als Sie die Daten schnell auswählen, gruppieren und ordnen konnten? Wenn Sie nun diese grundlegenden Funktionen beherrschen, fragen Sie sich wahrscheinlich, was fortgeschrittenes SQL ist und wie Sie damit neue Dimensionen der Datenverarbeitung und der Erstellung cooler Berichte erreichen können. Schließlich sind Datenanalysten mit viel Arbeit und wenig Spaß nicht zufrieden.

Nun, ich werde mich nicht mit der Definition aufhalten. Der beste Weg, fortgeschrittenes SQL zu erklären, ist, Ihnen einige fortgeschrittene Abfragen zu zeigen. Dann werden Sie sehen, wie hilfreich (und unterhaltsam) sie sein können.

Da ich viele SQL-Fensterfunktionen verwenden werde, ist es am besten, wenn Sie bereits wissen, was sie sind und wie sie funktionieren. Dieser Artikel wird Sie auf den neuesten Stand bringen, falls Sie mit ihnen nicht vertraut sind.

Abfrage 1: Ranking der Daten

Die Geschäftsleitung möchte für alles eine Rangliste sehen: verkaufte Produkte, Gehälter, Mitarbeiter pro Abteilung, erwirtschaftetes Geld in jedem erdenklichen Segment - sie wird immer nach einer Rangliste fragen. Um Ihnen ein Beispiel zu zeigen, wie man in SQL eine Rangliste erstellt, verwende ich die sales Tabelle. Sie hat die folgenden Spalten:

• product - Der Name des Produkts.
• product_price - Der Preis des Produkts.
• items_sold - Die Anzahl der verkauften Artikel.

Die Idee ist, den Umsatz für jedes Produkt zu berechnen und es mit der Funktion RANK() zu ordnen. Der folgende Code löst diese Aufgabe:

SELECT	product,
		product_price,
		items_sold,
		product_price * items_sold AS revenue,
		RANK() OVER (ORDER BY product_price * items_sold DESC) AS revenue_rank
FROM sales;

Der obige Code wählt alle Spalten der Tabelle aus. Um den Umsatz pro Produkt zu erhalten, müssen Sie den Preis mit den verkauften Artikeln multiplizieren. Genau das tut die Abfrage, und das Ergebnis wird in der neuen Spalte Umsatz angezeigt. Schließlich gibt es noch die Funktion RANK(). Diese Funktion ordnet alle Zeilen nach der neuen Spalte Umsatz (definiert durch product_price * items_sold). Der Rang wird in der neuen Spalte revenue_rank angezeigt.

Und hier ist das Ergebnis:

RANK() Die Funktion revenue_rank ist nur eine Möglichkeit, Daten in SQL zu ordnen. Die anderen Möglichkeiten können Sie in diesem Artikel nachlesen. Und wenn Sie tiefer in das Thema eintauchen möchten, finden Sie hier unseren vollständigen Leitfaden zu Ranking-Funktionen.

Abfrage 2: Berechnung von Delta-Werten

Neben dem Ranking ist die Berechnung von Delta-Werten wahrscheinlich eine der häufigsten Aufgaben von Datenanalysten. Dies ist in der Regel bei der Berechnung von Veränderungen von Tag zu Tag, von Monat zu Monat, von Quartal zu Quartal oder von Jahr zu Jahr erforderlich. Egal, ob es sich um Einnahmen, Kosten, Preisänderungen, Volumenänderungen oder etwas anderes handelt, Sie müssen die Differenz zwischen den Zahlen berechnen. Hierfür benötigen Sie eine erweiterte SQL-Abfrage mit der Funktion LAG(). Diese Funktion wird verwendet, um die Daten einer vorherigen Zeile abzurufen. Ich zeige Ihnen die Funktion anhand der revenue Tabelle. Die Tabelle besteht aus zwei Zeilen:

• month - Der Monat des Jahres.
• revenue - Der Umsatz für diesen Monat.

Ihre Aufgabe ist es, die Differenz zwischen dem Umsatz jedes Monats und dem des Vormonats zu berechnen (d. h. das monatliche Umsatzdelta). Wie können Sie das tun? Wenn Sie die Funktion LAG() kennen, ist es sehr einfach. Hier ist der Code:

SELECT	month,
		revenue,
		revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_delta
FROM revenue;

Das Prinzip zur Berechnung des Deltas besteht darin, den Vormonat vom aktuellen Monat abzuziehen. Der obige Code tut genau das. Zuerst wählt er die Spalten Monat und Umsatz aus. Danach wird der Betrag der Einnahmen des aktuellen Monats vom Vormonat abgezogen. Dies wird durch die Funktion LAG() definiert. Die Werte, die wir in die Klammern der Funktion (revenue, 1) setzen, geben an, dass der Wert in der Spalte Umsatz vom vorherigen Wert in dieser Spalte abgezogen wird. Deshalb steht dort die Zahl 1; sie gibt an, wie viele Zeilen die Funktion zurückgeht, um die Operation durchzuführen.

Theoretisch macht LAG(revenue) das Gleiche, da die Funktion LAG() standardmäßig eine Zeile zurückgeht. Ich wollte Ihnen dies jedoch explizit zeigen. So ist es einfacher zu verstehen und Sie wissen, was zu tun ist, wenn Sie mehr als eine Zeile zurückgehen müssen.

Die Deltas müssen sequentiell berechnet werden, nicht nach zufälligen Monaten; deshalb gibt es ORDER BY month. Das Delta wird in der neuen Spalte monthly_delta angezeigt. Führen Sie den Code aus und Sie erhalten die resultierende Tabelle:

Abfrage 3: Berechnung der laufenden Summen

Laufende Summen (auch bekannt als kumulative Summen) werden in der Datenanalyse häufig verwendet. Sie werden in der Regel mit Zeitreihendaten verwendet, um zu sehen, wie sich bestimmte Leistungsindikatoren im Laufe der Zeit entwickeln (oder entwickeln werden). Wie andere fortgeschrittene SQL-Konzepte haben auch laufende Summen einen sehr breiten praktischen Nutzen. Sie werden verwendet, um Verkäufe, Einnahmen, Kosten, Gewinne und Budgets zu überwachen. Hier finden Sie einen Artikel, in dem die laufenden Summen und ihre Berechnung in SQL sehr gut erklärt werden.

Zunächst zeige ich Ihnen, wie eine kumulierte Summe für Budgets funktioniert. Verwenden wir eine Tabelle mit dem fantasievollen Namen budget. Sie besteht aus diesen Spalten:

• month - Der Monat des Geldflusses.
• client - Der Name des Kunden.
• cash_flow - Der budgetierte Cashflow.

Es gibt drei Kunden. Das Budget enthält monatliche Projektionen für den jährlichen Cashflow, den Ihr Unternehmen von ihnen erhalten wird. Sie müssen den kumulierten Cashflow für jeden Kunden berechnen. Vielleicht wissen Sie bereits intuitiv, dass Sie die Funktion SUM() verwenden müssen, allerdings mit einer gewissen Abwandlung. Hier erfahren Sie, wie Sie laufende Summen berechnen:

SELECT	month,
		client,
		cash_flow,
		SUM (cash_flow) OVER (PARTITION BY client ORDER BY month) AS running_total

FROM budget;

Dies ist eine einfache kleine Abfrage, die wahre Wunder bewirkt! Sie wählt die Spalten Monat, Kunde und cash_flow aus. Um die laufende Summe zu berechnen, müssen Sie die Cashflows zusammenfassen. Dies wird durch SUM (cash_flow) definiert.

Sie sind jedoch nicht daran interessiert, laufende Summen auf Tabellenebene zu haben. Sie müssen irgendwie dafür sorgen, dass die Cashflows für den ersten Kunden Monat für Monat zusammengefasst werden und dann für den zweiten Kunden zurückgesetzt und neu begonnen werden. Dazu benötigen Sie OVER (PARTITION BY client ORDER BY month). Hier wird die Partition durch den Spaltenmandanten definiert, was bedeutet, dass jeder Datensatz durch die verschiedenen Mandanten definiert wird. Außerdem wird die Operation nur innerhalb der Partition durchgeführt, nicht auf der gesamten Tabelle. Auf diese Weise erhalten Sie eine laufende Summe für jeden Mandanten separat.

Natürlich müssen die Cashflows sequentiell zusammengefasst werden; deshalb sind sie nach der Monatsspalte geordnet. Die laufende Summe wird in der neuen Spalte running_total angezeigt.

Hier ist die resultierende Tabelle:

In den letzten drei Beispielen habe ich Fensterfunktionen verwendet. Wenn Sie mehr über dieses Thema erfahren möchten, ist der KursFensterfunktionen , einer unserer SQL-Kurse für Fortgeschrittene, ein guter Weg.

Was auch sehr hilfreich sein kann, vor allem wenn Sie Fensterfunktionen zum ersten Mal oder nur gelegentlich verwenden, ist dieses SQL Fensterfunktionen Cheat Sheet. Ich werde es sicher verwenden, wenn ich das nächste Mal über Fensterfunktionen schreibe!

Abfrage 4: Erstellen eines Berichts auf der Grundlage mehrerer Bedingungen

Eine der Hauptaufgaben von Datenanalysten ist es, Daten für andere Benutzer freundlicher zu gestalten. Indem wir ihnen Daten in einer Form zur Verfügung stellen, die sie leicht verwenden können, erleichtern wir ihnen die Arbeit. Um nützliche Berichte zu erstellen, muss ein Datenanalytiker den geschäftlichen Input mit seinem Wissen über die Daten kombinieren. Eines der Werkzeuge, die Ihnen dabei helfen können, ist die CASE -Anweisung, ein weiteres fortgeschrittenes SQL-Konzept.

Um Ihnen ein Beispiel zu geben, stellen wir uns folgendes Szenario vor. Sie arbeiten für eine Bank und werden von Ihren Kollegen gebeten, einen Bericht zu erstellen. Es gibt eine Tabelle namens debt die die Kunden der Bank und Einzelheiten über ihre Schulden enthält. Die Tabelle besteht aus den folgenden Spalten:

• client - Der Name des Kunden.
• date_due - Der Tag, an dem die Schuld fällig wurde.
• amount_due - Die Höhe der fälligen Forderung.

Sie müssen einen Bericht für den 30.4.2020 erstellen. Sie müssen irgendwie die Anzahl der fälligen Tage ab dem Berichtsdatum berechnen. Außerdem müssen Sie den Kunden entsprechend der Anzahl der Tage, die sein Konto fällig ist, einem bestimmten Zeitbereich zuordnen.

Die Abfrage finden Sie weiter unten. Keine Angst - ich werde sie für Sie analysieren. Es ist nicht so beängstigend, wie es aussieht!

SELECT	client,
		date_due,
		amount_due,
		DATEDIFF ('2020-04-30', date_due) AS days_due,
		CASE
	WHEN  DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 90 AND DATEDIFF ('2020-04-30', date_due) <=180 THEN '91-180 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 180 AND DATEDIFF ('2020-04-30', date_due) <=365 THEN '181-365 days'
	ELSE '> 365 days'
END AS time_bucket

FROM debt;

Zuerst müssen Sie den SELECT Teil der Abfrage angeben. Ich habe die vorhandenen Spalten client, date_due und amount_due ausgewählt.

Als nächstes müssen Sie die fälligen Tage berechnen. Dazu subtrahieren Sie das Fälligkeitsdatum vom Berichtsdatum. Genau das habe ich mit DATEDIFF ('2020-04-30', date_due) AS days_due getan. Ich habe die Funktion DATEDIFF() verwendet, um die erforderliche Differenz zu berechnen. Wenn Sie diese Funktion verwenden, müssen Sie zunächst angeben, welche Daten Sie subtrahieren möchten. In unserem Fall sind es das Berichtsdatum und das Fälligkeitsdatum. Als nächstes müssen Sie angeben, wie das Ergebnis angezeigt werden soll, d. h. in Jahren, Monaten oder Tagen. In diesem Fall benötigen Sie Tage, also geben Sie Tag als letzten Wert in DATEDIFF() ein.

Jetzt kommt der spannende Teil - die Erstellung der Bedingungen, die ich in der Anweisung CASE verwendet habe. Diese Anweisung beginnt mit CASE und endet mit END. Dazwischen müssen Sie die Bedingungen definieren, die den von Ihren Kollegen gewünschten Bericht erstellen. Hierfür verwenden Sie WHEN und THEN.

Nehmen wir an, dass der erste Bereich der fälligen Tage 0-30 Tage beträgt. Die erste Bedingung in der Anweisung CASE lautet WHEN DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'. Da Sie die Kunden entsprechend den fälligen Tagen einem Zeitbereich zuordnen müssen, tut dieser Teil des Codes genau das. Er lautet wie folgt: Wenn die Differenz zwischen dem Berichtsdatum und dem Fälligkeitsdatum weniger als oder gleich 30 Tage beträgt, wird dieser Kunde dem Zeitbereich 0-30 Tage zugeordnet.

Das nächste Zeitraster ist 31-90 Tage, und dies ist der Teil des Codes, der es definiert:

WHEN DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'

Das ist doch nicht so kompliziert, oder?

Das gleiche Prinzip gilt für die beiden verbleibenden Zeitspannen: 91-180 Tage und 181-365 Tage. Alle Schulden, die seit mehr als 365 Tagen fällig sind, gehören in die Zeitspanne von 365 Tagen. Dies wird durch ELSE '> 365 days' definiert. Hier werden einfach die Kriterien für die Berichterstattung festgelegt: Wenn der Wert dies ist, mache dies; wenn er das nicht ist, mache dies. Im Wesentlichen handelt es sich um eine komplexere Version der IF-Anweisung.

Bitte beachten Sie, dass es einen eleganteren Weg gibt, diesen Code zu schreiben: Ich hätte eine Variable mit dem Wert "2020-04-30" deklarieren können, anstatt überall im Code manuell "2020-04-30" zu schreiben. Ich wollte Sie jedoch nicht verwirren, wenn Sie mit Variablen nicht vertraut sind.

Was die Funktion DATEDIFF() betrifft, so habe ich die MySQL-Funktion und -Syntax verwendet. Je nachdem, welche Datenbank-Engine Sie verwenden, müssen Sie die Syntax möglicherweise entsprechend anpassen.

Alle diese Zeitabschnitte werden in der neuen Spalte time_bucket angezeigt. Da Sie möchten, dass Ihre Daten schön aussehen, ordnen Sie Ihre Tabelle nach days_due aufsteigend. Führen Sie den Code aus, und Sie erhalten eine schöne Tabelle. Und wahrscheinlich einen kostenlosen Kaffee von Ihren Kollegen!

Aufregend, nicht wahr? Wenn Sie mehr Aufregung wie diese wollen, Erstellen einfacher SQL-Berichte ist für Sie! Dort erfahren Sie mehr über CASE WHEN und die Feinheiten von GROUP BY.

Abfrage 5: Hinzufügen von Zwischensummen zu einem Bericht

Eine sehr häufige Anforderung ist es, Zwischensummen und Gesamtsummen im selben Bericht anzuzeigen. Die ROLLUP Klausel macht dies viel einfacher. Sie ist eine Erweiterung der GROUP BY Klausel. Sie ermöglicht es Ihnen, Zwischensummen und Gesamtsummen zu Ihren Daten hinzuzufügen.

Hier ist, wie man ROLLUP benutzt. Sie haben die Tabelle warehouse mit den folgenden Spalten:

• warehouse - Der Name des Lagers.
• brand - Die Marke des Produkts.
• product - Der Name des Produkts.
• quantity - Die Menge dieses Produkts im Lager.

Es gibt zwei verschiedene Marken mit jeweils fünf Produkten. Und es gibt zwei Lagerhäuser. Ihre Aufgabe ist es, die Gesamtproduktmenge für beide Marken in beiden Lagern zu berechnen. Sie brauchen auch die Gesamtsumme aller Produkte in beiden Lagern. Und schließlich müssen Sie alles in einer Tabelle mit einer einzigen Abfrage erledigen. Wie würden Sie das bewerkstelligen? Der Code lautet:

SELECT	warehouse,
		brand,
		SUM (quantity) AS sum_product

FROM warehouse
GROUP BY ROLLUP (warehouse, brand);

Zunächst wählen Sie die Spalten Lager und Marke aus der Tabelle aus. Sie wollen auch die Summe der Spalte Menge, die in der neuen Tabelle angezeigt wird sum_product. Was ist der nächste Schritt? Hier kommt ROLLUP ins Spiel! Es wird verwendet, um Summen für mehrere Datengruppierungsebenen zu erhalten. Der Teil GROUP BY ROLLUP (warehouse, brand) wird genau das tun. Er gruppiert die Daten nach den Spalten Lager und Marke. Danach werden die Daten nach jeder Gruppierung summiert. Das Ergebnis ist:

Die Tabelle enthält Summen für die Marken Brando und Ostap in den Lagern Amsterdam und Berlin sowie eine Gesamtsumme. Die Zwischensumme für beide Produkte im Amsterdamer Lager ist in der ersten Zeile mit dem Markenwert NULL angegeben. Sie beläuft sich auf 64 039, die Summe der beiden vorherigen Zeilen.

Als nächstes sehen Sie die Summen für beide Marken im Berliner Lager. Danach folgt eine weitere Zeile mit dem Markenwert NULL; dies ist eigentlich die Berliner Zwischensumme in Höhe von 80 807. Die letzte Zeile zeigt die Gesamtsumme aller Produkte in allen Lagern, die 144 846 beträgt.

Warum gibt es in einigen Zeilen NULL-Werte? Weil SQL nicht weiß, wie es Marken und Lager benennen soll, wenn sie gruppiert sind und eine Zwischensumme oder Gesamtsumme angezeigt wird. Weitere interessante Details über andere GROUP BY Erweiterungen finden Sie in unserem Kurs GROUP BY Erweiterungen.

Dies ist die letzte erweiterte SQL-Abfrage für den Moment.

Fanden Sie diese Fortgeschrittenes SQL Themen hilfreich?

Fortgeschrittenes SQL Die Verwendung von SQL durch Datenwissenschaftler, Datenanalysten und andere ist ein sehr umfangreiches Thema. Die Möglichkeiten für den Einsatz von SQL in der Datenanalyse sind enorm. Dieser Fortgeschrittenes SQL Track wird Ihnen eine Vorstellung davon geben, was Sie als Datenanalyst mit SQL tun können.

In diesem Artikel habe ich versucht, Ihnen einige der häufigsten Anwendungen von fortgeschrittenem SQL zu zeigen. Die Beispiele sind praxisnah und stammen aus meiner Erfahrung. Ich hoffe, sie waren hilfreich. Ich habe versucht, die Abfragen so einfach (und verständlich) wie möglich zu gestalten. Sie können diese Abfragen an Ihre eigenen Berichte und Datensätze anpassen. Sie können sie gerne verwenden!

Wenn Sie Fragen oder Kommentare haben, lassen Sie es mich im Kommentarbereich wissen!