string - HIVE:如何按键对键/值字符串中的值求和

Question

我有两个包含键/值对变量的字符串列。像这样的事情:

    column1         column2
a:1,b:2,c:3         a:5,c:3
   a:12,b:4     a:9,b:3,d:5

我如何通过特定的键对这些值(在现实生活中我不知道我有多少个键，有些键只能在一列中找到)求和来得到这个:

         column12
  a:6,b:2,c:6,d:0
 a:21,b:7,c:0,d:5

或者这个:

 a  b  c  d
 6  2  6  0
21  7  0  5

感谢您的帮助!

Answer 1

假设每一行都有一个唯一的标识符“id”，下面的查询
应该可以。

select id, collect_list(CONCAT(key,':',val)) as column12  
from  
(  
  select id, key, SUM(val) as val  
  from  
  (  
    select id, k1 as key, k2 as key, v1 as val, v2 as val  
    from  
    (  
      select id, str_to_map(col1,',',':') as c1, str_to_map(col2,',',':') as c2  
      from table  
    )x  
    LATERAL VIEW explode(c1) e1 as k1,v1  
    LATERAL VIEW explode(c2) e2 as k2,v2  
  )y  
)z