Funkcje analityczne w TSQL – ROLLUP, CUBE, GROUPING_SETS

Transact-SQL daje nam dosyć dużo możliwości związanych z agregacją danych. Większość deweloperów namiętnie używa standardowych wyrażeń grupowanych, funkcji okna i tym podobnych, ale niewielu zdaje sobie sprawę z funkcji analitycznych wbudowanych w nasz ulubiony język zapytań. Wyrażenia te możemy zgrupować pod pojęciem funkcji analitycznych – w ramach niniejszego artykułu powiemy sobie o trzech niezwykle ciekawych funkcjach wchodzących w ich skład tj. ROLLUP, CUBE oraz GROUPING SETS.

Na sam początek zajmiemy się funkcją ROLLUP. Pierwszą niezwykle istotną informacją jest to, że ROLLUP jest rozszerzeniem klauzuli GROUP BY- podstawowa składnia przedstawia się następująco:

SELECT 
     a,
     b,
     c, 
     sum(d)
FROM 
     Table_name
GROUP BY 
     ROLLUP(a,b,c)

Jak czytać coś takiego? W rezultacie otrzymamy sumę pola D na następujących poziomach:

• • ABC
  • AB
  • A

Ponadto wyświetlona zostanie sumę całkowita pola D.  Zapowiada się fajnie co? Sprawdźmy to na realnym przykładzie na podstawie AdventureWorksDW2012:

SELECT
	dpc.EnglishProductCategoryName,
	dps.EnglishProductSubcategoryName,
	dp.Color,
	SUM(fis.SalesAmount) AS SalesAmount
FROM 
	FactInternetSales AS fis
JOIN 
	DimProduct AS dp 
ON 
	dp.ProductKey=fis.ProductKey
JOIN 
	DimProductSubcategory AS dps 
ON 
	dps.ProductSubcategoryKey=dp.ProductSubcategoryKey
JOIN 
	DimProductCategory AS dpc 
ON 
	dpc.ProductCategoryKey=dps.ProductCategoryKey
GROUP BY 
	ROLLUP(dpc.EnglishProductCategoryName,dps.EnglishProductSubcategoryName, dp.Color)

Rezultatem powyższego zapytania były podsumowania pola Sales Amount na następujących poziomach:

• kategorii, podkategorii i koloru produktu
• kategorii, podkategorii:

• kategorii:

•  wszystkich pól(suma całkowita):

Fajne prawda? Część raportów można obsłużyć właśnie w ten sposób bez konieczności pisania bardziej skomplikowanych wyrażeń w narzędziu raportowym. Kolejną funkcjonalnością, którą się zajmiemy jest CUBE, która jest bardzo podobna do ROLLUP ale daje trochę więcej możliwości ponieważ w rezultacie daje wszystkie możliwe kombinacje dla przekazanych jako parametr pól. Składnia dla tego polecenia przedstawia się następująco:

SELECT 
     a,
     b,
     c, 
     SUM(d)
FROM 
     Table_name
GROUP BY 
     CUBE(a,b,c)

Powyższe zapytanie pozwoli nam otrzymać sumę na następujących poziomach: ABC,AB,AC,A,BC,B,C. Sprawdźmy na analogicznym do poprzedniego przykładzie:

SELECT
	dpc.EnglishProductCategoryName,
	dps.EnglishProductSubcategoryName,
	dp.Color,
	SUM(fis.SalesAmount) AS SalesAmount
FROM 
	FactInternetSales AS fis
JOIN 
	DimProduct AS dp 
ON 
	dp.ProductKey=fis.ProductKey
JOIN 
	DimProductSubcategory AS dps 
ON 
	dps.ProductSubcategoryKey=dp.ProductSubcategoryKey
join 
	DimProductCategory AS dpc 
ON 
	dpc.ProductCategoryKey=dps.ProductCategoryKey
GROUP BY 
	CUBE(dpc.EnglishProductCategoryName,dps.EnglishProductSubcategoryName, dp.Color)

Po wykonaniu zapytania otrzymaliśmy następujące poziomy:

• kategorii,podkategorii, koloru
• podkategorii, koloru
• koloru

• kategorii, koloru

• kategorii

• podkategorii

• kategorii, podkategorii

Jak widać CUBE daje nam wszystkie możliwe poziomy co w niektórych przypadkach może być niezwykle użyteczne – jednak powoduje, iż nasz rezultat zwraca naprawdę dużo wierszy co można wywnioskować po powyższych przykładach gdzie przedstawiłem tylko wybrane z nich.

Zarówno CUBE jak i ROLLUP w dużej mierze polegają na wartości NULL co natomiast gdy, któryś z naszych poziomów naprawdę ma wartość NULL? Możemy sobie z tym problemem radzić dwojako – po pierwsze możemy zastąpić wartości niezidentyfikowane używając ISNULL czy też COALESCE lub tez użyć specjalnie dedykowanej  do tego celu funkcji GROUPING_ID. Jak to zrobić? Wystarczy, że w sekcji SELECT dodamy funkcję GROUPING_ID i jako parametr podać poziom, który nas interesuje. Dzięki temu otrzymamy dodatkową kolumnę, która zwróci nam liczbę porządkową naszego podsumowania jeżeli dany wiersz jest podsumowaniem na poziomie, który podaliśmy jako argument:

GROUPING_ID(
dpc.EnglishProductCategoryName,
dps.EnglishProductSubcategoryName, 
dp.Color) AS GROUP_ID

Numer w naszej nowej kolumnie wskazuje poziom agregacji gdzie 0 odpowiada w naszej notacji zapisowi ABC, 1 AB itd.

Na koniec chciałbym wspomnieć o funkcji GROUPING_SETS, która daje nam możliwość jawnego wskazania jakie podsumowania będą obliczone:

GROUP BY GROUPING SETS(
(
dpc.EnglishProductCategoryName,
dps.EnglishProductSubcategoryName, 
dp.Color
),())

Podsumowanie otrzymamy oczywiście na poziomie przekazanym jako parametr czyli:

dpc.EnglishProductCategoryName,dps.EnglishProductSubcategoryName, dp.Color

oraz agregację na poziomie wszystkich wierszy co symbolizują nawiasy (). Tak więc dzięki GROUPING SETS mamy pełną kontrolę nad tym co będzie sumowane.

Jak widać funkcje analityczne w TSQL nie odbiegają od standardu kodowania zapytań i są bardzo proste w użyciu – warto je stosować gdyż są przejrzyste i dają spore możliwości szczególnie pod kątem raportowania i analityki szczególnie gdy chcemy uniknąć agregacji po stronie narzędzia. Warto również wspomnieć o tym, że w powyższych przykładach zastosowałem funkcję agregacji jaką była suma jednakże nic nie stoi na przeszkodzie aby użyć innych agregatów w zależności od potrzeby.