Probieren Sie dieses Notizbuch in Databricks aus

AKTUALISIERT 11/10/2018

Pivot wurde erstmals in Apache Spark 1.6 als neue DataFrame-Funktion eingeführt, mit der Benutzer einen Ausdruck mit Tabellenwert drehen können, indem sie die eindeutigen Werte aus einer Spalte in einzelne Spalten umwandeln.

Die Version Apache Spark 2.4 erweitert diese leistungsstarke Funktionalität zum Schwenken von Daten auch auf unsere SQL-Benutzer. In diesem Blog zeigen wir anhand von Temperaturaufzeichnungen in Seattle, wie wir mit dieser allgemeinen SQL Pivot-Funktion komplexe Datentransformationen erzielen können.

Untersuchen der Sommertemperaturen mit Pivot

In diesem Sommer In Seattle stiegen die Temperaturen im Juli neun Tage lang auf ein unangenehmes Niveau und erreichten einen Höchststand von 80 und 90.

Angenommen, wir möchten untersuchen oder untersuchen, ob es einen historischen Tren gibt d bei steigendem Quecksilbergehalt. Eine intuitive Möglichkeit, diese Zahlen zu untersuchen und darzustellen, besteht darin, Monate als Spalten und dann die monatlichen Durchschnittshochs jedes Jahres in einer einzigen Zeile zu haben. Auf diese Weise ist es einfach, die Temperaturen sowohl horizontal, zwischen benachbarten Monaten als auch vertikal zwischen verschiedenen Jahren zu vergleichen.

Jetzt, da wir die Syntax PIVOT unterstützen In Spark SQL können wir dies mit der folgenden SQL-Abfrage erreichen.

Die obige Abfrage führt zu einem Ergebnis wie:

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

Pivoting in SQL

Schauen wir uns diese Abfrage genauer an, um zu verstehen, wie sie funktioniert. Zuerst müssen wir die FROM -Klausel angeben, die die Eingabe des Pivots ist, dh die Tabelle oder Unterabfrage, auf deren Grundlage das Pivot ausgeführt wird. In unserem Fall sind wir besorgt über die Jahre, die Monate und die hohen Temperaturen. Dies sind also die Felder, die in der Unterabfrage angezeigt werden.

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

Die Pivot-Spalte ist der Punkt, um den die Tabelle gedreht wird, und die Pivot-Spaltenwerte werden in Spalten in der Ausgabetabelle transponiert. Mit der Klausel IN können Sie auch einen Alias für jeden Pivot-Wert angeben, um auf einfachere Weise aussagekräftigere Spaltennamen zu generieren.

Eine wichtige Idee zu Pivot ist die folgende Es führt eine gruppierte Aggregation basierend auf einer Liste impliziter group-by -Spalten zusammen mit der Pivot-Spalte durch. Die impliziten group-by -Spalten sind Spalten aus der FROM -Klausel, die in keiner Aggregatfunktion oder als Pivot-Spalte erscheinen.

In der obigen Abfrage ist der Ausdruck avg(temp) div, wobei die Pivot-Spalte der Spaltenmonat und die implizite group-by -Spalte das Spaltenjahr ist > wird für jedes einzelne Wertepaar von (year, month) aggregiert, wobei month einem der angegebenen Pivot-Spaltenwerte entspricht. Infolgedessen wird jeder dieser aggregierten Werte in die entsprechende Zelle der Zeilen year und column month.

Es ist erwähnenswert, dass wir aufgrund dieses impliziten group-by sicherstellen müssen, dass jede Spalte, die nicht Teil der Pivot-Ausgabe sein möchte, aus dem Feld weggelassen wird FROM -Klausel, andernfalls würde die Abfrage zu unerwünschten Ergebnissen führen.

Angeben mehrerer aggregierter Ausdrücke

Das obige Beispiel zeigt, dass nur ein aggregierter Ausdruck verwendet wird In der Klausel PIVOT können Benutzer bei Bedarf mehrere aggregierte Ausdrücke angeben. Mit den obigen Wetterdaten können wir die maximalen Höchsttemperaturen zusammen mit den durchschnittlichen Höchsttemperaturen zwischen Juni und September wieder auflisten.

Bei mehreren aggregierten Ausdrücken sind die Spalten das kartesische Produkt des Pivots Spaltenwerte und die aggregierten Ausdrücke mit den Namen <value>_<aggExpr>.

Gruppieren von Spalten im Vergleich zu Pivot-Spalten

Nehmen wir nun an, wir möchten niedrige 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önnen wir diese beiden Tabellen in der Spalte „Datum“ verknüpfen. Da wir jedoch den Pivot verwenden, der die Gruppierung der Daten durchführt, werden wir kann die beiden Tabellen einfach mit UNION ALL. 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 das durchschnittliche Hoch und das durchschnittliche Tief 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 sonst der Ausdruck avg(temp) auf einer Mischung aus high und low basiert Temperaturen.

Sie haben vielleicht bemerkt, dass wir jetzt zwei Reihen für jedes Jahr haben, eine für die hohen Temperaturen und die andere für die niedrigen Temperaturen. Dies liegt daran, dass wir eine weitere Spalte, flag, in die Pivot-Eingabe aufgenommen haben, die neben der ursprünglichen Spalte year.

Alternativ kann die flag auch als Pivot-Spalte dienen, anstatt eine Gruppierungsspalte zu sein. Jetzt haben wir zwei Pivot-Spalten: month und flag:

Diese Abfrage zeigt uns ein anderes Layout von Dieselben 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 zugehörigen Notizbuch.

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