Vyzkoušejte tento poznámkový blok v Databricks

AKTUALIZOVÁNO 10. 10. 2018

Pivot byl poprvé představen v Apache Spark 1.6 jako nová funkce DataFrame, která umožňuje uživatelům otočit výraz s hodnotou tabulky otočením jedinečných hodnot z jednoho sloupce do jednotlivých sloupců.

Verze Apache Spark 2.4 rozšiřuje tuto výkonnou funkčnost otočných dat také na naše uživatele SQL. V tomto blogu si pomocí záznamů teplot v Seattlu ukážeme, jak můžeme pomocí této běžné funkce SQL Pivot dosáhnout složitých transformací dat.

Zkoumání letních teplot pomocí Pivot

letos v létě v Seattlu teploty vzrostly na nepříjemné úrovně a vrcholily na 80. a 90. let po devět dní v červenci.

Předpokládejme, že chceme prozkoumat nebo prozkoumat, zda existoval historický tren d při zvyšování hladiny rtuti. Jedním z intuitivních způsobů, jak tato čísla prozkoumat a uvést, je mít měsíce jako sloupce a poté každý rok průměrné měsíční maxima v jednom řádku. Tímto způsobem bude snadné porovnávat teploty horizontálně, mezi sousedními měsíci a vertikálně mezi různými roky.

Nyní, když máme podporu pro PIVOT syntaxi ve Spark SQL toho můžeme dosáhnout pomocí následujícího dotazu SQL.

Výše uvedený dotaz vytvoří výsledek jako:

No, vypadá to, že existují dobré a špatné roky. Rok 2016 se zdá být energeticky velmi příjemným rokem.

Otočení v SQL

Podívejme se na tento dotaz blíže, abychom pochopili, jak funguje. Nejprve musíme zadat klauzuli FROM, která je vstupem pivot, jinými slovy tabulka nebo poddotaz, na jejichž základě bude otočení provedeno. V našem případě nás znepokojují roky, měsíce a vysoké teploty, takže to jsou pole, která se objevují v dílčím dotazu.

Zadruhé, pojďme zvážit další důležitou část dotazu, klauzule PIVOT. Prvním argumentem klauzule PIVOT je agregační funkce a sloupec, který se má agregovat. Poté jako druhý argument určíme kontingenční sloupec v podsekci FOR, za kterým následuje operátor IN obsahující hodnoty kontingenčního sloupce jako poslední argument.

Kontingenční sloupec je bod, kolem kterého bude tabulka otočena, a hodnoty kontingenčního sloupce budou převedeny do sloupců ve výstupní tabulce. Klauzule IN také umožňuje zadat alias pro každou hodnotu pivot, což usnadňuje generování smysluplnějších názvů sloupců.

Důležitou myšlenkou pivot je, že provádí seskupenou agregaci na základě seznamu implicitních group-by sloupců společně s kontingenčním sloupcem. Implicitní group-by sloupce jsou sloupce z klauzule FROM, které se neobjevují v žádné agregační funkci ani jako kontingenční sloupec.

Ve výše uvedeném dotazu, přičemž kontingenční sloupec je měsíc sloupce a implicitní group-by sloupec je rok sloupce, výraz avg(temp) budou agregovány na každé odlišné dvojici hodnot (year, month), kde měsíc se rovná jedné ze zadaných hodnot kontingenčního sloupce. Výsledkem je, že každá z těchto agregovaných hodnot bude mapována do odpovídající buňky řádku year a column měsíce.

Stojí za zmínku, že z tohoto implicitního group-by musíme zajistit, aby jakýkoli sloupec, který si nepřejeme být součástí pivotního výstupu, měl být vynechán z FROM klauzule, jinak by dotaz přinesl nežádoucí výsledky.

Specifikace více souhrnných výrazů

Výše uvedený příklad ukazuje, že se používá pouze jeden agregovaný výraz v klauzuli PIVOT, ve skutečnosti mohou uživatelé v případě potřeby zadat více souhrnných výrazů. S výše uvedenými údaji o počasí můžeme opět vypsat maximální vysoké teploty spolu s průměrnými vysokými teplotami mezi červnem a zářím.

V případě více souhrnných výrazů budou sloupce kartézským součinem pivot hodnoty sloupců a souhrnné výrazy s názvy jako <value>_<aggExpr>.

Seskupování sloupců vs. kontingenční sloupce

Předpokládejme, že chceme do průzkumu teplotních trendů z této tabulky denních nízkých teplot zahrnout nízké teploty:

Abychom tuto tabulku spojili s předchozí tabulkou denních vysokých teplot, mohli bychom tyto dvě tabulky spojit ve sloupci „Datum“. Jelikož však použijeme pivot, který provádí seskupení k datům, lze jednoduše zřetězit dvě tabulky pomocí UNION ALL. A uvidíte později, tento přístup nám také poskytuje větší flexibilitu:

Zkusme nyní náš kontingenční dotaz s novou kombinovanou tabulkou:

Výsledkem je, že v jedné tabulce dostaneme průměrné maximum a průměrné minimum za každý měsíc za poslední 4 roky. Upozorňujeme, že do kontingenčního dotazu musíme zahrnout sloupec flag, jinak by byl výraz avg(temp) založen na kombinaci vysokých a nízkých hodnot teploty.

Možná jste si všimli, že nyní máme pro každý rok dva řádky, jeden pro vysoké teploty a druhý pro nízké teploty. Je to proto, že jsme do pivotního vstupu zahrnuli ještě jeden sloupec flag, který se zase stal dalším implicitním seskupovacím sloupcem kromě původního sloupce year.

Alternativně může být flag místo seskupovacího sloupce také sloužit jako kontingenční sloupec. Takže nyní máme dva kontingenční sloupce, month a flag:

Tento dotaz nám nabízí jiné rozložení stejná data, s jedním řádkem pro každý rok, ale se dvěma sloupci pro každý měsíc.

Co bude dál

Chcete-li spustit příklady dotazů použité v tomto blogu, podívejte se na pivotní příklady SQL v tomto doprovodném notebooku.

Děkujeme přispěvatelům komunity Apache Spark za jejich příspěvky!