文章详情页_MaxCompute 复杂类型函数之 ARRAY 函数

在 MaxCompute SQL 中可以使用复杂类型函数处理复杂数据类型，例如 ARRAY、MAP、STRUCT、JSON。

Lambda 函数

限制

不支持子查询，例如 x -> 2+(SELECT3)
不支持聚合，例如 x -> max(y)

语法

参数列表 -> 表达式或代码块

参数列表：是指 Lambda 函数接收的参数，可以为空或包含一个或多个参数
->：分隔参数列表和表达式或代码块

使用示例

示例1：获取数组列的平方值

select 
    numbers
    ,transform(numbers, n -> n * n) as squared_numbers
from (
    values (array(1, 2)), (array(3, 4)), (array(5, 6, 7))
) as t(numbers)
order by numbers;

+------------+-----------------+
| numbers    | squared_numbers |
+------------+-----------------+
| [1,2]      | [1,4]           |
| [3,4]      | [9,16]          |
| [5,6,7]    | [25,36,49]      |
+------------+-----------------+

示例2：将数组转换为字符串

select 
    transform(prices, n -> cast(n as string ) || '$') as price_tags
from (
    values
        (array(100, 200)), (array(30, 4))
) as t(prices);

+-----------------+
| price_tags      |
+-----------------+
| ["30$","4$"]    |
| ["100$","200$"] |
+-----------------+

函数目录

MaxCompute SQL 支持的复杂类型 ARRAY 函数如下。

序号	函数名称	函数功能
1	ALL_MATCH	判断 ARRAY 数组中是否所有元素都满足指定条件。
2	ANY_MATCH	判断 ARRAY 数组中是否存在满足指定条件的元素。
3	ARRAY	使用给定的值构造 ARRAY 数组。
4	ARRAY_CONTAINS	检测指定的 ARRAY 数组中是否包含指定的值。
5	ARRAY_DISTINCT	去除 ARRAY 数组中的重复元素。
6	ARRAY_EXCEPT	找出在 ARRAY 数组 A 中，但不在 ARRAY 数组 B 中的元素，并去掉重复的元素后，以 ARRAY 形式返回结果。
7	ARRAY_INTERSECT	计算两个 ARRAY 数组的交集。
8	ARRAY_JOIN	将 ARRAY 数组中的元素按照指定字符串进行拼接。
9	ARRAY_MAX	计算 ARRAY 数组中的最大值。
10	ARRAY_MIN	计算 ARRAY 数组中的最小值。
11	ARRAY_POSITION	计算指定元素在 ARRAY 数组中第一次出现的位置。
12	ARRAY_REMOVE	在 ARRAY 数组中删除指定元素。
13	ARRAY_REPEAT	返回将指定元素重复指定次数后的 ARRAY 数组。
14	ARRAY_UNION	计算两个ARRAY 数组的并集并去掉重复元素。
15	ARRAYS_OVERLAP	判断两个 ARRAY 数组中是否包含相同元素。
16	CONCAT	将 ARRAY 数组或字符串连接在一起。
17	EXPLODE	将一行数据转为多行的 UDTF。
18	FILTER	将 ARRAY 数组中的元素进行过滤。
19	FLATTEN	将数组类型的数组转换为单个数组。
20	INDEX	返回 ARRAY 数组指定位置的元素值。
21	POSEXPLODE	将指定的 ARRAY 展开，每个 Value 一行，每行两列分别对应数组从 0 开始的下标和数组元素。
22	REVERSE	返回指定数组的元素倒序数组。
23	SEQUENCE	根据表达式生成包含指定元素的数组。
24	SIZE	返回指定 ARRAY 数组中的元素数目。
25	SLICE	对 ARRAY 数组数据切片，返回从指定位置开始、指定长度的数组。
26	SORT_ARRAY	为指定的数组中的元素排序。
27	SPLIT	将字符串按照指定的分隔符分割后返回数组。
28	COLLECT_LIST	将 colname 指定的列值聚合为一个数组。
29	COLLECT_SET	将 colname 指定的列值聚合为一个无重复元素的数组。

ALL_MATCH

命令格式

boolean all_match(array<T> <a>, function<T, boolean> <predicate>)

命令说明

判断 ARRAY 数组 a 中是否所有元素都满足 predicate 条件。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型
predicate：必填。用于对 ARRAY 数组 a 中的元素进行判断的函数（内建函数或自定义函数）或表达式。输入参数的数据类型必须与 ARRAY 数组 a 中元素的数据类型一致

返回值说明

返回 BOOLEAN 类型。返回规则如下：

