Prøv denne notesbog i databaser

OPDATERET 11/10/2018

Pivot blev først introduceret i Apache Spark 1.6 som en ny DataFrame-funktion, der giver brugerne mulighed for at rotere et tabelværdiansat udtryk ved at omdanne de unikke værdier fra en kolonne til individuelle kolonner.

Apache Spark 2.4-udgivelsen udvider også denne kraftfulde funktionalitet til at dreje data til vores SQL-brugere. I denne blog viser vi temperaturoptegnelser i Seattle, hvordan vi kan bruge denne fælles SQL Pivot-funktion til at opnå komplekse datatransformationer.

Undersøgelse af sommertemperaturer med Pivot

Denne sommer i Seattle steg temperaturerne til ubehagelige niveauer og toppede til høje 80’ere og 90 i ni dage i juli.

Dato	Temp (° F)
07-22-2018	86
07-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

Antag, at vi vil udforske eller undersøge, om der var en historisk træning d i stigende kviksølvniveauer. En intuitiv måde at undersøge og præsentere disse tal på er at have måneder som kolonner og derefter hvert års månedlige gennemsnitlige højder i en enkelt række. På den måde vil det være let at sammenligne temperaturerne både vandret, mellem tilstødende måneder og lodret mellem forskellige år.

Nu hvor vi har understøttelse af PIVOT syntaks i Spark SQL kan vi opnå dette med følgende SQL-forespørgsel.

Ovenstående forespørgsel giver et resultat som:

Nå ser det ud til at der er gode år og dårlige år. Året 2016 ser ud til at være et temmelig energivenligt år.

Drejning i SQL

Lad os se nærmere på denne forespørgsel for at forstå, hvordan den fungerer. Først skal vi specificere FROM -sætningen, som er input af drejningen, med andre ord tabellen eller underforespørgslen, som drejningen skal udføres på. I vores tilfælde er vi bekymrede over årene, månederne og de høje temperaturer, så det er de felter, der vises i underforespørgslen.

Lad os for det andet overveje en anden vigtig del af forespørgslen, PIVOT -klausulen. Det første argument i PIVOT -sætningen er en samlet funktion og den kolonne, der skal aggregeres. Vi specificerer derefter drejekolonnen i FOR underklausul som det andet argument efterfulgt af IN -operatoren, der indeholder drejekolonneværdierne det sidste argument.

Drejekolonnen er det punkt, omkring hvilket tabellen roteres, og omdrejningskolonnens værdier transponeres til kolonner i outputtabellen. IN -klausulen giver dig også mulighed for at specificere et alias for hver pivotværdi, hvilket gør det let at generere mere meningsfulde kolonnenavne.

En vigtig idé om pivot er, at den udfører en grupperet sammenlægning baseret på en liste over implicitte group-by kolonner sammen med pivotkolonnen. De implicitte group-by kolonner er kolonner fra FROM -sætningen, der ikke vises i nogen samlet funktion eller som omdrejningskolonnen.

I ovenstående forespørgsel, hvor pivot-kolonnen er kolonnemåneden og den implicitte group-by -kolonne er kolonneåret, udtrykket avg(temp) aggregeres på hvert særskilt værdipar af (year, month), hvor måned svarer til en af de angivne drejekolonneværdier. Som et resultat kortlægges hver af disse aggregerede værdier i sin tilsvarende celle i række year og column måned.

Det er værd at bemærke, at på grund af denne implicitte group-by skal vi sørge for, at enhver kolonne, som vi ikke ønsker at være en del af drejeproduktionen, skal udelades fra FROM klausul, ellers ville forespørgslen give uønskede resultater.

Angivelse af flere samlede udtryk

Ovenstående eksempel viser kun et samlet udtryk, der bruges i PIVOT -klausulen, mens brugerne faktisk kan angive flere samlede udtryk, hvis det er nødvendigt. Igen, med vejrdataene ovenfor, kan vi angive de maksimale høje temperaturer sammen med de gennemsnitlige høje temperaturer mellem juni og september.

I tilfælde af flere samlede udtryk vil kolonnerne være det kartesiske produkt af drejetappen kolonneværdier og de samlede udtryk med navnene som <value>_<aggExpr>.

Gruppering af kolonner vs. drejekolonner

Antag nu, at vi vil inkludere lave temperaturer i vores udforskning af temperaturudviklingen fra denne tabel 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 at kombinere denne tabel med den foregående tabel over daglige høje temperaturer, kunne vi slutte os til disse to tabeller i kolonnen “Dato”. Da vi imidlertid bruger drejning, der udfører gruppering på datoerne, kan bare sammenkæde de to tabeller ved hjælp af UNION ALL. Og du kan se senere, denne tilgang giver os også mere fleksibilitet:

Lad os nu prøve vores pivotforespørgsel med den nye kombinerede tabel:

Som et resultat får vi gennemsnittet højt og gennemsnitligt lavt for hver måned de sidste 4 år i en tabel. Bemærk, at vi skal medtage kolonnen flag i pivotforespørgslen, ellers ville udtrykket avg(temp) være baseret på en blanding af høj og lav temperaturer.

Du har måske bemærket, at vi nu har to rækker for hvert år, en til de høje temperaturer og den anden til lave temperaturer. Det skyldes, at vi har medtaget endnu en kolonne, flag, i pivotindgangen, som igen bliver en anden implicit grupperingskolonne ud over den originale kolonne year.

Alternativt kan flag i stedet for at være en grupperingskolonne også fungere som en drejekolonne. Så nu har vi to drejekolonner, month og flag:

Denne forespørgsel præsenterer os for et andet layout af de samme data med en række for hvert år, men to kolonner for hver måned.

Hvad er næste

For at køre forespørgselseksemplerne, der bruges i denne blog, skal du tjekke SQL-eksemplerne i denne ledsagende notesbog.

Tak til Apache Spark-community-bidragsydere for deres bidrag!