Kokeile tätä muistikirjaa Databricksissa
PÄIVITETTY 10.11.2018
Pivot esiteltiin ensimmäisen kerran Apache Spark 1.6: ssa uutena DataFrame-ominaisuutena, jonka avulla käyttäjät voivat kiertää taulukkoarvoista lauseketta muuttamalla yhden sarakkeen yksilölliset arvot yksittäisiksi sarakkeiksi.
Apache Spark 2.4 -julkaisu laajentaa tämän tehokkaan tietojen kääntämisen toiminnon myös SQL-käyttäjillemme. Tässä blogissa näytetään lämpötilatallennuksia Seattlessa, kuinka voimme käyttää tätä yleistä SQL Pivot -ominaisuutta monimutkaisten tietojen muunnosten aikaansaamiseksi.
Kesälämpötilojen tutkiminen pivotilla
Tänä kesänä Seattlessa lämpötilat nousivat epämiellyttävälle tasolle, korkeimmalle 80- ja 90-luvuille, yhdeksän päivän ajan heinäkuussa.
| Päivämäärä | Lämpötila (° F) |
|---|---|
| 07-22-2018 | 86 |
| 07-23-2018 | 90 |
| 07-24-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 |
Oletetaan, että haluamme tutkia tai tutkia, oliko olemassa historiallisia kaivoksia d nousevassa elohopeapitoisuudessa. Yksi intuitiivinen tapa tarkastella ja esittää nämä luvut on, että sarakkeina ovat kuukaudet ja sitten kunkin vuoden kuukausittaiset keskimääräiset korkeimmat arvot yhdellä rivillä. Näin on helppo verrata lämpötiloja sekä vaakasuoraan, vierekkäisten kuukausien välillä että pystysuunnassa eri vuosien välillä.
Nyt kun meillä on tuki PIVOT syntaksille Spark SQL: ssä voimme saavuttaa tämän seuraavalla SQL-kyselyllä.
Yllä oleva kysely tuottaa seuraavanlaisen tuloksen:
Näyttää siltä, että on hyviä ja huonoja vuosia. Vuosi 2016 näyttää melko energiaystävälliseltä vuodelta.
Kääntyminen SQL: ssä
Tarkastellaan tätä kyselyä tarkemmin, jotta ymmärrämme, miten se toimii. Ensinnäkin meidän on määritettävä lauseke FROM, joka on pivotin tulo, toisin sanoen taulukko tai alakysely, jonka perusteella kääntö suoritetaan. Meidän tapauksessamme olemme huolissamme vuosista, kuukausista ja korkeista lämpötiloista, joten nämä kentät näkyvät alakyselyssä.
Seuraavaksi tarkastellaan kyselyn toista tärkeää osaa, PIVOT -lauseke. PIVOT -lausekkeen ensimmäinen argumentti on aggregaattifunktio ja koottava sarake. Määritämme sitten pivot-sarakkeen FOR -lausekkeessa toisena argumenttina, jota seuraa IN -operaattori, joka sisältää pivot-sarakkeen arvot muodossa viimeinen argumentti.
Pivot-sarake on kohta, jonka ympärillä taulukko kiertyy, ja pivot-sarakkeen arvot siirretään tulostaulukon sarakkeisiin. IN -lausekkeen avulla voit myös määrittää aliaksen kullekin pivot-arvolle, mikä helpottaa merkityksellisten sarakkeiden nimien luomista.
Tärkeä ajatus pivotista on, että se suorittaa ryhmitellyn koosteen implisiittisten group-by -sarakkeiden luettelon ja pivot-sarakkeen perusteella. Epäsuorat group-by -sarakkeet ovat sarakkeita FROM -lausekkeesta, jotka eivät näy missään aggregaattifunktiossa tai pivot-sarakkeena.
Yllä olevassa kyselyssä pivot-sarake on sarakekuukausi ja implisiittinen group-by -sarake sarakevuosi, lauseke avg(temp) kootaan kullekin erilliselle arvoparille (year, month), jossa kuukausi on yhtä määritetyistä pivot-sarakearvoista. Tämän seurauksena kukin näistä yhdistetyistä arvoista kartoitetaan vastaavaan riviin year ja column kuukausi.
On syytä huomata, että tämän implisiittisen group-by vuoksi meidän on varmistettava, että kaikki sarakkeet, joita emme halua olla osa pivot-lähtöjä, jätetään pois FROM -lauseke, muuten kysely tuottaisi ei-toivottuja tuloksia.
Useiden aggregaattilausekkeiden määrittäminen
Yllä olevassa esimerkissä näkyy vain yksi käytettävä aggregaattilauseke PIVOT -lausekkeessa, vaikka itse asiassa käyttäjät voivat tarvittaessa määrittää useita aggregaattilausekkeita. Jälleen yllä olevien säätietojen avulla voimme listata korkeimmat lämpötilat yhdessä keskimääräisten korkeiden lämpötilojen kanssa kesäkuun ja syyskuun välisenä aikana.
Jos kyseessä on useita aggregaattilausekkeita, sarakkeet ovat nivelen suorakulmion tulo. sarakearvot ja aggregaattilausekkeet nimillä <value>_<aggExpr>.
Sarakkeiden ryhmittely vs. kääntyvät sarakkeet
Oletetaan, että haluamme nyt sisällyttää alhaiset lämpötilat lämpötilakehitystutkimuksiimme tästä päivittäisten matalien lämpötilojen taulukosta:
| päivämäärä | Lämpötila (° F) |
|---|---|
| … | … |
| 08-01-2018 | 59 |
| 08-02-2018 | 58 |
| 08-03-2018 | 59 |
| 04-04-2018 | 58 |
| 08-05-2018 | 59 |
| 08-06-2018 | 59 |
| … | … |
Jos haluat yhdistää tämän taulukon edelliseen päivittäisten korkeiden lämpötilojen taulukkoon, voisimme liittää nämä kaksi taulukkoa ”Päivämäärä” -sarakkeeseen. Koska kuitenkin aiomme käyttää pivotia, joka ryhmitellään päivämäärillä, voi yksinkertaisesti liittää nämä kaksi taulukkoa UNION ALL. Ja näet myöhemmin, tämä lähestymistapa tarjoaa meille enemmän joustavuutta:
Kokeillaan nyt pivot-kyselyä uudella yhdistetyllä taulukolla:
Tämän seurauksena saamme keskimäärin korkeimman ja keskimääräisen matalan viimeisen 4 vuoden jokaisesta kuukaudesta yhteen taulukkoon. Huomaa, että meidän on sisällytettävä sarake flag pivot-kyselyyn, muuten lauseke avg(temp) perustuisi korkean ja matalan sekoitukseen lämpötilat.
Olet ehkä huomannut, että nyt meillä on kaksi riviä vuodessa, yksi korkeille ja toinen matalille lämpötiloille. Tämä johtuu siitä, että olemme sisällyttäneet yhden sarakkeen, flag, pivot-syötteeseen, josta puolestaan tulee toinen implisiittinen ryhmittelysarake alkuperäisen sarakkeen year.
Vaihtoehtoisesti flag voi toimia ryhmittelysarakkeena myös pivot-sarakkeena. Joten nyt meillä on kaksi pivot-saraketta, month ja flag:
Tämä kysely antaa meille erilaisen asettelun samat tiedot, yksi rivi joka vuosi, mutta kaksi saraketta kutakin kuukautta kohti.
Mitä seuraavaksi
Jos haluat suorittaa tässä blogissa käytetyt kyselyesimerkit, tarkista pivot-SQL-esimerkit tässä mukana olevassa muistikirjassa.
Kiitos Apache Spark -yhteisön avustajille heidän panoksestaan!
