SQL-Pivot-Tabellen: Ein Schritt-für-Schritt-Tutorial

Sind Sie ein Datenanalyst, der seine Fähigkeiten verbessern möchte? Oder möchten Sie herausfinden, welche Tools Sie benötigen, um in dieser Rolle erfolgreich zu sein? Heute befassen wir uns mit einem der wichtigsten Tools für Datenanalysten: der SQL-Pivot-Tabelle.

Eine SQL-Pivot-Tabelle ist ein unverzichtbares Werkzeug, um Daten schnell zu ordnen und zusammenzufassen. Mit nur wenigen Codezeilen hilft sie Analysten, schnell und effektiv Muster und Trends in Daten zu erkennen. Dies wird erreicht, indem die Zeilen und Spalten der Tabelle gedreht oder geschwenkt werden und eine aggregierte Berechnung auf die zugrunde liegenden Daten angewendet wird.

Wenn Sie neu in der Erstellung von SQL-Berichten sind, besuchen Sie unseren Erstellen einfacher SQL-Berichte Kurs. Hier lernen Sie, wie man komplexe Abfragen in SQL schreibt. Zu den vielen Funktionen, die in diesem Kurs gelehrt werden, gehört die CASE WHEN Syntax, die für die Erstellung von Pivot-Tabellen unerlässlich ist.

Bevor wir uns mit Pivot-Tabellen befassen, sollten Sie unser Spickzettel für SQL zur Datenanalyse nicht aus den Augen verlieren. Sie können es als Auffrischung einiger der Funktionen verwenden, die wir besprechen werden.

Wie SQL-Pivot-Tabellen in der Datenanalyse funktionieren

SQL-Pivot-Tabellen sind ein nützliches Werkzeug, um große Datenmengen schnell zu reorganisieren, zusammenzufassen und zu analysieren. Vielleicht haben Sie bereits mit Tools wie Microsoft Excel und Google Sheets gearbeitet; diese bieten integrierte Pivot-Tabellenfunktionen, mit denen Sie solche Transformationen leicht durchführen können.

Lassen Sie uns ein Beispiel nehmen. Stellen Sie sich vor, Sie haben eine Tabelle, die die Musikstreaming-Daten eines Künstlers für verschiedene Musikplattformen und Länder anzeigt.

Tabelle:

Die Art und Weise, wie diese Daten angeordnet sind, erschwert den Vergleich, insbesondere wenn die Anzahl der Länder und Plattformen zunimmt.

Stellen Sie sich nun vor, wir könnten die Daten so strukturieren, dass die Streams für jedes Land nach Plattformen aufgeschlüsselt und zusammengerechnet werden. Dann wären wir in der Lage, Fragen zu beantworten wie "Welche Plattform ist in jedem Land am beliebtesten?" und "Auf welche Plattform und welches Land sollten wir uns stärker konzentrieren?" Diese neue Struktur könnte wie folgt aussehen:

Ergebnis:

Die neu formatierte Tabelle macht es viel einfacher, die Streams eines Künstlers über verschiedene Plattformen und Länder hinweg zu vergleichen. Sie können jetzt auf einen Blick sehen, welche Plattform in jedem Land am besten funktioniert - Spotify in Frankreich und Irland und SoundCloud in Deutschland. Das neue Format ermöglicht schnellere Analysen und Einblicke, indem es die geografische und plattformspezifische Leistung anzeigt, ohne dass Sie sich manuell durch Datenreihen wühlen müssen.

Die Bestandteile einer SQL-Pivot-Tabelle

Lassen Sie uns zunächst die Bausteine der Pivot-Tabelle anhand eines Beispiels erläutern. Zur Veranschaulichung verwenden wir die gleiche Tabelle wie im vorherigen Abschnitt:

