SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Konfiguracja Database Mail w SQL Server

Mail_smallWysyłanie powiadomień mailowych z poziomu serwera bazodanowego, jest niezwykle przydatną funkcjonalnością. Typowe zastosowanie to automatyczne przekazywanie informacji o statusie zadań administracyjnych, procesów utrzymaniowych czy ETL (zazwyczaj błędach lub ostrzeżeniach).

Możesz je wykorzystać równie dobrze w typowych aplikacjach klienckich. Informować użytkowników o pojawiających się zdarzeniach np. zleceniach, zmianach statusów czy przekroczeniu określonych wskaźników, bezpośrednio z poziomu SQL Server.

W artykule tym, znajdziesz opis koncepcji Database Mail w SQL Server. Zaprezentuję także przykładową konfigurację za pomocą procedur wbudowanych. Co istotne, Database Mail jest dostępny także w darmowej wersji SQL Server Express, choć bez graficznego wizzarda i widocznej osobnej gałęzi obiektów w Management Studio.


Architektura Database Mail

Konfiguracja, widoki systemowe oraz procedury składowane dotyczące Database Mail, znajdują się są w bazie systemowej MSDB. Działanie oparte jest na usłudze Brokera (Service Broker), czyli wbudowanemu w SQL Server mechanizmowi do przesyłania wiadomości, kolejkowania i obsługi zdarzeń.

Wysyłanie maili, z punktu widzenia użytkownika, sprowadza się do wywołania procedury składowanej sp_send_dbmail, która wrzuca wiadomość do kolejki. Pojawienie się w niej nowego rekordu, wyzwala proces odpowiedzialny za fizyczne wysłanie maila. Tym procesem jest zewnętrzny program DatabaseMail.exe, uruchamiany automatycznie przez proces SQL Server.

Z konfiguracją Database Mail, związane są obiekty kont pocztowych i profili – przedstawione na poniższym schemacie :

Database_Mail_00

Profile – mogą mieć przypisane jedno lub wiele kont pocztowych z których będą wysyłane wiadomości. Jest to proste zwiększenie niezawodności, na wypadek gdy podstawowy serwer (konto) SMTP jest nie dostępny.

W momencie przypisania konta do profilu (procedura sysmail_add_profileaccount_sp), określany jest parametr sequence_number (priorytet) zgodnie z którym, następuje próba wysłania wiadomości. Najpierw przez konto z najniższym sequence numer. W przypadku niepowodzenia, podejmowana jest próba wysłania przez kolejne.

Konta pocztowe, mogą być przypisane do jednego lub wielu profili (współdzielone) np. konto backupowe.

Każdemu użytkownikowi, możemy nadać uprawnienia (proc. sysmail_add_principalprofile_sp
) do wielu profili pocztowych. Jeden z nich, może być określony jako domyślny – wtedy nie trzeba będzie podawać jawnie nazwy profilu w momencie wywoływania procedury wysyłającej maila. Nadanie uprawnień do profilu nie jest jednoznaczne z tym, że już użytkownik będzie mógł wysyłać maile. Musi mieć także uprawnienia do wykonywania procedury msdb.dbo.sp_send_dbmail czyli dostęp do bazy MSDB ze stosownymi uprawnieniami.

Do zarządzania uprawnieniami w tym zakresie, istnieje dedykowana rola w bazie MSDB DatabaseMailUserRole. Najwygodniej jest dopisać do niej każdego użytkownika (grupę), która ma mieć możliwość wysyłania i dostępu do widoków związanych z mailami (np. sysmail_allitems). Widoki te są o tyle bezpieczne, że użytkownik będzie widział tylko te wiadomości, których jest autorem.

Wprowadzona architektura zależności pomiędzy kontami, profilami a użytkownikami daje elastyczność i jest łatwo skalowalna.


Konfiguracja Database Mail w SQL Server

Pierwszym krokiem, jest przygotowanie instancji SQL Server, czyli włączenie obsługi Database Mail.

sp_configure 'show advanced options',1
reconfigure
go
 
sp_configure 'Database Mail XPs',1;
reconfigure
go

Jak już wspominałem, Database Mail bazuje na usłudze Brokera i musi on być również aktywny w bazie MSDB. Jego status możemy sprawdzić za pomocą :

select name ,   is_broker_enabled 
from sys.databases
where name = 'msdb'

Database_Mail_01
W przypadku gdy nie jest aktywny (0), należy go włączyć :

USE master
GO
ALTER DATABASE msdb SET  ENABLE_BROKER WITH NO_WAIT
GO

Teraz, możemy zabrać się za właściwą konfigurację profili i skojarzonych z nimi kont pocztowych. Od razu podpowiem, że większość problemów z wysyłaniem maili, jest związane właśnie z błędami w tym kroku. Należy upewnić się co do parametrów konta SMTP , poprzez które chcemy wysyłać maila.

Na początek dodam dwa nowe konta – na serwerach home.pl oraz Gmail. Konfiguracja portów, konieczność uwierzytelniania SSL są z reguły identyczne dla większości providerów.

Use MSDB
GO
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'Gmail',
     @email_address = 'mojekonto@gmail.com',
     @display_name = 'Gmail Public', 
     @description = 'Konto Main', 
     @mailserver_name = 'smtp.gmail.com',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'mojekonto@gmail.com',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'SQLPEDIA',
     @email_address = 'adresemail@sqlpedia.pl',
     @display_name = 'backup', 
     @description = 'Konto Main', 
     @mailserver_name = 'domena.home.pl',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'adresemail@sqlpedia.pl',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
-- weryfikacja kroku
select * from sysmail_server

Kolejnym krokiem, jest utworzenie profili. Zróbmy dwa – jeden będzie wykorzystywany jako publiczny, drugi prywatny dla procesów utrzymaniowych.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public',
@description = 'Dla wszystkich userów' ;
 
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Priv',
@description = 'Tylko admin' ;
 
-- weryfikacja kroku
select * from sysmail_profile

Database_Mail_02
Teraz trzeba powiązać konta pocztowe z profilami. Profil publiczny będzie posiadał jedno konto a prywatny dwa, podstawowe i backupowe.

-- Public
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public', -- nazwa profilu
@account_name = 'Gmail',  -- nazwa konta
@sequence_number =1 ;
 
