6 erweiterte SQL-Abfragen für die Analyse von Finanzdaten

Sind Sie ein fortgeschrittener SQL-Benutzer, der neu im Finanzbereich ist? Möchten Sie lernen, wie Sie Ihr Wissen zur Analyse von Finanzdaten einsetzen können? Dieser Artikel gibt Ihnen einige Hinweise.

Sie wissen wahrscheinlich, dass SQL ein mächtiges Werkzeug für die Analyse aller Arten von Daten ist. Je größer und komplexer die Daten sind, desto nützlicher wird SQL. In bestimmten Situationen können Finanzdaten sehr kompliziert sein; für eine ausgefeilte Analyse sind ausgefeilte Werkzeuge erforderlich. SQL ist eines dieser Werkzeuge, und je besser Sie einige fortgeschrittene SQL-Praktiken kennen, desto einfacher wird es, komplexe Finanzdatenanalysen durchzuführen und Finanzberichte zu erstellen. Ich werde Ihnen einige Beispiele aus meiner eigenen Erfahrung zeigen, wie Sie dies tun können.

Die in diesem Artikel geforderten SQL-Kenntnisse - wie z. B. Fensterfunktionen, GROUP BY-Erweiterungen und rekursive Abfragen - werden in dem Kurs LearnSQL.de Fortgeschrittenes SQL behandelt.

Einführung in die Datenbank

Alle sechs Beispiele werden diese Datenbank verwenden, die aus fünf Tabellen besteht:

• country
• card_type
• customer
• card_number
• card_transaction

Wir stellen uns vor, dass es sich um die Datenbank eines Unternehmens handelt, das Kreditkarten verarbeitet, und dass Sie der beste Mitarbeiter sind, wenn es um die Analyse von Finanzdaten geht.

Kommen wir nun zu den Details der einzelnen Tabellen.

Die Tabelle country hat die folgenden Attribute:

• id - Die ID des Landes und der Primärschlüssel (PK) dieser Tabelle.
• country_name - Der Name des Landes.

Die nächste Tabelle ist card_typemit diesen Attributen:

• id - Die ID des Kartentyps; der Primärschlüssel (PK).
• card_type_name - Der Name des Kartentyps.

Die Tabelle customer besteht aus den Spalten:

• id - Die ID des Kunden; der Primärschlüssel (PK).
• NIN - Die nationale Identifikationsnummer des Kunden.
• first_name - Der Vorname des Kunden.
• last_name - Der Nachname des Kunden.
• country_id - Die ID des Landes; dies ist ein Fremdschlüssel (FK), der auf die Tabelle country.

Als nächstes kommt die card_number Tabelle mit den Attributen:

• id - Die ID der Karte; der Primärschlüssel (PK).
• card_number - Die Kartennummer.
• customer_id - Die ID des Kunden; ein Fremdschlüssel (FK), der auf die Tabelle customer.
• card_type_id - Die ID des Kartentyps; ein Fremdschlüssel (FK), der auf die Tabelle card_type.

Die letzte ist die card_transaction Tabelle, die diese Spalten enthält:

• id - Die ID der Transaktion; der Primärschlüssel (PK).
• date - Das Datum der Transaktion.
• amount - Der Transaktionsbetrag, in Dollar.
• card_number_id - Die ID der Karte; ein Fremdschlüssel (FK), der auf die Tabelle verweist card_number.

Nun, da Sie mit der Datenbank vertraut sind, kommen wir zu den Beispielen!

6 Beispiele für Fortgeschrittenes SQL Abfragen für die Finanzanalyse

1: Daten nach Jahr und Quartal gruppieren

Finanzdaten müssen normalerweise in bestimmten Zeiträumen oder Zeitspannen gruppiert werden. Bei der Erstellung von Finanzberichten werden Sie sicherlich Daten nach Jahren und Quartalen gruppieren müssen. Ich werde Ihnen zeigen, wie das geht. Wenn Sie dieses Prinzip gelernt haben, können Sie Daten auf jeder anderen Ebene gruppieren (z. B. nach Monaten, Wochen oder Tagen, je nachdem, welche Daten Sie haben).

