SQL: lateral view & explode & split (分割&侧视图)

支持该函数：Hive、阿里云odps sql

1.阿里云

初始样本数据SQL

--初始样本数据
select 
	distinct 
	get_json_object(extra_params,'$.order_id') order_id,
	get_json_object(extra_params,'$.aptitude_id') user_id
from jz_daojia.f_pt_traffic_normalized
where dt>='20200316'
and event_id='click_need_submit_aptitude'
and get_json_object(extra_params,'$.order_id') in (1123208136957624705,1123218787145351552,1123163859560874369)
;

初始样本数据结果截图

已知 user_id 字段内容如上：有的行字段内只有1个数字，有的行字段内有多个数字且用“，”分隔开；

需求场景：希望将包含多个数字的字段内容根据“，”给单独拆分出来，且拆开后user_id中的每一部分都跟原始这行数据的其他字段组成新的一行数据；

1.1 单独将 user_id 这一个字段根据“，”分隔开且每一部分重新生成一行数据，可以使用 explode 和 split 函数实现

SQL如下：

select
	explode(split(a.user_id,',')) as user_id_2
from
( --初始样本数据
select 
	distinct 
	get_json_object(extra_params,'$.order_id') order_id,
	get_json_object(extra_params,'$.aptitude_id') user_id
from jz_daojia.f_pt_traffic_normalized
where dt>='20200316'
and event_id='click_need_submit_aptitude'
and get_json_object(extra_params,'$.order_id') in (1123208136957624705,1123218787145351552,1123163859560874369)
) a 
;

数据结果如下：

1.2 将 user_id 这一个字段根据“，”分隔开且每一部分重新生成一行数据的同时，把原始 user_id 这一行其他字段的内容继承下来

期望的数据结果：

如果用下面的SQL，会报错，原因是explode和split函数不能和其他字段直接使用

select
	a.order_id,
	explode(split(a.user_id,',')) as user_id_2
from
( --初始样本数据
select 
	distinct 
	get_json_object(extra_params,'$.order_id') order_id,
	get_json_object(extra_params,'$.aptitude_id') user_id
from jz_daojia.f_pt_traffic_normalized
where dt>='20200316'
and event_id='click_need_submit_aptitude'
and get_json_object(extra_params,'$.order_id') in (1123208136957624705,1123218787145351552,1123163859560874369)
) a 

报错截图：

此时要想获取期望的数据，需要用到 lateral view （侧视图）

具体SQL如下：

select
	a.order_id,
	a.user_id,
	b.user_id_2
from
( --初始样本数据
select 
	distinct 
	get_json_object(extra_params,'$.order_id') order_id,
	get_json_object(extra_params,'$.aptitude_id') user_id
from jz_daojia.f_pt_traffic_normalized
where dt>='20200316'
and event_id='click_need_submit_aptitude'
and get_json_object(extra_params,'$.order_id') in (1123208136957624705,1123218787145351552,1123163859560874369)
) a 
lateral view explode(split(a.user_id,',')) b as user_id_2
;

数据结果如下：

SQL中的 lateral view explode(split(a.user_id,',')) b as user_id_2 相当于把a虚拟表的数据根据a表中的user_id字段按照“，”做了拆分且拆分后的每一个部分都跟该行原始的其他字段的数据单独成为了一个新的数据行，可以理解为新增加了一个user_id_2字段，该字段的内容即为a表中user_id字段拆分开的每一个部分；

其中b为生成新的虚拟表的别名，user_id_2 为将a表user_id字段内容处理后生成新的字段的字段别名，此处的表别名b，as符号，字段别名user_id_2均不能省略，缺少任何一个都会报错；

lateral view explode(split(a.user_id,',')) b as user_id_2 只要写在要处理的原始表名后面即可，此处a表是一个子查询，所以样式是这样，如果a子查询处是一个物理表 jz_daojia.order_detail ,SQL可以写成如下：

select
	a.order_id,
	a.user_id,
	b.user_id_2
from jz_daojia.order_detail a 
lateral view explode(split(a.user_id,',')) b as user_id_2
;

 lateral view 也可以多重使用，如果a表中custom_id字段内容情况跟user_id类似，如果都想拆开可以写成如下SQL形式，且每次拆分后都跟之前的数据按照笛卡尔积进行关联：

select
	a.order_id,
	a.user_id,
	b.user_id_2,
	a.custom_id,
	c.custom_id_2
from jz_daojia.order_detail a 
lateral view explode(split(a.user_id,',')) b as user_id_2
lateral view explode(split(a.custom_id,',')) c as custom_id_2
;

2.Hive 

转载链接：hive中的lateral view 与 explode函数的使用_guodong2k的博客-CSDN博客（hive中的lateral view 与 explode函数的使用）