posexplode函数实战总结

目录

1、建表和准备数据

2、炸裂实践

3、错误炸裂方式

4、当字段类型为string，需要split一下

---

对单列array类型的字段进行炸裂时，可以使用lateral view explode。

对多列array类型的字段进行炸裂时，可以使用lateral view posexplode。

1、建表和准备数据

--测试posexplode
create table tmp.test_lateral_view_movie_230831(class string,name array<string>,score array<string>);

insert into tmp.test_lateral_view_movie_230831 select 'calss1',array('A','B','C'),array('61','66','90');
insert into tmp.test_lateral_view_movie_230831 select 'calss2',array('D','E','F','G'),array('56','67','78','76');
insert into tmp.test_lateral_view_movie_230831 select 'calss3',array('X','Y','Z'),array('77','88','99');

select * from tmp.test_lateral_view_movie_230831;

表中数据

2、炸裂实践

--双列炸裂 poexplode 可以将index和数据都取出来，使用两次posexplode并令两次取到的index相等
select 
	class
	,index_na+1 as stu_id
	,tmp_name
	,tmp_score
from tmp.test_lateral_view_movie_230831 
lateral view posexplode(name) tmp_na as index_na,tmp_name
lateral view posexplode(score) tmp_sc as index_sc,tmp_score
where index_na = index_sc

结果

3、错误炸裂方式

--炸裂列会相互交叉，类似与笛卡尔积
select 
	class
	,tmp_name
	,tmp_score
from tmp.test_lateral_view_movie_230831 
lateral view explode(name) tmp as tmp_name
lateral view explode(score) tmp as tmp_score

或者

--嵌套炸裂
select  
	class
	,tmp_name
	,tmp_score
from 
(
	select 
		class
		,tmp_name
		,score
	from tmp.test_lateral_view_movie_230831 
	lateral view explode(name) tmp as tmp_name
) a
lateral view explode(score) tmp as tmp_score

结果

4、当字段类型为string，需要split一下

create table tmp.test_lateral_view_movie_230831_01(class string,name string,score string);

insert into tmp.test_lateral_view_movie_230831_01 select 'calss1','A,B,C','61,66,90';
insert into tmp.test_lateral_view_movie_230831_01 select 'calss2','D,E,F,G','56,67,78,76';
insert into tmp.test_lateral_view_movie_230831_01 select 'calss3','X,Y,Z','77,88,99';

select * from tmp.test_lateral_view_movie_230831_01;

表中数据：

炸裂：

select 
	class
	,index_n+1 as id
	,nn_name
	,ss_score
from tmp.test_lateral_view_movie_230831_01 
lateral view posexplode(split(name,',')) nn as index_n,nn_name
lateral view posexplode(split(score,',')) ss as index_s,ss_score
where index_n = index_s

结果