In diesem Beispiel möchte ich die Daten nach Jahren und Quartalen gruppieren und die Anzahl der Transaktionen anzeigen. Um dies zu erreichen, verwende ich die Tabelle card_transaction und diesen Code:

SELECT	EXTRACT(YEAR FROM date) AS year,
		EXTRACT(QUARTER FROM date) AS quarter,
		COUNT(amount) AS number_of_transactions
FROM card_transaction
GROUP BY EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date)
ORDER BY EXTRACT(YEAR FROM date) ASC, EXTRACT(QUARTER FROM date);

Der Code verwendet die Funktion EXTRACT(), um die Jahre und Quartale zu ermitteln. Dies ist eine Standard-SQL-Funktion, die in MySQL und PostgreSQL funktioniert. Wenn Sie jedoch SQL Server verwenden, müssen Sie eine andere Funktion verwenden - ich werde gleich erklären, welche.

Wenn Sie die Funktion EXTRACT() verwenden, müssen Sie den gewünschten Zeitraum und die Spalte angeben, die die Funktion verwenden soll, um den gewünschten Zeitraum zurückzugeben. Zunächst möchte ich die Jahre aus der Datumsspalte abrufen. Dann möchte ich Quartale, ebenfalls aus der Datumsspalte. Danach muss ich die Anzahl der Transaktionen zählen, was ich mit der Funktion COUNT() gemacht habe. Die Daten werden mit den Funktionen EXTRACT() gruppiert, die Jahre und Quartale darstellen. Schließlich werden die Daten nach Jahren und Quartalen in aufsteigender Reihenfolge sortiert, da ich das erste Quartal des ersten Jahres ganz oben im Bericht sehen möchte. Ich bin sicher, Sie wollen das Ergebnis sehen:

Dieser Bericht sieht sehr schön aus, muss ich sagen!

Wenn Sie SQL Server verwenden, müssen Sie die Funktion DATEPART() anstelle von EXTRACT() verwenden. Hier ist der erste Teil des Codes, nur damit Sie es verstehen. Für den Rest des Codes gilt das gleiche Prinzip:

SELECT	DATEPART(YEAR, date) AS year,
		DATEPART(QUARTER, date) AS quarter,
		COUNT(amount) AS number_of_transactions
...

2: Berechnen der laufenden Summen

Ihre Aufgabe besteht nun darin, die laufenden Summen für alle im Dezember 2020 getätigten Transaktionen anzuzeigen. Der Bericht muss auf der Ebene der Kartenart aggregiert werden. Um diesen Bericht zu erstellen, müssen Sie Fensterfunktionen und JOINs in den Code einfügen.

Bevor Sie weitermachen, sollten Sie vielleicht Ihre SQL-Kenntnisse überprüfen. Das können Sie im Track SQL Reporting tun. In diesem Kurs lernen Sie auch, wie Sie grundlegende SQL-Berichte erstellen und Umsatztrend- und Kundenverhaltensanalysen durchführen.

Der Code, der die gewünschten Daten zurückgibt, lautet wie folgt:

SELECT	DISTINCT (ct.date),
		cty.card_type_name,
		SUM (ct.amount) OVER (PARTITION BY cty.card_type_name ORDER BY ct.date ASC) AS transaction_running_total
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
WHERE date > '2020-11-30' AND date <= '2020-12-31'
ORDER BY cty.card_type_name ASC;

Der Code wählt zunächst das spezifische Datum aus, da es täglich mehrere Transaktionen desselben Kartentyps oder sogar derselben Kartennummer geben kann. Dann folgt die Spalte card_type_name. Die letzte Spalte in der Abfrage ist transaction_running_total. Um die laufende Summe zu berechnen, habe ich eine Fensterfunktion verwendet. Zunächst werden die Daten in der Spalte Betrag summiert. Dann habe ich mit der Klausel OVER() angegeben, dass die laufende Summe auf der Ebene des Kartentyps berechnet werden soll; daher PARTITION BY cty.card_type_name. Schließlich möchte ich, dass die laufende Summe vom ältesten bis zum neuesten Datum berechnet wird: ORDER BY date ASC.

