Oracle vs PostgreSQL, STRING_AGG(LISTAGG) 가이드

Oracle과 PostgreSQL에서 문자열 집계를 손쉽게 처리하는 STRING_AGG(LISTAGG) 함수의 개념과 활용법

STRING_AGG(LISTAGG) 이 글 하나면 끝!

SQL을 쓰다 보면 여러 행의 값을 한 줄로 모아야 할 때가 많습니다. 예를 들어, 한 부서의 직원 이름을 콤마로 연결하거나, 여러 제품 코드를 한 칸에 모아 보여주고 싶을 때가 그렇습니다.

 

이럴 때 유용한 함수가 Oracle의 LISTAGG와 PostgreSQL의 STRING_AGG입니다. 두 함수 모두 여러 행의 문자열을 하나로 합쳐주는 집계 함수로, 보고서 작성이나 데이터 요약에 자주 활용됩니다.

목차

SQL - STRING_AGG

소개

STRING_AGG는 여러 행의 문자열 값을 하나의 문자열로 결합해 주는 집계 함수로, Oracle과 PostgreSQL에서 모두 비슷한 기능을 제공합니다. Oracle 데이터베이스에서는 동일한 기능을 LISTAGG라는 이름의 함수로 제공하며 PostgreSQL에서는 STRING_AGG 함수로 구현되어 있습니다. 이 함수들을 사용하면 다중 행의 데이터를 단일 행의 구분된 문자열로 변환할 수 있기 때문에, 데이터베이스에서 데이터를 요약하거나 보고용으로 표시할 때 매우 유용합니다. 예를 들어, 여러 사람의 이름을 콤마로 구분된 하나의 문자열로 만들거나 여러 제품 코드를 한 줄에 모아 표시할 때 이 함수를 사용할 수 있습니다.

문법 및 사용방법

Oracle - LISTAGG : Oracle에서는 LISTAGG(값_표현식, 구분자) WITHIN GROUP (ORDER BY 정렬열) 문법을 사용합니다. WITHIN GROUP (ORDER BY ...) 절에는 문자열을 연결하기 전에 정렬할 기준 열을 지정하며, 구분자는 각 값 사이에 넣을 문자열(예: '', '' 콤마와 스페이스)입니다. 이 함수를 GROUP BY와 함께 사용하면 각 그룹별로 문자열을 모아주고, GROUP BY 없이 단독 집계로 쓰면 전체 결과를 하나의 문자열로 반환합니다. Oracle의 LISTAGG는 NULL 값을 무시하며, 구분자를 생략하면 기본으로 아무 구분자 없이 연결합니다.

Oracle 사용방법 예시

SELECT deptno,
 LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employees_list
FROM emp
GROUP BY deptno;

PostgreSQL - STRING_AGG : PostgreSQL에서는 'STRING_AGG(값_표현식, 구분자 [ORDER BY 정렬열])' 형식으로 사용하며, Oracle과 달리 함수 인자 내부에서 'ORDER BY' 절을 바로 지정할 수 있습니다. 구분자와 정렬 기준을 제외한 기본 동작은 Oracle의 LISTAGG와 동일하게, 여러 행을 모아 하나의 문자열로 반환합니다. PostgreSQL의 STRING_AGG 역시 NULL 값은 무시하고, 구분자를 'NULL'로 지정하면 결과 문자열 사이에 아무 문자도 넣지 않고 연결합니다.

PostgreSQL 사용방법 예시

SELECT deptno,
 STRING_AGG(ename, ', ' ORDER BY ename) AS employees_list
FROM emp
GROUP BY deptno;

구현 및 성능 차이

두 함수는 기능적으로 유사하지만 구현상의 세부 사항과 지원 기능에 차이가 있습니다.

 

함수명과 제공 버전 : Oracle에서는 11g R2 버전부터 LISTAGG 함수를 제공하며, PostgreSQL에서는 9.x 버전부터 STRING_AGG 함수를 지원해 왔습니다. SQL 표준에서는 LISTAGG라는 이름으로 정의되어 있어, Oracle뿐 아니라 SQL Server 등 일부 DB에서는 LISTAGG 또는 STRING_AGG를 표준에 맞게 구현하고 있습니다. Oracle과 PostgreSQL에서는 이름만 다를 뿐 기본 동작은 동일합니다.

 