如果 ARRAY 数组 a 中所有的元素满足 predicate 条件或 ARRAY 数组为空，返回结果为 True
如果 ARRAY 数组 a 中存在元素不满足 predicate 条件，返回结果为 False
如果 ARRAY 数组 a 中存在元素为 NULL，且其他元素都满足 predicate 条件，返回结果为NULL

使用示例

示例1：判断 ARRAY 数组 array(4, 5, 6) 的所有元素是否满足 x -> x > 3 条件（所有元素大于3）。

-- 返回 true
select all_match(array(4, 5, 6), x -> x > 3);

示例2：ARRAY 数组为空

-- 返回 true
select all_match(array(), x -> x > 3);

示例3：判断 ARRAY 数组 array(1, 2, -10, 100, -30) 的所有元素是否满足 x -> x > 3 条件

-- 返回 false
select all_match(array(1, 2, -10, 100, -30), x -> x > 3);

示例4：判断存在 NULL 元素的 ARRAY 数组 array(10, 100, 30, null) 的所有元素是否满足 x -> x > 3 条件

-- 返回 NULL
select all_match(array(10, 100, 30, null), x -> x > 3);

ANY_MATCH

命令格式

boolean any_match(array<T> <a>, function<T, boolean> <predicate>)

命令说明

判断 ARRAY 数组 a 中是否存在元素满足 predicate 条件。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。
predicate：必填。用于对 ARRAY 数组 a 中的元素进行判断的函数（内建函数或自定义函数）或表达式。输入参数的数据类型必须与 ARRAY 数组 a 中元素的数据类型一致

返回值说明

返回 BOOLEAN 类型。返回规则如下：

如果 ARRAY 数组 a 中存在一个或多个元素满足 predicate 条件，返回结果为 True
如果 ARRAY 数组 a 中没有元素满足 predicate 条件或 ARRAY 数组为空，返回结果为 False
如果 ARRAY 数组 a 中存在元素为 NULL，且其他元素都不满足 predicate 条件，返回结果为NULL

使用示例

示例1：判断 ARRAY 数组 array(1, 2, -10, 100, -30) 中是否有元素满足 x -> x > 3 条件

-- 返回 true
select any_match(array(1, 2, -10, 100, -30), x -> x > 3);

示例2：ARRAY 数组为空

-- 返回 false
select any_match(array(), x -> x > 3);

示例3：判断 ARRAY 数组 array(1, 2, -10, -20, -30) 中是否有元素满足 x -> x > 3 条件

-- 返回 false
select any_match(array(1, 2, -10, -20, -30), x -> x > 3);

示例4：判断存在 NULL 元素的 ARRAY 数组 array(1, 2, null, -10) 中是否有元素满足 x-> x > 3 条件

-- 返回 NULL
select any_match(array(1, 2, null, -10), x -> x > 3);

ARRAY

命令格式

array array(<value>,<value>[, ...])

命令说明

使用指定的值构造 ARRAY 数组。

参数说明

value：必填。可以为任意类型。所有 value 的数据类型必须一致

返回值说明

返回 ARRAY 类型。

使用示例

例如表 t_table 的字段为 c1 bigint, c2 string, c3 string, c4 bigint, c5 bigint，包含数据如下：

+------------+-----+-----+----------+----------+
| c1         | c2  | c3  | c4       | c5       |
+------------+-----+-----+----------+----------+
| 1000       | k11 | k21 | 86       | 15       |
| 1001       | k12 | k22 | 97       | 2        |
| 1002       | k13 | k23 | 99       | 1        |
+------------+-----+-----+----------+----------+

命令示例如下：

-- 根据 c2、c4、c3、c5 列的数据构造 ARRAY 数组
select array(c2, c4, c3, c5) from t_table;

-- 返回结果如下
+--------------------+
| _c0                |
+--------------------+
| [k11, 86, k21, 15] |
| [k12, 97, k22, 2]  |
| [k13, 99, k23, 1]  |
+--------------------+

ARRAY_CONTAINS

命令格式

boolean array_contains(array<T> <a>, value <v>)

命令说明

判断 ARRAY 数组 a 中是否存在元素 v。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型
v：必填。待判断的元素。必须与 ARRAY 数组 a 中元素的数据类型一致

返回值说明

返回 BOOLEAN 类型

使用示例

例如表 t_table_array 的字段为 c1 bigint, t_array array<string>，包含数据如下：

+------------+--------------------+
| c1         | t_array            |
+------------+--------------------+
| 1000       | [k11, 86, k21, 15] |
| 1001       | [k12, 97, k22, 2]  |
| 1002       | [k13, 99, k23, 1]  |
+------------+--------------------+

