hive 常用函数记录(3)

collect_list/collect_set、concat/concat_ws

collect_list(x) -- Returns a list of objects with duplicates [聚合数据后不会去重]

collect_set(x) -- Returns a set of objects with duplicate elements eliminated [聚合数据后会去重]

样例数据：

Create table test(
 id string ,
 dates string ,
 str string 
);
insert into test 
select '1','2023-12-31','aaa' 
union all
select '1','2023-12-31','aaa' 
union all
select '1','2023-12-31','bbb' 
union all
select '1','2023-12-31','ccc' 
union all
select '1','2023-10-31','aaa' 
union all
select '1','2023-10-31','bbb' 
union all
select '2','2023-12-31','aaa' 
union all
select '2','2023-12-31','bbb' 
union all
select '2','2023-12-31','ccc' 
union all
select '2','2023-10-31','aaa' 
union all
select '2','2023-10-31','bbb' 
union all
select '2','2023-12-31','ddd' 

select id,dates,collect_list(str) liststr,collect_set(str) setstr from test group by id,dates ;

concat(str1, str2, ... strN) -- returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data bin1, bin2, ... binN

eg：select concat(id,',',dates,',',str) from test ;

select concat(123213,null) ; -- 使用concat函数，如果某列为null时，则合并结果也为null

concat_ws(separator, [string | array(string)]+)  -- returns the concatenation of the strings separated by the separator.

separator：分隔符号

eg：select concat_ws(';','12312',null,'aaaa') -- null 字段的合并对于concat_ws 无影响

select id,dates,concat_ws(',',collect_list(str)) liststr1,collect_list(str) liststr2 from test group by id,dates ; -- concat_ws将数组类型修改成字符串类型

select id,dates,concat_ws(',',collect_set(str)) liststr1 ,collect_set(str) liststr2 from test group by id,dates ;