SQL ROLLUP, CUBE, GROUPING SETS – Součty a mezisoučty

Co takhle rozšířit si 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.

1) 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]);

2) 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.

SQL ROLLUP

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

GROUPING

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ů.

3) 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

SQL GROUPING SETS

4) CUBE jako součtová krychle

Tento operátor vytvoří součty přes všechny kombinace hodnot ve sloupcích uvedených jako argument.

SQL CUBE

Napsat komentář

Vaše emailová adresa nebude zveřejněna.