命令示例如下：

-- 检测 t_array 列是否包含元素 1
select c1, array_contains(t_array, '1') from t_table_array;

返回结果如下
+------------+-------+
| c1         | _c1   |
+------------+-------+
| 1000       | false |
| 1001       | false |
| 1002       | true  |
+------------+-------+

ARRAY_DISTINCT

命令格式

array<T> array_distinct(array<T> <a>)

命令说明

去除 ARRAY 数组 a 中的重复元素。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型

返回值说明

返回 ARRAY 类型。返回规则如下：

新 ARRAY 数组无重复元素且元素顺序与 a 中的元素顺序保持一致
ARRAY 数组 a 中存在元素为 NULL 时，NULL 值会参与运算
ARRAY 数组为空时，返回空数组

使用示例

示例1：去除 ARRAY 数组 array(10, 20, 30, 30, 20, 10) 中的重复元素

-- 返回 [10, 20, 30]
select array_distinct(array(10, 20, 30, 30, 20, 10));

示例2：去除 ARRAY 数组 array(10, 20, 20, null, null, 30, 20, null) 中的重复元素

--返回 [10, 20, null, 30]
select array_distinct(array(10, 20, 20, null, null, 30, 20, null));

示例3：ARRAY 数组为空

--返回 []
select array_distinct(array());

ARRAY_EXCEPT

命令格式

array<T> array_except(array<T> <a>, array<T> <b>)

命令说明

找出在 ARRAY 数组 a 中，但不在 ARRAY 数组 b 中的元素，并去掉重复的元素后，返回新的 ARRAY 数组。

参数说明

a、b：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。数组 a 和数组 b 的数据类型必须保持一致。

返回值说明

返回 ARRAY 类型。返回规则如下：

新 ARRAY 数组无重复元素且元素顺序与数组 a 中的元素顺序保持一致
ARRAY 数组中存在元素为 NULL 时，NULL 值会参与运算
任一输入数组为空时，返回对非空数组去重后的新 ARRAY 数组
输入数组全部为空时，返回空数组

使用示例

示例1：找出在 ARRAY 数组 array(1, 1, 3, 3, 5, 5) 中，不在 ARRAY 数组 array(1, 1, 2, 2, 3, 3) 中的元素并去重

-- 返回 [5]
select array_except(array(1, 1, 3, 3, 5, 5), array(1, 1, 2, 2, 3, 3));

示例2：找出在 ARRAY 数组 array(1, 1, 3, 3, 5, 5, null, null) 中，不在 ARRAY 数组 array(1, 1, 2, 2, 3, 3) 中的元素并去重

-- 返回 [5, null]
select array_except(array(1, 1, 3, 3, 5, 5, null, null), array(1, 1, 2, 2, 3, 3));

示例3：任一输入 ARRAY 数组为空

-- 返回 [2,1]
select array_except(array(2, 1, 1, 2), cast(array() as array<int>));

示例4：输入 ARRAY 数组全部为空

-- 返回[]
select array_except(cast(array() as array<int>), cast(array() as array<int>));

ARRAY_INTERSECT

命令格式

array<T> array_intersect(array<T> <a>, array<T> <b>)

命令说明

计算 ARRAY 数组 a 和 b 的交集，并去掉重复元素。

参数说明

a、b：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。数组 a 和数组 b 的数据类型必须保持一致。

返回值说明

返回 ARRAY 类型。返回规则如下：

ARRAY 数组中存在元素为 NULL 时，NULL 值会参与运算
新 ARRAY 数组无重复元素且元素顺序与数组 a 中的元素顺序保持一致
如果 ARRAY 数组 a 或 b 为 NULL，则返回 NULL

使用示例

示例1：计算 ARRAY 数组 array(1, 2, 3) 和 array(1, 3, 5) 的交集，并去掉重复元素

-- 返回 [1, 3]
select array_intersect(array(1, 2, 3), array(1, 3, 5));

示例2：计算 ARRAY 数组 array(10, 20, 20, 30, 30, null, null) 和 array(30, 30, 20, 20, 40, null, null) 的交集，并去掉重复元素

-- 返回 [20, 30, null]
select array_intersect(array(10, 20, 20, 30, 30, null, null), array(30, 30, 20, 20, 40, null, null));

ARRAY_JOIN

命令格式

array_join(array<T> <a>, <delimiter>[, <nullreplacement>])

命令说明

