- 카테고리:
집계 함수 (반정형 데이터) , 윈도우 함수 (일반) , 반정형 및 정형 데이터 함수 (배열/오브젝트)
ARRAY_AGG¶
배열로 피벗된 입력값을 반환합니다. 입력이 비어 있으면 함수는 빈 배열을 반환합니다.
- 별칭:
ARRAYAGG
구문¶
집계 함수
ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]
윈도우 함수
ARRAY_AGG( [ DISTINCT ] <expr1> )
[ WITHIN GROUP ( <orderby_clause> ) ]
OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ { ASC | DESC } ] ] [ <window_frame> ] )
인자¶
필수:
expr1배열에 넣을 값을 결정하는 식(일반적으로 열 이름)입니다.
OVER()OVER 절은 함수가 윈도우 함수로 사용되고 있음을 지정합니다. 자세한 내용은 윈도우 함수 구문 및 사용법 섹션을 참조하십시오.
선택 사항:
DISTINCT배열에서 중복된 값을 제거합니다.
WITHIN GROUP orderby_clause각 배열의 값 순서를 결정하는 하나 이상의 식(일반적으로 열 이름)을 포함하는 절입니다.
WITHIN GROUP(ORDER BY) 구문은 SELECT 문의 기본 ORDER BY 절과 동일한 매개 변수를 지원합니다. ORDER BY 섹션을 참조하십시오.
PARTITION BY expr2식(일반적으로 열 이름)을 지정하는 윈도우 함수 절입니다. 이 식은 함수가 적용되기 전에 입력 행을 그룹화하는 파티션을 정의합니다. 자세한 내용은 윈도우 함수 구문 및 사용법 섹션을 참조하십시오.
ORDER BY expr3[ { ASC | DESC } ] [{window_frame}]각 파티션 내에서 주문할 식과 선택적 윈도우 프레임을 지정할 수 있습니다. 자세한
window_frame구문은 윈도우 함수 구문 및 사용법 섹션을 참조하십시오.이 함수를 범위 기반 프레임과 함께 사용하는 경우 ORDER BY 절은 단일 열만 지원합니다. 행 기반 프레임에는 이러한 제한이 없습니다.
LIMIT 가 지원되지 않습니다.
반환¶
ARRAY 형식의 값을 반환합니다.
ARRAY_AGG가 단일 호출에 대해 반환할 수 있는 최대 데이터 양은 16MB입니다.
사용법 노트¶
WITHIN GROUP(ORDER BY)을 지정하지 않으면 각 배열 내의 요소 순서를 예측할 수 없습니다. (WITHIN GROUP 절 외부의 ORDER BY 절은 행 내 배열 요소의 순서가 아니라 출력 행의 순서에 적용됩니다.)
WITHIN GROUP(ORDER BY)의 식에 숫자를 지정하면 이 숫자는 SELECT 목록에 있는 열의 서수 위치가 아닌 숫자 상수로 구문 분석됩니다. 따라서 숫자를 WITHIN GROUP(ORDER BY) 식으로 지정하지 마십시오.
DISTINCT 및 WITHIN GROUP을 지정하는 경우, 둘 다 동일한 열을 참조해야 합니다. 예:
SELECT ARRAY_AGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
DISTINCT 및 WITHIN GROUP에 대해 서로 다른 열을 지정하면 다음과 같이 오류가 발생합니다.
SELECT ARRAY_AGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expressionDISTINCT 및 WITHIN GROUP에 대해 동일한 열을 지정하거나 DISTINCT를 생략해야 합니다.
DISTINCT 및 WITHIN GROUP 은 OVER 절 내에 ORDERBY 절이 없는 경우에만 윈도우 함수 호출에 지원됩니다. OVER 절에 ORDER BY 절을 사용하면 출력 배열의 값은 동일한 기본 순서(즉,
WITHIN GROUP (ORDER BY expr3)과 동일한 순서)를 따릅니다.NULL 값은 출력에서 생략됩니다.
예¶
아래의 예시 쿼리는 아래에 표시된 테이블과 데이터를 사용합니다.
CREATE TABLE orders ( o_orderkey INTEGER, -- unique ID for each order. o_clerk VARCHAR, -- identifies which clerk is responsible. o_totalprice NUMBER(12, 2), -- total price. o_orderstatus CHAR(1) -- 'F' = Fulfilled (sent); -- 'O' = 'Ordered but not yet Fulfilled'. ); INSERT INTO orders (o_orderkey, o_orderstatus, o_clerk, o_totalprice) VALUES ( 32123, 'O', 'Clerk#000000321', 321.23), ( 41445, 'F', 'Clerk#000000386', 1041445.00), ( 55937, 'O', 'Clerk#000000114', 1055937.00), ( 67781, 'F', 'Clerk#000000521', 1067781.00), ( 80550, 'O', 'Clerk#000000411', 1080550.00), ( 95808, 'F', 'Clerk#000000136', 1095808.00), (101700, 'O', 'Clerk#000000220', 1101700.00), (103136, 'F', 'Clerk#000000508', 1103136.00);
이 예는 ARRAY_AGG()를 사용하지 않는 쿼리의 피벗되지 않은 출력을 보여줍니다. 이 예와 다음 예 사이의 출력 대조는 ARRAY_AGG()가 데이터를 피벗한다는 것을 보여줍니다.
SELECT O_ORDERKEY AS order_keys FROM orders WHERE O_TOTALPRICE > 450000 ORDER BY O_ORDERKEY; +------------+ | ORDER_KEYS | |------------| | 41445 | | 55937 | | 67781 | | 80550 | | 95808 | | 101700 | | 103136 | +------------+
이 예는 ARRAY_AGG()를 사용하여 출력 열을 단일 행의 배열로 피벗하는 방법을 보여줍니다.
SELECT ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC) FROM orders WHERE O_TOTALPRICE > 450000; +--------------------------------------------------------------+ | ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC) | |--------------------------------------------------------------| | [ | | 41445, | | 55937, | | 67781, | | 80550, | | 95808, | | 101700, | | 103136 | | ] | +--------------------------------------------------------------+
이 예는 ARRAY_AGG()와 함께 DISTINCT 키워드를 사용하는 방법을 보여줍니다.
SELECT ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC) FROM orders WHERE O_TOTALPRICE > 450000 ORDER BY O_ORDERSTATUS ASC; +-----------------------------------------------------------------------------+ | ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC) | |-----------------------------------------------------------------------------| | [ | | "F", | | "O" | | ] | +-----------------------------------------------------------------------------+
이 예제에서는 두 개의 개별 ORDER BY 절을 사용합니다. 하나는 각 행 내부의 출력 배열 내 순서를 제어하고 다른 하나는 출력 행의 순서를 제어합니다.
SELECT O_ORDERSTATUS, ARRAYAGG(O_CLERK) WITHIN GROUP (ORDER BY O_TOTALPRICE DESC) FROM orders WHERE O_TOTALPRICE > 450000 GROUP BY O_ORDERSTATUS ORDER BY O_ORDERSTATUS DESC; +---------------+-------------------------------------------------------------+ | O_ORDERSTATUS | ARRAYAGG(O_CLERK) WITHIN GROUP (ORDER BY O_TOTALPRICE DESC) | |---------------+-------------------------------------------------------------| | O | [ | | | "Clerk#000000220", | | | "Clerk#000000411", | | | "Clerk#000000114" | | | ] | | F | [ | | | "Clerk#000000508", | | | "Clerk#000000136", | | | "Clerk#000000521", | | | "Clerk#000000386" | | | ] | +---------------+-------------------------------------------------------------+
다음 예제에서는 다른 데이터 세트를 사용합니다. ARRAY_AGG 함수는 ROWS BETWEEN 윈도우 프레임이 있는 윈도우 함수로 호출됩니다. 먼저 테이블을 만들고 14개의 행으로 로드합니다.
CREATE OR REPLACE TABLE array_data AS (
WITH data AS (
SELECT 1 a, [1,3,2,4,7,8,10] b
UNION ALL
SELECT 2, [1,3,2,4,7,8,10]
)
SELECT 'Ord'||a o_orderkey, 'c'||value o_clerk, index
FROM data, TABLE(FLATTEN(b))
);
이제 다음 쿼리를 실행합니다. 여기에는 일부 결과 세트만 표시됩니다.
SELECT o_orderkey,
ARRAY_AGG(o_clerk) OVER(PARTITION BY o_orderkey ORDER BY o_orderkey
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS result
FROM array_data;
+------------+---------+
| O_ORDERKEY | RESULT |
|------------+---------|
| Ord1 | [ |
| | "c1" |
| | ] |
| Ord1 | [ |
| | "c1", |
| | "c3" |
| | ] |
| Ord1 | [ |
| | "c1", |
| | "c3", |
| | "c2" |
| | ] |
| Ord1 | [ |
| | "c1", |
| | "c3", |
| | "c2", |
| | "c4" |
| | ] |
| Ord1 | [ |
| | "c3", |
| | "c2", |
| | "c4", |
| | "c7" |
| | ] |
| Ord1 | [ |
| | "c2", |
| | "c4", |
| | "c7", |
| | "c8" |
| | ] |
| Ord1 | [ |
| | "c4", |
| | "c7", |
| | "c8", |
| | "c10" |
| | ] |
| Ord2 | [ |
| | "c1" |
| | ] |
| Ord2 | [ |
| | "c1", |
| | "c3" |
| | ] |
...