결과 길이 제한 : Oracle의 LISTAGG는 반환 타입이 VARCHAR2로 고정되어 있어서, 연결 결과 문자열 길이가 SQL VARCHAR2의 한계(일반적으로 4000바이트)를 넘을 경우 ORA-01489: result of string concatenation is too long 오류가 발생할 수 있습니다. 이를 대비해 Oracle 12c R2부터는 ON OVERFLOW 절을 도입하여, 결과 문자열이 너무 길 때 오류 대신 잘라내거나 특정 표시(…)를 추가하는 옵션을 제공했습니다. 반면 PostgreSQL의 STRING_AGG는 반환 타입이 TEXT이며 가변 길이이므로 매우 큰 문자열도 그대로 반환할 수 있고, 별도의 오버플로 제어 옵션은 없습니다. 따라서 PostgreSQL에서는 이론적으로 메모리가 허용하는 한 상당히 긴 문자열까지도 결과로 얻을 수 있지만, 너무 큰 결과를 다루는 것은 성능에 영향을 줄 수 있으므로 주의가 필요합니다.

 

중복 제거(DISTINCT) 지원 : Oracle은 19c 버전부터 LISTAGG 함수에 DISTINCT 키워드를 지원하여 집계 과정에서 중복 값을 제거할 수 있게 되었습니다. 예를 들어 LISTAGG(DISTINCT 컬럼, ',') WITHIN GROUP (...) 형태로 사용하면 중복된 값은 한 번만 나타납니다. 반면 PostgreSQL의 STRING_AGG는 함수 자체로 DISTINCT 옵션을 제공하지는 않지만, 일반 집계 함수와 동일하게 STRING_AGG(DISTINCT 컬럼, ', ')와 같이 쿼리 수준에서 DISTINCT 키워드를 적용하는 방법으로 중복을 제거할 수 있습니다. 또는 하위쿼리에서 미리 DISTINCT 결과를 만든 후 STRING_AGG로 연결하는 방식으로도 처리합니다.

 

정렬 및 실행 계획 : Oracle의 LISTAGG는 WITHIN GROUP (ORDER BY ...)절이 필수적으로 포함되는 경우가 많은데 Oracle 옵티마이저는 LISTAGG 수행 시 SORT 연산을 수행하는 실행 계획을 선택합니다. 이는 그룹 내 데이터 정렬이 필요한 경우 불가피하지만, 대량의 데이터를 집계할 때 쿼리 성능에 부담이 될 수 있습니다. PostgreSQL의 STRING_AGG에서도 ORDER BY를 지정하면 마찬가지로 정렬 오버헤드가 발생합니다. 특별히 정렬이 필요 없는 경우 ORDER BY를 생략할 수도 있지만, 이때 결과 문자열의 요소 순서는 정의되지 않을 수 있으므로(테이블의 저장 순서 등에 따름) 일반적으로는 명시적으로 정렬하는 것이 좋습니다. 양쪽 DB 모두 대량의 문자열을 집계하면 메모리 사용량 증가 및 처리 시간 증가가 발생할 수 있으므로, 필요에 따라 적절한 인덱스 활용이나 데이터 필터링으로 성능을 튜닝해야 합니다.

 

성능 비교 : 일반적으로 STRING_AGG/LISTAGG 함수 자체의 성능은 다른 집계 함수들과 비슷한 수준이며, DB 엔진 레벨에서 최적화되어 있습니다. Oracle에서는 LISTAGG 도입 전 사용자 정의 함수로 구현하던 문자열 집계보다 LISTAGG가 성능 면에서 우수한 것으로 보고되었습니다. PostgreSQL에서도 배열 집계('ARRAY_AGG') 후 문자 변환을 하는 방법보다 전용 함수인 STRING_AGG를 사용하는 것이 성능상 유리합니다. 예컨대, PostgreSQL에서 ARRAY_AGG()으로 모은 후 ARRAY_TO_STRING()을 호출하는 방식은 큰 집합을 처리할 때 약간의 오버헤드가 있으므로, 가능하면 한 번에 처리하는 STRING_AGG를 사용하는 것이 효율적입니다. 요약하면, 두 DB 모두 해당 함수 사용 자체는 비교적 빠르지만, 정렬이나 매우 큰 결과 처리 시에는 부하를 유발할 수 있으므로 이러한 점을 감안해야 합니다.