将 ARRAY 数组 a 中的元素使用 delimiter 拼接为字符串。当数组中元素为 NULL 时，用 nullreplacement 替代，没有设置 nullreplacement 时，会忽略 NULL 元素。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型
delimiter：必填。STRING 类型。连接 ARRAY 数组 a 中元素的字符串
nullreplacement：可选。替代 NULL 元素的字符串

注：当 ARRAY 数组中的元素非 STRING 类型时，MaxCompute 会将非 STRING 类型元素转换为 STRING 类型。

返回值说明

返回 STRING 类型。

使用示例

-- 返回 10,20,20,30
select array_join(array(10, 20, 20, null, null, 30), ",");

-- 返回 10##20##20##null##null##30
select array_join(array(10, 20, 20, null, null, 30), "##", "null");

ARRAY_MAX

命令格式

T array_max(array<T> <a>)

命令说明

计算 ARRAY 数组 a 中的最大元素。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型

返回值说明

返回 ARRAY 数组 a 中的最大元素。返回规则如下：

如果 ARRAY 数组 a 为 NULL，返回 NULL
如果 ARRAY 数组 a 中存在元素为 NULL，NULL 值不参与运算

使用示例

-- 返回 20
select array_max(array(1, 20, null, 3));

ARRAY_MIN

命令格式

T array_min(array<T> <a>)

命令说明

计算 ARRAY 数组 a 中的最小元素。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型

返回值说明

返回ARRAY数组a中的最小元素。返回规则如下：

如果 ARRAY 数组 a 为 NULL，返回 NULL
如果 ARRAY 数组 a 中存在元素为 NULL 时，NULL 值不参与运算

使用示例

-- 返回 1
select array_min(array(1, 20, null, 3));

ARRAY_POSITION

命令格式

bigint array_position(array<T> <a>, T <element>)

命令说明

计算元素 element 在 ARRAY 数组 a 中第一次出现的位置。ARRAY 数组元素位置编号自左往右，从 1 开始计数。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型
element：必填。待查询的元素，数据类型必须与a中元素的数据类型相同

返回值说明

返回 BIGINT 类型。返回规则如下：

如果 ARRAY 数组 a 或 element 为 NULL，返回 NULL
未找到元素时返回 0

使用示例

示例1：计算元素 1 第一次出现在 ARRAY 数组 array(3, 2, 1) 中的位置

-- 返回 3
select array_position(array(3, 2, 1), 1);

示例2：element 为 NULL

-- 返回 NULL
select array_position(array(3, 1, null), null);

ARRAY_REMOVE

命令格式

array<T> array_remove(array<T> <a>, T <element>)

命令说明

在 ARRAY 数组 a 中删除与 element 相等的元素。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型
element：必填。待删除的元素，数据类型必须与数组 a 中元素的数据类型相同

返回值说明

返回 ARRAY 类型。返回规则如下：

如果 ARRAY 数组 a 中存在元素为 NULL 时，NULL 值不参与运算
如果 ARRAY 数组 a 或 element 为 NULL，返回 NULL
ARRAY 数组 a 中不存在 element 时返回原 ARRAY 数组 a

使用示例

示例1：删除 ARRAY 数组 array(3, 2, 1) 中等于 1 的元素

-- 返回 [3, 2]
select array_remove(array(3, 2, 1), 1);

示例2：element 为 NULL

-- 返回 NULL
select array_remove(array(3, 1, null), null);

示例3：删除 ARRAY 数组 array(3, 1, null) 中等于 2 的元素

-- 返回 [3, 1, null]
select array_remove(array(3, 1, null), 2);

ARRAY_REPEAT

命令格式

array<T> array_repeat(T <element>, int <count>)

命令说明

返回将元素 t 重复 count 次后新生成的 ARRAY 数组。

参数说明

t：必填。待重复的元素
count：必填。重复的次数，INT 类型

返回值说明

返回 ARRAY 类型。返回规则如下：

如果 count 为 NULL，返回 NULL
如果 count 小于 0，返回空数组

使用示例

示例1：将 123 重复 2 次，生成新的 ARRAY 数组

-- 返回 [123, 123]
select array_repeat('123', 2);

示例2：count 为 NULL

-- 返回 NULL
select array_repeat('123', null);

示例3：count 小于 0

-- 返回 []
select array_repeat('123', -1);

ARRAY_UNION

命令格式

array<T> array_union(array<T> <a>,  array<T> <b>)

命令说明

计算 ARRAY 数组 a 和 b 的并集，并去掉重复元素。

参数说明

a、b：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。数组 a 和数组 b 中元素的数据类型必须一致

返回值说明

返回 ARRAY 类型。如果数组 a 或数组 b 为 NULL，返回 NULL。