Um die Daten zu erhalten, musste ich drei Tabellen verbinden. Die erste Verknüpfung verbindet die Tabellen card_transaction und card_number Tabellen. Die zweite Verknüpfung verweist auf die Tabelle card_type, wodurch ich den Namen des Kartentyps in meinem Bericht erhalte. Ich habe allen verknüpften Tabellen Aliasnamen zugewiesen; auf diese Weise musste ich weniger eingeben. Das Ergebnis wird mit der Klausel WHERE gefiltert, so dass ich nur Transaktionen vom Dezember 2020 erhalte. Ich habe beschlossen, die Daten nach dem Namen der Kartenart in aufsteigender Reihenfolge zu sortieren.

Wenn Sie den Code ausführen, erhalten Sie den unten abgebildeten Bericht:

Wenn Sie mehr über kumulative Summen in SQL wissen möchten, lesen Sie diesen Artikel, um zu erfahren, was laufende Summen sind und wie man sie berechnet.

3: Berechnen von laufenden Durchschnitten

Im nächsten Beispiel erstellen Sie einen Bericht, der jede Transaktion im Dezember 2020 und deren Betrag für Visa Electron-Karten anzeigt. Zusätzlich zeigen Sie den durchschnittlichen täglichen Transaktionsbetrag anhand eines gleitenden Durchschnitts an. Werfen Sie einen Blick auf den Code:

SELECT	ct.date,
		cty.card_type_name,
		SUM(ct.amount) AS daily_sum,
		AVG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS transaction_running_average
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
WHERE ct.date > '2020-11-30' AND date <= '2020-12-31' 
AND cty.card_type_name = 'visa-electron'
GROUP BY ct.date, cty.card_type_name
ORDER BY cty.card_type_name;

Ich habe zunächst die Spalten ausgewählt, die das Datum der Transaktion und den Namen des Kartentyps anzeigen. Dann habe ich die täglichen Transaktionsbeträge summiert, und das Ergebnis wird in der Spalte daily_sum angezeigt. Das war notwendig, weil es täglich mehrere Transaktionen mit Visa Electron geben kann. Dann habe ich diese tägliche Summe der Transaktionswerte verwendet, um den Durchschnitt zu berechnen. Um jedoch den gleitenden Durchschnitt zu erhalten, muss ich die Klausel OVER() verwenden. Ich möchte, dass der gleitende Durchschnitt vom ersten bis zum letzten Tag des Monats Dezember 2020 berechnet wird, so dass die Operation nach aufsteigendem Datum geordnet ist.

Bei der Berechnung der gleitenden Durchschnitte habe ich mich für einen gleitenden Drei-Tage-Durchschnitt entschieden, der durch den folgenden Teil des Codes definiert wird: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Dadurch wird der Code angewiesen, die aktuelle Zeile und die beiden vorherigen Zeilen (insgesamt drei Zeilen/drei Daten) zur Berechnung des gleitenden Durchschnitts zu verwenden.

Die Tabellen werden dann auf genau die gleiche Weise verbunden wie im vorherigen Beispiel. Es gibt zwei Bedingungen in der WHERE Klausel; eine definiert die berücksichtigten Daten, die andere definiert den Kartentyp.

Mit dieser Abfrage erhalten Sie die folgende Tabelle:

Wenn Sie mit gleitenden Durchschnitten nicht vertraut sind, möchte ich Ihnen erklären, wie sie funktionieren. Sie wissen bereits, dass es sich um einen gleitenden Drei-Tage-Durchschnitt handelt, der drei Zeilen zur Berechnung des Durchschnitts verwendet.

Für das Datum "2020-12-01" ist der Durchschnitt in der obigen Tabelle derselbe wie die tägliche Summe. Das liegt daran, dass die Abfrage nur die Daten in der aktuellen Zeile verwenden kann - es gibt keine vorangehenden Zeilen.

