Własne agregacje w SQL Server
Co mamy do dyspozycji?
SQL Server, jak zresztą inne bazy danych spełniające standard, posiadają pewien zestaw funkcji agregujących. Pewnie każdy średnio doświadczony użytkownik zetknął się z funkcją COUNT, SUM lub MAX. Można sobie wyobrazić, jak komplikowałyby się niektóre zapytania, gdyby tych funkcji nie było. O ile funkcja SUM nie jest trudna do realizacji, o tyle funkcje wyliczające standardowe odchylenia już tak łatwe nie są.
Dla SQL Server 2012 lista wbudowanych agragacji jest następująca: AVG, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, GROUPING_ID, MIN, MAX, SUM, STDEV, STDEVP, VAR i VARP. Jak to zwykle z takimi listami bywa, dla niektórych są one za krótkie. Nie ukrywam, że i ja potrzebowałem czegoś innego. Na szczęście SQL Server pozwala na rozszerzenie tej listy o prawie dowolne konstrukcje dzięki integracji z .NET CLR.
Jak się zintegrować z SQL Server?
SQL Server potrzebuje biblioteki klas .NET, która będzie zawierała odpowiednio oznaczoną strukturę (structDa się też to zrobić definiując klasę, ale wymaga to dodatkowych czynności polegających na wymuszeniu określonego porządku poszczególnych pól do serializacji.) - służy do tego atrybut SqlUserDefinedAggregateAttribute. Taka struktura musi posiadać zestaw metod, które będą wywoływane przez SQL Server podczas przetwarzania danych. Zatrzymam się jednak chwilę na samym atrybucie i jego parametrach. Obowiązkowym parametrem jest format serializacji, który może przyjąć jedną z dwóch wartości: Native lub UserDefined. Jest jeszcze trzecia wartość, Unknown, oznaczająca format nieznany, ale nie ma potrzeby go używać. Format Native oznacza, że SQL Server sam będzie serializował strukturę wraz ze swoimi danymi. SQL Server potrafi w ten sposób zapisywać typy proste (int, bool, double) i typy SQL reprezentowane przez .NET (SqlInt32, SqlDateTime, SqlDouble, SqlMoney). Warto wiedzieć, że typ string nie jest wspierany. Format UserDefined pozwala na zdefiniowanie własnego sposobu serializacji. Wymaga on nadpisania dodatkowych metod (IBinarySerialize.Write and IBinarySerialize.Read i wykonaniu kilku dodatkowych czynności (np. ustawienie właściwości MaxByteSize w atrybucie SqlUserDefinedAggregateAttribute. Na początek, aby zrozumieć sposób definiowania własnych agregatów, wykorzystana zostanie serializacja Native.
Oprócz sposobu serializacji istnieje szereg innych właściwości atrybutu SqlUserDefinedAggregateAttribute, które można ustawić (kolejność alfabetyczna):
- IsInvariantToDuplicates - Czy na wynik końcowy ma wpływ to, że jedna z wartości pojawi się dwa razy? Jeżeli duplikaty nie mają znaczenia należy ustawić na true. Dla sumy duplikat ma znaczenie, dla sumy bitowej druga taka sama wartość nie zmieni wyniku.
- IsInvariantToNulls - Czy na wynik końcowy ma wpływ to, że wśród wartości pojawi NULL? Jeżeli NULL nie ma znaczenia należy ustawić na true. NULL może być omijany przy liczeniu średniej, ale brany pod uwagę przy liczeniu liczby wystąpień.
- IsInvariantToOrder - Czy na wynik końcowy ma wpływ to, że wartości są we właściwej kolejności? Jeżeli kolejność nie ma znaczenia należy ustawić na true. Dla sumy kolejność nie ma znaczenia, przy łączeniu wartości tekstowych przecinkiem, kolejność może być istotna.
- IsNullIfEmpty - Czy dla pustej listy wynikiem powinien być NULL?
- Name - nazwa funckji agregującej
Trzeba wiedzieć, że atrybuty mogą być wykorzystywane przez silnik SQL Server do różnych optymalizacji. Skoro dla naszej funkcji agregującej nie ma znaczenia kolejność, SQL Server może pominąć kosztowną operację sortowania. Skoro powtórzenia nie mają wpływu na wynik, to może wydajniej jest ominąć operację DISTINCT? A co ze zrównoleglaniem operacji, czy w niektórych konfiguracjach nie da się zlecić zadania kilku niezależnym procesorom? Takie rozważania SQL Server nie są pozbawione sensu, bo mogą mieć wpływ na użyty plan wykonania. Lepiej zatem dobrze przemyśleć i ustawić opisane powyżej atrybuty.
Wymagane metody
To, że obstawimy strukturę atrybutami to jedno, sama treść i logika agregacji to drugie. Agregacje w SQL Server realizowane są czteroetapowo: 1) inicjalizacja, 2) agregacja, 3) opcjonalne złączenie obliczeń równoległych, 4) zakończenie i zwrócenie wyników. Pociąga to za sobą konieczność zaimplementowania czterech metod:
- Init - Wywoływana, gdy SQL Server przystępuje do utworzenia nowej grupy agregacji. To miejsce na wszelkie operacje inicjalizacji wykorzystywanych struktur.
- Accumulate - Wywoływana, gdy trzeba zagregować kolejną wartość. Wartość przekazywana jest w postaci parametru funkcji. Jeżeli zamierzamy agregować liczby całkowite, musimy oczywiście dopasować typ parametru (np. SqlInt32).
- Merge - Wywoływana, gdy trzeba połączyć dwie grupy agregacji w jedną. SQL Server może stosować równoległe obliczenia, które z założenia powinny przyspieszyć całą operację. Przypuśćmy, że dwie agregacje policzyły sumę częściową - jak je złączyć? Dodać! A to, że dwa procesory, być może dwa niezależne dyski wykonały swoją pracę równolegle, to nasz zysk. Nie po to serwery wyposaża się w kilkanaście, nawet kilkadziesiąt procesorów, żeby odpoczywały!
- Terminate - Wywoływana, gdy trzeba pobrać wyniki operacji.
To te cztery metody są sercem agregacji. To one muszą zapewnić całą logikę.
Wyposażeni w, wprawdzie skromną, wiedzę teoretyczną, możemy przystąpić do przykładowej agregacji.
Własna agregacja - suma bitowa
Do czego taka agregacja może się przydać? W zasadzie wszędzie tam, gdzie mamy do czynienia z wartościami, które reprezentują coś bitowo. Przypuśćmy, że użytkownik posiada zestaw praw do czegoś, a sam zestaw może pochodzić z różnych ról. Jeden użytkownik może mieć zatem N rekordów z przysługującymi mu uprawnieniami. Dostęp do określonej funkcji ma wtedy, gdy choć jedna z ról mu takie prawo nadaje. Przy takim układzie bitowa suma będzie definiowała komplet praw! Przyjrzyjmy się zatem przykładowej implementacji funckji agregującą w .NET:
[SqlUserDefinedAggregate(
Format.Native,
IsInvariantToDuplicates = true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false,
Name = "BitOr"
)]
public struct BitOr
{
public SqlInt32 Result { get; private set; }
// Wywoływane na początku
public void Init()
{
this.Result = 0;
}
//Wywoływane, gdy trzeba dodać do agregacji
//kolejną wartość
public void Accumulate(SqlInt32 value)
{
if (!value.IsNull)
{
this.Result |= value;
}
}
//Wywoływane, gdy trzeba połączyć dwie
//równolegle liczone agregacje
public void Merge(BitOr group)
{
this.Result |= group.Result;
}
//Wywoływane na końcu, zwraca rezultat
public SqlInt32 Terminate()
{
return this.Result;
}
}
Sam algorytm, czyli operacja sumy bitowej, nie wymaga chyba komentarza.
Instalacja w SQL Server
Aby korzystać z .NET CLR w SQL Server należy taką opcję włączyć. Jeżeli integracja z CLR jest włączona, można ten krok pominąć, jeżeli nie, należy wykonać następujące instrukcje:
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Każde wywołanie sp_configure wymaga ponownej rekonfiguracji SQL Server, przez co obie instrukcje sp_configure mają odpowiadające sobie instrukcje RECONFIGURE. Opcję włącza się raz dla całemgo serwera. Gdy opcja jest włączona, można już instalować dowolne biblioteki z rozszerzeniami. Realizuje się to przy pomocy instrukcji CREATE ASSEMBLY.
Nazwa opcji jest trochę myląca, bo przecież nie tworzymy żadnego komponentu - komponent powstaje w Visual Studio podczas kompilacji. Przyjrzyjmy się jednak całej składni:
AUTHORIZATION dbo
FROM 'C:\SqlExtensions.dll'
WITH PERMISSION_SET=SAFE;
Należy oczywiście pamiętać o wskazaniu odpowiedniej ścieżki do pliku DLL. W pokazanym przykładzie plik leży na dysku C: i nazywa się SqlExtensions.dll.
Po zainstalowaniu komponentu można już utworzyć funkcję agregującą. Dlaczego nie można tego zrobić od razu? Dlatego, że w bibliotece mogą się znajdować inne agregacje, inne funkcje, własne typy .NET CLR. Wszystko to trzeba instalować świadomie. Popatrzmy zatem na sposób tworzenia agregacji pokazany na poniższym listingu:
EXTERNAL NAME SqlExtensions.BitOr;
Funkcja agregacji przyjmuje wartości typu int i taki sam typ zwraca (suma bitowa kolejnych wartości typu int). Dla NULL przyjmuje wartość 0, bo 0 nie zmienia sumy logicznej (dla dowolnego X mamy: 0 | X = X).
Przykład użycia
Własne funkcje agregujące działają w zasadzie tak samo jak wbudowane funkcje, poza koniecznością użycia przestrzeni (schematu). Popatrzmy na pierwszy lepszy przykład:
(VALUES (1),(2),(3),(NULL),(16)) V(N)
Wynikiem będzie suma bitowa wszystkich wartości, czyli 19. Popatrzmy na bardziej zaawansowany przykład:
(VALUES (1,1),(1,2),(1,3),(2,3),(2,16)) V(UserId, Rights)
GROUP BY UserId
Tym razem wynik będzie tabelą:
| UserId | Rights |
|---|---|
| 1 | 3 |
| 2 | 19 |
Czy takie wyliczenia bez użycia agregacji byłyby równie proste? Jako ćwiczenie polecam próbę wykonania tej samej operacji bez użycia agregacji.
Tak zdefiniowana agregacja posiada, oprócz możliwości grupowania, wiele innych cech charakterystycznych dla agregacji. Obsługuje między innymi funkcje okna, z partycjonowaniem włącznie:
(VALUES (1,1),(1,2),(1,3),(2,3),(2,16)) V(AppId, Rights)
Przykład może mniej praktyczny, ale pokazuje dużą elestyczność własnych agregacji. Czy własne funkcje, pisane bespośrednio w T-SQL mają takie możliwości? Nie mają.
Agregacja AND
Prezentowanie operacji sumy logicznej bez pokazania operacji iloczynu logicznego to rzadkie zjawisko. To jak rozdzielanie braci. Bez wnikania w szczegóły, popatrzmy na implementację:
using System;
using System.Data.SqlTypes;
[Serializable]
[SqlUserDefinedAggregate(
Format.Native,
IsInvariantToDuplicates = true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false,
Name = "BitAnd"
)]
public struct BitAnd
{
public SqlInt32 Result { get; private set; }
public void Init()
{
this.Result = 0;
}
public void Accumulate(SqlInt32 value)
{
if (!value.IsNull)
{
this.Result &= value;
}
}
public void Merge(BitAnd group)
{
this.Result &= group.Result;
}
public SqlInt32 Terminate()
{
return this.Result;
}
}
Implementacja jest bardzo podobna. Czy jest potrzebna? I tak, i nie. Dla tych, którzy unikali jak ognia matematyki, z pewnością jest potrzebna. Jest bardziej intuicyjna. Da się jednak bez niej obejść, stosując tylko agregację BitOr. Wystarczy skorzystać z prawa de Morgana.
Lub ogólniej:
Prawo to można zastosować do rozwiązania problemu iloczynu logicznego w następujący sposób:
FROM (VALUES (3), (5), (9)) T(a)
Wynikiem operacji będzie 1. Widać przy okazji, że nie jest to zbyt skomplikowane przekształcenie. A tym, którzy uznają matematykę za niepotrzebną, dedykuję powyższy przykład.
Podsumowanie
Własne agregacje to jeden z obszarów, które wymagają jednokrotnego wysiłku, ale pozwalają na dłuższą metę uprościć zapytania i zwiększyć ich elestyczność oraz wydajność. Ja ze swojej strony postaram się wkrótce przedstawić przykład rzeczywisty, w którym agregacje pozwalają znacznie zmniejszyć ilość zapytań, a co za tym idzie, zwiększyć wydajność całego rozwiązania. Przedstawię też przykład trudniejszy, z własną serializacją. Jest ona wymagana między innymi wtedy, gdy korzystamy z łańcuchów znaków (typ string).
Kategoria:AgregacjeC#SQL Server
Brak komentarzy - bądź pierwszy