使用示例

示例1：计算 ARRAY 数组 array(1, 2, 3) 和 array(1, 3, 5) 的并集，并去掉重复元素

-- 返回 [1, 2, 3, 5]
select array_union(array(1, 2, 3), array(1, 3, 5));

示例2：任一 ARRAY 数组为 NULL

-- 返回 NULL
select array_union(array(1, 2, 3), null);

ARRAYS_OVERLAP

命令格式

boolean arrays_overlap(array<T> <a>,  array<T> <b>)

命令说明

判断 ARRAY 数组 a 和 b 是否存在相同元素。

参数说明

a、b：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。数组 a 和 b 中元素的数据类型必须一致

返回值说明

返回 BOOLEAN 类型。返回规则如下：

如果 ARRAY 数组 a 中至少包含 ARRAY 数组 b 中的一个非 NULL 元素，返回结果为 True
如果 ARRAY 数组 a 和 b 中没有公共元素、都非空，且其中任意一个数组中包含 NULL 元素，返回结果为 NULL
如果 ARRAY 数组 a 和 b 中没有公共元素、都非空，且其中任意一个数组中都不包含 NULL 元素，返回结果为 False

使用示例

示例1：判断 ARRAY 数组 array(1, 2, 3) 和 array(3, 4, 5) 中是否存在相同元素

-- 返回 true
select arrays_overlap(array(1, 2, 3), array(3, 4, 5));

示例2：判断 ARRAY 数组 array(1, 2, 3) 和 array(6, 4, 5) 中是否存在相同元素

-- 返回 false
select arrays_overlap(array(1, 2, 3), array(6, 4, 5));

示例3：任一 ARRAY 数组中存在 NULL 元素

-- 返回 NULL
select arrays_overlap(array(1, 2, 3), array(5, 4, null));

CONCAT

命令格式

array<T> concat(array<T> <a>, array<T> <b>[,...])

命令说明

将多个 ARRAY 数组中的所有元素连接在一起，生成一个新的 ARRAY 数组。

参数说明

a、b：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。数组 a 和数组 b 中元素的数据类型必须一致。数组中的元素为 NULL 值时会参与运算

返回值说明

返回 ARRAY 类型。如果任一输入 ARRAY 数组为 NULL，返回结果为 NULL。

使用示例

示例1：连接 ARRAY 数组 array(10, 20) 和 array(20, -20)

-- 返回 [10, 20, 20, -20]
select concat(array(10, 20), array(20, -20));

示例2：ARRAY 数组元素包含 NULL

-- 返回 [10, NULL, 20, -20]
select concat(array(10, null), array(20, -20));

示例3：任一 ARRAY 数组为 NULL

-- 返回 NULL
select concat(array(10, 20), null);

EXPLODE

使用限制

在一个 select 中只能出现一个 explode 函数，不可以出现表的其他列
不可以与 group by、cluster by、distribute by、sort by 一起使用

命令格式

explode (<var>)

命令说明

将一行数据转为多行的 UDTF。

如果参数是 array<T> 类型，则将列中存储的 ARRAY 转为多行
如果参数是 map<K, V> 类型，则将列中存储的 MAP 的每个 Key-Value 对转换为包含两列的行，其中一列存储 Key，另一列存储 Value

参数说明

var：必填。array<T> 类型或 map<K, V> 类型

返回值说明

返回转换后的行。

使用示例

例如表 t_table_map 的字段为 c1 bigint, t_map map<string,bigint>，包含数据如下：

+------------+------------------+
| c1         | t_map            |
+------------+------------------+
| 1000       | {k11:86, k21:15} |
| 1001       | {k12:97, k22:2}  |
| 1002       | {k13:99, k23:1}  |
+------------+------------------+

命令示例如下：

select explode(t_map) from t_table_map;

-- 返回结果如下
+-----+------------+
| key | value      |
+-----+------------+
| k11 | 86         |
| k21 | 15         |
| k12 | 97         |
| k22 | 2          |
| k13 | 99         |
| k23 | 1          |
+-----+------------+

select explode(array(1, 2, -10, 100, -30, 2));

-- 返回结果如下
+------------+
| col        |
+------------+
| 1          |
| 2          |
| -10        |
| 2          |
| 100        |
| 30         |
| 2          |
+------------+

FILTER

命令格式

array<T> filter(array<T> <a>, function<T,boolean> <func>)

命令说明