• Zeilenbezeichner: Dies ist der erste Bestandteil; es handelt sich um eine Spalte, die aus Ihrem Datensatz ausgewählt wird, um die Zeilen der Pivot-Tabelle (d. h. die einzelnen Datensätze) darzustellen. Im obigen Beispiel haben wir die Spalte platform als Zeilenbezeichner verwendet, so dass jede Zeile die Angaben zu einer Plattform enthält.
• Spaltenbezeichner: Der zweite Bestandteil ist eine Spalte, die für den vertikalen Teil der Pivot-Tabelle ausgewählt wird. In unserem Beispiel haben wir die Spalte Land als Spaltenbezeichner verwendet; die Länder werden horizontal aufgelistet, wie Überschriften am oberen Rand der Ergebnistabelle.
• Aggregation: Nachdem wir nun den vertikalen und horizontalen Teil unserer Tabelle haben, ist die Aggregation der Bestandteil, der bestimmt, wie die zugrunde liegenden Daten in den Zeilen- und Spaltenbezeichnern zusammenwirken. Aggregationsfunktionen wie SUM(), AVG() und MAX() werden häufig verwendet. In unserem Beispiel haben wir SUM() verwendet, um die gesamten Streams für jede Plattform in jedem Land zu berechnen.
• Pivot-Tabelle: Die Pivot-Tabelle ist das letzte Gericht. Sie fasst die eingegebenen Daten zusammen, indem sie Zeilen mit einer Aggregationsfunktion in Spalten umwandelt. Durch die effektive Zusammenfassung der Daten hilft uns die Pivot-Tabelle, die analysierten Daten in einer strukturierteren Form zu visualisieren. Im obigen Beispiel sehen wir, dass die endgültige Ausgabe unserer Eingaben die Streams für jede Plattform in den in unserer ursprünglichen Tabelle aufgeführten Ländern anzeigt.

Native Pivot-Tabellen

Je nachdem, welche Datenbank Sie verwenden, verfügt diese möglicherweise über eine native Pivot-Tabelle . Das bedeutet im Wesentlichen, dass es eine integrierte Funktion gibt, mit der die Datenbank SQL-Pivot-Tabellen erstellen kann. SQL Server ist ein gutes Beispiel für eine Datenbank, die eine PIVOT() Funktion bietet.

Lassen Sie uns eine Pivot-Tabelle mit der SQL Server-Funktion PIVOT() erstellen, um Streaming-Daten zusammenzufassen. Die Daten, die wir verwenden werden, sind in einer Tabelle namens streams_table:

Abfrage:

SELECT platform, France, Ireland, Germany
FROM
(
    SELECT platform, country, streams
    FROM streams_table
) AS SourceTable
PIVOT
(
    SUM(streams)
    FOR country IN (France, Ireland, Germany)
) AS PivotTable;

Wir werden diese Abfrage aufschlüsseln. Wir beginnen mit der Unterabfrage.

1. Definieren Sie die Quelltabelle

SELECT platform, country, streams
FROM streams_table

In den Zeilen 4-6 (unmittelbar nach der offenen Klammer) wird die SourceTable durch eine Unterabfrage definiert. Die Quelltabelle hat drei Spalten, die in der Anweisung SELECT angegeben sind (platform, country und streams), die aus der ursprünglichen streams_table.

2. Umformung der Daten mithilfe von Pivot-Operationen

SUM(streams)
FOR country IN (France, Ireland, Germany)

Als Nächstes - innerhalb der Klammern der Funktion PIVOT() - definieren wir unsere PivotTable durch Anwendung der Aggregatfunktion SUM() auf die Spalte streams. Dadurch wird die Anzahl der Streams für jede Plattform summiert. Dann geben wir an, auf welche Spalte wir pivotieren wollen: country.

Beachten Sie, dass die Länderwerte ohne Anführungszeichen angegeben werden. Beachten Sie auch, dass in SQL Server Spaltennamen, die Leerzeichen oder Sonderzeichen enthalten oder mit einer Zahl beginnen, in eckige Klammern gesetzt werden müssen. Wenn wir eine Spalte mit Jahreszahlen hätten, würde sie wie folgt aussehen:

SUM(streams)
FOR stream_year IN ([2022], [2023])

3. Auswählen der endgültigen Spalten

SELECT platform, France, Ireland, Germany

Schließlich kehren wir zum Anfang der Abfrage zurück und wählen die Spalten für unsere Ausgabe aus. Dazu gehören die Spalte platform und die neu gebildeten Spalten für jedes Land.

Ergebnis:

Nicht alle SQL-Sprachen verfügen über integrierte PIVOT() Funktionen. Wenn diese Funktion in Ihrer Datenbank nicht verfügbar ist, gibt es eine andere einfache Technik, die wir anwenden können, um das Problem zu umgehen. Schauen wir sie uns an.

