Próbálja ki ezt a jegyzetfüzetet Databricks-ben
Frissítve 2018.11.10.
A Pivot először az Apache Spark 1.6-ban került bevezetésre, mint új DataFrame szolgáltatás, amely lehetővé teszi a felhasználók számára, hogy egy táblázat által értékelt kifejezést forgassanak azáltal, hogy az oszlopok egyedi értékeit egyedi oszlopokká alakítják.
Az Apache Spark 2.4 kiadás kiterjeszti az adatok elfordításának ezt a hatékony funkcióját az SQL felhasználóinkra is. Ebben a blogban a hőmérséklet-felvételek felhasználásával Seattle-ben megmutatjuk, hogyan használhatjuk ezt a közös SQL Pivot szolgáltatást komplex adatátalakítások eléréséhez.
Nyári hőmérsékletek vizsgálata a Pivot segítségével
Idén nyáron Seattle-ben a hőmérséklet júliusban kilenc napig kényelmetlen szintre emelkedett, a 80-as és a 90-es csúcsra emelkedett.
| Dátum | Hőmérséklet (° F) |
|---|---|
| 07-22-2018 | 86 |
| 2018-07-23 | 90 |
| 2018-04-24 | 91 |
| 2018-25-25 | 92 |
| 2018-07-26 | 92 |
| 2018-07-27 | 88 |
| 2018-08-28 | 85 |
| 2018-09-29 | 94 |
| 2018-30-30 | 89 |
Tegyük fel, hogy szeretnénk feltárni vagy megvizsgálni, hogy voltak-e történelmi árkok d az emelkedő higanyszintben. E számok vizsgálatának és bemutatásának egyik intuitív módja az, hogy hónapok szerepelnek oszlopként, majd az egyes évek havi átlaga egyetlen sorban. Így könnyű lesz összehasonlítani a hőmérsékleteket vízszintesen, a szomszédos hónapok között és függőlegesen, a különböző évek között.
Most, hogy támogatjuk a PIVOT szintaxist a Spark SQL-ben ezt a következő SQL lekérdezéssel érhetjük el.
A fenti lekérdezés olyan eredményt hoz létre, mint:
Nos, úgy tűnik, vannak jó és rossz évek. A 2016-os év meglehetősen energiatakarékos évnek tűnik.
Fordulás SQL-ben
Vizsgáljuk meg közelebbről ezt a lekérdezést, hogy megértsük a működését. Először meg kell adnunk a FROM záradékot, amely a pivot bemenete, más szóval azt a táblázatot vagy allekérdezést, amely alapján a pivot végrehajtásra kerül. Esetünkben aggódunk az évek, a hónapok és a magas hőmérséklet miatt, ezért ezek a mezők jelennek meg az allekérdezésben.
Másodszor vegyük fontolóra a lekérdezés egy másik fontos részét, a PIVOT záradék. A PIVOT záradék első argumentuma összesítő függvény és az összesítendő oszlop. Ezután a FOR albekezdésben a pivot oszlopot adjuk meg második argumentumként, majd a IN operátort, amely a pivot oszlop értékeit tartalmazza, az utolsó argumentum.
A pivot oszlop az a pont, amely körül a táblát elforgatják, és a pivot oszlop értékei átkerülnek a kimeneti táblázat oszlopaiba. A IN záradék lehetővé teszi egy álnév megadását az egyes kimutatási értékekhez, megkönnyítve ezzel az értelmesebb oszlopnevek létrehozását.
A kimutatás fontos ötlete, hogy csoportosított összesítést hajt végre az implicit group-by oszlopok listája és a forgóoszlop mellett. Az implicit group-by oszlopok a FROM záradék oszlopai, amelyek nem jelennek meg összesítő függvényben vagy kimutatható oszlopként.
A fenti lekérdezésben, amikor a pivot oszlop az oszlop hónapja, és az implicit group-by oszlop az oszlop éve, a avg(temp) a (year, month) minden egyes különálló értékpárján összesítésre kerül, ahol a hónap megegyezik a megadott pivot oszlopértékek egyikével. Ennek eredményeként ezek az összesített értékek a year és a column hónap megfelelő cellájába kerülnek leképezésre.
Érdemes megjegyezni, hogy emiatt az implicit group-by miatt meg kell győződnünk arról, hogy minden oszlopot, amely nem kívánjuk, hogy a pivot kimenet része legyen, hagyja ki az FROM záradék, különben a lekérdezés nem kívánt eredményeket eredményez.
Több összesített kifejezés megadása
A fenti példa csak egy összesített kifejezést mutat be a PIVOT záradékban, míg valójában a felhasználók több összesített kifejezést is megadhatnak, ha szükséges. Ismét a fenti időjárási adatokkal felsorolhatjuk a maximális magas hőmérsékleteket, valamint az átlagos magas hőmérsékleteket június és szeptember között.
Több összesített kifejezés esetén az oszlopok a tengely derékszögű szorzata oszlopértékek és az összesített kifejezések <value>_<aggExpr> névvel.
Oszlopok és forgóoszlopok csoportosítása
Most tegyük fel, hogy az alacsony hőmérsékleteket be akarjuk vonni a hőmérsékleti trendek feltárásába a napi alacsony hőmérsékletek táblázatából:
| Dátum | Temp (° F) |
|---|---|
| … | … |
| 2018-01-01 | 59 |
| 2018-02-08 | 58 |
| 2018-03-08 | 59 |
| 2018-04-08 | 58 |
| 2018-05-08 | 59 |
| 2018-06-08 | 59 |
| … | … |
Ha ezt a táblázatot össze akarjuk kapcsolni az előző napi magas hőmérsékleti táblázattal, ezt a két táblázatot összekapcsolhatjuk a “Dátum” oszlopban. Mivel azonban a pivot-ot fogjuk használni, amely csoportosítást végez a dátumokon, akkor egyszerűen összefűzheti a két táblázatot a UNION ALL. És később meglátjuk, ez a megközelítés nagyobb rugalmasságot is biztosít számunkra:
Most próbáljuk ki a pivot lekérdezést az új kombinált táblával:
Ennek eredményeként egy táblázatban megkapjuk az elmúlt 4 év minden hónapjának átlagát és átlagát. Ne feledje, hogy a flag oszlopot bele kell foglalnunk a kimutatásba, különben a avg(temp) kifejezés a magas és az alacsony keverékén alapulna hőmérsékleteket.
Észrevehette, hogy most minden évben két sor áll rendelkezésünkre, az egyik a magas hőmérsékletre, a másik az alacsony hőmérsékletre. Ez azért van, mert még egy oszlopot, flag beillesztettünk a pivot bemenetbe, amely az eredeti year.
Alternatív megoldásként ahelyett, hogy csoportosító oszlop lenne, a flag is oszlopként szolgálhat. Tehát most két forgóoszlopunk van: month és flag:
Ez a lekérdezés más elrendezést mutat be nekünk ugyanazok az adatok, minden évben egy sorral, de havonta két oszloppal.
Mi következik
A blogban használt lekérdezési példák futtatásához ellenőrizze a pivot SQL példákat ebben a kísérő jegyzetfüzetben.
Köszönjük az Apache Spark közösség közreműködőinek a hozzájárulásukat!
