PostgreSQL GROUP BY 쿼리에서 문자열 연결하는 방법

데이터베이스를 사용할 때 특히 PostgreSQL과 함께 작업할 때, 레코드 그룹 내에서 문자열을 연결해야 하는 일반적인 상황에 직면할 수 있습니다. 이는 일반적으로 동일한 카테고리(예: 같은 회사의 직원)에 대한 여러 항목을 포함하는 데이터 세트를 가지고 있고, 이러한 항목을 발표나 분석을 위해 하나의 문자열로 수집하고자 할 때 발생합니다.

이 블로그 포스트에서는 PostgreSQL에서 GROUP BY 쿼리를 사용하여 문자열 연결을 달성하는 방법에 대해 설명합니다. 현대적인 솔루션뿐만 아니라 포괄적인 이해를 위해 구 버전 PostgreSQL을 위한 접근 방식도 다룰 것입니다.

문제

예를 들어, 다음 직원 테이블을 고려해 보십시오:

이 항목들을 COMPANY_ID로 그룹화하여 각 회사와 관련된 직원 목록을 출력하고자 합니다. 원하는 출력은 다음과 같습니다:

솔루션

PostgreSQL 9.0 이상

PostgreSQL 버전 9.0 이상을 사용하고 있다면, 강력한 내장 함수 string_agg()를 사용하여 GROUP BY 절 내에서 문자열을 효과적으로 연결할 수 있습니다.

쿼리를 다음과 같이 작성할 수 있습니다:

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

정렬 포함

추가로, PostgreSQL 버전 9.0부터 집계 함수 내에서 ORDER BY를 지원하므로 직원의 순서를 지정할 수 있습니다:

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

이렇게 하면 직원 이름이 특정 순서로 연결됩니다.

PostgreSQL 8.4.x

아직 PostgreSQL 8.4를 사용하고 있다면 지원을 받지 않는 버전으로, array_agg() 함수를 array_to_string()와 조합하여 비슷한 결과를 얻을 수 있습니다. 여기에 사용할 SQL 쿼리는 다음과 같습니다:

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

PostgreSQL 8.3.x 및 이전 버전

PostgreSQL 8.3 및 이전 버전 사용자의 경우, 문자열 연결을 직접 수행하는 내장 함수가 없습니다. 이 제한을 우회하기 위한 사용자 정의 구현은 다음과 같습니다:

1. textcat 함수를 사용하여 새로운 집계 함수를 생성합니다:

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

2. 연결된 문자열 사이에 , 와 같은 구분자를 포함하도록 사용자 정의 함수를 생성합니다:

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 또는 빈 값에 대한 추가 쉼표를 제거하려면, 다음과 같이 더 세련된 버전을 사용할 수 있습니다:

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;

결론

PostgreSQL에서 문자열을 연결하는 것은 특히 GROUP BY 연산 중에 의미 있는 보고서와 데이터 세트를 생성하는 데 중요합니다. 최근 버전에서 string_agg()와 같은 함수가 도입되면서 이 작업이 간단해졌습니다.

구 버전을 사용하고 있다면, 여기에서 자세히 설명한 사용자 정의 집계 방법을 사용하여 유사한 결과를 얻을 수 있습니다. 항상 더 새로운 기능과 보안 개선을 활용하기 위해 PostgreSQL 버전을 업그레이드하는 것을 고려하세요.

행복한 쿼리 되세요!