-- Priv
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'SQLPEDIA',
@sequence_number =1 ;
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'Gmail',
@sequence_number =2 ;
 
-- weryfikacja kroku
select * from sysmail_profileaccount

Database_Mail_03

Ostatnią rzeczą która trzeba wykonać aby zwykły użytkownik mógł wysyłać maile, jest nadanie stosownych uprawnień. Nadajmy więc uprawnienia do profilu publicznego dla wszystkich (Principal_id = 0 to rola public w bazie MSDB).

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Public',
@principal_id = 0,
@is_default = 1 ;

Poza uprawnieniami do profilu, zwykły użytkownik musi mieć jeszcze dostęp do bazy MSDB oraz przypisanie do roli DatabaseMailUserRole

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [UserName]
GO

I to już wszystko. Teraz możemy na wysłać maila testowego.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Public',
@recipients = 'JakisAdresEmail@wp.pl',
@subject = 'SQL Server Databse Mail - Test',
@body = 'Dzieńdobry i dowidzenia ...';

Weryfikacja poprawności działania i typowe problemy

Do weryfikacji poprawności wysłania maili mamy dostępnych kilka widoków systemowych. Szczególnie użyteczne są dwa :

  • msdb.dbo.sysmail_allitems, pokazujący aktualny status i zawartość wiadomości w kolejce.
  • msdb.dbo.sysmail_event_log – tu znajdziemy szczegółowe informacje o błędach, odpowiedziach serwera SMTP.
USE msdb
go
 
SELECT sent_status, mailitem_id, profile_id, recipients,
	subject, body
FROM sysmail_allitems
ORDER BY mailitem_id desc
 
SELECT * FROM sysmail_event_log
ORDER BY log_id  desc

Database_Mail_04
W przypadku pojawienia się problemów z wysyłaniem wiadomości sprawdź przede wszystkim :

  1. Informację o błędach, odpowiedziach serwera w widoku sysmail_event_log.
  2. Wiem, że to banał ale literówki, nieprawidłowa konfiguracja konta to naprawdę najczęstsza przyczyna problemów (bywa że username to pełna nazwa adresu email ;)).
  3. Sprawdź czy usługa brokera jest aktywna.
  4. Sprawdź status kolejki – powinna być STARTED.

    EXECUTE msdb.dbo.sysmail_help_status_sp

    Jeśli nie jest, możesz uruchomić ją za pomocą

    EXECUTE msdb.dbo.sysmail_start_sp

  5. Ustawienia firewall lub co bardziej kłopotliwe – szczególnie w korporacjach, blokowanie portów pocztowych 587. Łatwo to zweryfikować, telnetując się na np. na port 578 serwera SMTP. Jeśli się nie powiedzie, to znaczy że ruch na dany port jest wycinany.
    Database_Mail_06
  6. Szczególne ustawienia serwerów pocztowych SMTP, np. GMAIL, w których podstawowa autentykacja jest traktowana jako niebezpieczna.

    W Gmail, wprowadzono standard Oauth 2.0 i jeśli chcemy korzystać z Basic Authentication musimy jawnie określić, aby z naszego konta mogły korzystać tzw. mniej bezpieczne aplikacje.
    Więcej na ten temat znajdziesz tutaj : https://support.google.com/accounts/answer/6010255?hl=en
    Akceptację aplikacji mniej bezpiecznych, możesz ustawić po zalogowaniu do GMail tutaj : https://www.google.com/settings/security/lesssecureapps

  7. Sprawdź kto jest ownerem bazy systemowej MSDB. Jeśli jakiś „zwykły” user – warto zmienić na SA :)

    select name, suser_sname(owner_sid) as Owner  
    from sys.databases
     
    USE msdb
    GO
     
    EXEC sp_changedbowner [sa]
    GO


Źródła MSDN :
http://technet.microsoft.com/en-us/library/ms175887(v=SQL.105).aspx

Powyższy artykuł dotyczy wersji SQL Server 2008 R2 – 2014

70-463 Implementing a Data Warehouse with SQL Server 2012 – ostatni krok do MCSA

Ostatni egzamin ze ścieżki MCSA SQL Server 2012 za mną – dziś zdałem ten odwlekany już chyba z półtora roku test. Okres wakacyjny + solidnie przepracowane ostatnie miesiące okazały się na szczęście wystarczającym bodźcem aby w końcu podejść do tematu i zamknąć ścieżkę MCSA SQL Server.
MCSA
Poniżej garść informacji na temat samego egzaminu, jak się do niego przygotować i czego spodziewać się na teście.

Przygotowanie do egzaminu 70-463

Najlepszym sposobem i okazją do nauki jest oczywiście komercyjne wdrożenie. Tak więc podczas ostatniego projektu hurtowni danych który prowadziłem, postanowiłem upiec dwie pieczenie na jednym ogniu i ogarnąć „po drodze”, pełen zakres materiału tego egzaminu.

Sam egzamin, odwlekałem strasznie nie tylko z powodu „braku czasu” ale także dlatego, że nie jestem do końca entuzjastą części technologii których dotyczy. Wręcz filozofii wyklikiwania pewnych rozwiązań vs pisanie własnego kodu. Data cleansing / profiling, Data Quality Services lub niektóre z zastosowań SSIS – pewnie to kwestia upodobań. W wielu scenariuszach rozwiązania oparte o T-SQL/skrypty czy CLR są mi zdecydowanie bliższe, działają lepiej i są bardziej wydajne. Choć przyznać trzeba, że pewnie czasem faktycznie prościej jest wyklikać pewne rozwiązania i skorzystać z gotowych komponentów niż pisać samemu własne procedurki. Granica jest dość rozmyta. Nie mniej jednak, aby tworzyć dobre rozwiązania z pewnością trzeba dobrze poznać różne narzędzia i nie ograniczać się tylko do SSIS lub czystego T-SQL.

Nie chciałbym się tutaj rozwodzić nad wyższością własnych, szytych na miarę rozwiązań :) od tych ustandaryzowanych, gotowych i z definicji łatwych w implementacji, które z pewnością sprawdzają się w wielu typowych scenariuszach. Nie mniej jednak, przygotowując się do zdania tego egzaminu, zdecydowałem, aby w projekcie hurtowni i procesów ETL wykorzystać (przetestować) jak największą liczbę funkcjonalności, których dotyka ten egzamin (oczywiście nie wszystkie z nich wdrożyłem komercyjnie).

