Hive 多数组合并 CONCAT_WS

轻风细雨

已于 2022-08-30 16:05:00 修改

阅读量6.5k

点赞数 3

分类专栏： hive 文章标签：数据库 sql hive

于 2022-08-30 11:50:39 首次发布

本文链接：https://blog.csdn.net/hxiaowang/article/details/126600858

版权

hive 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

多列的情况

先上结果

select split(concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC")), ",")

hive> select split(concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC")), ",");
OK
["AAA","bbb","CCC","AAA","bbb","GGG","CCC"]
Time taken: 0.291 seconds, Fetched: 1 row(s)

拆分concat_ws 可以拆分数组

select concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC"))

hive> select concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC"));
OK
AAA,bbb,CCC,AAA,bbb,GGG,CCC
Time taken: 0.456 seconds, Fetched: 1 row(s)

然后在用split切分再变回数组

select split(concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC")), ",")

多行合并

select
l1,split(concat_ws(',',collect_set(concat_ws(',',l2))),',')
from
(
select 666 l1,array("AAA", "bbb") l2
union
select 666 l1,array('CCC',"AAA", "bbb") l2
union
select 666 l1,array("AAABB", "bbbFF","AAAEE", "bbbFF") l2
union
select 666 l1,array("AATTA", "bbTb") l2
) res
group by l1

结果和上面效果一样