1、背景描述

通常我们在处理数据时，如果遇到一个字段存储多个值，常常需要把一行数据转换为多行数据，形成标准的结构化数据

例如，将下面的两列数据并列转换为三行，使得code和name一一对应的

2、Hive实现

使用Hive的lateral view posexplode实现

select id, pos1, sub_code, pos2, sub_name from tmp
lateral view posexplode(split(code,'、')) v1 as pos1, sub_code
lateral view posexplode(split(name,'、')) v2 as pos2, sub_name
where id='1' and pos1=pos2

3、PrestoSQL实现

使用PrestoSQL的cross join unnest实现

with temp1 as(select id,sub_name,row_number() over() rnfrom tempcross join unnest(split(code, '、'))  as t (sub_name)where id='1'),
temp2 as (select id,sub_code,row_number() over() rnfrom tempcross join unnest(split(name, '、')) as t (sub_code)where id='1')
select * 
from temp1
left join temp2 
on temp1.rn = temp2.rn