Odpowiednik Postgresql GROUP_CONCAT?


248

Mam tabelę i chciałbym pobrać jeden wiersz na identyfikator z połączonymi wartościami pól.

Na przykład w mojej tabeli mam to:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

I chciałbym wyprowadzić:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

W MySQL mogłem używać funkcji agregującej GROUP_CONCAT, ale wydaje się, że to tutaj nie działa ... Czy istnieje odpowiednik PostgreSQL, czy inny sposób na osiągnięcie tego?





1
Myślę, że najlepszą odpowiedzią jest wciąż inne pytanie: stackoverflow.com/a/47638417/243233
Jus12

Odpowiedzi:


237

Jest to prawdopodobnie dobry punkt wyjścia (tylko wersja 8.4+):

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array_agg zwraca tablicę, ale możesz CAST ją przesłać do tekstu i edytować w razie potrzeby (patrz wyjaśnienia poniżej).

Przed wersją 8.4 musisz to zdefiniować samodzielnie przed użyciem:

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(parafrazowany z dokumentacji PostgreSQL)

Wyjaśnienia:

  • W wyniku rzutowania tablicy na tekst powstały ciąg zaczyna się i kończy nawiasami klamrowymi. Te nawiasy klamrowe należy usunąć jakąś metodą, jeśli nie są pożądane.
  • Przesyłanie ANYARRAY do TEXT najlepiej symuluje wyjście CSV, ponieważ elementy zawierające osadzone przecinki są w cudzysłowie podwójnie cytowane w standardowym stylu CSV. Ani array_to_string (), ani string_agg () (funkcja „group_concat” dodana w 9.1) nie cytuje łańcuchów z osadzonymi przecinkami, co powoduje niepoprawną liczbę elementów na wynikowej liście.
  • Nowa funkcja 9.1 string_agg () NIE najpierw rzutuje wewnętrznych wyników na TEKST. Zatem „string_agg (pole_wartości)” generuje błąd, jeśli pole_wartości jest liczbą całkowitą. „string_agg (wartość_pole :: tekst)” byłoby wymagane. Metoda array_agg () wymaga tylko jednego rzutowania po agregacji (zamiast rzutowania na wartość).

1
A w 9.0 będziesz mieć listagg ()
Scott Bailey

6
Aby uzyskać CSV, zapytanie powinno wyglądać następująco: WYBIERZ pole_id, tablica_do_ciągu (tablica_agg (wartość_pola 1), ','), tablica do_string (tablica_agg (wartość_pola2), ',') FROM tabela_ danych GROUP BY id_field
Nux

2
Nie można tutaj użyć argumentu array_to_string. Jeśli twoje pole_wartości zawiera osadzony przecinek, wynikowy plik CSV jest niepoprawny. Użycie array_agg () i rzutowanie na TEXT poprawnie cytuje łańcuchy z osadzonymi przecinkami. Jedynym zastrzeżeniem jest to, że obejmuje również nawiasy klamrowe rozpoczynające i kończące się, stąd moje zdanie „i edytuj w razie potrzeby”. Przeredaguję, aby wyjaśnić tę kwestię.
Matthew Wood


256

Od wersji 9.0 jest to jeszcze łatwiejsze:

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id

32
Zauważ, że składnia pozwala również określić kolejność wartości w ciągu (lub tablicy, używając array_agg) np. string_agg(some_column, ',' ORDER BY some_column)Lub nawetstring_agg(surname || ', ' || forename, '; ' ORDER BY surname, forename)
IMSoP

8
To niesamowite, że distinctdziała z string_agg, więc można go używaćstring_agg(distinct some_solumn, ',')
arun

3
Pamiętaj, że może być konieczne rzutowanie wartości kolumny na, TEXTjeśli jest to wartość nie łańcuchowa (tj. uuid). To by wyglądało jakstring_agg(some_column::text, ',')
Kendall

48
SELECT array_to_string(array(SELECT a FROM b),', ');

Zrobi tak samo.


Czy można zrobić coś takiego jak w tym komentarzu , gdzie agregujesz w określonej kolejności? Jak poradziłbyś sobie z grupowaniem według jednej kolumny i porządkowaniem według innej (na przykład w celu łączenia zmiennych w podłużnym zbiorze danych)?
Michael A

15

Spróbuj tak:

select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;

2

oraz wersja do pracy z typem tablicy :

select
  array_to_string(
    array(select distinct unnest(zip_codes) from table),
    ', '
);

Duplikat odpowiedzi, @max_spy powiedział to samo pięć lat temu
Emil Vikström

@ EmilVikström: masz prawo się mylić, ale czytaj uważnie. Jest nie tylko inny, ale podałem przykład, który działa z typem tablicy - jak na przykład zip_codes character varying(5)[]. Sprawdziłem też, że do moich celów - potrzebna jest nieuczciwość, inaczej zobaczysz ERROR: cannot accumulate arrays of different dimensionality.
Sławomir Lenart,

1

Moja sugestia w postgresql

SELECT cpf || ';' || nome || ';' || telefone  
FROM (
      SELECT cpf
            ,nome
            ,STRING_AGG(CONCAT_WS( ';' , DDD_1, TELEFONE_1),';') AS telefone 
      FROM (
            SELECT DISTINCT * 
            FROM temp_bd 
            ORDER BY cpf DESC ) AS y
      GROUP BY 1,2 ) AS x   

1
Dlaczego robisz ORDER BYw wewnętrznym zapytaniu? Czy mimo to zamówienie się nie zgubi?
mypetlion

-1

Nadzieja poniżej zapytania Oracle będzie działać.

Select First_column,LISTAGG(second_column,',') 
    WITHIN GROUP (ORDER BY second_column) as Sec_column, 
    LISTAGG(third_column,',') 
    WITHIN GROUP (ORDER BY second_column) as thrd_column 
FROM tablename 
GROUP BY first_column

Przetestowałem to na rextester.com/l/postgresql_online_compiler i nie działałem : 42883: funkcja listagg (tekst, nieznany, tekst) nie istnieje
Manuel Romeiro

Oracle ma inną składnię i funkcje niż postgres.
Herman J. Radtke III
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.