6、Array函数

原创 于 2025-08-30 09:00:00 发布 · 1.8k 阅读 · 29 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#DataWorks #aliyun #阿里云 #maxcomputer #SQL #大数据 #函数

文章目录

六、Array函数

0、概述

分类函数功能
数组构造与生成ARRAY使用给定的值构造ARRAY。
数组构造与生成ARRAY_REPEAT返回将指定元素重复指定次数后的ARRAY数组。
数组构造与生成SEQUENCE根据表达式生成包含指定元素的数组。
数组构造与生成COMBINATIONS返回输入数组元素的N元组合组成的数组。
数组构造与生成NGRAMS返回指定数组元素的N元语法（n-gram）数组。
数组构造与生成ZIP_WITH将2个ARRAY数组按照位置进行元素级别的合并。
数组构造与生成ARRAYS_ZIP合并多个ARRAY数组。
数组变换与处理ARRAY_REDUCE将ARRAY数组的元素进行聚合。
数组变换与处理TRANSFORM将ARRAY数组中的元素进行转换。
数组变换与处理FILTER将ARRAY数组中的元素进行过滤。
数组变换与处理ARRAY_SORT将ARRAY数组的元素进行排序。
数组变换与处理SORT_ARRAY为指定的数组中的元素排序。
数组变换与处理REVERSE返回指定数组的元素倒序数组。
数组变换与处理SLICE对ARRAY数据切片，返回从指定位置开始、指定长度的数组。
数组变换与处理SHUFFLE返回指定数组的元素随机排列数组。
数组变换与处理ARRAY_NORMALIZE返回根据指定p范数（p Norm）对数组元素规范化后的数组。
数组聚合与统计ARRAY_MAX计算ARRAY数组中的最大值。
数组聚合与统计ARRAY_MIN计算ARRAY数组中的最小值。
数组聚合与统计SIZE返回指定ARRAY中的元素数目。
数组聚合与统计ARRAY_POSITION计算指定元素在ARRAY数组中第一次出现的位置。
数组聚合与统计INDEX返回ARRAY数组指定位置的元素值。
数组判断与条件ALL_MATCH判断ARRAY数组中是否所有元素都满足指定条件。
数组判断与条件ANY_MATCH判断ARRAY数组中是否存在满足指定条件的元素。
数组判断与条件ARRAY_CONTAINS检测指定的ARRAY中是否包含指定的值。
数组判断与条件ARRAYS_OVERLAP判断两个ARRAY数组中是否包含相同元素。
array操作ARRAY_INTERSECT计算两个ARRAY数组的交集。
array操作ARRAY_UNION计算两个ARRAY数组的并集并去掉重复元素。
array操作ARRAY_EXCEPT找出在ARRAY A中，但不在ARRAY B中的元素，
并去掉重复的元素后，以ARRAY形式返回结果。
array操作ARRAY_DISTINCT去除ARRAY数组中的重复元素。
array操作ARRAY_REMOVE在ARRAY数组中删除指定元素。
array操作ARRAY_JOIN将ARRAY数组中的元素按照指定字符串进行拼接。
array操作CONCAT将ARRAY数组或字符串连接在一起。
其它FLATTEN将数组类型的数组转换为单个数组。
其它EXPLODE将一行数据转为多行的UDTF。
其它POSEXPLODE将指定的ARRAY展开，每个Value一行，
每行两列分别对应数组从0开始的下标和数组元素。

1、构造与生成

  • ARRAY：使用给定的值构造ARRAY。
array array(<value>,<value>[, ...])
  • 入参
    • value：必填。可以为任意类型。所有value的数据类型必须一致。
-- [1, 2, 3, 4, 5]
SELECT array(1,2,3,4,5);
SELECT array(name,age,address) FROM user;
  • ARRAY_REPEAT：返回将指定元素重复指定次数后的ARRAY数组。
array<T> array_repeat(T <element>, int <count>)

