- array():创建一个数组。split(string,delimiter):按指定字符分隔字符串成数组。
select array(1,3,5) c1,split('a,c,b',',') c2; +----------+----------------+--+ | c1 | c2 | +----------+----------------+--+ | [1,3,5] | ["a","c","b"] | +----------+----------------+--+
- array[int]:获取数组元素。角标从0开始,越界返回NULL。
select array(1,3,5)[0] c1,array('a','b','c')[3] c2; +-----+-------+--+ | c1 | c2 | +-----+-------+--+ | 1 | NULL | +-----+-------+--+
- array_max(array):返回数组中的最大值。
select array_max(array(1,3,5)) c1,array_max(array('a','b','c')) c2; +-----+-----+--+ | c1 | c2 | +-----+-----+--+ | 5 | c | +-----+-----+--+
- array_min(array):返回数组中的最小值。
select array_min(array(1,3,5)) c1,array_min(array('a','b','c')) c2; +-----+-----+--+ | c1 | c2 | +-----+-----+--+ | 1 | a | +-----+-----+--+
- array_join(array, delimiter):使用指定的分隔符将数组中的元素连接成一个字符串。与concat_ws函数功能相同,注意参数顺序。
select array_join(array(1,3,5),'_') c1,concat_ws('#',array('a','b','c')) c2; +--------+--------+--+ | c1 | c2 | +--------+--------+--+ | 1_3_5 | a#b#c | +--------+--------+--+
- array_sort(array):返回一个按升序排序的数组。
select array_sort(array(1,5,3)) c1,array_sort(array('c','b','a')) c2; +----------+----------------+--+ | c1 | c2 | +----------+----------------+--+ | [1,3,5] | ["a","b","c"] | +----------+----------------+--+
-
split(reverse(concat_ws(delimiter,array_sort(array))),delimiter):按降序排序单字符元素数组。reverse(string):反转字符串反转。思路:a.将数组升序排序;b.转成字符串;c.反转字符串;d.将字符串拆成数组。
select split(reverse(concat_ws('#',array_sort(array('c','a','b')))),'#') c1; +----------------+--+ | c1 | +----------------+--+ | ["c","b","a"] | +----------------+--+
-
按降序排列长字符元素数组。explode(array):炸裂数组(列转行);row_number()over():开窗排序函数;collect_list():收集数据成数组(列转行)。思路:a.炸裂数组;b.按照id开窗分组为元素降序排序,并与元素拼接;c.按照id分组,收集所有元素,注意要用collect_list(不去重),不能用collect_set(去重)并拼接成字符串;d.去掉排序序号和连接符之后再拆分成数组。
select keyid,split(regexp_replace(concat_ws('\073',collect_list(e_str)),'(^|\073)([0-9]+_)','$1'),'\073') a from ( select keyid,concat_ws('_',row_number()over(partition by keyid order by e desc),e) e_str from ( select keyid,explode(a) e from (select 1 keyid,array('dbc','aef','ghij','aef','ghij','aef','ghij','aef','ghij','aef','ghij') a) t0 ) t1 ) t2 group by keyid; +--------+---------------------------------------------------------------------------+--+ | keyid | a | +--------+---------------------------------------------------------------------------+--+ | 1 | ["ghij","ghij","ghij","ghij","ghij","dbc","aef","aef","aef","aef","aef"] | +--------+---------------------------------------------------------------------------+--+
-
array_contains(array, value):判断数组中是否包含指定的值。包含返回true,否则返回false。
select array_contains(array(1,2,3), 2) c1,array_contains(array('a','b','c'), 'd') c2; +-------+--------+--+ | c1 | c2 | +-------+--------+--+ | true | false | +-------+--------+--+
-
array_position(array, value):返回value在数组中的位置,如果不存在则返回0。注意位置和角标不同,角标=位置-1。
select array_position(array(1,2,3), 4) c1,array_position(array('a','b','c'), 'a') c2; +-----+-----+--+ | c1 | c2 | +-----+-----+--+ | 0 | 1 | +-----+-----+--+
-
array_remove(array, value):去除数组中的所有value元素。
select array_remove(array(1,2,3,2), 2) c1,array_remove(array('a','b','c'), 'd') c2; +--------+----------------+--+ | c1 | c2 | +--------+----------------+--+ | [1,3] | ["a","b","c"] | +--------+----------------+--+
-
array_except(array1, array2):在数组array1中去除掉所有数组array2中的元素(数组array1比数组array2多的元素)。
select array_except(array(1,2,3,2),array(2,3)) c1,array_except(array('a','b','c'),array('d')) c2; +------+----------------+--+ | c1 | c2 | +------+----------------+--+ | [1] | ["a","b","c"] | +------+----------------+--+
-
array_intersect(array1, array2):返回一个包含所有同时在数组array1和数组array2中的元素的数组(数组array1和数组array2的交集元素)。
select array_intersect(array(1,2,3,2),array(2,3)) c1,array_intersect(array('a','b','c'),array('d')) c2; +--------+-----+--+ | c1 | c2 | +--------+-----+--+ | [2,3] | [] | +--------+-----+--+
-
split(concat_ws(delimiter,array1,array2,array3),delimiter):把相同类型的多个数组合并成一个数组。
select split(concat_ws('\073',array('a','c'),array('b','c'),array('d')),'\073') c1; +------------------------+--+ | c1 | +------------------------+--+ | ["a","c","b","c","d"] | +------------------------+--+
-
在把数组array插入到hive表(有array类型字段)中时,如果报错,有可能是数组中没有元素。
select id,collect_set(b) b_ar from ( select 1 id, null b union select 1 id, null b ) t group by id; +-----+-------+--+ | id | b_ar | +-----+-------+--+ | 1 | [] | +-----+-------+--+
解决办法:a.判断数组元素个数,size(array)=0时,直接插入NULL值;
select id,if(size(collect_set(b))=0,null,collect_set(b)) b_ar from ( select 1 id, null b union select 1 id, null b ) t group by id; +-----+-------+--+ | id | b_ar | +-----+-------+--+ | 1 | NULL | +-----+-------+--+
解决办法:b.拼接成字符串(默认变成""),再拆分成数组。
select id,concat_ws('\073',(collect_set(b))) b_str from ( select 1 id, null b union select 1 id, null b ) t group by id; +-----+--------+--+ | id | b_str | +-----+--------+--+ | 1 | | +-----+--------+--+ select id,split(concat_ws('\073',(collect_set(b))),'\073') b_ar from ( select 1 id, null b union select 1 id, null b ) t group by id; +-----+-------+--+ | id | b_ar | +-----+-------+--+ | 1 | [""] | +-----+-------+--+
Hive中数组array的相关应用
于 2023-07-18 14:50:34 首次发布