GROUP_CONCAT w SQL Server
Agregacja łącząca wiele wierszy w jeden
Jakiś czas temu pisałem o tworzeniu własnych agregacji w SQL Server. Wtedy była to bardzo prosta agregacja i polegała na zliczeniu sumy bitowej wartości w kolumnie (zob. a href="/Artykuly/Artykul/95,Wlasne_agregacje_w_SQL_Server" title="Własne agregacje w SQL Server">Własne agregacje w SQL Server). Tym razem będzie coś trudniejszego - konkatenacja, złączenie wartości z kolumny. Dzięki takiemu rozwiązaniu można wrzucić atrybuty z wielu rekordów i skumulować je w jednym, oddzielając dane przecinkiem lub innym separatorem. Prościej mówiąc, chcemy z takiej tabeli:
| ID | Name |
|---|---|
| 1 | borsuki |
| 2 | kuny |
| 1 | konie |
| 2 | surykatki |
| 3 | marchewki |
| 4 | bakłażany |
| 1 | psy |
Uzyskać coś takiego:
| ID | Names |
|---|---|
| 1 | borsuki,konie,psy |
| 2 | kuny,surykatki |
| 3 | marchewki |
| 4 | bakłażany |
Istnieje rozwiązanie w czystym kodzie T-SQL, ale ma ono pewne ograniczenia i nie wygląda elegancko (zob. Tworzenie listy rozdzielonej przecinkiem z danych jednej kolumny w SQL Server). Na dłuższą metę znacznie lepiej prawdzają się agregacje .NET.
Agregacja GROUP_CONCAT
Nowa agregacja przyjmie nazwę GROUP_CONCAT, bo taka już istnieje. Co prawda nie w SQL Server, tylko w MySQL, ale istnieje. Niektórym będzie łatwiej zapamiętać nazwę. Tych, którzy nie zetknęli się jeszcze z agregacjami CLR odsyłam do artukułu wymienionego we wstępie. Tam są podstawy teoretyczne. Tutaj opiszę tylko te rzeczy, które nie były cześniej wykorzystane. A kilka tych rzeczy jest. Przyjrzyjmy się przykładowej implementacji tytułowej agregacji w C#:
using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
IsNullIfEmpty = true,
Name = "GROUP_CONCAT",
MaxByteSize = -1
)]
public struct GROUP_CONCAT : IBinarySerialize
{
private StringBuilder accumulator;
private string separator;
public bool isNotNull;
public void Init()
{
accumulator = new StringBuilder();
separator = string.Empty;
}
public void Accumulate(SqlString value, SqlString separator)
{
if (!separator.IsNull)
{
this.separator = separator.Value;
}
if (!value.IsNull)
{
if (accumulator.Length > 0)
accumulator.Append(separator);
accumulator.Append(value.Value);
isNotNull = true;
}
}
public void Merge(GROUP_CONCAT group)
{
if (isNotNull && group.isNotNull)
accumulator.Append(separator);
isNotNull |= group.isNotNull;
accumulator.Append(group.accumulator);
}
public SqlString Terminate()
{
if (isNotNull)
return new SqlString(accumulator.ToString());
else
return SqlString.Null;
}
public void Read(BinaryReader r)
{
isNotNull = r.ReadBoolean();
separator = r.ReadString();
accumulator = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(isNotNull);
w.Write(separator);
w.Write(accumulator.ToString());
}
}
W pokazanej definicji funkcji agregującej, w stosunku do agregacji sumy bitowej, pojawiły sie nowe elementy.
Własny format i serializacja
Poprzednia agregacja była prosta, bo zawierała w sobie tylko liczbę całkowitą. SQL Server potrafił taką liczbę zapamiętać i odtworzyć w dowolnym momencie. Tym razem klasa jest bardziej skomplikowana, bo posiada 3 pola (da się to ograniczyć, ale traci na tym czytelność), które muszą być jakoś zarządzane. Muszą być gdzieś przechowywane. SQL Server zna typ int, SqlInt32, ale nie zna typu StringBuilder (nie potrafi też obsługiwać typu string). Musimy taki obiekt serializować ręcznie. Nie trzeba używać atrybutu Serializable, bo i tak sami wykonujemy serializację. Musimy za to ustawić format agregacji na Format.UserDefined oraz zaimplementować interfejs IBinarySerialize. Interfejs posiada dwie metody, do odczytu i zapisu, które będą wywoływane przez SQL Server, gdy zaistnieje potrzeba takiej serializacji. Implementacja metod serializacji nie jest trudna i chyba nie wymaga komentarza. Jest jeszcze jedna rzecz, na którą warto zwrócić uwagę - w atrybucie SqlUserDefinedAggregate pojawiła się właściwość MaxByteSize. Oznacza ona maksymalny rozmiar obiektu po serializacji w bajtach. Można tam wstawić wartości od 1 do 8000, lub specjalną wartość -1, która reprezentuje maksymalny możliwy obszar wynoszący 2 GB. Warto wiedzieć, że w tym zarezerwowanym rozmiarze musi się zmieścić także informacja na potrzeby samej serializacji! Przykładowo, jeżeli serializujemy typ string o długości N, będzie on zajmował 2 * N + 2 bajtów (za dokumentacją). Oprócz znaków UNICODE będą tam również bajty kontrolne, które też należy wziąć pod uwagę.
Tyle w obszarze wyjaśnień. Czas na przykłady, które będą jednocześnie testami.
Przykład konkatenacji i grupowania
Przyjrzyjmy się kolejnym instrukcjom i otrzymanym rezulatom. Na początek złączenie trzech wartości:
(VALUES ('A'), ('B'), ('C')) T(A)
| Items |
|---|
| A, B, C |
Co się stanie, gdy wśród wartości pojawi się NULL?
(VALUES ('A'), ('B'), ('C'), (NULL)) T(A)
| Items |
|---|
| A, B, C |
Otrzymamy ten sam rezultat. NULL jest ignorowany. A co wtedy, gdy mamy tylko NULL lub pusty zbiór?
(VALUES (NULL)) T(A)
SELECT dbo.GROUP_CONCAT(A, ', ') Items FROM
(SELECT 1 WHERE 1=2) T(A)
| Items |
|---|
| NULL |
Następny test to zbiór z powtarzającymi się wartościami:
(VALUES ('A'), ('A'), ('B'), ('C'), ('D'), ('A')) T(A)
| Items |
|---|
| A, A, B, C, D, A |
A gdyby tak pogrupować? Czy taka agregacja sobie z tym poradzi? Naturalnie!
(VALUES (1, 'A'), (2, 'A'), (1, 'B'), (2, 'C'), (3, 'D'), (4, 'A')) T(A, B)
GROUP BY A
| A | Items |
|---|---|
| 1 | B, A |
| 2 | C, A |
| 3 | D |
| 4 | A |
Niby dobrze, ale jedna rzecz może tutaj zaskakiwać. Kolejność elementów agregowanych. Co się stało?
Kolejność elementów agregowanych
Umieściliśmy w atrybucie SqlUserDefinedAggregate właściwość IsInvariantToOrder i ustawiliśmy ją na false. Teoretycznie kolejność elementów powinna mieć znaczenie. Dlaczego zatem wartości są złączone odwrotnie? Po części dlatego, że tabele w SQL są zbiorami. Idąc dalej: są zbiorami, więc kolejność rekordów nie ma znaczenia. Czy da się stworzyć podzapytanie, które będzie miało w sobie sekcję ORDER BY? Nie da się. Z tego samego powodu - zastosowanie ORDER BY zamienia zbiór na uporządkowaną listę elementów. Są to dwa różne pojęcia. Do czego w takim razie jest nam potrzebna właściwość IsInvariantToOrder?
Umieściłem ją tam razem z innymi właściwościami grupy IsInvariantToXXX. Od razu uspokajam - wszystkie pozostałe właściwości działają jak należy. Jedynie przy właściwości IsInvariantToOrder, w sekcji uwag dokumentacji możemy wyczytać:
Reserved for future use. This property is not currently used by the query processor: order is currently not guaranteed.
Zarezerwowane dla przyszłych zastosowań, nie jest używana przez procesor zapytań, porządek nie jest gwarantowany. Plan był dobry, bo istnieją agregacje, dla których kolejność ma znaczenie. Problem jest, moim zdaniem, architektoniczny. Bieżąca architektura, oparta na zbiorach, uniemożliwiająca porządkowanie podzapytań, chyba uniemożliwia, bez wprowadzenia dodatkowych instrukcji, porządkowanie elementów agregowanych. Nie przypuszczam, żeby nagle dało się łatwo zmienić to długoletnie zachowanie procesora zapytań. Zbyt dużo technik generowania planów zapytań i przetwarzania tych zapytań zakłada swobodę w wyborze kolejności. Ta kolejność to wybór indeksu, odwracanie kolejności złączeń. Rozsądek podpowiada, że nie będzie to takie proste. I pewnie proste nie było, skoro nie udało się tego zaimplementować przez tyle lat. Pozostaje jedynie wierzyć, że w przyszłości to zarezerwowane pole będzie działało i że ta przyszłość nie będzie zbyt odłegła, nie będzie dążyła do nieskończoności.
Co można zrobić, jeżeli naprawdę zależy nam na uporządkowaniu elementów wypisywanych po przecinku (lub dowolnym innym separatorze - agregacja pozwala przecież przekazać separator!)? Należy taką logikę zaimplementować w samej agregacji. Zamiast kumulować wartości w polu/właściwości typu StringBuilder, można je kumulować w zwykłej liście, a na końcu, w metodzie Terminate, posortować. Metoda Merge mogłaby te listy łączyć.
Przykład właściwości IsInvariantToOrder pokazuje, że nawet najlepsze plany, pomimo dużego wysiłku projektowego, mogą pozostać jedynie w sferze marzeń i życzeń. Życie i praktyka wszystko zweryfikują. Jak to ktoś powiedział - w teorii nie ma żadnej różnicy między teorią a praktyką, w praktyce jest. Pamiętajmy o tym.
Kategoria:AgregacjeC#SQL Server
Brak komentarzy - bądź pierwszy