- 카테고리:
집계 함수 (일반) , 윈도우 함수 구문 및 사용법 (일반)
LISTAGG¶
delimiter
문자열로 구분된 연결된 입력 값을 반환합니다.
구문¶
집계 함수
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
[ WITHIN GROUP ( <orderby_clause> ) ]
윈도우 함수
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
[ WITHIN GROUP ( <orderby_clause> ) ]
OVER ( [ PARTITION BY <expr2> ] )
필수 인자¶
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) ...;
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 절.
명시적 윈도우 프레임.
데이터 정렬 세부 정보¶
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;
예¶
다음 예에서는 LISTAGG 함수를 사용합니다.
LISTAGG 함수를 사용하여 쿼리 결과의 값 연결하기¶
다음 예에서는 LISTAGG 함수를 사용하여 주문 데이터에 대한 쿼리 결과의 값을 연결합니다.
참고
다음 예에서는 TPC-H 샘플 데이터 를 쿼리합니다. 쿼리를 실행하기 전에 다음 SQL 문을 실행합니다.
USE SCHEMA snowflake_sample_data.tpch_sf1;
다음 예에서는 o_totalprice
가 520000
보다 큰 주문에 대한 고유한 o_orderkey
값을 나열하고 delimiter
에 대해 빈 문자열을 사용합니다.
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
값을 나열하고 delimiter
에 대해 세로 막대를 사용합니다.
SELECT LISTAGG(DISTINCT o_orderstatus, '|')
FROM orders
WHERE o_totalprice > 520000;
+--------------------------------------+
| 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;
+---------------+---------------------------------------------------+
| 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');
INSERT INTO collation_demo (spanish_phrase) VALUES
('piña colada'),
('Pinatubo (Mount)'),
('pint'),
('Pinta');
다른 데이터 정렬 사양을 사용한 출력 순서의 차이에 유의하십시오. 이 쿼리에서는 es
데이터 정렬 사양을 사용합니다.
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
데이터 정렬 사양을 사용합니다.
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 |
+-----------------------------------------+