Für das Datum "2020-12-03" wird der gleitende Durchschnitt wie folgt berechnet: (16,881.70 + 17,375.79) / 2 = 17,128.75. Seien Sie hier vorsichtig! Der gleitende Durchschnitt wird nicht wie folgt berechnet: (16.881,70 + 17.375,79) / 3 = 11.419,16. Das liegt daran, dass es nur eine vorherige Zeile oder nur zwei zu mittelnde Werte gibt.

Der gleitende Durchschnitt für das Datum "2020-12-13" wird wie folgt berechnet: (16.881,70 + 17.375,79 + 17.725,49) / 3 = 17.327,66.

4: Zeitreihenanalyse

Eine sehr häufige Anforderung bei der Arbeit mit Finanzdaten ist die Analyse einer Zeitreihe (d. h. der Unterschied zwischen verschiedenen Zeiträumen, z. B. von Tag zu Tag oder von Monat zu Monat). Nehmen wir an, Ihr Chef hat Sie beauftragt, einen Bericht zu erstellen, der die täglichen Veränderungen der Transaktionswerte von Kunden aus China im Dezember 2020 aufzeigt. Die Verkaufsabteilung in China ist mit der Leistung im Dezember 2020 nicht zufrieden und möchte daher diesen Monat im Detail analysieren, um herauszufinden, wo es einen Rückgang der Transaktionen gab.

Um einen solchen Bericht zu erstellen, benötigen Sie wieder eine SQL-Fensterfunktion. Diesmal ist es die Funktion LAG(), mit der Sie Daten aus der vorherigen Zeile abrufen können. So sollten Sie vorgehen:

SELECT	ct.date,
		SUM(ct.amount) AS daily_sum,
		(SUM(ct.amount)-LAG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC)) 
AS daily_difference,
		co.country_name
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN customer cu ON cn.customer_id = cu.id 
JOIN country co ON cu.country_id = co.id
WHERE ct.date > '2020-11-30' AND date <= '2020-12-31'
AND co.country_name = 'China'
GROUP BY ct.date, co.country_name;

Die Abfrage beginnt mit einem bekannten Prozess: Sie wählt das Datum aus, dann berechnet sie die Tagessumme der Transaktionen (falls es mehrere tägliche Transaktionen aus China gibt). Um die tägliche Differenz zu berechnen, müssen Sie die Transaktionssumme des Vortags von der Summe des aktuellen Tages abziehen. Diese Berechnung wird in diesem Teil der Abfrage durchgeführt:

(SUM(ct.amount)-LAG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC)) AS daily_difference. 

Die Transaktionssumme des Vortags wird von der Funktion LAG() in Kombination mit der Klausel OVER() zurückgegeben. Die Operation sollte vom ältesten bis zum neuesten Datum durchgeführt werden, das wir in ORDER BY ct.date ASC angeben. Die letzte Spalte in der Abfrage ist die Spalte country_name aus der Tabelle country.

Drei Tabellen werden verknüpft, um die erforderlichen Daten zu erhalten; auch hier habe ich Aliase verwendet. Die WHERE Klausel enthält zwei Bedingungen, von denen eine das Datum und die andere das Land definiert. Schließlich wird das Ergebnis nach Datum und Ländername gruppiert.

Führen Sie die Abfrage aus, um diese Tabelle zu erhalten:

Der erste Wert ist NULL, weil es keine Zeilen vor der ersten gibt, d. h. die erste Zeile hat keine Zeile, mit der sie verglichen werden kann.

Mehr über die Funktion LAG() finden Sie in diesem Artikel über die Berechnung der Differenz zwischen zwei Zeilen.

Wenn Sie Fensterfunktionen üben möchten, probieren Sie unseren SQL-Kurs Fensterfunktionen .

5: Mehrere Gruppierungsebenen hinzufügen