활용 예시

Dept | Employee
-------------
10 | CHOEYEONGHWAN
20 | ODINBOX
10 | ULSAN
20 | BUSAN

Oracle(LISTAGG 사용)

-- 부서 번호별로 직원 이름을 취합하여 콤마로 구분된 하나의 문자열로 만들 수 있습니다.
SELECT deptno,
 LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employees_list
FROM employees
GROUP BY deptno;

PostgreSQL(STRING_AGG 사용)

-- 부서 번호별로 직원 이름을 취합하여 콤마로 구분된 하나의 문자열로 만들 수 있습니다.
SELECT deptno,
 STRING_AGG(ename, ', ' ORDER BY ename) AS employees_list
FROM employees
GROUP BY deptno;

결과

Dept | Employees
-------------------------
10 | CHOEYEONGHWAN,ULSAN
20 | ODINBOX,BUSAN

사용 사례 및 활용범위

보고서 데이터 요약 : 여러 행의 상세 데이터를 한 줄로 요약하여 보고할 때 유용합니다. 예를 들어 영업 보고서에서 지역별 판매 상품 목록을 콤마로 구분된 문자열로 표시하거나, 과목별 학생 명단을 하나의 칸에 넣어 보여주는 식으로 사용할 수 있습니다. 이러한 데이터 비정규화(denormalization) 된 표현은 사람에게 읽기 편한 요약 정보를 제공합니다.

 

CSV 또는 목록 문자열 생성 : 애플리케이션에 데이터를 전달할 때, 다수의 값을 하나의 문자열 (예: CSV 형식)로 묶어서 전달해야 하는 경우가 있습니다. 예를 들어 여러 태그(tag) 값을 하나의 문자열로 결합하여 API로 보내거나, 로그 메시지에 여러 이벤트를 한 줄로 기록할 때 사용할 수 있습니다.

 

동적 쿼리 구성 : SQL문을 동적으로 생성해야 할 때, 특정 컬럼 값들을 콤마로 이어 붙여 인자 목록을 만드는 경우가 있습니다. 이때 STRING_AGG로 컬럼들을 한 줄로 만들고 그 결과를 서브쿼리 등으로 활용하면 편리합니다. (예: ''IN (' || LISTAGG(id, ',') || ')'' 형태로 ID 목록을 쿼리에 삽입 등).

 

XML/JSON 등과 결합 : 문자열로 합친 값을 further 가공하여 XML 혹은 JSON 문자열을 생성할 수도 있습니다. PostgreSQL의 경우 STRING_AGG 결과를 JSON 함수와 함께 사용해 여러 키를 한꺼번에 표현하거나, ARRAY_AGG 및 JSON_AGG와 조합하여 복잡한 구조의 문자열을 만들기도 합니다.

 

ETL 및 데이터 마이그레이션 : 데이터 마이그레이션 과정에서 다대일(여러 행 -> 한 행) 관계를 처리할 때 임시로 문자열로 합쳐서 이동한 후, 대상 시스템에서 파싱하는 전략을 쓰기도 합니다. 이런 경우 간단히 문자열로 합치기 위해 해당 함수를 사용할 수 있습니다.

 

STRING_AGG/LISTAGG는 데이터베이스 레벨에서 목록을 만들어야 할 때 두루 쓰일 수 있는 범용 도구입니다. 다만, 너무 많은 데이터를 한 번에 연결하면 결과 문자열이 과도하게 길어질 수 있으므로, 실제로 표시하거나 활용할 목적에 맞게 적절한 규모로 사용하는 것이 좋습니다.

같이 사용하였던 부분 확인

