Jak połączyć ciągi pól string w zapytaniu PostgreSQL 'group by'?
Szukam sposobu na połączenie łańcuchów pól w grupie za pomocą zapytania. 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ś w stylu:
COMPANY_ID EMPLOYEE
1 Anna, Bill
2 Carol, Dave
W mySQL jest wbudowana funkcja do tego group_concat
14 answers
PostgreSQL 9.0 lub nowszy:
Najnowsze wersje Postgres (od końca 2010 roku) mają string_agg(expression, delimiter)
funkcja, która wykona dokładnie to, o co pytano, pozwalając nawet określić ciąg ogranicznika:
SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;
Postgres 9.0 dodał również możliwość określenia klauzuli ORDER BY
w dowolnym zbiorczym wyrażeniu; w przeciwnym razie kolejność jest niezdefiniowana. Więc możesz teraz napisać:
SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;
Albo rzeczywiście:
SELECT string_agg(actor_name, ', ' ORDER BY first_appearance)
PostgreSQL 8.4 lub później:
PostgreSQL 8.4 (w 2009 roku) wprowadził funkcję agregacjiarray_agg(expression)
który łączy wartości w tablicę. Następnie array_to_string()
można 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 pre-8.4:
W przypadku, gdy ktoś natknie się na to szukając kompatybilnego Shima dla baz danych sprzed wersji 9.0, możliwe jest zaimplementowanie wszystkiego w string_agg
z wyjątkiem ORDER BY
klauzuli.
Więc z poniższą definicją powinno to działać tak samo jak w 9.X Postgres DB:
SELECT string_agg(name, '; ') AS semi_colon_separated_names FROM things;
Ale To będzie błąd składni:
SELECT string_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things;
--> ERROR: syntax error at or near "ORDER"
Testowane 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
);
Custom variations (all Postgres versions)
Przed 9.0 nie było wbudowanej funkcji agregującej do łączenia łańcuchów. Najprostszą implementacją niestandardową (zasugerowaną m.in. przez Vajdę Gabo w tym poście na liście dyskusyjnej ) jest użycie wbudowanej funkcji textcat
(która leży za operatorem ||
): {]}
CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);
Proszę. jest dokumentacją CREATE AGGREGATE
.
To po prostu skleja wszystkie łańcuchy razem, bez separatora. W celu wstawienia", "pomiędzy nimi bez konieczności posiadania go na końcu, możesz stworzyć własną funkcję konkatenacji i zastąpić ją" textcat " powyżej. Oto jeden, który zebrałem i przetestował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 null lub pusta, więc otrzymasz wynik w następujący sposób:
a, b, c, , e, , g
If you would preferuj usunięcie dodatkowych przecinków, aby wypisać to:
a, b, c, e, g
Następnie dodaj ELSIF
sprawdzanie do funkcji tak:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-10-16 13:00:44
A co powiesz na używanie funkcji tablic wbudowanych w Postgres? Przynajmniej na 8.4 to działa po wyjęciu z pudełka:
SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-18 22:55:30
Od wersji PostgreSQL 9.0 można używać funkcji zbiorczej 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-26 13:44:22
Nie przypisuję sobie żadnej odpowiedzi, ponieważ znalazłem ją po kilku poszukiwaniach:
Nie wiedziałem, że PostgreSQL pozwala definiować własne funkcje agregujące za pomocą CREATE AGREGATE
Ten post na liście PostgreSQL pokazuje jak trywialne jest tworzenie funkcji do wykonania tego, co jest wymagane:
CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);
SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-04 14:35:09
Jak już wspomniano, tworzenie własnej funkcji agregacyjnej jest właściwą rzeczą do zrobienia. Oto moja funkcja agregacji konkatenacyjnej (szczegóły znajdziesz 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 ...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-12-09 19:54:44
Ten fragment listy ogłoszeń może Cię zainteresować, jeśli będziesz aktualizować do wersji 8.4:
Dopóki 8.4 nie wyjdzie z super efektowny natywny, można dodać funkcja array_accum () w Dokumentacja PostgreSQL do toczenia up dowolnej kolumny do tablicy, która może następnie użyć kodu aplikacji, lub w połączeniu z array_to_string () do sformatuj go jako listę:
Podlinkowałbym do dokumentacji rozwojowej 8.4, ale wydaje się, że nie wymieniają jeszcze tej funkcji.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-02-09 13:22:57
Podążając za odpowiedzią Kev ' a, używając Postgres docs:
Najpierw Utwórz tablicę elementów, a następnie użyj wbudowanej funkcji array_to_string
.
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
select array_to_string(array_accum(name),'|') from table group by id;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-22 15:36:02
Po raz kolejny po użyciu niestandardowej zagregowanej funkcji konkatenacji łańcuchów: musisz pamiętać, że instrukcja select będzie umieszczać wiersze w dowolnej kolejności, więc będziesz musiał wykonać pod select w instrukcji from z order by klauzula, a następnie zewnętrzną select z group by klauzula, aby połączyć łańcuchy, a więc:{[2] 12]}
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-22 15:38:06
Użyj funkcji STRING_AGG
dla PostgreSQL i Google BigQuery SQL :
SELECT company_id, STRING_AGG(employee, ', ')
FROM employees
GROUP BY company_id;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-04-12 21:36:08
Uważam, że dokumentacja PostgreSQL jest pomocna: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html .
W moim przypadku Szukałem zwykłego SQL, 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;
Zgodnie z wersją PostgreSQL 9.0 i wyżej można użyć funkcji zbiorczej 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-01 09:02:09
Można również użyć funkcji formatowania. Który może również pośrednio samodzielnie zająć się konwersją typu 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-30 11:35:31
Używam JetBrains Rider i było to kłopotliwe kopiowanie wyników z powyższych przykładów do ponownego wykonania, ponieważ wydawało się, że zawija to wszystko 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_%$$
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-12-04 03:31:02
Jeśli korzystasz z usługi 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-03-15 09:34:39