类别：: 聚合函数（半结构化数据）、窗口函数（常规）、半结构化和结构化数据函数（数组/对象）

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

包含一个或多个表达式（通常是列名）的子句，用于确定每个数组中值的顺序。

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 在单次调用中可返回的最大数据量为 128 MB。

使用说明¶

如果您没有指定 WITHIN GROUP(ORDER BY)，每个数组内元素的顺序是不可预测的。（WITHIN GROUP 子句外部的 ORDER BY 子句可应用于输出行的顺序，而不是行内数组元素的顺序。）
如果您为 WITHIN GROUP(ORDER BY) 中的表达式指定一个数字，这个数字被解析为一个数字常量，而不是 SELECT 列表中的列顺序位置。因此，不要将数字指定为 WITHIN GROUP(ORDER BY) 表达式。
如果您指定 DISTINCT 和 WITHIN GROUP，则两者必须引用同一列。例如：
```
SELECT ARRAY_AGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
```
如果为 DISTINCT 和 WITHIN GROUP 指定不同的列，则会发生错误：
```
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 和 WITHIN GROUP 指定同一列，或者省略 DISTINCT。
仅当 OVER 子句中没有 ORDER BY 子句时，窗口函数调用才支持 DISTINCT 和 WITHIN GROUP。在 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                                                     |
| ]                                                            |
+--------------------------------------------------------------+

以下示例展示了 DISTINCT 关键字与 ARRAY_AGG() 的配合使用。

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"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+

以下示例使用两个单独的 ORDER BY 子句，其中一个子句控制每行内输出数组内的顺序，另一个子句控制输出行的顺序：

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"  |
|            | ]       |
...