Utworzyłem dwa równoległe rozwiązania – pierwsze bazujące głównie na logice skryptowej, własnych procedurach składowanych. Drugie, alternatywne, realizujące te same zadania – przeładowane fajerwerkami projekty SSIS :), Data Quality Services i inne funkcjonalności, które uznałem że warto przetestować w praktyce pod kątem egzaminu. Przygotowując się do tego testu, z pewnością najwięcej czasu warto poświęcić na budowaniu procesów ETL, bazujących na SQL Server Integration Services czyli paczek i projektów SSIS. Podejście to w moim przypadku pozwoliło mi na osiągnięcie całkiem przyzwoitego wyniku 874/1000 i tym samym realizacji ścieżki MCSA.

Materiały i pomoce

Moje nieco old-schoolowe podejście do tradycyjnych pomocy naukowych, pchnęło mnie do kupna oficjalnej pozycji edukacyjnej, rekomendowanej przez MS do przygotowań do tego testu czyli – Trainning Kit 70-463. Z tymi Trainning Kitami to różnie bywa, jednak tym razem dodatkową zachętą do kupna tej książki- była magia nazwisk autorów.

70463_TrainningKit

Dejan Sarka to firma podobnie jak Itzik Ben-Gan, której nie trzeba szczególnie reklamować. Jest autorem wielu świetnych książek i publikacji. Tym razem również się nie zawiodłem – uważam, że Trainning Kit 70-463 to lektura, którą można polecić z czystym sumieniem wszystkim przygotowującym się do tego egzaminu a także początkującym developerom rozwiązań DW/SSIS.

Książka zawiera szereg scenariuszy, które po przerobieniu dają solidną podstawę do zdania tego egzaminu. Jak to na tego typu testach bywa, pytania są dość tendencyjne i warto wcześniej odpowiednio ukierunkować się, korzystając z tego typu pomocy. Nawet jeśli czujesz, że masz spore doświadczenie w tworzeniu hurtowni czy paczek SSIS, pytania testowe mogą czasem zaskoczyć. Szkoda że nie można przejrzeć po egzaminie błędnych odpowiedzi bo pomimo, że zdałem na ponad 87% nie wiem w których pytaniach popełniłem błędy.

Drugim źródłem wiedzy które można z pewnością polecić jest oficjalny kurs MS-10777. Szczególnie dla osoby początkującej w zagadnieniach DW/SSIS to szkolenie jest nieocenionym punktem wyjścia i drogą na skróty o ile tylko cena nie stanowi przeszkody (niestety są one dość drogie).

Dlaczego warto zdać te trzy egzaminy i zdobyć MCSA SQL Server ? Chociażby dlatego że jest to wspólna dla wersji 2012 i 2014 ścieżka certyfikująca, potwierdzająca szeroką wiedzę z zakresu administracji, pisania zapytań i tworzenia rozwiązań DW / ETL dla tych platform. Poza tym jest to zawsze świetna, dodatkowa motywacja do nauki nowych funkcjonalności.

Wyszukiwanie ciągłych zakresów, dziur, problemy wysp w SQL

Weryfikacja ciągłości zakresu, identyfikacja dziur oraz scalanie przedziałów, przydaje się do rozwiązywania różnych zadań. W systemach transakcyjnych, szczególnie mocno obciążonych przez wielu użytkowników, mogą wystąpić sytuacje gdy funkcja IDENTITY nie zapewnia bezwzględnej ciągłości identyfikatorów. Dotyczy to również obiektów wprowadzonych w SQL Server 2012, znanych już od dawna w Oracle – sekwencji.

Artykuł ten, nie będzie jednak o bezpiecznym nadawaniu kolejnych, ciągłych identyfikatorów (polecam triggery INSTEAD OF INSERT). Zaprezentuję tu kilka wskazówek i praktycznych przykładów jak wykrywać nieciągłości, identyfikować dziury oraz scalać zakresy.

Niezwykle pomocne okazują się tutaj funkcje szeregujące bazujące na funkcji okna OVER() oraz wspólne wyrażenia tablicowe.


Wykrywanie nieciągłości w zbiorze

Na początek coś bardzo prostego. Trochę podstaw, bez których trudno jest rozwiązywać bardziej skomplikowane przykłady.

Załóżmy, że mamy zbiór elementów ponumerowanych w zakresie od 1-1000, inkrementowanych co 1. Każdy ciągły, jednostajnie rosnący zbiór wartości, powinien zwrócić ten sam wynik dla takiego wyrażenia:

Wartość_elementu  – ( Inkrementacja * Nr_elementu_w_szeregu ) = Const.


Wykorzystując funkcję ROW_NUMBER() i powyższą zasadę, sprawdźmy ciągłość dla poniższego zbioru:

Use tempdb
GO
 
set nocount on 
go
 
IF ( OBJECT_ID('dbo.zakres') is not null)  
drop table dbo.zakres;
 
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1
 
WHILE (@zmienna<=1000)
BEGIN
	insert into  dbo.zakres values (@zmienna)
	set @zmienna = @zmienna + 1
END;
 
-- kwerenda sprawdzająca
with CheckCont as (
	-- dla inkrementowanego o 1
	select distinct T1.VLAN  - (1*(ROW_NUMBER() OVER (ORDER BY  VLAN))) as Diff
	from dbo.zakres t1
)
Select CASE WHEN Count(Diff)> 1 then 'Discontinuous' else 'Continuous' end as Result
from CheckCont

Zakresy_ciagle_01

Jak widać bez niespodzianek – jest ok. Skasujemy teraz kilka losowych rekordów i ponowie zweryfikujemy ciągłość elementów, tą samą kwerendą sprawdzającą.

DELETE FROM dbo.zakres
WHERE VLAN IN (
SELECT TOP 3 VLAN from dbo.zakres ORDER BY NEWID() 
)

Zakresy_ciagle_02


Scalanie zakresów

