Jak połączyć łańcuchy pola ciągu w grupie PostgreSQL przez zapytanie?

Szukam sposobu na połączenie łańcuchów pola w grupie przez zapytanie. Na przykład mam tabelę:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

i chciałem pogrupować według company_id, aby uzyskać coś takiego:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

W mySQL jest wbudowana funkcja umożliwiająca wykonanie tego group_concat

PostgreSQL 9.0 lub nowszy:

Najnowsze wersje Postgres (od końca 2010 r.) Mają string_agg(expression, delimiter)funkcję, która wykona dokładnie to, o co pytano, nawet umożliwiając określenie ciągu ogranicznika:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9.0 dodał także możliwość określenia ORDER BYklauzuli w dowolnym wyrażeniu zbiorczym ; w przeciwnym razie kolejność jest niezdefiniowana. Możesz teraz pisać:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

Lub rzeczywiście:

SELECT string_agg(actor_name, ', ' ORDER BY first_appearance)

PostgreSQL 8.4 lub nowszy:

PostgreSQL 8.4 (w 2009 r.) Wprowadził funkcję agregującą,array_agg(expression) która łączy wartości w tablicę. Następnie array_to_string()można go użyć do uzyskania pożądanego rezultatu:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

string_agg dla wersji wcześniejszych niż 8.4:

W przypadku, gdy ktoś natknie się na to, szukając kompatybilnego podkładki dla baz danych starszych niż 9.0, możliwe jest zaimplementowanie wszystkiego string_aggoprócz ORDER BYklauzuli.

Tak więc w poniższej definicji powinno to działać tak samo, jak w DB Postgres 9.x:

SELECT string_agg(name, '; ') AS semi_colon_separated_names FROM things;

Będzie to jednak błąd składniowy:

SELECT string_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things;
--> ERROR: syntax error at or near "ORDER"

Testowany na PostgreSQL 8.3.

CREATE FUNCTION string_agg_transfn(text, text, text)
    RETURNS text AS 
    $$
        BEGIN
            IF $1 IS NULL THEN
                RETURN $2;
            ELSE
                RETURN $1 || $3 || $2;
            END IF;
        END;
    $$
    LANGUAGE plpgsql IMMUTABLE
COST 1;

CREATE AGGREGATE string_agg(text, text) (
    SFUNC=string_agg_transfn,
    STYPE=text
);

Odmiany niestandardowe (wszystkie wersje Postgres)

Przed wersją 9.0 nie było wbudowanej funkcji agregującej do łączenia łańcuchów. Najprostszą niestandardową implementacją ( zasugerowaną przez Vajdę Gabo w tym poście na liście adresowej ) jest użycie wbudowanej textcatfunkcji (która stoi za ||operatorem):

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Oto CREATE AGGREGATEdokumentacja.

To po prostu skleja wszystkie struny razem, bez separatora. Aby wstawić między nimi znak „,” bez konieczności umieszczania go na końcu, możesz utworzyć własną funkcję konkatenacji i zastąpić ją „textcat” powyżej. Oto jeden zestawiłem i testowałem na 8.3.12:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

Ta wersja wyświetli przecinek, nawet jeśli wartość w wierszu jest pusta lub pusta, więc otrzymujesz dane wyjściowe w następujący sposób:

a, b, c, , e, , g

Jeśli wolisz usunąć dodatkowe przecinki, aby to wyświetlić:

a, b, c, e, g

Następnie dodaj ELSIFzaznaczenie do funkcji w następujący sposób:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

Co powiesz na korzystanie z wbudowanych funkcji tablicy Postgres? Przynajmniej na 8.4 działa to od razu po wyjęciu z pudełka:

SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;

Począwszy od PostgreSQL 9.0, możesz użyć funkcji agregującej o nazwie string_agg . Twój nowy SQL powinien wyglądać mniej więcej tak:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Nie żądam uznania za odpowiedź, ponieważ znalazłem ją po kilku poszukiwaniach:

Nie wiedziałem, że PostgreSQL pozwala definiować własne funkcje agregujące za pomocą CREATE AGGREGATE

