ARRAY_AGG 集約関数

ARRAY_AGG 関数は、エレメントのセットを配列に集約します。

ARRAY_AGG 集約関数の呼び出しは、結果の配列タイプに基づいています。

通常配列の集約

Read syntax diagramSkip visual syntax diagramARRAY_AGG(element-expression ORDER BY,sort-keyASCDESC )
element-expression
Read syntax diagramSkip visual syntax diagram expressionrow-expression(expression,,expression)

連想配列の集約

Read syntax diagramSkip visual syntax diagramARRAY_AGG(index-expression ,element-expression)
element-expression
Read syntax diagramSkip visual syntax diagram expressionrow-expression(expression,,expression)

スキーマは SYSIBM です。

通常配列の集約
element-expression
配列のエレメントのソースを指定します。
expression
配列のエレメント値を指定する式。 式のデータ・タイプは、CREATE TYPE (配列) ステートメントで指定できるデータ・タイプでなければなりません (SQLSTATE 429C2)。
行式
配列のエレメントとして行データ・タイプを持つ値を指定する行の式。
( expression, 式 )
配列のエレメントとして行データ・タイプを持つ値のフィールドを指定する 2 つ以上の式のリスト。 それぞれの式のデータ・タイプは、CREATE TYPE (行) ステートメントで説明されているように、行フィールドの有効なデータ・タイプである必要があります (SQLSTATE 429C5)。
ORDER BY
通常配列の集約で処理される、同じグループ化集合からの行の順序を指定します。 ORDER BY 節が列データの順序を識別できない場合、同一のグループ化集合内の行は任意に順序付けられます。

ORDER BY を指定すると、通常配列の集約されるエレメントの順序が決まります。 ORDER BY が指定されず、同じ SELECT 節の中にその他の ARRAY_AGG、 LISTAGG、または XMLAGG が指定された順序で組み込まれていない場合、通常配列内のエレメントの順序付けは一律には決まりません。 ORDER BY を指定せず、 同じ SELECT 節内に順序を指定する ARRAY_AGG、LISTAGG、または XMLAGG を複数使用した場合、ARRAY_AGG のそれぞれの結果では、通常配列内のエレメントと同じ順序付けが使用されます。

ソート・キー
ソート・キーは、列名または sort-key-expression のどちらでも構いません。 ソート・キーが定数の場合、これは出力列の位置を (照会の ORDER BY 節におけるように) 指すのではなく、どのソート・キーも暗黙に示さない単なる定数です。
ASC
sort-key を昇順で処理します。 これはデフォルト・オプションです。
DESC
sort-key を降順で処理します。

結果のデータ・タイプは通常配列です。 エレメント値が単一の expression または row-expression を使用して指定されている場合、配列エレメントのデータ・タイプは expression または row-expression のタイプと同じです。 エレメント値が式のリストで指定されている場合、配列エレメントは、式に対応するフィールド・タイプを持つ行タイプでです。

SELECT 節に ARRAY_AGG 関数が組み込まれている場合、同じ SELECT 節にある ARRAY_AGG、LISTAGG、XMLAGG、および XMLGROUP 関数のすべての呼び出しにおいて、同じ順序を指定するか、または順序を指定しないかのいずれかにする必要があります (SQLSTATE 428GZ)。

連想配列の集約
索引式
連想配列の索引を指定します。 同じステートメントにターゲットのユーザー定義の配列データがある、または ARRAY_AGG の結果がユーザー定義の配列データ・タイプに明示的にキャストされるコンテキストで使用される場合、index-expression のデータ・タイプは、ターゲットの連想配列データ・タイプの索引データ・タイプにキャスト可能でなければなりません。 そうでない場合は、index-expression のデータ・タイプは、CREATE TYPE (配列) ステートメントの連想配列の索引に指定できるデータ・タイプでなければなりません (SQLSTATE 429C2)。

連想配列を集約するために処理されるグループ化セットには、index-expression の値が重複して存在することはできません (SQLSTATE 22545)。

element-expression
配列のエレメントのソースを指定します。
expression
配列のエレメント値を指定する式。 式のデータ・タイプは、CREATE TYPE (配列) ステートメントで指定できるデータ・タイプでなければなりません (SQLSTATE 429C2)。
行式
配列のエレメントとして行データ・タイプを持つ値を指定する行の式。
( expression, 式 )
配列のエレメントとして行データ・タイプを持つ値のフィールドを指定する 2 つ以上の式のリスト。 それぞれの式のデータ・タイプは、CREATE TYPE (行) ステートメントで説明されているように、行フィールドの有効なデータ・タイプである必要があります (SQLSTATE 429C5)。

