カテゴリ：

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

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 } ] [ NULLS { FIRST | LAST } ] ] [ <window_frame> ] )

引数

必須：

expr1

配列に入れる値を決定する式（通常は列名）。

OVER()

OVER 句は、関数がウィンドウ関数として使用されていることを指定します。詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。

オプション：

DISTINCT

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

WITHIN GROUP orderby_clause

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

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

PARTITION BY expr2

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

ORDER BY expr3 [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ {window_frame} ]

各パーティション内の順序を指定するオプション式、その後にオプションのウィンドウフレームが続きます。window_frame の構文の詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。

この関数を範囲ベースのフレームで使用する場合、 ORDER BY 句は単一列のみをサポートします。行ベースのフレームにはこの制限はありません。

LIMIT はサポートされていません。

戻り値

ARRAY 型の値を返します。

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

使用上の注意

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

    DISTINCT と WITHINGROUP に異なる列を指定すると、エラーが発生します。

    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 と WITHINGROUP に同じ列を指定するか、 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"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+

この例では、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"                                         |
|               | ]                                                           |
+---------------+-------------------------------------------------------------+

次の例では、別のデータセットを使用します。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"  |
|            | ]       |
...