lateral view 的语法格式
lateral view:
LATERAL VIEW udtf (expression) tableAlias AS coluumAlias ( ',' , columnAlias)*
fromClause:
FROM baseTable (lateralView)*
用法描述
lateral view 要与UDTF函数一起使用，比如 explode() 函数。这里的UDTF函数是指用户自定义的表生成函数（英文全称：user defined table-generating functions），它可以接受一行输入然后产生0行或多行输出。lateral view 首先将utdf函数应用到每一行上，这时每一行经utdf处理后得到多行输出，这些输出将会组建成一张虚拟表，然后这张虚拟表会跟当前表进行join操作，join完成之后会得出一张结果虚拟表，这张结果表里就有了utdf生成的列，当然原表的列除了utdf消耗的列之外肯定也在都里面。
示例说明
考虑下面这张 pageAds 表：
| Column name | Column type |
|---|---|
| page_id | String |
| adid_list | Array<int> |
里面有两行数据：
| page_id | adid_list |
|---|---|
| front_page | [1,2,3] |
| contact_page | [3,4,5] |
这两行数据的意思是：front_page页有三条广告，id分别是1，2，3。contact_page页也有三条广告，id分别是3，4，5。
现在需要解决的问题是：在所有页面中统计每条广告出现的次数，输出广告id和该广告出现的总次数。
第一步：
使用 lateral view 和 explore() 函数将 adid_list 列的 list 拆分，sql代码如下：
SELECT page_id, ad_id
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS ad_id;
可以得到以下结果：
| page_id (string) | adid (int) |
|---|---|
| "front_page" | 1 |
| "front_page" | 2 |
| "front_page" | 3 |
| "contact_page" | 3 |
| "contact_page" | 4 |
| "contact_page" | 5 |
然后我们再使用 count/group by 语句统计出每个adid出现的次数：
SELECT ad_id, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS ad_id
GROUP BY ad_id
得到最终结果：
| ad_id | count(1) |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
outer lateral view
later view 前面还可以加上一个 outer 关键字，这是为了避免 当udtf 没有得到任何结果时最终虚拟结果表里丢失原数据行的问题。具体来将，由于later view 的工作原理是将原表与 udtf 产生的虚拟表做 inner join 操作，所以如果 udtf 不产生任何结果时，那么对应原表的那一行也会在 inner join 操作后消失。outer关键字就是来解决这个问题的，加上这个关键字之后执行的就是 outer join 操作了，因此原表数据会被完全保留下来。
例如，上面的 pageAds 表中如果有以下三行数据：
| page_id | adid_list |
|---|---|
| front_page | [1,2,3] |
| contact_page | [3,4,5] |
| end_page | [ ] |
那么执行 lateral view 的sql：
SELECT page_id, ad_id
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS ad_id;
得到的结果将会是：
| page_id (string) | adid (int) |
|---|---|
| "front_page" | 1 |
| "front_page" | 2 |
| "front_page" | 3 |
| "contact_page" | 3 |
| "contact_page" | 4 |
| "contact_page" | 5 |
可以看到，end_page 那一行消失了，因为它没有在 inner join 中关联到任何数据。因此如果想保留原表全部数据的话，就需要加上outer关键字：
SELECT page_id, ad_id
FROM pageAds OUTER LATERAL VIEW explode(adid_list) adTable AS ad_id;
得到如下结果
| page_id (string) | adid (int) |
|---|---|
| "front_page" | 1 |
| "front_page" | 2 |
| "front_page" | 3 |
| "contact_page" | 3 |
| "contact_page" | 4 |
| "contact_page" | 5 |
| “end_page” | <NULL> |
网友评论