Scenariusz, z którym ostatnio się spotkałem to scalanie zakresów VLAN w konfiguracji interfejsów urządzeń sieciowych (routerów). Informacje o VLANach są przechowywane w tabeli, w której każdy rekord, odpowiada pojedynczemu VLANowi, przypisanemu do danego portu, określonego urządzenia. To co trzeba wykonać, to agregacja (sklejenie zakresów) do skrótowej postaci przyjaznej dla użytkownika.

Wygenerujmy sobie prostą tabelę, z nieciągłą numeracją w kolumnie VLAN :

IF ( OBJECT_ID('dbo.zakres') is not null)  drop table dbo.zakres;
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1, @los int =0, @cnt int =1
 
WHILE (@cnt<=10)
BEGIN
 
	SET @los = case when   CONVERT(INT, (11) * RAND()) > 8  then 1 else 0 end
 
	insert into dbo.zakres values (@zmienna + @los)
	SELECT @zmienna = @zmienna +1+ @los, @cnt +=1
END;
 
select * from dbo.zakres

Zakresy_ciagle_03

Zadaniem jest wykrycie i scalenie zakresów rekordów o ciągłej numeracji. Stosując się do reguły ciągłości, którą pokazałem w poprzednim przykładzie, sprawdźmy rezultat tej formułki dla kolejnych wierszy. Widać, że wynik może posłużyć jako bezwzględny identyfikator określonego, ciągłego zakresu. W naszym przypadku mamy 4 zakresy, w tym dwa wieloelementowe.

Zakresy_ciagle_04

Kluczowym znów będzie numerowanie rekordów i późniejsze ich grupowanie względem wyliczonego w ten sposób identyfikatora zakresu. Dwa podejścia – stare, sprzed SQL Server 2005 z zapytaniem skorelowanym :

with PoNumerowane as
(
	select VLAN ,  
           (select count(*) from dbo.zakres t2 where t2.VLAN  <= t1.VLAN )  as IdRek
	from dbo.zakres t1
)
select VLAN - IdRek as ZakresID, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from PoNumerowane
group by VLAN - IdRek

Oraz lepsze, zdecydowanie bardziej wydajne, rozwiązanie z użyciem funkcji ROW_NUMBER() :

with Ponumerowane as (
	select VLAN , ROW_NUMBER() OVER(order by VLAN)  as IdRek,
	 VLAN - ROW_NUMBER() OVER(order by VLAN) as IdZakres
	from dbo.zakres t1 
)
select IdZakres, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from Ponumerowane
group by IdZakres

Zakresy_ciagle_05
Takie rozwiązanie można ubrać w funkcję użytkownika i prezentować scalone zakresy liczb w przyjaznej formie, np. dodatkowo agregując je do postaci skalarnej (FOR XML PATH).


Wyszukiwanie brakujących elementów w zbiorze

Na koniec sytuacja, gdy interesuje nas to, czego nie ma w tabeli. Mogą to być braki numerów zamówień, albo dziury w ciągłym zakresie adresacji urządzeń IP, nadające się do ponownego przydzielenia. Identyfikację brakujących elementów, na przykładzie zbioru inkrementowanego o 1, możemy wykonać na przykład tak :

with cte as (
 
	SELECT (select MIN(VLAN) from dbo.zakres) as VLAN, 
(select MAX(VLAN) from dbo.zakres) as MaxVal
	UNION ALL
 
	-- zakładamy inkrementację + 1 
	SELECT VLAN + 1 , MaxVal
	FROM CTE  c 
	where VLAN < MaxVal
)
select c.VLAN as MissingVlan from cte c left join dbo.zakres t on c.VLAN = t.VLAN
where t.VLAN is null
OPTION (MAXRECURSION 0)

Zakresy_ciagle_06

Grafy, drzewa i hierarchie w SQL

Z grafami można spotkać się nad wyraz często. Takie najprostsze to struktura katalogów na dysku czy hierarchia przełożony-podwładny w firmie.

Bardziej skomplikowane znajdziesz w serwisach społecznościowych – np. drzewa znajomości. W sieciach teleinformatycznych będą to struktury połączeń pomiędzy węzłami. W nawigacji GPS – najkrótsze ścieżki od punktu A-Z itd.

Z punktu widzenia baz danych i tworzenia zapytań do tego typu struktur – zadania z grafami nie są trywialne. Szczególnie, gdy chcemy je rozwiązać za pomocą pojedynczej kwerendy.
W artykule tym, zaprezentuję kilka praktycznych scenariuszy wraz z rozwiązaniami bazującymi na SQL Server 2012. Mam nadzieję, że jeśli szukasz gotowych rozwiązań – uda Ci się zaadoptować prezentowane tutaj przykłady do swoich zastosowań.

W każdym z nich, wyzwaniem było napisanie rozwiązania w postaci pojedynczej kwerendy. Podejście proceduralne jest prostsze w zrozumieniu i może prowadzić do bardziej efektywnych rozwiązań (materializowanie pośrednie, indeksy na tabelach tymczasowych).

Trochę teorii

Patrząc na matematyczną teorię grafów, możemy mieć do czynienia z różnymi typami tych struktur. Na początek zajmiemy się grafami prostymi, wprowadzając przy okazji kilka podstawowych pojęć. Zbudujemy sobie tło do bardziej zaawansowanych scenariuszy.


Graf prosty (acykliczny, nieskierowany)

Graf prosty – to drzewo bez cykli, krawędzie nie są skierowane (nie ma ograniczeń kierunkowych) a pomiędzy każdą parą dowolnych wierzchołków, jest tylko jedna ścieżka. Dodanie jakiejkolwiek nowej krawędzi, spowoduje powstanie cyklu a usunięcie istniejącej doprowadzi do niespójności (podział grafu).

Grafy_Drzewa_SQL_01

W przypadku, gdy jeden z wierzchołków, jest wyszczególniony (korzeń), mówimy o drzewie ukorzenionym. Grafy proste, ukorzenione są łatwe w implementacji w relacyjnych bazach. Tworzenie zapytań do takich struktur nie stwarza większych problemów. Tego typu graf, to na przykład organizacja systemu plikowego lub hierarchia pracownicza – przełożony/podwładny.