GROUP BY 절 : 대부분 STRING_AGG/LISTAGG는 GROUP BY와 함께 사용됩니다. 그룹화된 각 그룹마다 별도의 문자열 집계 결과를 얻어낼 수 있기 때문입니다. 예를 들어 부서별, 카테고리별 등의 그룹을 지정하여 각 그룹 내 값들을 연결하면 그룹별 요약정보를 손쉽게 생성할 수 있습니다.

 

ORDER BY 절 : 연결되는 문자열 내부의 값 순서를 제어하기 위해 정렬 기능을 사용합니다. Oracle의 경우 WITHIN GROUP (ORDER BY ...) 절에, PostgreSQL의 경우 함수 인자 내부에 ORDER BY를 넣어서 원하는 순서로 값들을 나열할 수 있습니다. 이를 통해 결과 문자열을 의미 있게 정렬할 수 있습니다 (예: 이름순, 날짜순 등). 정렬 기준을 명시하지 않으면 시스템은 임의의 순서로 값을 연결하므로, 결과의 재현성을 위해서는 가급적 정렬 조건을 지정하는 것이 좋습니다.

 

FILTER 절 (PostgreSQL) : PostgreSQL에서는 집계 함수에 FILTER (WHERE 조건) 절을 추가하여 특정 조건을 만족하는 행만을 집계할 수 있습니다. STRING_AGG에도 응용하면, 예를 들어 STRING_AGG(value, ', ') FILTER (WHERE condition) 형태로 특정 조건에 부합하는 값들만 선택적으로 연결 가능합니다. Oracle의 LISTAGG에는 직접적인 FILTER 절이 없지만, 대신 CASE WHEN ... THEN value END와 같은 표현식을 사용하여 조건에 따라 값이 'NULL'이 되도록 함으로써 비슷한 효과를 낼 수 있습니다.

 

DISTINCT 및 중복 처리 : 앞서 언급한 대로 Oracle 19c의 LISTAGG DISTINCT 기능은 중복 제거된 값들의 목록을 만드는데 편리합니다. PostgreSQL에서도 SELECT 구문에서 DISTINCT를 사용하거나 서브쿼리를 통해 중복을 제거한 후 STRING_AGG로 연결하면 동일한 목적을 달성할 수 있습니다.

 

윈도우 함수로 사용 : 두 DB의 문자열 집계 함수는 윈도우 함수로도 활용 가능합니다. Oracle의 LISTAGG는 OVER (PARTITION BY ...) 절과 함께 사용하여 각 파티션별 결과를 모든 행에 반복 표시하는 식으로 쓸 수 있고, PostgreSQL의 STRING_AGG도 OVER (PARTITION BY ... ORDER BY ...) 구문을 통해 비슷한 작업을 할 수 있습니다. 이를 활용하면 별도의 그룹핑 없이도 원본 행에 집계 결과를 추가적으로 표시할 수 있어, 일부 분석 쿼리에서 유용합니다.

 

기타 함수와 조합 : 경우에 따라 다른 문자열 함수나 집계 함수와 함께 사용하면 좋습니다. 예를 들어 Oracle에서 LISTAGG 결과가 4000자를 초과할 수 있을 때는 DBMS_LOB.SUBSTR 등을 사용해 자르거나, PostgreSQL에서 STRING_AGG 결과를 LEFT()나 SUBSTRING()으로 잘라서 표시할 수도 있습니다. 또한, Oracle에서는 LISTAGG 결과에 접두어나 접미어를 붙이기 위해 'CONCAT' 함수를 함께 쓰거나, PostgreSQL에서는 STRING_AGG 결과를 다른 문자열과 결합하여 문장을 만들 수도 있습니다.

마무리

STRING_AGG (Oracle의 LISTAGG) 함수는 SQL 레벨에서 손쉽게 문자열 리스트를 만드는 강력한 도구입니다. 여러 행의 데이터를 하나의 요약 문자열로 표현해야 할 때 이 함수를 사용하면 별도의 코딩 없이 한 줄의 SQL로 결과를 얻을 수 있습니다. Oracle과 PostgreSQL 모두 이 기능을 제공하므로 사용하는 데이터베이스에 맞는 구문만 선택하면 됩니다.