将 ARRAY 数组 a 中的元素利用 func 进行过滤，返回一个新的 ARRAY 数组。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型
func：必填。用于对数组 a 中元素进行过滤的函数（内置函数或自定义函数）或表达式，其输入参数类型必须与数组 a 中元素的数据类型一致，其输出结果数据类型为 BOOLEAN

返回值说明

返回 ARRAY 类型。

使用示例

-- 返回 [2, 3]
select filter(array(1, 2, 3), x -> x > 1);

FLATTEN

命令格式

flatten(arrayOfArray)

命令说明

将数组类型的数组转换为单个数组。

参数说明

arrayOfArray：为数组类型的数组

返回值说明

将数组类型的数组按元素顺序展开为单个数组。

如果输入值为 NULL，则返回 NULL
如果输入参数不是数组类型的数组，则抛出异常

使用示例

-- 返回 [1, 2, 3, 4]
SELECT flatten(array(array(1, 2), array(3, 4)));

INDEX

命令格式

index(<var1>[<var2>])

命令说明

如果 var1 是 array<T> 类型，获取 var1 的第 var2 个元素。ARRAY 数组元素编号自左往右，从 0 开始计数。
如果 var1 是 map<K, V> 类型，获取 var1 中 Key 为 var2 的 Value。

说明：使用该函数时需要去掉 index，请直接执行 <var1>[<var2>]，否则会返回报错。

参数说明

var1：必填。array<T> 类型或 map<K, V> 类型。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型。map<K, V> 中的 K、V 指代 MAP 对象的 Key、Value
var2：必填。如果 var1 是 array<T> 类型，则 var2 为 BIGINT 类型且大于等于 0。如果 var1 是 map<K, V> 类型，则 var2 与 K 的类型保持一致。

返回值说明

如果 var1 是 array<T> 类型，函数返回 T 类型。返回规则如下：

如果 var2 超出 var1 的元素数目范围，返回结果为 NULL
如果 var1 为 NULL，返回结果为 NULL

如果 var1 是 map<K, V> 类型，函数返回 V 类型。返回规则如下：

如果 map<K, V> 中不存在 Key 为 var2 的情况，返回结果为 NULL
如果 var1 为 NULL，返回结果为 NULL

使用示例

示例1：var1 为 array<T> 类型

-- 返回 c
select array('a','b','c')[2];

示例2：var1 为 map<K, V> 类型

-- 返回 1
select str_to_map("test1=1, test2=2")["test1"];

POSEXPLODE

命令格式

posexplode(array<T> <a>)

命令说明

将 ARRAY 数组 a 展开，每个 Value 一行，每行两列分别对应数组从 0 开始的下标和数组元素。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型

返回值说明

返回表。

使用示例

select posexplode(array('a','c','f','b'));

-- 返回结果如下
+------------+------------+
| pos        | val        |
+------------+------------+
| 0          | a          |
| 1          | c          |
| 2          | f          |
| 3          | b          |
+------------+------------+

REVERSE

命令格式

array reverse(array <value>)

命令说明

根据输入数组生成一个元素倒序的数组。

参数说明

value：输入数组

返回值说明

返回输入数组元素倒序的数组。如果输入值为 NULL，则返回 NULL

使用示例

-- 返回 [3, 4, 1, 2]
SELECT reverse(array(2, 1, 4, 3));

SEQUENCE

命令格式

sequence(start, stop, [step]) -> array

命令说明

根据表达式生成包含指定元素的数组。

参数说明

start：表示元素序列开始的表达式，元素序列包含 start
stop：表示元素序列结束的表达式，元素序列包含 stop
step：可选参数。元素序列步长值；默认情况下, 当 start 小于等于 stop 时, step 为 1，否则为 -1；如果元素序列为时间类型时，默认分别为 1 天或 -1 天

返回值说明

返回由指定表达式生成元素组成的数组。

如果start大于stop而step为正数时抛出异常，反之亦然。

sequence函数默认生成的元素数量上限为10000，可以通过设置odps.sql.max.sequence.lengthFlag值改变元素数量上限。

使用示例

-- 返回 [1, 2, 3, 4, 5]
select sequence(1, 5);

-- 返回 [5, 4, 3, 2, 1] 
select sequence(5, 1);

-- 返回 [2018-01-01,  2018-02-01,  2018-03-01]    
select sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);

SIZE

命令格式

int size(array<T> <a>)
int size(map<K, V> <b>)

命令说明

输入为 ARRAY 数组：计算 ARRAY 数组 a 中的元素数目
输入为 MAP 对象：计算 MAP 对象 b 中的 Key-Value 对数

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型
b：必填。MAP 对象。map<K, V> 中的 K、V 指代 MAP 对象的 Key、Value