結果のデータ・タイプは連想配列です。 同じステートメントにターゲットのユーザー定義の配列データ・タイプがある、または ARRAY_AGG の結果がユーザー定義の配列データ・タイプに明示的にキャストされるコンテキストで ARRAY_AGG が使用される場合、索引のデータ・タイプは、ターゲットの連想配列データ・タイプに一致します。 エレメント値が単一の expression または row-expression を使用して指定されている場合、配列エレメントのデータ・タイプは expression または row-expression のタイプと同じです。 エレメント値が式のリストで指定されている場合、配列エレメントは、式に対応するフィールド・タイプを持つ行タイプでです。

注

  • ARRAY_AGG 関数は、以下の特定のコンテキストの SQL プロシージャー、コンパイル済み SQL 関数、またはコンパウンド SQL (コンパイル済み) ステートメント内でのみ指定できます (SQLSTATE 42887)。
    • SELECT INTO ステートメントの選択リスト
    • スクロール可能でないカーソルの定義の全選択の選択リスト
    • SET ステートメントの右側にあるスカラー副照会の選択リスト
  • ARRAY_AGG は OLAP 関数の一部としては使用できません (SQLSTATE 42887)。
  • ARRAY_AGG を使用する SELECT ステートメントには ORDER BY 節または DISTINCT 節を含めることができず、SELECT 節または HAVING 節は、副照会を含めること、または副照会を返すインライン化された SQL 関数を呼び出すことはできません (SQLSTATE 42887)。

例

  • 例 1: 以下のような DDL があるとします。
     CREATE TYPE PHONELIST AS DECIMAL(10, 0)ARRAY[10]

 CREATE TABLE EMPLOYEE (
   ID          INTEGER NOT NULL,
   PRIORITY    INTEGER NOT NULL,
   PHONENUMBER DECIMAL(10, 0),
   PRIMARY KEY(ID, PRIORITY))
    従業員に連絡を取れる連絡先番号の優先順位付けされたリストを戻す SELECT INTO ステートメントを使用するプロシージャーを作成します。
     CREATE PROCEDURE GETPHONENUMBERS
   (IN  EMPID   INTEGER,
    OUT NUMBERS PHONELIST)
 BEGIN
   SELECT ARRAY_AGG(PHONENUMBER ORDER BY PRIORITY)
     INTO NUMBERS
   FROM EMPLOYEE
   WHERE ID = EMPID;
 END
    SET ステートメントを使用して従業員の連絡先の電場番号を任意の順序で並べたリストを返すプロシージャーを作成します。
    CREATE PROCEDURE GETPHONENUMBERS
   (IN  EMPID   INTEGER,
    OUT NUMBERS PHONELIST)
 BEGIN
   SET NUMBERS =
     (SELECT ARRAY_AGG(PHONENUMBER)
     FROM EMPLOYEE
     WHERE ID = EMPID);
 END
  • 例 2: SELECT INTO ステートメントを使用して、優先順位 1 の電話番号を EMPLOYEE 表の ID によって索引付けされた連想配列に集約するプロシージャーを作成します。
    CREATE TYPE EMPPHONES AS DECIMAL(10,0) ARRAY[INTEGER]

CREATE PROCEDURE GETPHONES
(OUT EMPLOYEES EMPPHONES)
 BEGIN
  SELECT ARRAY_AGG(ID, PHONENUMBER)
  INTO EMPLOYEES
  FROM EMPLOYEE WHERE PRIORITY=1;
 END
  • 例 3: SELECT INTO ステートメントを使用して、EMPLOYEE 表を行変数の配列に集約するプロシージャーを作成します。
    CREATE TYPE EMPROW AS ROW ANCHOR ROW EMPLOYEE

CREATE TYPE EMPARRAY AS EMPROW ARRAY[]

CREATE PROCEDURE GETEMPLOYEES
   (OUT EMPLOYEES EMPARRAY)
BEGIN
  SELECT ARRAY_AGG((ID, PRIORITY, PHONENUMBER) ORDER BY ID)
  INTO EMPLOYEES
  FROM EMPLOYEE;
END