Testa den här anteckningsboken i databaser

UPPDATERAD 11/10/2018

Pivot introducerades först i Apache Spark 1.6 som en ny DataFrame-funktion som tillåter användare att rotera ett tabellvärderat uttryck genom att förvandla de unika värdena från en kolumn till enskilda kolumner.

Apache Spark 2.4-utgåvan utökar också denna kraftfulla funktionalitet för att svänga data till våra SQL-användare. I den här bloggen, med hjälp av temperaturinspelningar i Seattle, visar vi hur vi kan använda den här vanliga SQL Pivot-funktionen för att uppnå komplexa datatransformationer.

Undersök sommartemperaturer med Pivot

I sommar i Seattle steg temperaturerna till obehagliga nivåer och toppade till höga 80- och 90-talet i nio dagar i juli.

Datum	Temp (° F)
07-22-2018	86
23-23-2018	90
07-24-2018	91
07-25-2018	92
27-7-2018	92
27-7-2018	88
07-28-2018	85
07-29-2018	94
30-30-2018	89

Antag att vi vill utforska eller undersöka om det fanns en historisk träning d i stigande kvicksilvernivåer. Ett intuitivt sätt att undersöka och presentera dessa siffror är att ha månader som kolumner och sedan varje års månatliga genomsnitt på en enda rad. På det sättet blir det enkelt att jämföra temperaturerna både horisontellt, mellan intilliggande månader och vertikalt, mellan olika år.

Nu när vi har stöd för PIVOT syntax i Spark SQL kan vi uppnå detta med följande SQL-fråga.

Ovanstående fråga ger ett resultat som:

Tja, ser ut som det finns bra år och dåliga år. År 2016 verkar ett ganska energivänligt år.

Pivotering i SQL

Låt oss titta närmare på denna fråga för att förstå hur den fungerar. Först måste vi specificera FROM -satsen, som är ingången till pivoten, med andra ord tabellen eller underfrågan baserad på vilken pivoten kommer att utföras. I vårt fall är vi bekymrade över åren, månaderna och de höga temperaturerna, så det är de fält som visas i underfrågan.

Låt oss för det andra överväga en annan viktig del av frågan, PIVOT -satsen. Det första argumentet i PIVOT -satsen är en aggregerad funktion och kolumnen som ska aggregeras. Vi anger sedan pivotkolumnen i FOR underklausul som det andra argumentet, följt av IN -operatören som innehåller pivotkolumnvärdena det sista argumentet.

Pivotkolumnen är den punkt kring vilken tabellen kommer att roteras och pivotkolumnvärdena kommer att transponeras till kolumner i utmatningstabellen. IN klausulen låter dig också ange ett alias för varje pivotvärde, vilket gör det enkelt att skapa mer meningsfulla kolumnnamn.

En viktig idé om pivot är att den utför en grupperad aggregering baserad på en lista med implicita group-by kolumner tillsammans med svängkolumnen. De implicita group-by -kolumnerna är kolumner från FROM -satsen som inte visas i någon aggregerad funktion eller som pivotkolumn.

I ovanstående fråga, där pivotkolumnen är kolumnmånaden och den implicita group-by -kolumnen är kolumnåret, uttrycket avg(temp) kommer att aggregeras på varje distinkt värdepar (year, month), där månaden är lika med ett av de angivna pivotkolumnvärdena. Som ett resultat mappas vart och ett av dessa aggregerade värden till motsvarande cell i rad year och column månad.

Det är värt att notera att på grund av denna implicita group-by måste vi se till att alla kolumner som vi inte vill vara en del av pivotutmatningen ska utelämnas från FROM klausul, annars skulle frågan ge oönskade resultat.

Ange flera sammanlagda uttryck

Ovanstående exempel visar endast ett samlat uttryck som används i PIVOT -satsen, medan användarna faktiskt kan ange flera sammanlagda uttryck om det behövs. Med väderuppgifterna ovan kan vi återigen lista de högsta höga temperaturerna tillsammans med de genomsnittliga höga temperaturerna mellan juni och september.

Om det finns flera sammanlagda uttryck kommer kolumnerna att vara den kartesiska produkten av svängningen kolumnvärden och aggregerade uttryck, med namnen som <value>_<aggExpr>.

Gruppera kolumner kontra pivotkolumner

Antag att vi vill inkludera låga temperaturer i vår utforskning av temperaturtrender från denna tabell över dagliga låga temperaturer:

Datum	Temp (° F)
…	…
08-01-2018	59
08-02-2018	58
08-03-2018	59
08-04-2018	58
08-05-2018	59
08-06-2018	59
…	…

För att kombinera denna tabell med föregående tabell över dagliga höga temperaturer kan vi gå med i dessa två tabeller i kolumnen ”Datum”. Eftersom vi kommer att använda pivot, som utför gruppering på datumen, kan helt enkelt sammanfoga de två tabellerna med UNION ALL. Och du kommer att se senare, detta tillvägagångssätt ger oss också mer flexibilitet:

Låt oss nu prova vår pivotfråga med den nya kombinerade tabellen:

Som ett resultat får vi genomsnittet högt och genomsnittligt lågt för varje månad de senaste fyra åren i en tabell. Observera att vi måste inkludera kolumnen flag i pivotfrågan, annars skulle uttrycket avg(temp) baseras på en blandning av hög och låg temperaturer.

Du kanske har lagt märke till att vi nu har två rader för varje år, en för höga temperaturer och en för låga temperaturer. Det beror på att vi har inkluderat ytterligare en kolumn, flag, i pivotingången, som i sin tur blir en annan implicit grupperingskolumn förutom den ursprungliga kolumnen year.

Alternativt, istället för att vara en grupperingskolumn, kan flag också fungera som en svängkolumn. Så nu har vi två pivotkolumner, month och flag:

Denna fråga ger oss en annan layout av samma data, med en rad för varje år, men två kolumner för varje månad.

Vad är nästa

För att köra frågeexemplen som används i den här bloggen, se pivot-SQL-exemplen i den här medföljande anteckningsboken.

Tack till Apache Spark-community-bidragsgivarna för deras bidrag!