返回值说明

返回 INT 类型。

使用示例

示例1：计算 ARRAY 数组 array('a', 'b') 中的元素数目

-- 返回 2
select size(array('a', 'b'));

示例2：计算 MAP 对象 map('a', 123, 'b', 456) 中的 Key-Value 对数

-- 返回 2
select size(map('a', 123, 'b', 456));

SLICE

命令格式

array<T> slice(array<T> <a>, <start>, <length>)

命令说明

对 ARRAY 数组切片，截取从 start 位置开始长度为 length 的元素组成新的 ARRAY 数组。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型
start：必填。切片起点，从 1 开始，表示从数组的首个元素开始向右切片。start 可以为负数，表示从数组的末尾元素开始向右切片
length：必填。切片长度，必须大于或等于 0。切片长度如果大于 ARRAY 数组长度时，会返回从 start 位置开始到末尾元素组成的 ARRAY 数组

返回值说明

返回 ARRAY 类型。

使用示例

示例1：截取 ARRAY 数组 array(10, 20, 20, null, null, 30) 从第 1 个位置开始，切片长度为 3 的元素

-- 返回 [10, 20, 20]
select slice(array(10, 20, 20, null, null, 30), 1, 3);

示例2：截取 ARRAY 数组 array(10, 20, 20, null, null, 30) 从第 -2 个位置开始，切片长度为 2 的元素

-- 返回 [NULL, 30]
select slice(array(10, 20, 20, null, null, 30), -2, 2);

示例3：截取 ARRAY 数组 array(10, 20, 20, null, null, 30) 从第 3 个位置开始，切片长度为 10 的元素

-- 返回 [20, NULL, NULL, 30]
select slice(array(10, 20, 20, null, null, 30), 3, 10);

示例4：截取 ARRAY 数组 array(10, 20, 20, null, null, 30) 从第 3 个位置开始，切片长度为 0 的元素

-- 返回 []
select slice(array(10, 20, 20, null, null, 30), 3, 0);

SORT_ARRAY

命令格式

array<T> sort_array(array<T> <a>[, <isasc>])

命令说明

对 ARRAY 数组中的元素进行排序。

参数说明

a：必填。ARRAY 数组。array<T> 中的 T 指代 ARRAY 数组元素的数据类型，数组中的元素可以为任意类型
isasc：可选。用于设置排序规则。取值为 True（升序）或 False（降序）。默认为升序

返回值说明

返回 ARRAY 类型。NULL 值为最小值。

使用示例

示例1：例如表 t_array 的字段为 c1 array<string>, c2 array<int>, c3 array<string>，包含数据如下：

+---------------+---------------------+---------------+
| c1            | c2                  | c3            |
+---------------+---------------------+---------------+
| [a, c, f, b]  | [4, 5, 7, 2, 5, 8]  | [你, 我, 他]  |
+---------------+---------------------+---------------+

对表的每列数据进行排序：

-- 返回 [a, b, c, f] [2, 4, 5, 5, 7, 8] [他, 你, 我]
select sort_array(c1), sort_array(c2), sort_array(c3) from t_array;

示例2：对 ARRAY 数组 array(10, 20, 40, 30, 30, null, 50) 进行降序排序

-- 返回 [50, 40, 30, 30, 20, 10, NULL]
select sort_array(array(10, 20, 40, 30, 30, null, 50), false);

SPLIT

命令格式

split(<str>, <pat>)

命令说明

通过 pat 将 str 分割后返回数组。

参数说明

str：必填。STRING 类型。指被分割的字符串
pat：必填。STRING 类型的分隔符。支持正则表达式

返回值说明

返回 ARRAY 数组。数组中的元素为 STRING 类型。

使用示例

-- 返回 [a, b, c]
select split("a, b, c", ",");

COLLECT_LIST

命令格式

array collect_list(<colname>)

命令说明

将 colname 指定的列值聚合为一个数组。

参数说明

colname：必填。表的列名称，可为任意类型。

返回值说明

返回 ARRAY 类型。colname 值为 NULL 时，该行不参与计算。

使用示例

创建表 emp

-- drop table emp;
create table if not exists emp (
    empno       bigint
    ,ename      string
    ,job        string
    ,mgr        bigint
    ,hiredate   date
    ,sal        bigint
    ,comm       bigint
    ,deptno     bigint
);

