ARRAY_AGG¶

구문¶

집계 함수

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

  Copy

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

  SELECT ARRAY_AGG(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를 생략해야 합니다.

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

Copy

이 예는 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 |
+------------+

Copy

이 예는 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                                                     |
| ]                                                            |
+--------------------------------------------------------------+

Copy

이 예는 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"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+

Copy

이 예제에서는 두 개의 개별 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"                                         |
|               | ]                                                           |
+---------------+-------------------------------------------------------------+

Copy

다음 예제에서는 다른 데이터 세트를 사용합니다. 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"  |
|            | ]       |
...