- カテゴリ：
集計関数 （半構造化データ）、 ウィンドウ関数 （一般）、 半構造化データ関数と構造化データ関数 （配列/オブジェクト）
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各配列の値の順序を決定する1つ以上の式（通常は列名）を含む句。
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 が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) ...;
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 expressionDISTINCT と WITHINGROUP に同じ列を指定するか、 DISTINCT を省略する必要があります。
DISTINCT および WITHIN GROUP は、 OVER 句内に ORDER BY 句がない場合にのみウィンドウ関数呼び出しに対応します。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" | | ] | +-----------------------------------------------------------------------------+
この例では、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" |
| | ] |
...