W typowej organizacji plików i katalogów zakładamy, że dany obiekt (plik lub katalog) nie może być w dwóch różnych miejscach jednocześnie (pomijając skróty, które mogą tworzyć cykle – o tym później). Do każdego węzła, jest tylko jedna ścieżka, prowadząca ,od korzenia grafu. Patrząc na tą strukturę, całość stanowi jedno spójne drzewo – graf prosty, ukorzeniony.

Grafy_Drzewa_SQL_02

Podobnie w strukturze zatrudnienia. Dany pracownik zazwyczaj nie może mieć na tym samym poziomie dwóch bezpośrednich szefów. Droga eskalacji od podwładnego do przełożonego najwyższego szczebla, jest jedna.

Implementacja takiej struktury w bazie danych może być bardzo prosta. Realizuje się ją zazwyczaj, jako SELF JOIN, czyli złączenie tabeli samej ze sobą.

USE tempdb
GO
 
CREATE TABLE dbo.Folders (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    Folder VARCHAR(100) NOT NULL,
    Parent INT NULL REFERENCES dbo.Folders( ID ) 
);
 
INSERT INTO dbo.Folders ( Folder ) VALUES ('C:');
 
INSERT INTO dbo.Folders ( Folder, Parent )
VALUES  ('Inetpub',1),('Program Files',1),('Windows',1),('wwwroot',2),
('Adobe',3),('MS SQL',3),('Data',7),('system32',4),('Data',9);
 
select * from dbo.Folders

Grafy_Drzewa_SQL_03

Zapytania SQL do takiego grafu, również nie są szczególnie skomplikowane. Wystarczy wykorzystać rekurencję wspólnych wyrażeń tablicowych (CTE Common Table Expressions) i przeszukiwać drzewo w głąb od korzenia do liści.

