sql - 我需要创建一个新列或将原始列从 array 更改为 map 其中 int 值位于相应字符串的新表中

Question

我有两张 table 。 1.table1:(string,array(string))abc ["s","m"]def ["m","a","l"]xyz ["s","a"]2.table2: (string,int)m 12s 26l 57a 45现在我想要一个如下表:(string,map(string,int))abc ["s":26,"m":12]def ["m":12,"a":45,"l":57]xyz ["s":26,"a":45] 1. 我需要 hive 查询做this.2。如何为像 abc 38 这样的特定行总和进行交互

Answer 1

这会成功的

select t1.cat, collect_list(concat_ws(":", t1.subs, cast(t2.cnt as string))) from 
(select cat, subs from temp.table1
lateral view explode(sub) s as subs) t1
join 
    temp.table2 t2
on
    t1.subs = t2.sub
group by t1.cat

如果你想根据上表做一个总和。

select cat, sum(split(sub, ":")[1]) from temp.test3
lateral view explode(`_c1`) c as sub
group by cat