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

ARRAY_AGG¶

返回输入值，并将其转换为数组。如果输入为空，该函数返回一个空数组。

别名：: ARRAYAGG

语法¶

聚合函数

ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]

Copy

窗口函数

ARRAY_AGG( [ DISTINCT ] <expr1> )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Copy

实参¶

必填：

expr1: 确定要输入至数组的值的表达式（通常是列名）。
OVER(): OVER 子句指定将该函数用作窗口函数。有关详细信息，请参阅窗口函数。

可选：

DISTINCT

从数组中删除重复的值。

WITHIN GROUP orderby_clause

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

WITHIN GROUP (ORDER BY) 语法支持与 SELECT 语句中的主 ORDER BY 子句相同的参数。请参阅 ORDER BY。

PARTITION BY expr2

指定表达式（通常是列名）的窗口函数分子句。此表达式定义在应用函数之前对输入行进行分组的分区。有关详细信息，请参阅窗口函数。

返回¶

返回类型 ARRAY 的值。

ARRAY_AGG 单次调用可返回的最大数据量为 16MB。

使用说明¶

如果您没有指定 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) ...;

Copy

如果为 DISTINCT 和 WITHIN GROUP 指定不同的列，则会发生错误：

SELECT ARRAY_AGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;

Copy

SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression

必须为 DISTINCT 和 WITHIN GROUP 指定同一列，或者省略 DISTINCT。

输出中省略 NULL 值。
用作窗口函数时，此函数不支持：
- OVER() 子句中的 ORDER BY 分子句。
- 窗口框架。

示例¶

以下示例查询使用如下所示的表和数据：

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

Copy

以下示例显示了来源于不使用 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 |
+------------+

Copy

以下示例展示如何使用 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                                                     |
| ]                                                            |
+--------------------------------------------------------------+

Copy

以下示例展示了 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"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+

Copy

以下此示例使用两个单独的 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"                                         |
|               | ]                                                           |
+---------------+-------------------------------------------------------------+

Copy