-- [123, 123, 123]
SELECT array_repeat('123',3);
-- NULL
SELECT array_repeat('123',NULL);
  • SEQUENCE：根据表达式生成包含指定元素的数组。
  sequence(start, stop, [step])
  • 入参
    • start：表示元素序列开始的表达式，元素序列包含start。
    • stop：表示元素序列结束的表达式，元素序列包含stop。
    • step：可选参数。元素序列步长值。
-- [1, 2, 3, 4, 5]
SELECT sequence(1,5);
-- [5, 4, 3, 2, 1]
SELECT sequence(5,1);
-- [1, 3, 5]（从1至5，步长=2）
SELECT sequence(1,5,2);
-- [2025-05-01, 2025-06-01, 2025-07-01]
SELECT sequence(TO_DATE('2025-05-01'),TO_DATE('2025-07-01'),interval 1 month);
  • COMBINATIONS：返回输入数组元素的N元组合组成的数组。
  combinations(array(T), n)
  • 入参
    • array：为输入数组。
    • n：元数。
-- [[1, 2], [1, 3], [2, 3]]
SELECT combinations(array(1,2,3),2);
  • NGRAMS：返回指定数组元素的N元语法（n-gram）数组。
ngrams(array(T), n)
  • 入参
    • array：为输入数组。
    • n：元数，数组中的元素数量。
-- [[1, 2], [2, 3], [3, 4]](按2个元素进行拆分)
SELECT ngrams(array(1,2,3,4),2);
  • ZIP_WITH：将2个ARRAY数组按照位置进行元素级别的合并。
array<R> zip_with(array<T> <a>, array<S> <b>, function<T, S, R> <combiner>)
  • 入参
    • a、b：必填。ARRAY数组。array<T>、array<S>中的T、S指代ARRAY数组元素的数据类型，数组中的元素可以为任意类型。
    • combiner：必填。用于合并ARRAY数组a、b中元素的函数（内置函数或自定义函数）或表达式。
-- [2, 4, 6, null](按照自定义规则，处理array数据)
SELECT zip_with(array(1,2,3),array(1,2,3,4),(x,y)->x+y);
  • ARRAYS_ZIP：合并多个ARRAY数组。
array<struct<T, U, ...>> arrays_zip(array<T> <a>, array<U> <b>[, ...])

-- [{0:1, 1:7}, {0:2, 1:8}, {0:3, 1:9}]
SELECT arrays_zip(array(1,2,3),array(7,8,9));
-- [{0:1, 1:7}, {0:2, 1:8}, {0:3, 1:null}]
SELECT arrays_zip(array(1,2,3),array(7,8));

2、变换与处理

  • ARRAY_REDUCE：将ARRAY数组的元素进行聚合。
R array_reduce(array<T> <a>, buf <init>, function<buf, T, buf> <merge>, function<buf, R> <final>)

-- 6
select array_reduce(array(1, 2, 3), 0, (buf, e)->buf + e ,buf->buf);
-- 8
select array_reduce(array(1, 2, 3), 0, (buf, e)->buf + e ,buf->buf+2);
  • TRANSFORM：将ARRAY数组中的元素进行转换。
array<R> transform(array<T> <a>, function<T, R> <func>)

-- [4, 5, 6]
SELECT transform(array(1,2,3) ,x->x+3);
  • FILTER：将ARRAY数组中的元素进行过滤。
array<T> filter(array<T> <a>, function<T,boolean> <func>)

-- [3]
SELECT filter(array(1,2,3), x->x>2);
  • ARRAY_SORT：将ARRAY数组的元素进行排序。
array<T> array_sort(array<T> <a>, function<T, T, bigint> <comparator>)

-- [{a:1, b:10}, {a:2, b:12}, {a:3, b:11}]
SELECT array_sort(a, (x,y)-> 
            CASE 
                WHEN x.a>y.a THEN 1L 
                WHEN x.a<y.a THEN -1L 
                ELSE 0L 
            END)
-- [{a:1, b:10}, {a:3, b:11}, {a:2, b:12}]
FROM VALUES (
    ARRAY(named_struct('a', 1, 'b', 10),
        named_struct('a', 3, 'b', 11),
        named_struct('a', 2, 'b', 12))
)
AS t(a);
  • SORT_ARRAY：为指定的数组中的元素排序。
