ARRAY_AGG
ARRAY_AGG
ARRAY_AGG() 함수는 입력 값들을 배열로 피벗(pivot)해 반환해요. 입력이 비어 있으면 함수는 빈 배열을 반환해요. (ARRAYAGG는 ARRAY_AGG의 동의어예요.)
본문
문법
집계 함수 (Aggregate function):
ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]
윈도우 함수 (Window function):
ARRAY_AGG( [ DISTINCT ] <expr1> )
[ WITHIN GROUP ( <orderby_clause> ) ]
OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] ] [ <window_frame> ] )
인자
expr1 (필수) — 배열에 넣을 값을 결정하는 표현식(보통 열 이름)이에요.
OVER 절 — 함수를 윈도우 함수로 사용하고 있음을 지정해요. 자세한 내용은 윈도우 함수 문법과 사용을 참고하세요.
DISTINCT (선택) — 배열에서 중복 값을 제거해요.
WITHIN GROUP (ORDER BY ...) (선택) — 각 배열의 값 순서를 결정하는 표현식(보통 열 이름)을 하나 이상 담은 절이에요. WITHIN GROUP(ORDER BY) 문법은 SELECT 문의 기본 ORDER BY 절과 같은 매개변수를 지원해요. ORDER BY 참고.
PARTITION BY <expr2> (선택) — 함수를 적용하기 전에 입력 행을 그룹화하는 파티션을 정의하는 표현식(보통 열 이름)을 지정하는 윈도우 함수 절이에요.
ORDER BY <expr3> (선택) — 각 파티션 안에서 정렬할 선택적 표현식이고, 그 뒤에 선택적 윈도우 프레임이 따라와요. 자세한 window_frame 문법은 윈도우 함수 문법과 사용을 참고하세요.
- 이 함수를 범위 기반 프레임(range-based frame)과 함께 사용하면 ORDER BY 절은 단일 열만 지원해요. 행 기반 프레임에는 이 제한이 없어요.
- LIMIT는 지원하지 않아요.
반환
ARRAY 타입의 값을 반환해요. ARRAY_AGG가 단일 호출로 반환할 수 있는 최대 데이터 양은 128MB예요.
사용 노트
- 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 expression
DISTINCT와 WITHIN GROUP에 같은 열을 지정하거나 DISTINCT를 생략해야 해요.
- DISTINCT와 WITHIN GROUP은 OVER 절 안에 ORDER BY 절이 없을 때만 윈도우 함수 호출에서 지원돼요. OVER 절에 ORDER BY 절을 사용하면 출력 배열의 값은 같은 기본 순서(즉 WITHIN GROUP (ORDER BY expr3)에 해당하는 순서)를 따라요.
- NULL 값은 출력에서 제외돼요.
예제
아래 쿼리 예제들은 아래 표시된 테이블과 데이터를 사용해요:
CREATE TABLE orders (
o_orderkey INTEGER,
o_clerk VARCHAR,
o_totalprice NUMBER(12, 2),
o_orderstatus CHAR(1)
);
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" |
| | ] |
...