Prøv denne notatblokken i Databricks

OPPDATERT 11/10/2018

Pivot ble først introdusert i Apache Spark 1.6 som en ny DataFrame-funksjon som lar brukerne rotere et tabellverdig uttrykk ved å gjøre de unike verdiene fra en kolonne til individuelle kolonner.

Apache Spark 2.4-utgivelsen utvider også denne kraftige funksjonaliteten til å svinge data til våre SQL-brukere. I denne bloggen, ved bruk av temperaturopptak i Seattle, viser vi hvordan vi kan bruke denne vanlige SQL Pivot-funksjonen for å oppnå komplekse datatransformasjoner.

Undersøk sommertemperaturer med Pivot

Denne sommeren i Seattle steg temperaturene til ubehagelige nivåer og toppet seg til høye 80- og 90-tallet i ni dager i juli.

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

Anta at vi vil utforske eller undersøke om det var en historisk tren d i økende kvikksølvnivå. En intuitiv måte å undersøke og presentere disse tallene på er å ha måneder som kolonnene og deretter hvert års månedlige gjennomsnittlige høyder i en enkelt rad. På den måten vil det være enkelt å sammenligne temperaturene både horisontalt, mellom tilstøtende måneder og vertikalt, mellom forskjellige år.

Nå som vi har støtte for PIVOT syntaks i Spark SQL kan vi oppnå dette med følgende SQL-spørring.

Spørringen ovenfor vil gi et resultat som:

Vel, ser ut som det er gode år og dårlige år. Året 2016 virker et ganske energivennlig år.

Sving i SQL

La oss se nærmere på dette spørsmålet for å forstå hvordan det fungerer. Først må vi spesifisere FROM -satsen, som er inngangen til pivoten, med andre ord tabellen eller underforespørselen basert på hvilken pivoteringen vil bli utført. I vårt tilfelle er vi bekymret for årene, månedene og de høye temperaturene, så det er feltene som vises i delspørringen.

For det andre, la oss vurdere en annen viktig del av spørringen, PIVOT klausulen. Det første argumentet i PIVOT -setningen er en samlet funksjon og kolonnen som skal aggregeres. Vi spesifiserer deretter pivotkolonnen i FOR underklausul som det andre argumentet, etterfulgt av IN -operatøren som inneholder pivot-kolonneverdiene som det siste argumentet.

Pivot-kolonnen er punktet rundt hvilket tabellen skal roteres, og pivot-kolonneverdiene vil bli transponert til kolonner i utgangstabellen. IN -klausulen lar deg også spesifisere et alias for hver pivotverdi, noe som gjør det enkelt å generere mer meningsfulle kolonnenavn.

En viktig ide om pivot er at den utfører en gruppert aggregering basert på en liste over implisitte group-by kolonner sammen med pivotkolonnen. De implisitte group-by -kolonnene er kolonner fra FROM -satsen som ikke vises i noen samlet funksjon eller som pivotkolonnen.

I den ovennevnte spørringen, med pivotkolonnen som kolonnemåned og den implisitte group-by -kolonnen som kolonneåret, er uttrykket avg(temp) vil bli samlet på hvert distinkt verdipar av (year, month), der måneden tilsvarer en av de angitte pivotkolonneverdiene. Som et resultat vil hver av disse samlede verdiene bli kartlagt i den tilsvarende cellen i rad year og column måned.

Det er verdt å merke seg at på grunn av denne implisitte group-by, må vi sørge for at enhver kolonne som vi ikke ønsker å være en del av pivotutgangen, skal utelates fra FROM klausul, ellers ville spørringen gi uønskede resultater.

Spesifisere flere samlede uttrykk

Eksemplet ovenfor viser bare ett samlet uttrykk som brukes i PIVOT -klausulen, mens brukerne faktisk kan spesifisere flere samlede uttrykk om nødvendig. Igjen, med værdataene ovenfor, kan vi liste opp de høyeste høye temperaturene sammen med de gjennomsnittlige høye temperaturene mellom juni og september.

Ved flere samlede uttrykk vil kolonnene være det kartesiske produktet av dreietappen kolonneverdier og samlede uttrykk, med navnene som <value>_<aggExpr>.

Gruppering av kolonner kontra pivotkolonner

Anta at vi vil inkludere lave temperaturer i utforskingen av temperaturtrender fra denne tabellen over daglige lave temperaturer:

Dato	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
…	…

For å kombinere denne tabellen med den forrige tabellen over daglige høye temperaturer, kan vi bli med i disse to tabellene i kolonnen «Dato». Siden vi skal bruke pivot, som utfører gruppering på datoene, vil vi kan bare sammenkoble de to tabellene ved hjelp av UNION ALL. Og du vil se senere, denne tilnærmingen gir oss også mer fleksibilitet:

La oss nå prøve vår pivot-spørring med den nye kombinerte tabellen:

Som et resultat får vi gjennomsnittet høyt og gjennomsnittlig lavt for hver måned de siste 4 årene i en tabell. Merk at vi må inkludere kolonnen flag i pivot-spørringen, ellers ville uttrykket avg(temp) være basert på en blanding av høyt og lavt temperaturer.

Du har kanskje lagt merke til at nå har vi to rader for hvert år, en for høye temperaturer og den andre for lave temperaturer. Det er fordi vi har tatt med en kolonne til, flag, i pivotinngangen, som igjen blir en annen implisitt grupperingskolonne i tillegg til den originale kolonnen year.

Alternativt, i stedet for å være en grupperingskolonne, kan flag også tjene som en pivotkolonne. Så nå har vi to pivotkolonner, month og flag:

Dette spørsmålet gir oss et annet oppsett av de samme dataene, med en rad for hvert år, men to kolonner for hver måned.

Hva er neste

For å kjøre spørreeksemplene som brukes i denne bloggen, vennligst sjekk pivot SQL-eksemplene i denne medfølgende notatblokken.

Takk til bidragsyterne fra Apache Spark-fellesskapet for deres bidrag!