Der Bericht, den ich Ihnen im ersten Beispiel gezeigt habe, ist recht gut, aber das heißt nicht, dass er nicht noch besser sein kann. Was mir hier fehlt, ist zum Beispiel eine Zwischensumme für 2019 und 2020 und eine Gesamtsumme - so etwas wie Pivot-Tabellen in Excel. Dieser Bericht lässt sich leicht mit der Funktion ROLLUP() verbessern. Fügen wir einige Zwischensummen hinzu und zeigen wir alle Werte auch auf der Ebene der Kartentypen an. Und so geht's:

SELECT	EXTRACT(YEAR FROM ct.date) AS year,
		EXTRACT(QUARTER FROM ct.date) AS quarter,
		COUNT(ct.amount) AS number_of_transactions,
		cty.card_type_name
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
GROUP BY ROLLUP(EXTRACT(YEAR FROM ct.date), EXTRACT(QUARTER FROM ct.date), cty.card_type_name);

Der erste Teil des Codes ist direkt aus dem ersten Beispiel übernommen. Er verwendet die Funktion EXTRACT(), um die Daten in Jahre und Quartale zu kategorisieren, und zählt dann die Anzahl der Transaktionen mit der Funktion COUNT(). Die letzte Spalte, die der Code auswählt, ist card_type_name aus der Tabelle card_type.

Die Daten werden aus den Tabellen card_transaction, card_number, und card_typeausgewählt, die verbunden sind. Jetzt kommt der magische Teil - die Verwendung von ROLLUP(), die eine Erweiterung der GROUP BY Klausel ist. Nachdem Sie GROUP BY in den Code geschrieben haben, verwenden Sie einfach die Funktion ROLLUP(), um mehrere Gruppierungsebenen in Ihrem Bericht anzugeben. Da Ihre Aufgabe darin besteht, die Daten auf jährlicher, vierteljährlicher und kartenartiger Ebene zu gruppieren, sollten diese Spalten in ROLLUP() enthalten sein. Führen Sie den obigen Code aus und Sie erhalten diesen schönen Bericht:

Lassen Sie sich von den NULL-Werten nicht abschrecken! Mit dem Bericht ist alles in Ordnung; NULL-Werte erscheinen, wenn es eine Zwischensumme, eine Gesamtsumme oder eine Gesamtsumme gibt. Hier ist der erste Teil der Tabelle:

Diese Tabelle zeigt die Anzahl der Transaktionen für das erste Quartal 2019 nach Kartentyp. Die Zwischensumme aller Transaktionen im ersten Quartal ist 49 + 46 +36 = 131. Die Logik ist die gleiche für den Rest der Tabelle. Wenn Sie also zum Beispiel eine Zeile wie die folgende sehen, bedeutet das, dass die Gesamtzahl der Transaktionen für das Jahr 2019 500 beträgt:

ROLLUP() und andere GROUP BY Erweiterungen sind cool; ich mag sie sehr! Wenn Sie sehen wollen, wie nützlich sie sein können, dann ist unser Kurs GROUP BY-Erweiterungen genau das Richtige für Sie.

Kommen wir nun zu unserem letzten Beispiel für SQL-Abfragen zur Finanzdatenanalyse.

6: Erstellen eines Umsatzberichts auf Jahresebene

Dies ist wahrscheinlich die komplexeste Abfrage, die ich Ihnen zeigen werde, aber ich denke, es lohnt sich. Wenn Sie lernen, wie diese Abfrage funktioniert, können Sie komplexe Berichte erstellen und statistische Analysen durchführen - beides wird in der Finanzbranche sehr häufig benötigt.

Ihre Aufgabe besteht darin, einen Bericht zu erstellen, der die Einnahmen für 2020, die Einnahmen für 2019 und die Gesamteinnahmen anzeigt. Alle drei Kategorien müssen auf Kundenebene angezeigt werden. Denken Sie daran, dass Sie für ein Unternehmen arbeiten, das Kreditkarten verarbeitet, so dass der Umsatz dem Transaktionsbetrag entspricht. Außerdem müssen Sie dem Kunden Kategorien zuweisen; wenn der Kunde einen Gesamtumsatz von 1.000.000 $ oder mehr erzielt hat, wird er als "Platin" eingestuft. Liegt der Gesamtumsatz unter 1.000.000 $, sollte der Kunde als "Gold" eingestuft werden. Hier ist die Abfrage:

