LISTAGG

LISTAGG (목록 결합)

입력 값을 구분자(delimiter) 문자열로 구분하여 연결(concatenate)한 값을 반환해요.

출처: Snowflake SQL Reference - LISTAGG

본문

구문 (Syntax)

집계 함수(Aggregate function):

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]

창 함수(Window function):

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

필수 인자 (Required arguments)

expr1

목록에 넣을 값을 결정하는 표현식(일반적으로 열 이름)이에요. 표현식은 문자열 또는 문자열로 캐스팅할 수 있는 데이터 타입으로 평가되어야 해요.

OVER()

OVER 절은 함수를 창 함수로 사용할 때 필요해요. 자세한 내용은 Window function syntax and usage를 참고하세요.

선택 인자 (Optional arguments)

DISTINCT

목록에서 중복 값을 제거해요.

delimiter

문자열 또는 문자열로 평가되는 표현식이에요. 일반적으로 이 값은 단일 문자 문자열이에요. 아래 예시처럼 문자열은 작은따옴표로 감싸야 해요. 구분자를 지정하지 않으면 빈 문자열이 구분자로 사용돼요. 구분자는 상수(constant)여야 해요.

WITHIN GROUP orderby_clause

목록의 각 그룹에 대해 값의 순서를 결정하는 하나 이상의 표현식(일반적으로 열 이름)이에요. WITHIN GROUP (ORDER BY) 구문은 SELECT 문의 ORDER BY 절과 같은 파라미터를 지원해요.

PARTITION BY expr2

함수를 적용하기 전에 입력 행을 그룹화하는 파티션을 정의하는 표현식(일반적으로 열 이름)을 지정하는 창 함수 하위 절이에요. 자세한 내용은 Window function syntax and usage를 참고하세요.

반환 값 (Returns)

구분자로 구분된 모든 비-NULL 입력 값을 포함하는 문자열을 반환해요.

이 함수는 목록이나 배열을 반환하지 않아요. 모든 비-NULL 입력 값이 포함된 단일 문자열을 반환해요.

사용 참고 사항 (Usage notes)

WITHIN GROUP (ORDER BY)를 지정하지 않으면 각 목록 내 요소의 순서는 예측할 수 없어요. (WITHIN GROUP 절 밖의 ORDER BY 절은 목록 요소의 순서가 아니라 출력 행의 순서에 적용돼요.)

WITHIN GROUP (ORDER BY)의 표현식에 숫자를 지정하면 이 숫자는 SELECT 목록에서 열의 서수 위치가 아니라 숫자 상수로 구문 분석돼요. 따라서 WITHIN GROUP (ORDER BY) 표현식으로 숫자를 지정하지 마세요.

DISTINCT와 WITHIN GROUP을 모두 지정하면 둘 다 같은 열을 가리켜야 해요. 예:

SELECT LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;

DISTINCT와 WITHIN GROUP에 서로 다른 열을 지정하면 오류가 발생해요:

SELECT LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
-- SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression

DISTINCT와 WITHIN GROUP에 같은 열을 지정하거나 DISTINCT를 생략해야 해요.

NULL 또는 빈 입력 값에 대해: 입력이 비어 있으면 빈 문자열을 반환해요. 모든 입력 표현식이 NULL로 평가되면 출력은 빈 문자열이에요. 일부 입력 표현식만 NULL로 평가되면 출력에는 모든 비-NULL 값이 포함되고 NULL 값은 제외돼요.

이 함수를 창 함수로 호출하면 다음을 지원하지 않아요: OVER 절 내의 ORDER BY 절, 명시적 창 프레임(window frames).

데이터 정렬(Collation) 세부 사항

결과의 collation은 입력의 collation과 같아요.

ORDER BY 하위 절이 collation이 있는 표현식을 지정하면 목록 내 요소는 collation에 따라 정렬돼요.

구분자는 collation 사양을 사용할 수 없어요.

