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


Jak bude reklama vypadat?
-
Kup si reklamu navždy pod tímto článkem jen za 350 Kč
Zobrazit formulář pro nákup

Napsat komentář

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