hive sql中的用 爆炸函数lateral view explode将一行转成多行

方法一：
爆炸函数lateral view explode将一行转成多行
lateral view 常用于和split，explode这个UDTF（User defined Table-Generating Functions）一起使用，该组合能进一行数据拆分成多行数据，并对拆分后的数据进行聚合。
split()函数通过指定分隔符对字符串进行切片，并返回分割后的字符串列表。
lateral view首先为原始表的每行调用UDTF，explode这个UDTF函数会把一个列表或数组元素拆分成一行或者多行，lateral view会把explode的结果组合，产生一个支持别名表的虚拟表。常用于处理一些特定格式的数据。

Eg：某手机应用市场采集了每天用户下载、卸载APP的数据，定义一个详情表table，该应用市场想要统计每个APP的下载、卸载量，对于table表中的数据，就可以借助lateral view explode函数来完成统计。
table表：

第一步：先将原始数据APP字段的中的信息进行简单加工，将下载和卸载的APP拆分开

Select substring_index(list,’:’,1) as type
     , substring_index(list,’:’,-1) as APP
from table t1
lateral  view  explode (split(APP,’,’))  t2  as  list
Where t1.pt_d=’2021-02-03’
group by 
      substring_index(list,’:’,1) 
     , substring_index(list,’:’,-1)
;

得到结果表为：

第二步：在第一步的基础上再进行统计

Select 
type,APP,count(*) as num 
from
(
     Select substring_index(list,’:’,1) as type
           , substring_index(list,’:’,-1) as APP
     from table t1
     lateral  view  explode (split(APP,’,’))  t2  as  list
     Where t1.pt_d=’2021-02-03’
     group by 
           substring_index(list,’:’,1) 
          , substring_index(list,’:’,-1)
)t
group by 
       type,APP
Order by num desc

最终结果表为：

该结果实现了最初的数据统计述求。

方法二：
可以结合substring_index() 函数和left join来实现将一个字符串中的多个元素转换成多行，需提前生成一张含有序号的数据表t。基本思路是①计算每个字符串中分隔符的个数，利用这个数量和表t关联，通过1对多的关系将一行生成多行。②通过substring_index() 函数和关联到的数字，对字符串中的元素进行截取，最后实现数据清洗。
t表：

注：表t中是序号可以根据实际字符中的元素个数进行拓展

table表：

select 
 customer,
 substring_index(substring_index(APP, ',', t.id+1), ',', -1) as APP,
 Pt_d
from table
left join
t
on t.id < (length(APP) - length(replace(APP, ',', ''))+1)  ///在on条件中通过数值的比较符进行关联在mysql中可以行得通，但在其他方言中不一定，此方法需注意使用

最后就可以得到拓展成多行的数据表。