카테고리:

집계 함수 (일반) , 윈도우 함수 구문 및 사용법 (일반)

LISTAGG

delimiter 문자열로 구분된 연결된 입력 값을 반환합니다.

구문

집계 함수

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

윈도우 함수

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

필수 인자

expr1

목록에 넣을 값을 결정하는 식(일반적으로 열 이름)입니다. 식은 문자열로 평가되거나, 문자열로 캐스팅 될 수 있는 데이터 타입으로 평가되어야 합니다.

OVER()

OVER 절은 함수가 윈도우 함수로 사용되고 있을 때 필수적입니다. 자세한 내용은 윈도우 함수 구문 및 사용법 섹션을 참조하십시오.

선택적 인자

DISTINCT

목록에서 중복된 값을 제거합니다.

delimiter

문자열 또는 문자열로 평가되는 식입니다. 일반적으로 이 값은 단일 문자로 구성된 문자열입니다. 문자열은 아래 예와 같이 작은따옴표로 묶어야 합니다.

delimiter 가 지정되지 않은 경우 빈 문자열이 delimiter 로 사용됩니다.

delimiter 는 상수여야 합니다.

WITHIN GROUP orderby_clause

목록의 각 그룹에 대한 값의 순서를 결정하는 하나 이상의 식(일반적으로 열 이름)입니다.

WITHIN GROUP(ORDER BY) 구문은 SELECT 문의 ORDER BY 절과 동일한 매개 변수를 지원합니다.

PARTITION BY expr2

식(일반적으로 열 이름)을 지정하는 윈도우 함수 하위 절입니다. 이 식은 함수가 적용되기 전에 입력 행을 그룹화하는 파티션을 정의합니다. 자세한 내용은 윈도우 함수 구문 및 사용법 섹션을 참조하십시오.

반환

delimiter 로 구분된, NULL 이외의 모든 입력 값을 포함하는 문자열을 반환합니다.

이 함수는 목록이나 배열을 반환하지 않습니다. NULL이 아닌 모든 입력 값을 포함하는 단일 문자열을 반환합니다.

사용법 노트

  • 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) ...;
    Copy

    DISTINCT 및 WITHIN GROUP에 대해 서로 다른 열을 지정하면 다음과 같이 오류가 발생합니다.

    SELECT LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
    Copy
    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 절.

    • 명시적 윈도우 프레임.

데이터 정렬 세부 정보

  • The collation of the result is the same as the collation of the input.

  • ORDER BY 하위 절이 데이터 정렬이 있는 식을 지정하는 경우, 목록 내의 요소는 데이터 정렬에 따라 정렬됩니다.

  • delimiter 는 데이터 정렬 사양을 사용할 수 없습니다.

  • ORDER BY 내에서 데이터 정렬을 지정해도 이는 결과의 데이터 정렬에 영향을 미치지 않습니다. 예를 들어, 아래 문에는 두 개의 ORDER BY 절이 포함되어 있는데, 하나는 LISTAGG용이고 다른 하나는 쿼리 결과용입니다. 첫 번째 절 내에서 데이터 정렬을 지정해도 이는 두 번째 절의 데이터 정렬에 영향을 미치지 않습니다. 두 ORDER BY 절에서 출력 데이터를 정렬해야 하는 경우, 두 절에서 명시적으로 데이터 정렬을 지정해야 합니다.

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

예

다음 예에서는 LISTAGG 함수를 사용합니다.

LISTAGG 함수를 사용하여 쿼리 결과의 값 연결하기

다음 예에서는 LISTAGG 함수를 사용하여 주문 데이터에 대한 쿼리 결과의 값을 연결합니다.

참고

다음 예에서는 TPC-H 샘플 데이터 를 쿼리합니다. 쿼리를 실행하기 전에 다음 SQL 문을 실행합니다.

USE SCHEMA snowflake_sample_data.tpch_sf1;
Copy

다음 예에서는 o_totalprice 가 520000 보다 큰 주문에 대한 고유한 o_orderkey 값을 나열하고 delimiter 에 대해 빈 문자열을 사용합니다.

SELECT LISTAGG(DISTINCT o_orderkey, ' ')
  FROM orders
  WHERE o_totalprice > 520000;
Copy
+-------------------------------------------------+
| LISTAGG(DISTINCT O_ORDERKEY, ' ')               |
|-------------------------------------------------|
| 2232932 1750466 3043270 4576548 4722021 3586919 |
+-------------------------------------------------+

다음 예에서는 o_totalprice 가 520000 보다 큰 주문에 대한 고유한 o_orderstatus 값을 나열하고 delimiter 에 대해 세로 막대를 사용합니다.

SELECT LISTAGG(DISTINCT o_orderstatus, '|')
  FROM orders
  WHERE o_totalprice > 520000;
Copy
+--------------------------------------+
| LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
|--------------------------------------|
| O|F                                  |
+--------------------------------------+

다음 예에서는 o_totalprice 가 520000 보다 큰 각 주문의 o_orderstatus 및 o_clerk 값을 o_orderstatus 로 그룹화하여 나열합니다. 이 쿼리에서는 delimiter 에 쉼표를 사용합니다.

SELECT o_orderstatus,
   LISTAGG(o_clerk, ', ')
     WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 520000
  GROUP BY o_orderstatus;
Copy
+---------------+---------------------------------------------------+
| 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 함수로 데이터 정렬 사용하기

다음 예에서는 LISTAGG 함수를 사용한 데이터 정렬 을 보여줍니다. 예제에서는 다음 데이터를 사용합니다.

CREATE OR REPLACE TABLE collation_demo (
  spanish_phrase VARCHAR COLLATE 'es');
Copy
INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');
Copy

다른 데이터 정렬 사양을 사용한 출력 순서의 차이에 유의하십시오. 이 쿼리에서는 es 데이터 정렬 사양을 사용합니다.

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'es')) AS es_collation
  FROM collation_demo;
Copy
+-----------------------------------------+
| ES_COLLATION                            |
|-----------------------------------------|
| Pinatubo (Mount)|pint|Pinta|piña colada |
+-----------------------------------------+

이 쿼리에서는 utf8 데이터 정렬 사양을 사용합니다.

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8')) AS utf8_collation
  FROM collation_demo;
Copy
+-----------------------------------------+
| UTF8_COLLATION                          |
|-----------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada |
+-----------------------------------------+
언어: 한국어