ARRAY_AGG 聚集函数

ARRAY_AGG 函数将一组元素聚集到数组中。

ARRAY_AGG 聚集函数的调用基于结果数组类型。

普通阵列聚集

element-expression

关联数组聚集

element-expression

该模式是 SYSIBM。

Ordinary array aggregation

元素表达式

指定数组元素的源。

expression: 用于指定数组的元素值的表达式。表达式的数据类型必须是可以在 CREATE TYPE (数组) 语句中指定的数据类型 (SQLSTATE 429C2)。
row-expression: 行表达式，用于指定将行数据类型作为数组元素的值。
( 表达式，表达式 ... ): 两个或更多表达式的列表，这些表达式指定具有行数据类型作为数组元素的值的字段。每个表达式的数据类型必须是行字段的有效数据类型，如 CREATE TYPE (row) 语句中所述 (SQLSTATE 429C5)。

ORDER BY

指定在普通数组聚集中处理的同一分组集中的行的顺序。如果 ORDER BY 子句不能区分列数据的顺序，那么同一分组集中的行将采用任意排序顺序。

如果指定了 ORDER BY ，那么它将确定普通数组中聚集元素的顺序。如果未指定 ORDER BY ，并且在指定了排序的同一 SELECT 子句中未包含其他 ARRAY_AGG ， LISTAGG 或 XMLAGG ，那么普通数组中元素的排序不是确定性的。如果未指定 ORDER BY ，并且同一 SELECT 子句多次出现 ARRAY_AGG ， LISTAGG 或 XMLAGG ，而这些 ARRAY_AGG ， LISTAGG 或 XMLAGG 指定对普通数组中的元素进行相同排序的顺序用于 ARRAY_AGG 的每个结果。

sort-key: 排序键可以是列名或 sort-key-expression。如果排序键是常量，那么它不会引用输出列的位置 (如在查询的 ORDER BY 子句中) ，而只是一个不包含排序键的常量。
ASC: 按升序顺序处理 sort-key。这是缺省选项。
DESC: 按降序顺序处理 sort-key。

结果数据类型是普通数组。如果使用单个表达式或行表达式指定元素值，那么数组元素的数据类型与表达式或行表达式的类型相同。如果使用表达式列表指定元素值，那么数组元素是具有对应于表达式的字段类型的行类型。

如果 SELECT 子句包含 ARRAY_AGG 函数，那么在同一 SELECT 子句中对 ARRAY_AGG、LISTAGG、XMLAGG 和 XMLGROUP 函数的所有调用必须指定相同的顺序或不指定顺序 (SQLSTATE 428GZ)。

Associative array aggregation

index-expression

指定关联数组的索引。在同一语句中存在目标用户定义的数组数据类型或 ARRAY_AGG 的结果显式强制转换为用户定义的数组数据类型的上下文中使用时， index-expression 的数据类型必须可强制转换为目标关联数组数据类型的索引数据类型。否则， index-expression 的数据类型必须是可以在 CREATE TYPE (array) 语句中为关联数组下标指定的数据类型 (SQLSTATE 429C2)。

在为聚集关联数组而处理的分组集中，不能有任何重复的 index-expression 值 (SQLSTATE 22545)。

元素表达式

指定数组元素的源。

expression: 用于指定数组的元素值的表达式。表达式的数据类型必须是可以在 CREATE TYPE (数组) 语句中指定的数据类型 (SQLSTATE 429C2)。
row-expression: 行表达式，用于指定将行数据类型作为数组元素的值。
( 表达式，表达式 ... ): 两个或更多表达式的列表，这些表达式指定具有行数据类型作为数组元素的值的字段。每个表达式的数据类型必须是行字段的有效数据类型，如 CREATE TYPE (row) 语句中所述 (SQLSTATE 429C5)。

结果数据类型是关联数组。如果在上下文中使用了 ARRAY_AGG ，而同一语句中有目标用户定义的数组数据类型，或者 ARRAY_AGG 的结果显式强制转换为用户定义的数组数据类型，那么索引的数据类型与目标关联数组的数据类型相匹配。如果使用单个表达式或 row-expression指定元素值，那么数组元素的数据类型与表达式或 row-expression 的类型相同。如果使用表达式列表指定元素值，那么数组元素是具有对应于表达式的字段类型的行类型。

注意

ARRAY_AGG 函数只能在 SQL 过程，编译型 SQL 函数或复合 SQL (编译型) 语句中指定以下特定上下文 (SQLSTATE 42887):
- SELECT INTO 语句的 SELECT 列表
- 游标定义中不可滚动的全查询的选择列表
- 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 表中的标识建立索引的关联数组中。

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