array<T> sort_array(array<T> <a>[, <isasc>])
  • 入参
    • a：必填。ARRAY数组。
    • isasc：可选。用于设置排序规则。取值为True（升序）或False（降序）。默认为升序。
-- [null, 10, 20, 30, 30, 40, 50]
select sort_array(array(10, 20, 40, 30, 30, null, 50), true);
-- [50, 40, 30, 30, 20, 10, NULL]。
select sort_array(array(10, 20, 40, 30, 30, null, 50), false);
  • REVERSE：返回指定数组的元素倒序数组。
string|array reverse(string|array <value>)

-- nuyila evol I
select reverse('I love aliyun');
-- NULL
SELECT reverse(NULL);
  • SLICE：对ARRAY数据切片，返回从指定位置开始、指定长度的数组。
array<T> slice(array<T> <a>, <start>, <length>)
  • 入参
    • a：必填。ARRAY数组。
    • start：必填。切片起点，从1开始。start为负数，表示从右侧开始。
    • length：必填。切片长度，必须大于或等于0。
-- [2, null]
select slice(array(1, 2, null, 4, 5), 2, 2);

-- [4, 5]
select slice(array(1, 2, null, 4, 5), -2, 2);
  • SHUFFLE：返回指定数组的元素随机排列数组。
shuffle(array)

-- [1, 4, 2, 3]
SELECT shuffle(array(1,2,3,4));
-- [4, 2, null, 1]
SELECT shuffle(array(1,2,null,4));
  • ARRAY_NORMALIZE：返回根据指定p范数（pNorm）对数组元素规范化后的数组。
array_normalize(array, p)

-- [0.125, 0.25, 0.625]
SELECT  array_normalize(array(10.0, 20.0, 50.0), 1.0);

3、聚合与统计

  • ARRAY_MAX：计算ARRAY数组中的最大值。
T array_max(array<T> <a>) 

-- 4
SELECT ARRAY_MAX(ARRAY(4,2,null,3));
  • ARRAY_MIN：计算ARRAY数组中的最小值。
T array_min(array<T> <a>) 

-- 2
SELECT array_min(ARRAY(4,2,null,3));
  • SIZE：返回指定ARRAY中的元素数目。
int size(array<T> <a>)
int size(map<K, V> <b> )
  • 入参
    • a：必填。ARRAY数组。
    • b：必填。MAP对象。
-- 3
SELECT size(array(1,2,3));
-- 2
SELECT size(map('a',1,'b',2));
  • ARRAY_POSITION：计算指定元素在ARRAY数组中第一次出现的位置。
bigint array_position(array<T> <a>, T <element>)

-- 2
SELECT array_position(array(5,9,1),9);
-- 2
SELECT array_position(array(5,9,null),9);
  • INDEX：返回ARRAY数组指定位置的元素值。
INDEX (<var1>[<var2>])
  • 入参
    • var1：必填。array<T>类型或map<K, V>类型。
    • var2：必填。
      • array<T>类型，为BIGINT类型且大于等于0。
      • map<K, V>类型，与K的类型保持一致。
-- 3
SELECT array(1,2,3,4)[2];
-- 2
SELECT map('a',1,'b',2,'c',3)['b'];

4、判断与条件

  • ALL_MATCH：判断ARRAY数组中是否所有元素都满足指定条件。
boolean all_match(array<T> <a>, function<T, boolean> <predicate>)

-- false
SELECT all_match(array(1,2,3), x -> x>2);
-- true
select all_match(array(4,5,6), x -> x>3);
  • ANY_MATCH：判断ARRAY数组中是否存在满足指定条件的元素。
boolean any_match(array<T> <a>, function<T, boolean> <predicate>)

-- true
SELECT any_match(array(1,2,3), x -> x>2);
-- true
select any_match(array(4,5,6), x -> x>3);
  • ARRAY_CONTAINS：检测指定的ARRAY中是否包含指定的值。
boolean array_contains(array<T> <a>, value <v>)

