- 类别：
聚合函数 （半结构化数据）、窗口函数 （常规）、半结构化和结构化数据函数 （数组/对象）
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包含一个或多个表达式（通常是列名）的子句，用于确定每个数组中值的顺序。
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 单次调用可返回的最大数据量为 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) ...;
如果为 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, -- 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 | | ] | +--------------------------------------------------------------+
以下示例展示了 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" |
| | ] |
...