カテゴリ：: 集計関数（半構造化データ）、ウィンドウ関数（一般）、半構造化データ関数と構造化データ関数（配列/オブジェクト）

ARRAY_AGG¶

配列にピボットされた入力値を返します。入力が空の場合、関数は空の配列を返します。

エイリアス：: ARRAYAGG

構文¶

集計関数

ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]

Copy

ウィンドウ関数

ARRAY_AGG( [ DISTINCT ] <expr1> )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Copy

引数¶

必須：

expr1: 配列に入れる値を決定する式（通常は列名）。
OVER(): OVER 句は、関数がウィンドウ関数として使用されていることを指定します。詳細については、ウィンドウ関数をご参照ください。

オプション：

DISTINCT

配列から重複する値を削除します。

WITHIN GROUP orderby_clause

各配列の値の順序を決定する1つ以上の式（通常は列名）を含む句。

WITHIN GROUP（ORDER BY）構文は、 SELECT ステートメントのメインの ORDER BY 句と同じパラメーターをサポートしています。 ORDER BY をご参照ください。

PARTITION BY expr2

式（通常は列名）を指定するウィンドウ数のサブ句。この式は、関数が適用される前に入力行をグループ化するパーティションを定義します。詳細については、ウィンドウ関数をご参照ください。

戻り値¶

ARRAY 型の値を返します。

ARRAY_AGG が1回の呼び出しで返すことができるデータの最大量は 16MB です。

使用上の注意¶

WITHIN GROUP（ORDER BY）を指定しない場合、各配列内の要素の順序は予測できません。（WITHIN GROUP 句以外の ORDERBY 句は、行内の配列要素の順序ではなく、出力行の順序に適用されます。）
WITHIN GROUP（ORDER BY）の式に数値を指定した場合、この数値は SELECT リストの列の序列としてではなく、数値定数として解析されます。このため、 WITHIN GROUP（ORDER BY）式として数値を指定しないでください。
DISTINCT と WITHINGROUP を指定する場合は、両方が同じ列を参照する必要があります。例:
```
SELECT ARRAY_AGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
```
Copy
DISTINCT と WITHINGROUP に異なる列を指定すると、エラーが発生します。
```
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 と WITHINGROUP に同じ列を指定するか、 DISTINCT を省略する必要があります。
NULL 値は出力から省略されます。
ウィンドウ関数として使用する場合、この関数は以下をサポートしません。
- OVER() 句の ORDER BY サブ句。
- ウィンドウフレーム。

例¶

以下のクエリ例では、以下に示すテーブルとデータを使用しています。

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

この例では、2つの個別の ORDER BY 句を使用します。1つは各行内の出力配列内の順序を制御し、もう1つは出力行の順序を制御します。

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