SQL-Pivotierung: Konvertieren von Zeilen in Spalten

Probieren Sie dieses Notebook in Databricks aus

Sehen Sie sich das E-Book Warum das Data Lakehouse Ihr nächstes Data Warehouse ist an, um die Funktionsweise der Databricks Lakehouse Platform kennenzulernen.

AKTUALISIERT AM 10.11.2018

Pivot wurde erstmals in Apache Spark 1.6 als neue DataFrame-Funktion eingeführt, die es Benutzern ermöglicht, einen Tabellenausdruck zu drehen, indem die eindeutigen Werte aus einer Spalte in einzelne Spalten umgewandelt werden.

Die Version Apache Spark 2.4 erweitert diese leistungsstarke Funktionalität der Datendrehung auch auf unsere SQL-Benutzer. In diesem Blog zeigen wir anhand von Temperaturaufzeichnungen in Seattle, wie wir diese gängige SQL-Pivot-Funktion verwenden können, um komplexe Datentransformationen zu erzielen.

Untersuchung der Sommertemperaturen mit Pivot

In diesem Sommer stiegen die Temperaturen in Seattle auf unangenehme Werte und erreichten an neun Tagen im Juli Höchstwerte von über 26 bis 32 Grad Celsius.

Angenommen, wir möchten untersuchen, ob es einen historischen Trend bei steigenden Quecksilberwerten gab. Eine intuitive Möglichkeit, diese Zahlen zu untersuchen und darzustellen, besteht darin, die Monate als Spalten und dann die monatlichen Durchschnittswerte jedes Jahres in einer einzigen Zeile darzustellen. Auf diese Weise ist es einfach, die Temperaturen sowohl horizontal, zwischen benachbarten Monaten, als auch vertikal, zwischen verschiedenen Jahren, zu vergleichen.

Nachdem wir nun die PIVOT-Syntax in Spark SQL unterstützen, können wir dies mit der folgenden SQL-Abfrage erreichen.

Die obige Abfrage erzeugt ein Ergebnis wie folgt:

Nun, es sieht so aus, als gäbe es gute und schlechte Jahre. Das Jahr 2016 scheint ein eher energiesparendes Jahr zu sein.

Pivotieren in SQL

Werfen wir einen genaueren Blick auf diese Abfrage, um zu verstehen, wie sie funktioniert. Zuerst müssen wir die FROM-Klausel angeben, die die Eingabe des Pivots ist, mit anderen Worten, die Tabelle oder Unterabfrage, auf der die Pivotierung durchgeführt wird. In unserem Fall interessieren wir uns für die Jahre, die Monate und die hohen Temperaturen, also sind dies die Felder, die in der Unterabfrage erscheinen.

Zweitens betrachten wir einen weiteren wichtigen Teil der Abfrage, die PIVOT-Klausel. Das erste Argument der PIVOT-Klausel ist eine Aggregatfunktion und die zu aggregierende Spalte. Anschließend geben wir die Pivotspalte in der FOR-Unterklausel als zweites Argument an, gefolgt vom Operator IN, der die Pivotspaltenwerte als letztes Argument enthält.

Die Pivotspalte ist der Punkt, um den die Tabelle gedreht wird, und die Pivotspaltenwerte werden in Spalten in der Ausgabetabelle transponiert. Mit der IN-Klausel können Sie auch einen Alias für jeden Pivotwert angeben, wodurch es einfach ist, aussagekräftigere Spaltennamen zu generieren.

Eine wichtige Idee bei Pivot ist, dass es eine gruppierte Aggregation basierend auf einer Liste von impliziten group-by-Spalten zusammen mit der Pivotspalte durchführt. Die impliziten group-by-Spalten sind Spalten aus der FROM-Klausel, die in keiner Aggregatfunktion oder als Pivotspalte vorkommen.

In der obigen Abfrage, wobei die Pivotspalte die Spalte month und die implizite group-by-Spalte die Spalte year ist, wird der Ausdruck avg(temp) für jedes eindeutige Wertepaar von (year, month) aggregiert, wobei month einem der angegebenen Pivotspaltenwerte entspricht. Infolgedessen wird jeder dieser aggregierten Werte seiner entsprechenden Zelle der Zeile year und der column month zugeordnet.

Es ist erwähnenswert, dass wir aufgrund dieser impliziten group-by sicherstellen müssen, dass jede Spalte, die nicht Teil der Pivot-Ausgabe sein soll, aus der FROM-Klausel entfernt wird, da die Abfrage sonst unerwünschte Ergebnisse liefern würde.

Angeben mehrerer Aggregatausdrücke

Das obige Beispiel zeigt nur einen Aggregatausdruck, der in der PIVOT-Klausel verwendet wird, während Benutzer bei Bedarf tatsächlich mehrere Aggregatausdrücke angeben können. Auch hier können wir mit den obigen Wetterdaten die maximalen Höchsttemperaturen zusammen mit den durchschnittlichen Höchsttemperaturen zwischen Juni und September auflisten.

Im Fall von mehreren Aggregatausdrücken sind die Spalten das kartesische Produkt der Pivotspaltenwerte und der Aggregatausdrücke, wobei die Namen _ lauten.

Gruppierungsspalten vs. Pivotspalten

Nehmen wir nun an, wir möchten die niedrigen Temperaturen in unsere Untersuchung der Temperaturtrends aus dieser Tabelle der täglichen niedrigen Temperaturen einbeziehen:

Um diese Tabelle mit der vorherigen Tabelle der täglichen Höchsttemperaturen zu kombinieren, könnten wir diese beiden Tabellen über die Spalte „Datum“ verknüpfen. Da wir jedoch Pivot verwenden werden, das eine Gruppierung nach Datum durchführt, können wir die beiden Tabellen einfach mit UNION ALL verketten. Und Sie werden später sehen, dass dieser Ansatz uns auch mehr Flexibilität bietet:

Versuchen wir nun unsere Pivot-Abfrage mit der neuen kombinierten Tabelle:

Als Ergebnis erhalten wir die durchschnittlichen Höchst- und Tiefstwerte für jeden Monat der letzten 4 Jahre in einer Tabelle. Beachten Sie, dass wir die Spalte flag in die Pivot-Abfrage aufnehmen müssen, da der Ausdruck avg(temp) sonst auf einer Mischung aus hohen und niedrigen Temperaturen basieren würde.

Sie haben vielleicht bemerkt, dass wir jetzt zwei Zeilen für jedes Jahr haben, eine für die hohen Temperaturen und die andere für die niedrigen Temperaturen. Das liegt daran, dass wir eine weitere Spalte, flag, in die Pivot-Eingabe aufgenommen haben, die wiederum zu einer weiteren impliziten Gruppierungsspalte zusätzlich zur ursprünglichen Spalte year wird.

Alternativ kann die flag, anstatt eine Gruppierungsspalte zu sein, auch als Pivotspalte dienen. Wir haben also jetzt zwei Pivotspalten, month und flag:

Diese Abfrage präsentiert uns ein anderes Layout derselben Daten, mit einer Zeile für jedes Jahr, aber zwei Spalten für jeden Monat.

Wie geht es weiter?

Um die in diesem Blog verwendeten Abfragebeispiele auszuführen, überprüfen Sie bitte die Pivot-SQL-Beispiele in diesem begleitenden Notebook.

Vielen Dank an die Mitwirkenden der Apache Spark-Community für ihre Beiträge!

(Dieser Blogbeitrag wurde mit KI-gestützten Tools übersetzt.) Originalbeitrag