HIVE SQL实现分组字符串拼接concat

在Mysql中可以通过

group_concat()

函数实现分组字符串拼接，在HIVE SQL中可以使用

concat_ws()+collect_set()/collect_list()

函数实现相同的效果。
实例：
abc2014B92015A82014A102015B72014B6
1.concat_ws+collect_list 非去重拼接

select a 
    ,concat_ws('-',collect_list(b))  as col_b
    ,concat_ws('-',collect_list(cast(c as string)))  as col_c
from tb_name
group by a
;

查询结果：
acol_bcol_c2014B-A-B9-10-62015A-B8-7
2.concat_ws+collect_set 去重拼接

select a 
    ,concat_ws('-',collect_set(b))  as col_b
    ,concat_ws('-',collect_set(cast(c as string)))  as col_c

from tb_name
group by a
;

查询结果：
acol_bcol_c2014B-A9-10-62015A-B8-7
3.如果在分组拼接的时候需要保持拼接结果为有序排序，可以通过以下两种方式
1）先排序再拼接

select a 
    ,concat_ws('-',collect_set(b))  as col_b
    ,concat_ws('-',collect_set(cast(c as string)))  as col_c

from(
select a 
    ,b
    ,c
    ,row_number()over(partition by a order by b asc) as rn
from tb_name
) t
group by a
;

查询结果：
acol_bcol_c2014A-B10-6-92015A-B8-7

select a 
    ,concat_ws('-',collect_list(b))  as col_b
    ,concat_ws('-',collect_list(cast(c as string)))  as col_c

from(
select a 
    ,b
    ,c
    ,row_number()over(partition by a order by b asc) as rn
from tb_name
) t
group by a
;

查询结果：(col_b与col_c 的位置对应且col_b中的字符有序)
acol_bcol_c2014A-B-B10-6-92015A-B8-7
2）sort_array（）函数升序排列

select a 
    ,concat_ws('-',sort_array(collect_list(b)))  as col_b

from tb_name
group by a
;

acol_b2014A-B-B2015A-B