-- true
SELECT array_contains(array(1,2,3),2);
-- false
SELECT array_contains(array(1,2,3,null),2);
  • ARRAYS_OVERLAP：判断两个ARRAY数组中是否包含相同元素。
boolean arrays_overlap(array<T> <a>,  array<T> <b>)

-- true
SELECT arrays_overlap(array(1,2,3),array(3,4,5));
-- false
SELECT arrays_overlap(array(1,2,3),array(5,6,7));

5、array操作

  • ARRAY_INTERSECT：计算两个ARRAY数组的交集。
array<T> array_intersect(array<T> <a>, array<T> <b>) 

-- [3]
SELECT array_intersect(array(1,2,3),array(3,4,5));
-- [null]
SELECT array_intersect(array(1,2,null),array(4,5,null));
  • ARRAY_UNION：计算两个ARRAY数组的并集并去掉重复元素。
array<T> array_union(array<T> <a>,  array<T> <b>)

-- [1, 2, 3, 5, 6, 7]
SELECT array_union(array(1,2,3),array(5,6,7));
-- [1, 2, 3, 5, null, 7]
SELECT array_union(array(1,2,3),array(5,null,7));
  • ARRAY_EXCEPT：找出在ARRAYA中，但不在ARRAYB中的元素，并去掉重复的元素后，以ARRAY形式返回结果。
array<T> array_except(array<T> <a>, array<T> <b>)

-- [1](存在于第一个不是第二个数组中，并去重)
select array_except(array(1,1,2,2), array(2,2,3,3));
  • ARRAY_DISTINCT：去除ARRAY数组中的重复元素。
array<T> array_distinct(array<T> <a>)

-- [1, 2, 3]
SELECT array_distinct(array(1,1,2,2,3,3));
-- [1, 2, null]
SELECT array_distinct(array(1,1,2,2,null));
-- NULL 
SELECT array_distinct(null);
  • ARRAY_REMOVE：在ARRAY数组中删除指定元素。
array<T> array_remove(array<T> <a>, T <element>)

-- [1, 3]
SELECT array_remove(array(1,2,3),2);
-- [1, null]
SELECT array_remove(array(1,null,3),3);
-- NULL 
SELECT array_remove(array(1,null,3),null);
  • ARRAY_JOIN：将ARRAY数组中的元素按照指定字符串进行拼接。
array_join(array<T> <a>, <delimiter>[, <nullreplacement>])

-- 1=2=3
SELECT array_join(array(1,2,3),'=');
-- 1=2=4
SELECT array_join(array(1,2,null,4),'=');
-- 1=2=ccc=4
SELECT array_join(array(1,2,null,4),'=','ccc');
  • CONCAT：将ARRAY数组或字符串连接在一起。
array<T> concat(array<T> <a>, array<T> <b>[,...])
string concat(string <str1>, string <str2>[,...])

-- [1, 2, 3, -4]
select concat(array(1, 2), array(3, -4));
-- abc123
select concat('abc','123');

6、其它

  • FLATTEN：将数组类型的数组转换为单个数组。
  flatten(arrayOfArray)

-- [1, 2, 3, 4]
SELECT flatten(array(array(1,2),array(3,4)));
  • EXPLODE：将一行数据转为多行的UDTF。
explode (<var>)
  • 入参
    • 如果参数是array<T>类型，则将列中存储的ARRAY转为多行。
    • 如果参数是map<K, V>类型，则将列中存储的MAP的每个Key-Value对转换为包含两列的行，其中一列存储Key，另一列存储Value。
    • 如果表中存在NULL数据，不会单独返回。
SELECT EXPLODE(a) FROM VALUES (array(1,2,null,'a','b')) AS t(a)
-- ====== 源数据
[1, 2, null, a, b]
-- ===== 结果
col
1
2
\N
a
b
  • POSEXPLODE：将指定的ARRAY展开，每个Value一行，每行两列分别对应数组从0开始的下标和数组元素。
posexplode(array<T> <a>)

SELECT posexplode(array('a','b','c'));
-- ===== 结果
pos val
0 a
1 b
2 c
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ha_lydms

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值