WITH Paths AS 
(
    SELECT Id, Folder, CAST(null AS VARCHAR(8000)) AS Parent,
           CAST(Folder + '\' AS VARCHAR(8000)) FullFolderName, 0 as Poziom
    FROM dbo.Folders
    WHERE Parent IS NULL   
 
    UNION ALL
 
    SELECT f.Id, f.Folder, p.FullFolderName, 
           CAST(ISNULL(p.Parent, '')  + p.Folder + '\' 
		+  f.Folder + '\'  AS VARCHAR(8000)) , p.Poziom +1
    FROM dbo.Folders f INNER JOIN Paths p
                                ON f.Parent= p.ID
)
SELECT * FROM Paths
Order by FullFolderName

Grafy_Drzewa_SQL_04

Takie podejście, załatwia również sprawę, w sytuacji, gdy mamy do czynienia ze zbiorem grafów prostych (las drzew). Zakotwiczenie CTE, identyfikuje wszystkie grafy i rekurencyjnie rozpina ja analogicznie jak poprzednio. Drzewa możemy ponumerować, do późniejszej analizy.

-- dorzućmy nowe drzewo - dysk D:
Insert into dbo.Folders(Folder) Values('D:');
 
Insert into dbo.Folders ( [Folder], [Parent] )
VALUES('Music',11),('Docs',11),('Mike Oldfield',12),('Tres Lunas',14);
 
WITH Paths AS 
(
    SELECT Id, Folder, CAST(null AS VARCHAR(8000)) AS Parent,
CAST(Folder + '\' AS VARCHAR(8000)) FullFolderName, 0 as Poziom, 
ROW_NUMBER() OVER(ORDER BY FOLDER) as TreeNo
    FROM dbo.Folders
    WHERE Parent IS NULL   
 
    UNION ALL
 
    SELECT f.Id, f.Folder, p.FullFolderName, 
           CAST(ISNULL(p.Parent, '')  + p.Folder + '\' 
		+  f.Folder + '\'  AS VARCHAR(8000)) , p.Poziom +1, TreeNo
    FROM dbo.Folders f INNER JOIN Paths p
                                ON f.Parent= p.ID
)
SELECT * FROM Paths
Order by FullFolderName

Grafy_Drzewa_SQL_05

W SQL Server od wersji 2008, mamy dostępny specjalny typ danych HierarchyId. Pozwala on na tworzenie bardziej zaawansowanych struktur w porównaniu do SELF JOINów. Za jego pomocą, można bardzo łatwo zamodelować scenariusz w którym istotna jest kolejność węzłów na danym poziomie hierarchii.


Graf nieskierowany z cyklami

Sytuacja staje się bardziej złożona, gdy mamy do czynienia z grafami, które posiadają cykle. Jest to typowy przykład architektury sieci teleinformatycznej, w której istnieją redundantne ścieżki w celu zapewnienia protekcji. Innym przykładem z którego korzystasz na co dzień to po prostu siatka połączeń drogowych pomiędzy miastami.

Grafy_Drzewa_SQL_06

Wyszukiwanie ścieżek pomiędzy dowolnymi węzłami takiego grafu, musi uwzględniać możliwość zapętlenia. Dla każdej pary węzłów, może pojawić się wiele ścieżek. Zależnie od stopnia złożoności grafu (liczby węzłów i krawędzi) analiza możliwych dróg, może stanowić nie lada wyzwanie.

Grafy_Drzewa_SQL_07

Skrypt z danymi do przeprowadzenia dalszych ćwiczeń od pobrania tutaj.

Implementacja takiego grafu w relacyjnej bazie danych, może być oparta o dwie tabele – węzłów i krawędzi między nimi. Na początek znajdźmy wszystkie ścieżki z punktu A-Z.

WITH Edges AS (
	-- graf nieskierowany więc bierzemy pod uwagę krawędzi dwukierunkowo
        -- A-Z i Z-A
	SELECT Node1 as Start, Node2 as Koniec, Metric from EDGE
	UNION
	SELECT Node2, Node1 , Metric from EDGE	
),
Paths as 
(
	-- rekurencyjne CTE
        SELECT start , koniec ,  
        CAST('.' + start + '.' + koniec + '.' as varchar(max)) as paths, 1 as Dist
	FROM Edges
 
	UNION ALL
 
	SELECT f.start, t.koniec , CAST(f.paths + t.koniec + '.' as varchar(max)), 
	F.dist+1
	FROM Paths as F join Edges T on 
	--  wykluczenie cykli, czyli nie przechodzimy 
        -- przez żaden wierzchołek dwukrotnie
		case when F.paths like '%.' + T.koniec  + '.%' then 1 else 0 end = 0
		 and f.koniec = T.start
)
SELECT *  
FROM  Paths
WHERE Start = 'A' and Koniec = 'Z'
ORDER BY Dist

Grafy_Drzewa_SQL_08

Powyższe podejście, pomimo że w jednej kwerendzie, jest słabe wydajnościowo i przy dużej liczbie (tysiące) węzłów / krawędzi działa po prostu bardzo wolno. Wystarczy spojrzeć na plan wykonania – przypomina zapytanie prawdziwie skorelowane. Alternatywnym rozwiązaniem jest podejście proceduralne, w którym tworzymy drzewa rozpinające.


Graf skierowany z cyklami i metrykami krawędzi

Wyszukiwanie najkrótszej drogi z punktu A-Z, zazwyczaj wymaga analizy jakości poszczególnych odcinków. Sytuacja taka ma zastosowanie zarówno w sieciach drogowych (autostrady, drogi lokalne), jak również informatycznych. W tych drugich, brane są pod uwagę zazwyczaj parametry jakościowe, czyli pasmo, opóźnienia, jitter. O ile w sieciach teleinformatycznych kierunkowość zazwyczaj jest tematem pomijalnym, o tyle drogi jednokierunkowe, to norma.

Bazując na danych z poprzedniego przykładu, wyznaczmy raz jeszcze najlepszą ścieżkę z A-Z, biorąc pod uwagę kierunkowość krawędzi oraz metryki. Nasz graf będzie wyglądał teraz tak :

Grafy_Drzewa_SQL_09

W tej sytuacji widać, że pomiędzy węzłami A i Z powinny zostać znalezione tylko dwie ścieżki. Ponadto nie istnieje żadna ścieżka powrotna (Z > A). Zmodyfikujemy odrobinę zapytanie z poprzedniego przykładu :

WITH Edges AS (
	-- graf skierowany więc bierzemy drogi takie jakie są
	SELECT Node1 as Start, Node2 as Koniec, Metric from EDGE
),
Paths as 
(
	SELECT start , koniec ,  
        CAST('.' + start + '.' + koniec + '.' as varchar(max)) as paths, 1 as Dist, Metric
	FROM Edges 
 
	UNION ALL
 
	select f.start, t.koniec , CAST(f.paths + t.koniec + '.' as varchar(max)), 
	F.dist+1, F.Metric + T.Metric
	from Paths as F join Edges T on 
                 case when F.paths like '%.' + T.koniec  + '.%' then 1 else 0 end = 0
		 and f.koniec = T.start
),
PathRanking as (
select   *, 
	DENSE_RANK() OVER(Partition BY Start, Koniec Order by Metric, Dist) as RNK
from Paths)
Select * from PathRanking
where start = 'A' and koniec = 'Z';

Grafy_Drzewa_SQL_10

Obydwie kosztują tyle samo (suma metryk jest identyczna), ale droga bezpośrednia A>Z ma mniej skoków, stąd zostaje wybrana jako najlepsza.


Las drzew (grafów) nieskierowanych, nieukorzenionych

Bazując na powyższej metodzie, możemy pokusić się o rozwiązanie jeszcze jednego problemu. Zadaniem nietrywialnym, jest analiza zbioru grafów nieskierowanych bez korzenia. Tego typu struktury, spotkać możesz na przykład w serwisach społecznościowych.

Grafy_Drzewa_SQL_11

Wyobraźmy sobie prostą tabelę powiązań (znajomości) pomiędzy użytkownikami. Pytanie w jaki sposób, określić czy dana osoba należy do jakiegoś kręgu (grupy znajomości mogą się tworzyć samoczynnie, bez dodatkowych relacji). Daną grupę, wyznaczają wszystkie osoby, które są w jakikolwiek sposób ze sobą powiązane (nawet pośrednio). Czyli tak naprawdę, musimy zidentyfikować przypisanie każdej krawędzi do określonego drzewa.

Rozwiązaniem w postaci jednej kwerendy, może być modyfikacja znanego już algorytmu wyznaczania ścieżek.

WITH Znajomi as 
(
	select * from (
	VALUES  ('a','b'),('a','i'),('b','i'),('c','j'),('d','j'),
		('e','k'),('f','k'),('g','l'),('h','l'),('e','l')--,('a','e')
 
	) as Tab(a,b)
 
), 
Edge as (
 
	select l.a as Start , cast( l.b  as varchar) as Koniec, 1 as dist 
	from Znajomi l
	union all 
		select cast( l.b  as varchar) , l.a, 1
	from Znajomi l
),
Paths as 
(
	select start , koniec ,  CAST('.' + cast(start as varchar(10))+ '.' 
		+ cast(koniec  as varchar(10)) + '.' as varchar(max)) as path, dist
	from Edge
 
	UNION ALL
 
	select f.start, t.koniec , 
		CAST(f.path + cast(t.koniec as varchar(10)) + '.' as varchar(max)), F.dist+1
	from Paths as F join Edge T on 
	case when F.path like '%.' + cast(T.koniec as varchar(10)) + '.%' then 1 else 0 end = 0
		and f.koniec = T.start
)
, 
Grupy as (
 
   select c.a, c.b , SUBSTRING(max(path),2,CHARINDEX('.',max(path),2)-2) as GRID
   from  Znajomi c left join Paths    r  on r.Path like '%' + c.a + '.' +  
   cast( c.b  as varchar) + '%' or r.Path like '%' + cast( c.b  as varchar) + '.' + c.a  + '%' 
   group by c.a, c.b 
)
select a, b, DENSE_RANK() OVER(order by GRID) as Grupa from Grupy

Grafy_Drzewa_SQL_12

Zauważ, że wykorzystałem tu pewne fundamentalne i oczywiste założenie. W danym drzewie, mamy możliwość dojścia do każdego węzła. W przeciwnym razie, dany graf byłby niespójny i stanowiłby tak naprawdę dwa lub więcej drzew.

W związku z tym, w ostatnim CTE (Grupy), grupuję po maksymalnej (równie dobrze mogłaby to by być minimalna) ścieżce, która zawiera identyfikatory węzłów. Na podstawie tego największego identyfikatora węzła, określam przynależność do grupy.


Podsumowanie

Zaprezentowane w tym artykule rozwiązania to tylko jedne z możliwych podejść. Proceduralnie można by je było rozwiązać znacznie prościej i szybciej. Jeśli masz propozycję rozwiązania tych zadań w bardziej efektywny sposób za pomocą jeden kwerendy – zapraszam do dyskusji !

Tematyka grafów jest znacznie szerzej (świetnie) opisana w książce Itzika Ben Gana „Inside Microsoft SQL Server 2008: T-SQL Querying” – polecam wszystkim.

Wielokrotne grupowanie – GROUPING SETS, ROLLUP, CUBE

Tworząc raporty czy zestawienia bezpośrednio z bazy transakcyjnej, wchodzimy na teren analityki biznesowej BI (Business Intelligence) w zwykłej bazie OLTP (OnLine Transaction Processsing). W środowiskach komercyjnych, granica pomiędzy OLTP a bazami OLAP (OnLine Analytical Processing) jest czasem całkowicie zatarta. Zdarza się tak w sytuacji gdy po prostu nie ma wdrożonych, dedykowanych rozwiązań BI. Całość raportowania odbywa się wtedy wprost z bazy transakcyjnej.

Analityka biznesowa (raportowanie), cechuje się wieloma operacjami przekształcania danych i przekuwania ich na konkretną wiedzę. Takie zapytania bywają kosztowne jeśli chodzi o wydajność. Dlatego żeby nie obciążać systemu transakcyjnego, wykonywana są w osobnych strukturach. Jest to powód dla którego tworzone są hurtownie danych i wszelkiej maści rozwiązania BI.

W artykule tym opisuje rozszerzenia T-SQL pozwalające na wielokrotne grupowanie (wstęp do kostek analitycznych). Opisywane tu metody, bazują na zagadnieniach dotyczących grupowania danych i łączenia pionowego zbiorów, prezentowanych w tym kursie we wcześniejszych artykułach.


Wielokrotne grupowanie

W T-SQL mamy dostępnych kilka rozszerzeń składni, pozwalających na sięgnięcie do wiedzy analitycznej. Obok funkcji szeregujących i analitycznych, jednymi z ciekawszych są trzy funkcje pozwalające na wykonywanie wielokrotnych grupowań w zwykłej (pojedynczej) kwerendzie.

Wyobraźmy sobie taki scenariusz (działając na danych z bazy Northwind) :

Chcemy utworzyć raport, przedstawiający informacje o sprzedaży w latach 1997-1998 na kilku poziomach szczegółowości. Zagregowane dane na temat liczby zleceń, chcemy wyświetlić względem lat, kwartałów i na koniec całościowo (jako najwyższy poziom agregacji).

Wielokrotne_grupowanie_01

Tego typu rozbicie wielopoziomowej agregacji, można wykorzystać np. w aplikacji raportującej, w której mamy możliwość „drążenia danych”, czy po prostu w zwykłym raporcie. Powyższy przykład to nic innego jako fragment kostki analitycznej z kilkoma wymiarami czasu.

Znając już operacje na zbiorach (UNION) i możliwości grupowania (GROUP BY), mógłbyś to zadanie rozwiązać za pomocą tych elementarnych konstrukcji, np. w taki sposób :

USE Northwind
GO
 
-- Query1
SELECT 1 as Poziom, NULL as Rok, NULL as Kw , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
 
UNION
 
SELECT 2, Year(Orderdate) as Rok, NULL, count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate) 
 
UNION
 
SELECT 3,Year(Orderdate)  as Rok, DatePart(q,Orderdate), count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate)  , DatePart(q,Orderdate)
ORDER BY 2,1

Zaprezentowane powyżej rozwiązanie, łączenia wyników grupowań tej samej kwerendy w różnych kombinacjach, jest mało efektywne.

Z punktu widzenia przetwarzania są to trzy osobne kwerendy, które odpytują te same dane (dublujące się odczyty) i procesowane są niezależnie. Z pomocą przychodzą nam rozszerzenia T-SQL, pozwalające zrealizować tego typu scenariusze znacznie prościej i wydajniej.


ROLLUP

ROLLUP rozszerza funkcjonalność klauzuli GROUP BY, o możliwość tworzenia tzw. kostek analitycznych połówkowych. Funkcja ta, przyjmuje jako parametry, analogicznie jak w zwykłym grupowaniu, atrybuty (nazwy kolumny) tabel wejściowych lub ich przekształcenia.

Jeśli zastosujesz ROLLUP (a,b,c), wykonane zostaną grupowania dla kolejnych kombinacji atrybutów :

  • GROUP BY (a,b,c)
  • GROUP BY (a,b)
  • GROUP BY (a)
  • oraz po całości, czyli GROUP BY () – co w praktyce zapisujemy bez jawnego grupowania (wszystkie kolumny tworzą część danych surowych)

ROLLUP to równoważnik realizacji N+1 grupowań. W przypadku podania trzech (a,b,c) atrybutów, zostaną wykonane 3 + 1 = 4 operacje. Co warto podkreślić, wydajnościowo będzie to znacznie bardziej efektywne zapytanie, niż osobne grupowania i łączenia zbiorów za pomocą UNION, pokazane w poprzednim przykładzie (Query1).

Zobaczmy teraz rozwiązanie naszego scenariusza, czyli na trzech poziomach za pomocą ROLLUP :

-- Query2
SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP( Year(Orderdate)  , DatePart(q,Orderdate) )

Wielokrotne_grupowanie_02

Sam zapis kwerendy, jak również jej plan wykonania (Query2) przy tym podejściu jest znacznie prostszy i co ważniejsze – bardziej efektywny. Wystarczy zerknąć na porównanie wydajności zapytań :

Wielokrotne_grupowanie_03

a także statystyki odczytów :

Wielokrotne_grupowanie_04

Odnosząc się do poprzedniego przykładu –3 krotnie została zmniejszona liczba odczytów (tu też widać, że każda kwerenda w UNION, była realizowana niezależnie).

Sam wynik może jeszcze nie być do końca satysfakcjonujący (brakuje informacji o poziomach agregacji), ale tym zajmiemy się w dalszej części tego artykułu – przy okazji omówienia funkcji GROUPING_ID().


CUBE

Drugą funkcjonalnością wielokrotnego grupowanie to CUBE czyli pełna kostka analityczna. Stosujemy ją podobnie jak ROLLUP w poleceniu GROUP BY. Jej działanie to wykonanie grupowania we wszystkich wymiarach. Czyli jeśli podamy 3 atrybuty, to wykonanych zostanie 2^3 = 8 operacji.

Chciałbym przy okazji przypomnieć, jedną z podstawowych zasad pisania zapytań. Zawsze pobieramy tylko takie dane, jakie w danym momencie potrzebujemy. CUBE wykonuje dla nas 2^N-tej grupowań – to kosztuje. Odnieśmy się do naszego przykładu i zamieńmy ROLLUP na CUBE :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY CUBE(Year(Orderdate)  , DatePart(q,Orderdate))

Wielokrotne_grupowanie_05

Dla dwóch atrybutów w CUBE(), wykonane zostały 4 grupowania. W porównaniu do ROLLUP, pojawił się dodatkowy poziom, tylko po kolumnie Q – kwartały. Niezależnie od roku, mamy informacje o całkowitej liczbie zleceń w kwartałach.

W praktyce, CUBE stosujemy w procesach ETL, rzadko w zwykłych widokach czy zapytaniach, właśnie ze względu na ilość grupowań (pełna kostka).


GROUPING SETS

Na koniec zostawiłem wisienkę na torcie, czyli najbardziej elastyczny i chyba najczęściej wykorzystywany w praktyce sposób na wielokrotne grupowanie.

Za pomocą GROUPING SETS możemy określić konkretne poziomy grupowań. ROLLUP i CUBE opisane wcześniej miały z góry narzuconą liczebność grup. W tym przypadku, możemy jawnie określić zbiory atrybutów po których wykonamy agregacje. Jest to więc także bardziej wydajna, bo oszczędna metoda, gdy nie potrzebujemy tylu wymiarów, ile dają dwie poprzednie metody. Z tego też powodu, jest chętnie stosowana na przykład w widokach, czy zwykłych kwerendach.

Odpowiednikiem ROLLUP i rozwiązaniem naszego scenariusza za pomocą GROUPING SETS będzie poniższa kwerenda :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(),
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Sama struktura jest intuicyjna i wymaga tylko określenia zbiorów grupujących. Będą to atrybuty tabel wejściowych lub ich przekształcenia, analogicznie jak w zwykłym grupowaniu, po których będą wykonywane kolejne agregacje.

Załóżmy, że nie interesuje nas grupowanie podsumowujące całą sprzedaż w zadanym okresie (po całości), ale chcemy wyświetlić tylko raport na dwóch poziomach szczegółowości . Zagregowane dane na temat ilości zamówień w poszczególnych latach i kwartałach.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Wielokrotne_grupowanie_06

W takim scenariuszu, najlepiej właśnie użyć GROUPING SETS – zgodnie z zasadą, pobierania tylko takich danych jakie są nam potrzebne.


Funkcje GROUPING() oraz GROUPING_ID()

Z przedstawionymi powyżej trzema metodami grupowania wielokrotnego, skojarzone są dwie specjalne funkcje skalarne. W grupowaniu wielokrotnym, nie mamy jawnie podanej informacji, na jakim poziomie dana operacja się odbywa. Za pomocą tych funkcji, możemy zidentyfikować, czy dana kolumna, tworzy sekcję grupującą czy nie. Jest to szczególnie przydatne, w ostatecznym sortowaniu wyniku, aby np. otrzymać taki raport jak w pierwszym przykładzie tego artykułu.

Funkcja GROUPING(), przyjmuje jako parametr, dowolny atrybut (zazwyczaj nazwę kolumny), używany w grupowaniu. Zwraca wartość 0 lub 1, w zależności od tego, czy dana kolumna, wchodzi w skład sekcji grupującej (0) czy nie(1). Trochę to na odwrót w przyjętej powszechnie logice, ale można się przyzwyczaić.

Zobaczmy jej działanie na przykładzie :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING( Year(Orderdate) ) +  GROUPING( DatePart(q,Orderdate) ) as Poziom 
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, Poziom DESC

Wielokrotne_grupowanie_07

Na najwyższym poziome grupowania, czyli bez wnikania w wartości lat i kwartałów, zarówno atrybut YEAR(OrderDate) jak i DATEPART(q,OrderDate) nie tworzą sekcji grupującej. Funkcja GROUPING() dla tych atrybutów zwraca wartość 1.

Analogicznie w wierszu drugim i siódmym, mamy grupowanie tylko po latach, bez wnikania w wartości kwartałów. Jest to odpowiednik GROUP BY YEAR(OrderDate). Wartość GROUPING(YEAR(OrderDate)) będzie w tym przypadku 0, ponieważ ten atrybut tworzy sekcję grupującą.

Jak widać na tym przykładzie, za pomocą GROUPING(), możemy obliczyć wartość identyfikującą poziom grupowania.

Drugą funkcją, która już bez zbędnych ceregieli wyznaczy nam od razu identyfikator, jest GROUPING_ID().

Wynik takiej funkcji jest zawsze w postaci bitowej zamienionej na dziesiętną. Poniżej przykład obliczania jej wartości dla ROLLUP ( a, b, c ).

Wielokrotne_grupowanie_08

Odnieśmy się na koniec do naszego przykładu. Będziemy mieli dwa atrybuty grupujące, zatem wartości GROUPING_ID, będzie dla ROLLUP (a , b) zwracała wartość 0, 1 oraz 3.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING_ID( Year(Orderdate) ,  DatePart(q,Orderdate) ) as PoziomBinarny
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, PoziomBinarny DESC

Wielokrotne_grupowanie_09


Podsumowanie

Wielokrotne grupowanie za pomocą GROUPING SETS, ROLLUP I CUBE sprawdza się szczególnie w procesach ETL (Extract, Transform and Load). Rozszerza także możliwości zwykłych kwerend, skracając ich zapis i poprawiając wydajność w porównaniu do tradycyjnych metod wykorzystujących pojedyncze grupowanie i łączenie zbiorów za pomocą UNION.