string_agg(expression, separator) łączy wartości każdej grupy w jeden napis, a array_agg(expression) zbiera je w tablicę. Kolejność ustalasz przez ORDER BY wewnątrz wywołania: string_agg(name, ', ' ORDER BY name).
IT zwraca Bo, Kim, Li i {6100,4800,3900}; Sales zwraca Ana, Raj i {5000,4200}. Obie to zwykłe agregaty, jak count czy sum: działają dla każdej grupy z GROUP BY, a bez niego dla całej tabeli.
Kolejność wartości
Bez ORDER BY wewnątrz agregatu wartości przychodzą w kolejności, w jakiej docierają wiersze, a ta może się zmieniać między uruchomieniami. ORDER BY na końcu zapytania sortuje wiersze wyniku, a nie wartości w każdym napisie. Wewnętrzne ORDER BY stoi po ostatnim argumencie, bez przecinka:
string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)
Usuwanie duplikatów przez DISTINCT
Ada kupiła długopis dwa razy, ale DISTINCT pokazuje go raz: book, pen. Z DISTINCT ORDER BY może używać tylko agregowanej wartości. Sortowanie po innej kolumnie kończy się błędem "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list".
Jak zachowują się NULL i puste grupy
string_agg pomija wartości NULL i zwraca Bo, Li. array_agg je zachowuje: {Bo,Li,NULL}. Żeby usunąć NULL z array_agg, dodaj FILTER (WHERE name IS NOT NULL).
Dla zerowej liczby wierszy obie zwracają NULL, a nie pusty napis albo pustą tablicę. Opakuj je, gdy wywołujący oczekuje wartości: COALESCE(string_agg(...), '') albo COALESCE(array_agg(...), '{}').
Agregowanie części wierszy przez FILTER
FILTER (WHERE ...) ogranicza jeden agregat do pasujących wierszy, a reszta zapytania widzi wszystkie:
IT ma 3 osoby i wypisuje Bo, Li; Sales ma 2 i wypisuje Ana.
Lista przy każdym wierszu innej tabeli
Żeby pokazać każdego klienta z listą jego produktów, agreguj w podzapytaniu, a wtedy zewnętrzne zapytanie nie potrzebuje GROUP BY:
SELECT c.id, c.name, c.email,
(SELECT string_agg(o.product, ', ' ORDER BY o.product)
FROM orders o
WHERE o.customer_id = c.id) AS products
FROM customers c;
Klienci bez zamówień zostają w wyniku, z products równym NULL. Złączenie z GROUP BY zachowa ich tylko wtedy, gdy jest to LEFT JOIN.
Częste błędy i warianty
- Liczby:
string_aggprzyjmuje tekst, więcstring_agg(id, ',')kończy się błędem "function string_agg(integer, unknown) does not exist". Zrzutuj:string_agg(id::text, ','). - Pierwsze N w grupie: wytnij fragment posortowanej tablicy,
(array_agg(name ORDER BY salary DESC))[1:2]. - Wynik w JSON:
json_aggijsonb_aggzbierają wiersze w tablicę JSON, ajsonb_object_agg(key, value)buduje obiekt; zobacz JSONB. - Masz już tablicę:
array_to_string(arr, ', ')łączy istniejącą tablicę, aunnestzamienia ją z powrotem na wiersze (tablice). - Wersja okienkowa: obie działają też jako funkcje okna, na przykład
string_agg(name, ', ') OVER (PARTITION BY dept), żeby pokazać całą listę w każdym wierszu.
Najczęściej zadawane pytania
Jaki jest odpowiednik GROUP_CONCAT w PostgreSQL?
string_agg(column, ','). W przeciwieństwie do GROUP_CONCAT z MySQL separator jest wymagany, a kolejność podaje się w wywołaniu: string_agg(name, ', ' ORDER BY name).
Jak użyć string_agg z liczbami?
Zrzutuj wartość na tekst: string_agg(id::text, ','). string_agg przyjmuje tylko tekst (albo bytea), więc argument całkowity kończy się błędem "function string_agg(integer, unknown) does not exist".
Czy string_agg ma limit długości?
Nie ma osobnego limitu; wynik to zwykła wartość text, która mieści do 1 GB. GROUP_CONCAT w MySQL obcina wynik na group_concat_max_len, a PostgreSQL niczego nie obcina.
Jak pobrać pierwsze N wartości przez array_agg?
Posortuj wewnątrz agregatu i wytnij fragment wyniku: (array_agg(name ORDER BY score DESC))[1:3] zwraca trzy najlepsze imiona w każdej grupie.