Hive中数组array的相关应用

1. array()：创建一个数组。split(string,delimiter)：按指定字符分隔字符串成数组。

   select array(1,3,5) c1,split('a,c,b',',') c2;
   +----------+----------------+--+
   |    c1    |       c2       |
   +----------+----------------+--+
   | [1,3,5]  | ["a","c","b"]  |
   +----------+----------------+--+
   

2. array[int]：获取数组元素。角标从0开始，越界返回NULL。

   select array(1,3,5)[0] c1,array('a','b','c')[3] c2;
   +-----+-------+--+
   | c1  |  c2   |
   +-----+-------+--+
   | 1   | NULL  |
   +-----+-------+--+

3. array_max(array)：返回数组中的最大值。

   select array_max(array(1,3,5)) c1,array_max(array('a','b','c')) c2;
   +-----+-----+--+
   | c1  | c2  |
   +-----+-----+--+
   | 5   | c   |
   +-----+-----+--+
   

4. array_min(array)：返回数组中的最小值。

   select array_min(array(1,3,5)) c1,array_min(array('a','b','c')) c2;
   +-----+-----+--+
   | c1  | c2  |
   +-----+-----+--+
   | 1   | a   |
   +-----+-----+--+
   

5. 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  |
   +--------+--------+--+
   

6. 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"]  |
   +----------+----------------+--+
   

7. 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"]  |
   +----------------+--+
   

8. 按降序排列长字符元素数组。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"]  |
   +--------+---------------------------------------------------------------------------+--+
   

9. 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  |
   +-------+--------+--+
   

10. 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   |
    +-----+-----+--+
    

11. 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"]  |
    +--------+----------------+--+
    

12. 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"]  |
    +------+----------------+--+
    

13. 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]  | []  |
    +--------+-----+--+
    

14. 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"]  |
    +------------------------+--+
    

15. 在把数组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   | [""]  |
    +-----+-------+--+