|
一行拆多行
1、使用explode或posexplode方法
并不限制是逗号，其他分隔符都可以
1.1 对单列实行列转行 explode 配合 lateral view 使用
-- 测试数据
with temp as(select 1 as id ,'a,b,c' as name
union
select 2 as id ,'d,e,f' as name)
测试数据如下： 
select id,name,s_name
from temp
lateral view explode(split(name,',')) t as s_name
结果如下： 
1.2 对多列实行列转行 posexplode 配合 lateral view 使用
-- 测试数据
with temp as(select '1,2,3' as id ,'a,b,c' as name
union
select '4,5,6' as id ,'d,e,f' as name)
测试数据如下： 
-- 单个posexplode 返回index及值
select id,name,s_name_index,s_name
from temp
lateral view posexplode(split(name,',')) t as s_name_index,s_name
结果如下： 
-- 同时对两列都是用posexplode
select id,name,s_id_index,s_id,s_name_index,s_name
from temp
lateral view posexplode(split(id,',')) t as s_id_index,s_id
lateral view posexplode(split(name,',')) t as s_name_index,s_name
结果如下： 发现此时是全连接 
-- 添加where限制
select id,name,s_id,s_name
from temp
lateral view posexplode(split(id,',')) t as s_id_index,s_id
lateral view posexplode(split(name,',')) t as s_name_index,s_name
where s_id_index = s_name_index
结果如下： 
2、find_in_set方法
用法介绍： find_in_set(str,stlist） strlist必须是用逗号分割的字符串，返回str在strlist的索引，没有则返回0。 主要针对想筛选只包含某个编码的情况。
select find_in_set('a','a,b,c')
结果如下： 
2.1 基本用法在where中使用，用于寻找包含某个字符串的行（不能拆分多行）
注意：在hive中，需要使用find_in_set(‘a’,‘a,b,c’) >0,才可以；在mysql中不需要加>0就可以判断。 另外，str，strlist都可以是表中的一列数据，str就是没有带逗号的常量列，strlist是有可能在逗号的list列，那这个找在strlist列中出现过str列中的常量都会被挑选出来。 测试数据如下：
with temp as(select 1 as id ,'a,b,c,d' as name
union
select 2 as id ,'d,e,f' as name
union
select 3 as id ,'g,h,k' as name)
select *
from temp
where (find_in_set('a',name) >0 or find_in_set('d',name)>0)
结果如下： 
2.2 在俩表连接join中使用，可以实现拆分多行
with temp as(select 1 as id ,'a,b,c,d' as name
union
select 2 as id ,'d,e,f' as name
union
select 3 as id ,'g,h,k' as name),
temp2 as (select 'd' as dict
union
select 'e' as dict)
 
select temp.*,temp2.*
from temp left join temp2 on find_in_set(temp2.dict,temp.name)>0
结果如下：id 为2的行既包含d又包含e，故拆成两行；id为1的行只包含d,故也只有一行；id为3的行不包含d,e,故left join时dict字段下为空。 
ps:可以看出explode或posexplode方法是将索引逗号隔开的字段全部拆开，而find_in_set是根据你的需要符合某种条件的才拆开，可以根据需求选择使用方法。
如何将拆分的多行，合并成一行，mysql可以使用group_concat，hive可以使用collect_list搭配concat_ws实现。
【参考资料】
1、Hive–sql中的explode()函数和posexplode()函数
|