[sql] PostgreSQL ‘group by’쿼리에서 문자열 필드의 문자열을 연결하는 방법은 무엇입니까?

PostgreSQL 9.0 이상 :

최신 버전의 Postgres (2010 년 말부터)에는 string_agg(expression, delimiter)구분 기호 문자열을 지정할 수 있도록하면서 질문에 대한 내용을 정확하게 수행 하는 기능이 있습니다.

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

Postgres 9.0은 또한 모든 집계 표현식에서ORDER BY 절 을 지정하는 기능을 추가했습니다 . 그렇지 않으면 순서가 정의되지 않습니다. 따라서 다음과 같이 작성할 수 있습니다.

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

또는 실제로 :

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

PostgreSQL 8.4 이상 :

PostgreSQL 8.4 (2009) 는 값을 배열로 연결하는 집계 함수array_agg(expression) 를 도입 했습니다 . 그런 다음 array_to_string()원하는 결과를 제공하는 데 사용할 수 있습니다.

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

string_agg 8.4 이전 버전의 경우 :

9.0 이전의 데이터베이스에 대한 호환성 심을 찾는 사람이 있다면 string_agg,ORDER BY 조항 .

따라서 아래 정의에서는 9.x Postgres DB와 동일하게 작동합니다.

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

그러나 이것은 구문 오류입니다.

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

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
);

맞춤형 변형 (모든 Postgres 버전)

9.0 이전에는 문자열을 연결하는 내장 집계 함수가 없었습니다. Vajda Gabo가이 메일 링리스트 post에서 제안한 가장 간단한 사용자 정의 구현 은 내장 textcat함수 ( ||연산자 뒤에 있음 )를 사용하는 것입니다.

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

다음은 CREATE AGGREGATE설명서입니다.

이렇게하면 구분자가없는 모든 문자열을 간단하게 붙입니다. 끝에 “,”를 삽입하지 않고 그 사이에 “,”를 삽입하려면 자체 연결 기능을 만들고 위의 “textcat”을 대체 할 수 있습니다. 다음은 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;

이 버전은 행의 값이 null이거나 비어 있어도 쉼표를 출력하므로 다음과 같이 출력됩니다.

a, b, c, , e, , g

추가 출력을 위해 쉼표를 제거하려면 다음을 수행하십시오.

a, b, c, e, g

그런 다음 ELSIF함수에 다음과 같이 검사를 추가 하십시오.

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;

답변

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

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

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

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

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 = ''

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

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

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