Erstellen von SQL-Pivots ohne native PIVOT()-Tabellen

Wie bereits erwähnt, verfügen nicht alle Datenbanken - einschließlich bekannter Datenbanken wie MySQL und PostgreSQL - über native Pivot-Funktionen. Vielleicht erinnern Sie sich, dass wir in unserer Einführung die CASE WHEN Syntax angesprochen haben. Mit dieser Syntax können wir genau dasselbe Ergebnis erzielen!

Da MySQL und PostgreSQL weit verbreitete Datenbanken sind, ist die CASE WHEN-Technik eine weit verbreitete Methode zur Erstellung von Pivot-Tabellen. Werfen wir einen Blick auf einige Beispiele:

Beispiel 1: Pivotierung von Streaming-Daten

Wir verwenden dasselbe Beispiel, um zu zeigen, wie der Ansatz CASE WHEN zu denselben Ergebnissen führen kann:

Abfrage:

SELECT platform,
    SUM(CASE WHEN country = 'France' THEN streams END) AS france_streams,
    SUM(CASE WHEN country = 'Ireland' THEN streams END) AS ireland_streams,
    SUM(CASE WHEN country = 'Germany' THEN streams END) AS germany_streams
FROM streams_table
GROUP BY platform;

Die resultierenden SQL-Pivot-Tabellen zeigen die gesamten Streams nach Plattform für die aufgeführten Länder.

• Zeilenbezeichner: Da wir die Spalte platform auswählen, erscheint jede Plattform als Spalte in der Ausgabe. Sie werden feststellen, dass wir auch platform in unsere GROUP BY aufnehmen, um unsere Plattformen in Gruppen zu unterteilen.
• Spaltenbezeichner: Um die Länder in den vertikalen Spalten anzugeben, verwenden wir die Struktur CASE WHEN country = 'France'. Wenn der Datenstrom aus Frankreich stammt, gibt die Anweisung CASE WHEN die Datenströme zurück. Sie werden dann mit der Funktion SUM() summiert. Wenn der Stream nicht aus Frankreich stammt, gibt CASE WHEN NULL zurück, und die Werte werden in der Summe nicht berücksichtigt, und die Abfrage wird mit der nächsten CASE WHEN fortgesetzt. Eine genauere Erläuterung finden Sie in unserem Artikel über die Verwendung von CASE WHEN in GROUP BY.
• Aggregation: Genau wie zuvor verwenden wir SUM() als Aggregationsfunktion. Die Logik unserer Case-Anweisung besagt, dass Ströme kumuliert werden, wenn das Land mit dem angegebenen Land übereinstimmt; andernfalls wird nichts kumuliert.

Zur Veranschaulichung verwenden wir dieselbe Tabelle, kehren aber die Zeilen- und Spaltenbezeichner um und sehen, was passiert. T

Abfrage:

SELECT country,
    SUM(CASE WHEN platform = 'Apple Music' THEN streams END) AS apple_music_streams,
    SUM(CASE WHEN platform = 'SoundCloud' THEN streams END) AS soundcloud_streams,
    SUM(CASE WHEN platform = 'Spotify' THEN streams END) AS spotify_streams
FROM streams_table
GROUP BY country;

Ergebnis:

Wie Sie sehen können, bleibt das Ergebnis der Aggregation gleich. Die einzige Änderung ist die Darstellung der Daten:

• Zeilenbezeichner: Diesmal verwenden wir country als Zeilenbezeichner, so dass jedes Land als horizontale Zeile im Ergebnis erscheint. Außerdem fügen wir country in die Klausel GROUP BY ein, um sicherzustellen, dass die Ergebnisse ordnungsgemäß nach Ländern gruppiert werden, was zu separaten Zeilen für die gesamten Streaming-Daten der einzelnen Länder führt.
• Spaltenbezeichner: Wir haben unsere Spaltenidentifikation in Plattform geändert, so dass die Plattformen als vertikale Spalten in der Ausgabe erscheinen. Dies wird erreicht durch die SUM(CASE WHEN platform =...)
• Aggregation: Ähnlich wie bei der letzten Abfrage sind die numerischen Ergebnisse die gleichen, da wir die gleiche Aggregationsmethode verwendet haben (SUM()).

