类别：

聚合函数 (Counting Distinct Values) , Window functions (Semi-structured Data Aggregation)

ARRAY_UNION_AGG

Returns an ARRAY that contains the union of the distinct values from the input arrays in a column. You can use this to aggregate distinct values in arrays produced by ARRAY_UNIQUE_AGG.

另请参阅：

ARRAY_UNIQUE_AGG、使用数组计算分层聚合的非重复值

语法

Aggregate function

ARRAY_UNION_AGG( <column> )
Copy

Window function

ARRAY_UNION_AGG( <column> ) OVER ( [ PARTITION BY <expr> ] )
Copy

For details about the OVER clause, see 窗口函数的语法和用法.

实参

column

The column containing the arrays with the distinct values (the arrays produced by ARRAY_UNIQUE_AGG).

返回

The function returns an array containing the distinct values from the arrays in column. The values in the array are in no particular order, and the order is not deterministic.

Note that this function uses multiset semantics (link removed), which means that the maximum number of occurrences of an individual value in a single input array determines the number of occurrences of that value in the output array. See Examples.

The function ignores NULL values in column and in the arrays in column. If column contains only NULL values or the table containing column is empty, the function returns an empty array.

使用说明

  • 该函数可用作以下任一类型的函数：

  • 当此函数作为窗口函数调用时，它不支持显式窗口框架。

  • 当您将 结构化数组 传递给该函数时，该函数会返回相同类型的结构化数组。

示例

聚合：数组的并集

The following example illustrates how the function returns the union of distinct values from two arrays:

CREATE TABLE union_test(a array);

INSERT INTO union_test
    SELECT PARSE_JSON('[ 1, 1, 2]')
    UNION ALL
    SELECT PARSE_JSON('[ 1, 2, 3]');

SELECT ARRAY_UNION_AGG(a) FROM union_test;
+-------------------------+
| ARRAY_UNION_AGG(A)      |
+-------------------------+
| [ 1, 1, 2, 3]           |
+-------------------------+
Copy

该操作使用 多集 (link removed) 语义。值 1 在输出中出现了两次，因为它在其中一个输入数组中出现了两次。

请参阅 使用数组计算分层聚合的非重复值。

语言: 中文