Ten post na liście PostgreSQL pokazuje, jak proste jest utworzenie funkcji umożliwiającej wykonywanie wymaganych zadań:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;

Jak już wspomniano, należy utworzyć własną funkcję agregującą. Oto moja funkcja agregująca konkatenację ( szczegóły w języku francuskim ):

CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS '
    SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2
            WHEN $2 IS NULL OR $2 = \'\' THEN $1
            ELSE $1 || \' / \' || $2
            END; 
'
 LANGUAGE SQL;

CREATE AGGREGATE concatenate (
  sfunc = concat2,
  basetype = text,
  stype = text,
  initcond = ''

);

A następnie użyj go jako:

SELECT company_id, concatenate(employee) AS employees FROM ...

Ten najnowszy fragment listy ogłoszeń może być interesujący, jeśli będziesz aktualizować do wersji 8.4:

Dopóki 8.4 nie pojawi się z superefektywnym rodzimym, możesz dodać funkcję array_accum () w dokumentacji PostgreSQL, aby zwinąć dowolną kolumnę w tablicę, która może być następnie użyta przez kod aplikacji lub połączona z array_to_string () w celu sformatowania to jako lista:

http://www.postgresql.org/docs/current/static/xaggr.html

Odsyłam link do dokumentów programistycznych 8.4, ale wydaje się, że nie zawierają jeszcze tej funkcji.

W odpowiedzi na odpowiedź Kev, korzystając z dokumentów Postgres:

Najpierw utwórz tablicę elementów, a następnie użyj wbudowanej array_to_stringfunkcji.

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

select array_to_string(array_accum(name),'|') from table group by id;

Po raz kolejny na temat korzystania z niestandardowej funkcji agregującej łączenia łańcuchów: musisz pamiętać, że instrukcja select umieści wiersze w dowolnej kolejności, więc będziesz musiał dokonać wyboru podrzędnego w instrukcji from z klauzulą kolejności według i następnie wybierz zewnętrzny z grupą za pomocą klauzuli, aby agregować ciągi, a zatem:

SELECT custom_aggregate(MY.special_strings)
FROM (SELECT special_strings, grouping_column 
        FROM a_table 
        ORDER BY ordering_column) MY
GROUP BY MY.grouping_column

Uważam, że ta dokumentacja PostgreSQL jest pomocna: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html .

W moim przypadku szukałem zwykłego SQL-a, aby połączyć pole z nawiasami wokół niego, jeśli pole nie jest puste.

select itemid, 
  CASE 
    itemdescription WHEN '' THEN itemname 
    ELSE itemname || ' (' || itemdescription || ')' 
  END 
from items;

Użyj STRING_AGGfunkcji dla PostgreSQL i Google BigQuery SQL :

SELECT company_id, STRING_AGG(employee, ', ')
FROM employees
GROUP BY company_id;

Zgodnie z wersją PostgreSQL 9.0 i nowszymi możesz użyć funkcji agregującej o nazwie string_agg. Twój nowy SQL powinien wyglądać mniej więcej tak:

SELECT company_id, string_agg(employee, ', ')
    FROM mytable GROUP BY company_id;

Możesz także użyć funkcji formatowania. Który może również pośrednio zająć się konwersją tekstu, int itp.

create or replace function concat_return_row_count(tbl_name text, column_name text, value int)
returns integer as $row_count$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$row_count$ language plpgsql;


postgres=# select concat_return_row_count('tbl_name','column_name',2); --2 is the value

Korzystam z Jetbrains Rider, a kopiowanie wyników z powyższych przykładów w celu ponownego uruchomienia było kłopotliwe, ponieważ wydawało się, że wszystko to zawinęło w JSON. To łączy je w jedną instrukcję, która była łatwiejsza do uruchomienia

select string_agg('drop table if exists "' || tablename || '" cascade', ';') 
from pg_tables where schemaname != $$pg_catalog$$ and tableName like $$rm_%$$

Jeśli korzystasz z Amazon Redshift, gdzie string_agg nie jest obsługiwany, spróbuj użyć listagg.

SELECT company_id, listagg(EMPLOYEE, ', ') as employees
FROM EMPLOYEE_table
GROUP BY company_id;