Weitere Beispiele für die CASE WHEN mit SUM() Aggregation finden Sie in unserem Artikel How to Use CASE WHEN with SUM() in SQL.

Beispiel 2: Pivotierung von Kundendaten

Als Nächstes sehen wir uns an, wie Pivot-Tabellen uns bei der Ermittlung von Zielmarktsegmenten helfen können.

Die customer_table beschreibt Kundenverkaufsdaten mit vier Spalten: customer, age_category, country und purchases:

Abfrage:

SELECT age_category,
    SUM(CASE WHEN country = 'France' THEN purchases END) AS France,
    SUM(CASE WHEN country = 'Spain' THEN purchases END) AS Spain,
    SUM(CASE WHEN country = 'Italy' THEN purchases END) AS Italy
FROM customer_table
GROUP BY age_category;

Ergebnis:

Die resultierenden SQL-Pivot-Tabellen zeigen die Gesamteinkäufe nach Alterskategorie in Frankreich, Spanien und Italien, wo:

• Der Zeilenbezeichner ist die Spalte age_category.
• Der Spaltenbezeichner ist country, wobei France, Spain und Italy angegeben werden.
• Die Aggregatfunktion ist SUM().

Beispiel 3: Pivotierung von Operationsdaten

Sehen wir uns nun ein Beispiel dafür an, wie die Verwendung von SQL-Pivot-Tabellen potenzielle Engpässe oder Fehler in Vorgängen aufdecken kann.

Die folgende Tabelle, cake_baking_databeschreibt Bäckereidaten anhand von vier Spalten: order_number, stage, order_day und mins_taken:

Tabelle:

Angenommen, wir möchten die durchschnittliche Zeit ermitteln, die jede Aufgabe an jedem Tag benötigt. Hier ist die Abfrage, die wir verwenden würden.

Abfrage:

SELECT 
    order_day,
    AVG(CASE WHEN stage = 'Mixing' THEN mins_taken END) AS avg_mixing_time,
    AVG(CASE WHEN stage = 'Baking' THEN mins_taken END) AS avg_baking_time,
    AVG(CASE WHEN stage = 'Decorating' THEN mins_taken END) AS avg_decorating_time
FROM cake_baking_data
GROUP BY order_day;

Ergebnis:

Die resultierende Pivot-Tabelle zeigt die durchschnittlichen Zeiten für das Mischen, Backen und Dekorieren nach Auftragstagen an:

• Der Zeilenbezeichner ist die Spalte order_day.
• Der Spaltenbezeichner ist stage, wobei mixing, baking oder decorating angegeben wird.
• Die Aggregatfunktion ist AVG().

Wie Sie sehen, können Sie mit dem CASE WHEN Workaround in den gängigen Datenbanken MySQL und PostgreSQL bleiben und trotzdem die gleichen SQL-Pivot-Tabellen erreichen.

Ein letztes Wort zu SQL-Pivot-Tabellen

Zusammenfassend lässt sich sagen, dass es klar ist, warum SQL-Pivot-Tabellen so wichtig sind. Die Möglichkeit der Pivotierung von Daten ist ein wichtiges Werkzeug in Ihrem Werkzeugkasten für die Datenanalyse. Die Fähigkeit, Datensätze schnell und effizient umzuwandeln und zusammenzufassen, ermöglicht es Ihnen, Trends zu entdecken und intelligentere Entscheidungen zu treffen.

In diesem Artikel haben wir uns hauptsächlich auf das Pivoting für Datenanalysten konzentriert. Wenn Sie sich für eine Karriere in der Datenanalyse interessieren, empfehle ich Ihnen unseren Artikel 25 SQL-Interview-Fragen für Datenanalysten. Er wird Ihnen helfen zu verstehen, was Sie wissen müssen, um in diesem Bereich erfolgreich zu sein.

Der beste Weg, Ihre SQL-Kenntnisse zu verbessern, besteht darin, zu üben, zu üben, zu üben! Wir empfehlen unseren Kurs Erstellen einfacher SQL-Berichte Hier können Sie nicht nur üben, was wir heute gelernt haben, sondern Sie lernen auch, wie Sie aussagekräftige Berichte erstellen können. Probieren Sie es aus und verbessern Sie Ihre SQL-Kenntnisse in Bezug auf Pivot-Tabellen!