爆炸函数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表:
Customer | APP | Pt_d |
---|---|---|
张三 | 下载:微信,下载:今日头条 | 2021-02-03 |
李四 | 下载:国泰君安,下载:知乎,卸载:微博 | 2021-02-03 |
王五 | 下载:今日头条,下载:知乎,卸载:英语流利说 | 2021-02-03 |
第一步:先将原始数据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)
;
得到结果表为:
Type | APP |
---|---|
下载 | 微信 |
下载 | 今日头条 |
下载 | 国泰君安 |
下载 | 知乎 |
卸载 | 微博 |
下载 | 今日头条 |
下载 | 知乎 |
卸载 | 英语流利说 |
第二步:在第一步的基础上再进行统计 |
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
最终结果表为:
Type | APP | num |
---|---|---|
下载 | 知乎 | 2 |
下载 | 今日头条 | 2 |
下载 | 国泰君安 | 1 |
下载 | 微信 | 1 |
卸载 | 微博 | 1 |
卸载 | 英语流利说 | 1 |
该结果实现了最初的数据统计述求。