insert into emp values 
    (7369, 'SMITH'  , 'CLERK'       ,   7902, date'1980-12-17', 800 ,   null,   20),
    (7499, 'ALLEN'  , 'SALESMAN'    ,   7698, date'1981-02-20', 1600,   300 ,   30),
    (7521, 'WARD'   , 'SALESMAN'    ,   7698, date'1981-02-22', 1250,   500 ,   30),
    (7566, 'JONES'  , 'MANAGER'     ,   7839, date'1981-04-02', 2975,   null,   20),
    (7654, 'MARTIN' , 'SALESMAN'    ,   7698, date'1981-09-28', 1250,   1400,   30),
    (7698, 'BLAKE'  , 'MANAGER'     ,   7839, date'1981-05-01', 2850,   null,   30),
    (7782, 'CLARK'  , 'MANAGER'     ,   7839, date'1981-06-09', 2450,   null,   10),
    (7788, 'SCOTT'  , 'ANALYST'     ,   7566, date'1987-04-19', 3000,   null,   20),
    (7839, 'KING'   , 'PRESIDENT'   ,   null, date'1981-11-17', 5000,   null,   10),
    (7844, 'TURNER' , 'SALESMAN'    ,   7698, date'1981-09-08', 1500,   0   ,   30),
    (7876, 'ADAMS'  , 'CLERK'       ,   7788, date'1987-05-23', 1100,   null,   20),
    (7900, 'JAMES'  , 'CLERK'       ,   7698, date'1981-12-03', 950 ,   null,   30),
    (7902, 'FORD'   , 'ANALYST'     ,   7566, date'1981-12-03', 3000,   null,   20),
    (7934, 'MILLER' , 'CLERK'       ,   7782, date'1982-01-23', 1300,   null,   10),
    (7948, 'JACCKA' , 'CLERK'       ,   7782, date'1981-04-12', 5000,   null,   10),
    (7956, 'WELAN'  , 'CLERK'       ,   7649, date'1982-07-20', 2450,   null,   10),
    (7956, 'TEBAGE' , 'CLERK'       ,   7748, date'1982-12-30', 1300,   null,   10);

示例1：将所有职工薪资（sal）聚合为一个数组

select collect_list(sal) from emp;

+------------------------------------------------------------------------------------------------------+
| _c0                                                                                                  |
+------------------------------------------------------------------------------------------------------+
| [800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300, 5000, 2450, 1300] |
+------------------------------------------------------------------------------------------------------+

示例2：与 group by 配合使用，对所有职工按照部门（deptno）进行分组，并将同组的职工薪资（sal）聚合为一个数组

select deptno, collect_list(sal) from emp group by deptno;

+------------+--------------------------------------+
| deptno     | _c1                                  |
+------------+--------------------------------------+
| 10         | [2450, 5000, 1300, 5000, 2450, 1300] |
| 20         | [800, 2975, 3000, 1100, 3000]        |
| 30         | [1600, 1250, 1250, 2850, 1500, 950]  |
+------------+--------------------------------------+

示例3：与 group by 配合使用，对所有职工按照部门（deptno）进行分组，并将同组的职工薪资（sal）去重后聚合为一个数组

select deptno, collect_list(distinct sal) from emp group by deptno;

+------------+-------------------------------+
| deptno     | _c1                           |
+------------+-------------------------------+
| 10         | [1300, 2450, 5000]            |
| 20         | [800, 1100, 2975, 3000]       |
| 30         | [950, 1250, 1500, 1600, 2850] |
+------------+-------------------------------+

COLLECT_SET

命令格式

array collect_set(<colname>)

命令说明

将 colname 指定的列值聚合为一个无重复元素的数组。

参数说明

将 colname 指定的列值聚合为一个无重复元素的数组

返回值说明

返回 ARRAY 类型。colname 值为 NULL 时，该行不参与计算。

使用示例

示例1：将所有职工薪资（sal）聚合为一个无重复值的数组

select collect_set(sal) from emp;

+------------------------------------------------------------------------+
| _c0                                                                    |
+------------------------------------------------------------------------+
| [800, 950, 1100, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 5000] |
+------------------------------------------------------------------------+

示例2：与 group by 配合使用，对所有职工按照部门（deptno）进行分组，并将同组的职工薪资（sal）聚合为一个无重复值的数组

select deptno, collect_set(sal) from emp group by deptno;

+------------+-------------------------------+
| deptno     | _c1                           |
+------------+-------------------------------+
| 10         | [1300, 2450, 5000]            |
| 20         | [800, 1100, 2975, 3000]       |
| 30         | [950, 1250, 1500, 1600, 2850] |
+------------+-------------------------------+

参考资料

MaxCompute SQL 复杂类型函数

Lambda 函数

原创文章，转载请注明出处：http://www.opcoder.cn/article/73/