sql - 如何在 hive 中进行字符串连接

Question

我有 2 个表让我们说表 1 如下

和表2如下

我期望的结果是

我在 hive 中尝试了 concat_ws 方法，但结果与预期不符，而且当我使用 concat_ws 方法时，我只能申请 1 个字段。请让我知道如何克服或有任何可用的解决方案。

标签： sqlarrayshiveconcatenationhiveql

Answer 1

用于collect_set获取每个 ( name, function) 的部分数组，然后用于concat_ws使用逗号作为分隔符连接数组：

select t.name1, t1.function, t2.parts, t2.body, t1.scope
from
(select name1, function,
        concat_ws(',',collect_set(scope)) as scope
   from table1
  group by name1, function
) t1
 inner join
(select name2, function,
        concat_ws(',',collect_set(parts)) as parts,
        concat_ws(',',collect_set(Body)) as Body
   from table2 t2 
  group by name2, function
)t2
 on t1.name1=t2.name2 and t1.function=t2.function 

另一种方法 - 没有加入，使用 UNION ALL+聚合，这可能更高效：

select name, function,
       concat_ws(',',collect_set(parts)) as parts,
       concat_ws(',',collect_set(Body)) as Body,
       concat_ws(',',collect_set(scope)) as scope
from
(
select name1 as name, function, null as parts, null as Body, scope from table1
UNION ALL
select name2 as name, function, parts, Body, null as scope from table2
)s
group by name, function