ORDER BY 안에 collation을 지정해도 결과의 collation에는 영향을 주지 않아요. 예를 들어 아래 명령문에는 LISTAGG용과 쿼리 결과용 두 개의 ORDER BY 절이 있어요. 첫 번째 절 안에 collation을 지정해도 두 번째 절에는 영향을 주지 않아요. 두 ORDER BY 절 모두에서 출력을 정렬해야 한다면 두 절 모두에 collation을 명시적으로 지정해야 해요.

SELECT LISTAGG(x, ',') WITHIN GROUP (ORDER BY last_name COLLATE 'es') FROM table1 ORDER BY last_name;

예시 (Examples)

이 예시들은 LISTAGG 함수를 사용해요.

LISTAGG 함수로 쿼리 결과의 값 연결

다음 예시들은 orders 데이터에 대한 쿼리 결과의 값을 연결하기 위해 LISTAGG 함수를 사용해요.

참고: 이 예시들은 TPC-H 샘플 데이터를 쿼리해요. 쿼리를 실행하기 전에 다음 SQL 문을 실행하세요:

USE SCHEMA snowflake_sample_data.tpch_sf1;

이 예시는 o_totalprice가 520000보다 큰 주문의 고유한 o_orderkey 값을 나열하고 구분자로 빈 문자열을 사용해요:

SELECT LISTAGG(DISTINCT o_orderkey, ' ')
  FROM orders
  WHERE o_totalprice > 520000;

+-------------------------------------------------+
| LISTAGG(DISTINCT O_ORDERKEY, ' ')               |
|-------------------------------------------------|
| 2232932 1750466 3043270 4576548 4722021 3586919 |
+-------------------------------------------------+

이 예시는 o_totalprice가 520000보다 큰 주문의 고유한 o_orderstatus 값을 나열하고 구분자로 세로 막대(|)를 사용해요:

SELECT LISTAGG(DISTINCT o_orderstatus, '|')
  FROM orders
  WHERE o_totalprice > 520000;

+--------------------------------------+
| LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
|--------------------------------------|
| O|F                                  |
+--------------------------------------+

이 예시는 o_totalprice가 520000보다 크고 o_orderstatus로 그룹화된 각 주문의 o_orderstatus와 o_clerk 값을 나열해요. 이 쿼리는 구분자로 쉼표를 사용해요:

SELECT o_orderstatus,
   LISTAGG(o_clerk, ', ')
     WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 520000
  GROUP BY o_orderstatus;

+---------------+---------------------------------------------------+
| O_ORDERSTATUS | LISTAGG(O_CLERK, ', ')                            |
|               |      WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)    |
|---------------+---------------------------------------------------|
| O             | Clerk#000000699, Clerk#000000336, Clerk#000000245 |
| F             | Clerk#000000040, Clerk#000000230, Clerk#000000924 |
+---------------+---------------------------------------------------+

LISTAGG 함수와 함께 collation 사용

다음 예시들은 LISTAGG 함수와 collation을 보여줘요. 예시들은 다음 데이터를 사용해요:

CREATE OR REPLACE TABLE collation_demo (
  spanish_phrase VARCHAR COLLATE 'es');

INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');

collation 사양이 다르면 출력 순서가 다르다는 점을 주목하세요. 이 쿼리는 es collation 사양을 사용해요:

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'es')) AS es_collation
  FROM collation_demo;

+-----------------------------------------+
| ES_COLLATION                            |
|-----------------------------------------|
| Pinatubo (Mount)|pint%Pinta%piña colada |
+-----------------------------------------+

이 쿼리는 utf8 collation 사양을 사용해요:

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8')) AS utf8_collation
  FROM collation_demo;

+-----------------------------------------+
| UTF8_COLLATION                          |
|-----------------------------------------|
| Pinatubo (Mount)|Pinta%pint%piña colada |
+-----------------------------------------+

더 알아보기 (Learn more)