Grupowanie danych przy użyciu poleceń ROLLUP, CUBE oraz GROUPING SETS

2012-10-03

W dzisiejszym temacie zajmę się omówieniem poleceń ROLLUP, CUBE oraz GROUPING SETS. Polecenia te umieszczane są w klauzuli GROUP BY i pozwalają na wygenerowanie odpowiednich grupowań w celu agregacji danych. Warto zaznaczyć, iż omawiane polecenia zastępują instrukcje COMPUTE, COMPUTE BY, WITH ROLLUP oraz WITH CUBE. Dwie pierwsze zostały wycofane z języka T-SQL w SQL Server 2012, pozostałe dwie zostaną wycofane w kolejnej wersji. Aby zaprezentować działanie omawianych instrukcji stworzyłem tabelę zawierającą informacje o wartości sprzedaży uzyskanej przez pracowników w poszczególnych latach:

create table #Sales
(
	EmployeeId int,
	[Year] int,
	Sales money
)

insert into #Sales values(1, 2005, 120000)
insert into #Sales values(1, 2006, 180000)
insert into #Sales values(1, 2007, 250000)
insert into #Sales values(2, 2005, 150000)
insert into #Sales values(2, 2006, 110000)
insert into #Sales values(3, 2006, 200000)
insert into #Sales values(3, 2007, 240000)

ROLLUP

Polecenie ROLLUP grupuje dane według wskazanych kolumn w sposób hierarchiczny. Użycie ROLLUP(a, b, c) spowoduje wygenerowanie następujących grupowań: (a, b, c), (a, b), (a) oraz rekordu agregującego cały zbiór. Inaczej mówiąc, użycie w zapytaniu konstrukcji ROLLUP(a, b, c) jest równoważne z wykonaniem czterech zapytań połączonych klauzulą UNION ALL, gdzie każde zapytanie posiada odpowiednią klauzulę GROUP BY. Poniższy przykład pokazuje działanie polecenia ROLLUP:

select
    [Year],
    EmployeeId,
    sum(Sales) as Sales
from #Sales
group by rollup([Year], EmployeeId)

Jak widać dzięki przekazaniu do polecenia ROLLUP kolumn Year i EmployeeId w wynikowym zbiorze uzyskaliśmy następujące grupowania: (Year, EmployeeId), (Year) oraz rekord ogólny dla całego zbioru.

CUBE

Polecenie CUBE działa w sposób podobny, z tą różnicą, iż przy grupowaniu danych uwzględniane są wszystkie kombinacje wskazanych kolumn. Użycie CUBE(a, b, c) spowoduje więc wygenerowanie następujących grupowań: (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c) oraz rekordu agregującego cały zbiór. Poniższy przykład pokazuje działanie polecenia CUBE:

select
    [Year],
    EmployeeId,
    sum(Sales) as Sales
from #Sales
group by cube([Year], EmployeeId)

Użycie polecenia CUBE dla kolumn Year i EmployeeId spowodowało wygenerowanie następujących grupowań: (Year, EmployeeId), (Year), (EmployeeId) oraz rekordu ogólnego dla całego zbioru.

GROUPING SETS

Polecenie GROUPING SETS działa analogicznie do powyższych, ale daje nam możliwość jawnego określenia wszystkich grupowań jakie mają być uwzględnione w wynikowym zbiorze. Poszczególne grupy wymieniane są po przecinku, przy czym jeżeli w ich skład wchodzi kilka kolumn muszą być ujęte w nawiasy. Puste nawiasy () oznaczają rekord ogólny dla całego zbioru. Poniżej przykład użycia polecenia GROUPING SETS:

select
    [Year],
    EmployeeId,
    sum(Sales) as Sales
from #Sales
group by grouping sets((EmployeeId, [Year]), EmployeeId, ())

W powyższym zapytaniu jawnie określone zostały następujące grupowania: (EmployeeId, Year), (EmployeeId) oraz rekord ogólny dla całego zbioru.

Funkcja GROUPING

Podczas korzystania z omawianych poleceń bardzo użyteczna może okazać się funkcja GROUPING. Pozwala ona określić dla każdego wiersza wynikowego zbioru czy dana kolumna jest zagregowana (posiada wartość NULL) czy nie. W przypadku kolumny zagregowanej funkcja zwraca 1, w przeciwnym wypadku 0. Oto przykład użycia:

select
	[Year],
	EmployeeId,
	sum(Sales) as Sales,
	grouping([Year]) as AgrYear,
	grouping(EmployeeId) as AgrEmployeeId
from #Sales
group by cube([Year], EmployeeId)

Jak widać kolumny AgrYear oraz AgrEmployeeId wskazują czy w danym wierszu kolumny Year i EmployeeId posiadają wartość NULL wynikającą z określonego grupowania (wartość 0 zwrócona przez funkcję GROUPING oznacza, że dana kolumna bierze udział w grupowaniu).

Funkcja GROUPING_ID

Inną metodą na uzyskanie informacji o zagregowanych kolumnach jest użycie funkcji GROUPING_ID. Zastępuje ona wielokrotne użycie funkcji GROUPING (dla każdej kolumny). Dla wskazanego zestawu kolumn generowana jest postać binarna na podstawie poszczególnych wyników funkcji GROUPING, następnie wynik zwracany jest w postaci wartości typu int. Załóżmy, że uzyskaliśmy następujące wyniki w danym wierszu: GROUPING(a) = 1, GROUPING(b) = 0, GROUPING(c) = 1. Wywołanie funkcji GROUPING_ID(a, b, c) na podstawie wartości binarnej 101 (GROUPING(a) + GROUPING(b) + GROUPING(c)) da wynik 5. Przykład użycia:

select
	[Year],
	EmployeeId,
	sum(Sales) as Sales,
	grouping([Year]) as AgrYear,
	grouping(EmployeeId) as AgrEmployeeId,
	grouping_id([Year], EmployeeId) Agr
from #Sales
group by cube([Year], EmployeeId)

Więcej informacji na temat omawianych poleceń można znaleźć na MSDN: GROUP BY, Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS, GROUPING SETS Equivalents.

Reklamy

Posted on 2012-10-03, in SQL Server and tagged , , , , . Bookmark the permalink. Dodaj komentarz.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: