Wypróbuj ten notatnik w Databricks

ZAKTUALIZOWANO 10.10.2018

Pivot został po raz pierwszy wprowadzony w Apache Spark 1.6 jako nowa funkcja DataFrame, która umożliwia użytkownikom obracanie wyrażeń wycenianych w tabeli przez zamianę unikalnych wartości z jednej kolumny na osobne kolumny.

Wersja Apache Spark 2.4 rozszerza tę potężną funkcjonalność przestawiania danych również na naszych użytkowników SQL. Na tym blogu, korzystając z nagrań temperatur w Seattle, pokażemy, jak możemy użyć tej typowej funkcji SQL Pivot, aby uzyskać złożone przekształcenia danych.

Badanie letnich temperatur za pomocą Pivota

Tego lata w Seattle temperatury wzrosły do nieprzyjemnych poziomów, osiągając szczytowe poziomy 80 i 90 przez dziewięć dni w lipcu.

Data	Temperatura (° F)
22.07.2018	86
23.07.2018	90
24.07.2018	91
25.07.2018	92
26.07.2018	92
27.07.2018	88
28.07.2018	85
29.07.2018	94
30.07.2018	89

Załóżmy, że chcemy zbadać lub zbadać, czy istniał historyczny tren d we wzroście poziomów rtęci. Intuicyjnym sposobem sprawdzenia i przedstawienia tych liczb jest umieszczenie w kolumnach miesięcy, a następnie średnich miesięcznych wartości szczytowych każdego roku w jednym wierszu. W ten sposób łatwo będzie porównać temperatury zarówno w poziomie, między sąsiednimi miesiącami, jak i w pionie w różnych latach.

Teraz, gdy mamy obsługę składni PIVOT w Spark SQL możemy to osiągnąć za pomocą następującego zapytania SQL.

Powyższe zapytanie da wynik taki:

Cóż, wygląda na to, że są dobre i złe lata. Rok 2016 wydaje się raczej energooszczędny.

Zmiana w SQL

Przyjrzyjmy się bliżej temu zapytaniu, aby zrozumieć, jak to działa. Najpierw musimy określić klauzulę FROM, która jest danymi wejściowymi przestawienia, innymi słowy, tabelą lub podzapytaniem, na podstawie którego zostanie wykonane przestawienie. W naszym przypadku niepokoją nas lata, miesiące i wysokie temperatury, więc to są pola, które pojawiają się w zapytaniu podrzędnym.

Po drugie, rozważmy inną ważną część zapytania, klauzula PIVOT. Pierwszy argument klauzuli PIVOT to funkcja agregująca i kolumna, która ma być agregowana. Następnie określamy kolumnę przestawną w klauzuli FOR jako drugi argument, po którym następuje operator IN zawierający wartości kolumny przestawnej jako ostatni argument.

Kolumna przestawna to punkt, wokół którego zostanie obrócona tabela, a wartości kolumny przestawnej zostaną przeniesione do kolumn w tabeli wyjściowej. Klauzula IN umożliwia również określenie aliasu dla każdej wartości przestawnej, co ułatwia generowanie bardziej znaczących nazw kolumn.

Ważną ideą dotyczącą przestawienia jest to, że wykonuje agregację grupową na podstawie listy niejawnych kolumn group-by razem z kolumną przestawną. Niejawne kolumny group-by to kolumny z klauzuli FROM, które nie pojawiają się w żadnej funkcji agregującej ani jako kolumna przestawna.

W powyższym zapytaniu, gdzie kolumna przestawna to miesiąc kolumny, a niejawna kolumna group-by to rok kolumny, wyrażenie avg(temp) zostaną zagregowane dla każdej odrębnej pary wartości (year, month), gdzie miesiąc jest równy jednej z określonych wartości kolumny przestawnej. W rezultacie każda z tych zagregowanych wartości zostanie zamapowana na odpowiadającą jej komórkę wiersza year i column miesiąca.

Warto zauważyć, że z powodu tego niejawnego group-by, musimy upewnić się, że każda kolumna, której nie chcemy być częścią wyniku przestawnego, powinna zostać pominięta w FROM, w przeciwnym razie zapytanie dałoby niepożądane wyniki.

Określanie wielu wyrażeń agregujących

Powyższy przykład pokazuje tylko jedno używane wyrażenie agregujące w klauzuli PIVOT, podczas gdy w rzeczywistości użytkownicy mogą w razie potrzeby określić wiele wyrażeń agregujących. Ponownie, z powyższymi danymi pogodowymi, możemy wymienić maksymalne wysokie temperatury wraz ze średnimi wysokimi temperaturami między czerwcem a wrześniem.

W przypadku wielu wyrażeń zagregowanych kolumny będą stanowić iloczyn kartezjański osi wartości kolumn i wyrażenia agregujące o nazwach <value>_<aggExpr>.

Grupowanie kolumn w porównaniu z kolumnami obrotowymi

Teraz przypuśćmy, że chcemy uwzględnić niskie temperatury w naszej eksploracji trendów temperaturowych z poniższej tabeli dziennych niskich temperatur:

Data	Temp (° F)
…	…
08-01-2018	59
08-02-2018	58
08.03.2018	59
08.04.2018	58
08.05.2018	59
06.08.2018	59
…	…

Aby połączyć tę tabelę z poprzednią tabelą dziennych wysokich temperatur, moglibyśmy połączyć te dwie tabele w kolumnie „Data”. Jednak ponieważ zamierzamy użyć tabeli przestawnej, która wykonuje grupowanie według dat, może po prostu połączyć dwie tabele za pomocą UNION ALL. Zobaczysz później, takie podejście zapewnia nam również większą elastyczność:

Teraz wypróbujmy nasze zapytanie przestawne z nową połączoną tabelą:

W rezultacie otrzymujemy w jednej tabeli średnią najwyższą i średnią najniższą dla każdego miesiąca z ostatnich 4 lat. Zwróć uwagę, że w zapytaniu przestawnym musimy uwzględnić kolumnę flag, w przeciwnym razie wyrażenie avg(temp) będzie oparte na połączeniu wysokich i niskich wartości Temperatury.

Być może zauważyłeś, że teraz mamy dwa rzędy na każdy rok, jeden dla wysokich temperatur, a drugi dla niskich temperatur. Dzieje się tak, ponieważ zawarliśmy jeszcze jedną kolumnę flag w danych wejściowych przestawnych, która z kolei staje się kolejną niejawną kolumną grupującą oprócz oryginalnej kolumny year.

Alternatywnie, zamiast być kolumną grupującą, flag może również służyć jako kolumna przestawna. Mamy więc teraz dwie kolumny przestawne, month i flag:

To zapytanie przedstawia nam inny układ te same dane, z jednym wierszem na każdy rok, ale z dwiema kolumnami na każdy miesiąc.

Co dalej

Aby uruchomić przykłady zapytań użyte na tym blogu, zapoznaj się z przykładami przestawnego SQL w dołączonym notatniku.

Podziękowania dla współtwórców społeczności Apache Spark za ich wkład!