STRING_AGG w SQL: łączenie wierszy z kontrolą kolejności

Gdy odbiorca chce listę etykiet w jednej komórce raportu, sięgnąłbym po agregację tekstową. STRING_AGG łączy wartości z wielu wierszy w jeden tekst z wybranym separatorem. Zanim jej użyję, ustalam trzy rzeczy: co jest grupą, w jakiej kolejności mają pojawić się elementy i czy powtórzenia coś znaczą.

Tutaj porównuję PostgreSQL 18 oraz SQL Server 2017 i nowsze. Microsoft potwierdza dostępność funkcji od SQL Server 2017; uporządkowany wariant wymaga poziomu zgodności bazy co najmniej 110. Nie przedstawiam jednego fragmentu sortowania jako wspólnej składni obu silników.

Jedne syntetyczne dane, dwa zapisy

Przyjmijmy tabelę task_labels z całkowitymi project_id i unikalnym item_id oraz tekstowym label. W SQL Server etykieta może być typu nvarchar, w PostgreSQL typu text. Puste pole oznaczam jako NULL, a nie pusty tekst.

W PostgreSQL porządek elementów zapisuję wewnątrz agregacji:

SELECT project_id,
       string_agg(label, ', ' ORDER BY item_id) AS labels
FROM task_labels
GROUP BY project_id
ORDER BY project_id;

Dokumentacja wyrażeń agregujących PostgreSQL pokazuje pozycję ORDER BY po argumentach. W SQL Server analogiczny przykład zapisuję z WITHIN GROUP:

SELECT project_id,
       STRING_AGG(CAST(label AS nvarchar(max)), N', ')
           WITHIN GROUP (ORDER BY item_id) AS labels
FROM task_labels
GROUP BY project_id
ORDER BY project_id;

W obu przykładach dostajemy taki wynik:

Dla projektu 10 czytamy po kolei pozycje 1, 2 i 3. Pozycja 4 nie dopisuje wartości ani dodatkowego przecinka. Projekt 20 ma jedną etykietę. Projekt 30 ma wiersz wejściowy, ale nie ma żadnej wartości różnej od NULL do połączenia, więc wynikiem jest NULL.

Dwie niezależne kolejności

Wewnętrzne sortowanie określa kolejność etykiet w jednej komórce. Końcowe ORDER BY project_id porządkuje wiersze gotowego wyniku. Nie można zastąpić pierwszego drugim. Bez porządku w agregacji nie obiecywałbym czytelnikowi konkretnej kolejności listy, nawet jeśli akurat kilka odczytów wyglądało tak samo.

PostgreSQL wskazuje wprost zależność string_agg od kolejności wejścia. W naszym przykładzie item_id jest unikalne, więc rozstrzyga kolejność. Gdy sortujesz po nazwie lub dacie, zastanów się nad remisami i potrzebnym dodatkowym kluczem. Dla tekstu znaczenie mają też reguły collation.

Ja określiłbym porządek zgodnie z zastosowaniem. Lista etykiet może być alfabetyczna. Historia etapów procesu potrzebuje raczej czasu i klucza zdarzenia. To, że oba wyniki da się zapisać w jednym polu, nie oznacza, że odbiorca będzie czytał je w ten sam sposób.

Duplikaty i braki wymagają decyzji

Dwie etykiety Alpha pozostają w zwykłej agregacji. Mogą oznaczać dwa poprawne przypisania albo kopię powstałą po złączeniu. Najpierw ustaliłbym przyczynę. Usunięcie powtórzeń wyłącznie z gotowego tekstu może ukryć błąd przygotowania danych bez wyjaśnienia jego wpływu na pozostałe miary.

Jeżeli naprawdę potrzebuję unikalnych nazw, w PostgreSQL mogę użyć:

SELECT project_id,
       string_agg(DISTINCT label, ', ' ORDER BY label) AS labels
FROM task_labels
GROUP BY project_id
ORDER BY project_id;

Przy zwykłym porządku tych przykładowych nazw projekt 10 da Alpha, Beta. Zmieniliśmy jednocześnie znaczenie listy: pokazuje unikalne nazwy posortowane po nazwie, a nie kolejność przypisań. W SQL Server przygotowałbym najpierw SELECT DISTINCT project_id, label w podzapytaniu, a potem agregował jego wynik. Składnia funkcji SQL Server nie przyjmuje tu DISTINCT jako argumentu.

Jeśli brak ma być widoczny dla każdego wiersza, można zastąpić go przed agregacją. W PostgreSQL dla naszych danych:

SELECT project_id,
       string_agg(COALESCE(label, '(missing)'), ', ' ORDER BY item_id) AS labels
FROM task_labels
GROUP BY project_id
ORDER BY project_id;

Projekt 10 otrzyma Beta, Alpha, Alpha, (missing), a projekt 30 (missing). Zastąpienie NULL dopiero po agregacji zmieniłoby wyłącznie pusty wynik całej grupy. To inny cel niż pokazanie brakującej etykiety na konkretnej pozycji. Z kolei pusty tekst '' nie jest NULL i może pozostawić w liście sąsiadujące separatory.

Typ i długość wyniku w SQL Server

W przykładzie SQL Server rzutuję wejście na nvarchar(max) przed łączeniem. Typ pierwszego argumentu wpływa na typ wyniku: dla nvarchar o ograniczonej długości wynikiem jest nvarchar(4000), a dla varchar o ograniczonej długości varchar(8000). Samo rzutowanie gotowej agregacji nie zmienia typu, w którym powstawała.

Ta decyzja nie oznacza, że każda lista powinna mieć dowolną długość. Ja nadal ustaliłbym, ile elementów odbiorca potrafi wykorzystać, i sprawdził ograniczenia klienta wyświetlającego wynik. Komórka z tysiącem etykiet może być poprawnym tekstem i bardzo niewygodnym interfejsem. Nie obiecuję też przewagi wydajności nad każdym wcześniejszym sposobem konkatenacji.

Odpowiedniki w innych bazach

SQLite 3.44.0 dodał string_agg; bieżąca dokumentacja opisuje alias i własny zapis kolejności. W MySQL zwróciłbym uwagę również na group_concat_max_len. Dla Oracle LISTAGG sprawdziłbym obsługę przepełnienia. Nazwa odpowiednika nie gwarantuje identycznych limitów ani składni.

Ćwiczenie i granice zastosowania

Usuń w myślach wiersz z item_id = 2: dla projektu 10 zostanie Beta, Alpha. Następnie zastanów się, czy wiersz z pustym tekstem powinien być widoczny, pominięty czy zastąpiony opisem. Odpowiedź wynika z definicji listy, nie z chęci pozbycia się przecinka.

Agregacji użyłbym do prezentacji, zachowując osobne wiersze jako podstawę dalszych obliczeń. Jeżeli etykiety zawierają przecinki, taki tekst nie staje się automatycznie poprawnym CSV ani JSON-em. Do wymiany danych wybrałbym właściwy format i jego reguły kodowania. Dla samego łączenia fragmentów jednego wiersza zajrzyj do hasła CONCAT.