Co takhle rozšířit si agregační GROUP BY klauzuli o využití užitečných operátorů? GROUP BY používáme v SQL při agregačních operacích a platí, že při jejím použítí dochází k agregaci přes všechny sloupce. Pomocí takto jednoduchého “groupování” ale nejsme schopni udělat součty a mezisoučty (Totals a subtotals). Existuje několik operátorů, pomocí kterých se jednoduše můžeme na Totals dotázat.
Po osvojení těchto operátorů budete moci vytvářet SQL dotazy a views se součtovými řádky, což jen tak někdo neumí 🙂 Všechny 3 operátory fungují podobně, takže to udělejme tak, že podrobně vysvětlím ROLLUP a zbytek už jen proletím.
Syntaxe ROLLUP | CUBE | GROUPING SETS
SELECT [Sloupec1], [Sloupec2], AgregacniFunkce([Sloupec3]) AS Alias FROM Tabulka WHERE Podminka GROUP BY ROLLUP ([Sloupec1], [Sloupec2]) |nebo| CUBE ([Sloupec1], [Sloupec2]) |nebo| GROUPING SETS ([Sloupec1], [Sloupec2]);
Součty a mezisoučty přes sloupce po všech úrovních = ROLLUP
Prvním z operátorů je ROOLUP, který vrátí součty přes všechny úrovně (sloupce). Nejefektivnější je ukázat použítí na příkladu a zobrazíme si tržby za první 4 měsíce roku 2012 a 2013 regionu Evropa.
SELECT [ModelRegion] AS Region, [CalendarYear] AS Rok, [Month] AS Mesic, SUM([Amount]) AS Objem FROM [AdventureWorksDW2014].[dbo].[vTimeSeries] WHERE CalendarYear in (2012,2013) AND month<=4 AND ModelRegion in ('M200 Europe') GROUP BY ROLLUP([ModelRegion], [CalendarYear], [Month]);
Výsledek po aplikaci ROLLUP
Vidímě, že dotaz vrátí o 4 řádky více a obsahuje díky ROLLUP všechny mezisoučty a celkový součet.
Výsledek si ještě můžeme vylepšit přes funkci, která je užitečná zejména při práci s ROLLUP a podobnými operátory. Jde o funkci GROUPING(), která přijímá parametr název sloupce.
Funkce GROUPING – pomůže vám s identifikací součtových řádků
Funkce vrací 1 nebo 0 podle toho jestli je daný sloupec Total/subtotal nebo původní base hodnota. Ukážu na předchozím příkladu s tím, že upravíme SQL dotaz
Pomocí funkce jsme si každý řádek označili správným flagem podle toho o jaký součet jde. Položka Super grand total je v tomto případě rovna celkovému součtu přes region Evropa, protože více regionů jsme nezahrnuli – pokud bychom jich měli více, byl by tento součet součtem regionů.
GROUPING SETS – vytvořte si vlastní agregační skupiny
Pokud chcete, můžete si vytvořit vlastní skupiny přes které se následně provedou agragační operace. Takových skupin si můžete nadefinovat několik. Na příkladu níže vytvořím 2 agragační skupiny
- Subtotals přes region a rok
- Subtotals přes rok a měsíc
CUBE jako součtová krychle
Tento operátor vytvoří součty přes všechny kombinace hodnot ve sloupcích uvedených jako argument.
