ARRAY_MAP
更新时间：2025-10-17
描述
使用一个 lambda 表达式作为输入参数，对其他的输入 ARRAY 参数的内部数据做对应表达式计算。 在 lambda 表达式中输入的参数为 1 个或多个，必须和后面的输入 array 列数量一致。 在 lambda 中可以执行合法的标量函数，不支持聚合函数等。
语法
SQL
1ARRAY_MAP(lambda, <arr> [ , <arr> ... ] )参数
| 参数 | 说明 |
|---|---|
lambda |
lambda 表达式，表达式中输入的参数为 1 个或多个，必须和后面的输入 array 列数量一致。在 lambda 中可以执行合法的标量函数，不支持聚合函数等。 |
<arr> |
ARRAY 数组 |
返回值
经过表达式 lambda 计算之后 ARRAY 数组。
举例
SQL
1CREATE TABLE array_test2 (
2 id INT,
3 c_array1 ARRAY<INT>,
4 c_array2 ARRAY<INT>
5)
6 duplicate key (id)
7distributed by hash(id) buckets 1
8properties(
9 'replication_num' = '1'
10 );
11INSERT INTO array_test2 (id, c_array1, c_array2) VALUES
12 (1, [1, 2, 3, 4, 5], [10, 20, -40, 80, -100]),
13 (2, [6, 7, 8], [10, 12, 13]),
14 (3, [1], [-100]),
15 (4, NULL, NULL);
16select *, array_map(x->x,[1,2,3]) from array_test2 order by id;
Text
1+------+-----------------+-------------------------+----------------------------------------+
2| id | c_array1 | c_array2 | array_map([x] -> x(0), ARRAY(1, 2, 3)) |
3+------+-----------------+-------------------------+----------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 2, 3] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [1, 2, 3] |
6| 3 | [1] | [-100] | [1, 2, 3] |
7| 4 | NULL | NULL | [1, 2, 3] |
8+------+-----------------+-------------------------+----------------------------------------+
SQL
1select *, array_map(x->x+2,[1,2,3]) from array_test2 order by id;
Text
1+------+-----------------+-------------------------+--------------------------------------------+
2| id | c_array1 | c_array2 | array_map([x] -> x(0) + 2, ARRAY(1, 2, 3)) |
3+------+-----------------+-------------------------+--------------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [3, 4, 5] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [3, 4, 5] |
6| 3 | [1] | [-100] | [3, 4, 5] |
7| 4 | NULL | NULL | [3, 4, 5] |
8+------+-----------------+-------------------------+--------------------------------------------+
SQL
1select c_array1, c_array2, array_map(x->x,[1,2,3]) from array_test2 order by id;
Text
1+-----------------+-------------------------+----------------------------------------+
2| c_array1 | c_array2 | array_map([x] -> x(0), ARRAY(1, 2, 3)) |
3+-----------------+-------------------------+----------------------------------------+
4| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 2, 3] |
5| [6, 7, 8] | [10, 12, 13] | [1, 2, 3] |
6| [1] | [-100] | [1, 2, 3] |
7| NULL | NULL | [1, 2, 3] |
8+-----------------+-------------------------+----------------------------------------+
SQL
1select c_array1, c_array2, array_map(x->power(x,2),[1,2,3]) from array_test2 order by id;
Text
1+-----------------+-------------------------+----------------------------------------------------+
2| c_array1 | c_array2 | array_map([x] -> power(x(0), 2.0), ARRAY(1, 2, 3)) |
3+-----------------+-------------------------+----------------------------------------------------+
4| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 4, 9] |
5| [6, 7, 8] | [10, 12, 13] | [1, 4, 9] |
6| [1] | [-100] | [1, 4, 9] |
7| NULL | NULL | [1, 4, 9] |
8+-----------------+-------------------------+----------------------------------------------------+
SQL
1select c_array1, c_array2, array_map((x,y)->x+y,c_array1,c_array2) from array_test2 order by id;
Text
1+-----------------+-------------------------+----------------------------------------------------------+
2| c_array1 | c_array2 | array_map([x, y] -> x(0) + y(1), `c_array1`, `c_array2`) |
3+-----------------+-------------------------+----------------------------------------------------------+
4| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [11, 22, -37, 84, -95] |
5| [6, 7, 8] | [10, 12, 13] | [16, 19, 21] |
6| [1] | [-100] | [-99] |
7| NULL | NULL | NULL |
8+-----------------+-------------------------+----------------------------------------------------------+
SQL
1select c_array1, c_array2, array_map((x,y)->power(x,2)+y,c_array1, c_array2) from array_test2 order by id;
Text
1+-----------------+-------------------------+----------------------------------------------------------------------+
2| c_array1 | c_array2 | array_map([x, y] -> power(x(0), 2.0) + y(1), `c_array1`, `c_array2`) |
3+-----------------+-------------------------+----------------------------------------------------------------------+
4| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [11, 24, -31, 96, -75] |
5| [6, 7, 8] | [10, 12, 13] | [46, 61, 77] |
6| [1] | [-100] | [-99] |
7| NULL | NULL | NULL |
8+-----------------+-------------------------+----------------------------------------------------------------------+
SQL
1select *,array_map(x->x=3,c_array1) from array_test2 order by id;
Text
1+------+-----------------+-------------------------+----------------------------------------+
2| id | c_array1 | c_array2 | array_map([x] -> x(0) = 3, `c_array1`) |
3+------+-----------------+-------------------------+----------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [0, 0, 1, 0, 0] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [0, 0, 0] |
6| 3 | [1] | [-100] | [0] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+----------------------------------------+
SQL
1select *,array_map(x->x>3,c_array1) from array_test2 order by id;
Text
1+------+-----------------+-------------------------+----------------------------------------+
2| id | c_array1 | c_array2 | array_map([x] -> x(0) > 3, `c_array1`) |
3+------+-----------------+-------------------------+----------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [0, 0, 0, 1, 1] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [1, 1, 1] |
6| 3 | [1] | [-100] | [0] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+----------------------------------------+
SQL
1select *,array_map((x,y)->x>y,c_array1,c_array2) from array_test2 order by id;
Text
1+------+-----------------+-------------------------+----------------------------------------------------------+
2| id | c_array1 | c_array2 | array_map([x, y] -> x(0) > y(1), `c_array1`, `c_array2`) |
3+------+-----------------+-------------------------+----------------------------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [0, 0, 1, 0, 1] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [0, 0, 0] |
6| 3 | [1] | [-100] | [1] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+----------------------------------------------------------+
SQL
1select array_map(x->cast(x as string), c_array1) from test_array_map_function;
Text
1+-----------------+-------------------------------------------------------+
2| c_array1 | array_map([x] -> CAST(x(0) AS CHARACTER), `c_array1`) |
3+-----------------+-------------------------------------------------------+
4| [1, 2, 3, 4, 5] | ['1', '2', '3', '4', '5'] |
5| [6, 7, 8] | ['6', '7', '8'] |
6| [] | [] |
7| NULL | NULL |
8+-----------------+-------------------------------------------------------+