SELECT	cu.NIN,
		cu.first_name,
		cu.last_name,
		SUM(ct.amount) AS total_revenue_per_customer,
		CASE
			WHEN SUM(ct.amount) >= 1000000 THEN 'Platinum'
			WHEN SUM(ct.amount) < 1000000 THEN 'Gold'
		END AS customer_category,
		SUM(CASE WHEN ct.date >= '2019-01-01' AND ct.date < '2020-01-01' THEN ct.amount ELSE 0 END) AS revenue_2019,
		SUM(CASE WHEN ct.date >= '2020-01-01' AND ct.date < '2021-01-01' THEN ct.amount ELSE 0 END) AS revenue_2020
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN customer cu ON cn.customer_id = cu.id
GROUP BY cu.NIN, cu.first_name, cu.last_name
ORDER BY total_revenue_per_customer DESC;

Beginnen wir mit dem einfachsten Teil: Die Abfrage wählt die Spalten NIN, first_name und last_name aus der Tabelle customer. Dann werden die Beträge summiert, was die Gesamteinnahmen ergeben wird. Danach folgt die Kategorisierung der Kunden mit Hilfe einer CASE WHEN Anweisung. Die erste WHEN weist die Kategorie "Platin" zu, während die andere die Kategorie "Gold" zuweist. Diese Anweisungen werden durch END abgeschlossen, und diese neue Spalte wird customer_category genannt.

Als Nächstes musste ich Bedingungen für die Spalten festlegen, die die Zahlen für die Umsätze 2019 und 2020 enthalten werden. Dazu habe ich wieder die Anweisung CASE WHEN verwendet. Für den Umsatz 2019 lautet die Bedingung, dass die Daten gleich dem 01.01.2019 oder größer/neuer, aber kleiner/älter als der 01.01.2020 sein müssen. Diese Spalte heißt revenue_2019.

Das gleiche Prinzip wird bei der Erstellung der Spalte revenue_2020 angewandt.

Um die Daten zu erhalten, müssen Sie drei Tabellen verbinden: card_transaction, card_number, und customer. Alle drei Tabellen sind mit Aliasen verknüpft.

Am Ende der Abfrage werden die Daten nach den Spalten NIN, first_name und last_name gruppiert, da Sie die Daten auf Kundenebene benötigen. Außerdem wird das Ergebnis nach dem Gesamtumsatz in absteigender Reihenfolge geordnet, um es ansehnlicher zu machen.

Hier ist die sexy Tabelle, die Sie bei den Datenfreaks beliebt machen wird:

Wie gefällt Ihnen die Verwendung von Fortgeschrittenes SQL Abfragen in der Finanzdatenanalyse?

Ich wollte Ihnen relativ fortgeschrittene SQL-Verfahren zeigen, mit denen Sie Finanzdaten analysieren und Berichte erstellen können. Das ist nicht alles, was SQL kann, aber vielleicht habe ich Ihr Interesse an einigen fortgeschrittenen SQL-Funktionen geweckt. Alle sechs Beispiele beruhen auf meinen Erfahrungen als Datenanalyst. Ich habe in meinem Leben schon viele solcher Berichte erstellt, und ich wäre begeistert gewesen, wenn ich all diese SQL-Möglichkeiten schon zu Beginn meiner Arbeit gekannt hätte; es wäre viel einfacher für mich gewesen.

Es ist nie zu spät, fortgeschrittenes SQL zu lernen, aber es ist immer besser, früher damit anzufangen. Wenn Sie die Möglichkeit haben, melden Sie sich für den Kurs Fortgeschrittenes SQL an; es wird sich in Zukunft auszahlen.