描述
lateral view用于和split, explode等UDTF一起使用锰悼，它能夠?qū)⒁恍袛?shù)據(jù)拆成多行數(shù)據(jù)宋光，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進行聚合闯袒。lateral view首先為原始表的每行調(diào)用UDTF历帚，UTDF會把一行拆分成一或者多行沽甥，lateral view再把結(jié)果組合桶蝎，產(chǎn)生一個支持別名表的虛擬表驻仅。
例子
假設(shè)我們有一張表pageAds谅畅，它有兩列數(shù)據(jù)，第一列是pageid string噪服，第二列是adid_list毡泻，即用逗號分隔的廣告ID集合：
| string pageid | Array<int> adid_list |
|---|---|
| "front_page" | [1, 2, 3] |
| "contact_page" | [3, 4, 5] |
要統(tǒng)計所有廣告ID在所有頁面中出現(xiàn)的次數(shù)。
首先分拆廣告ID：
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
執(zhí)行結(jié)果如下：
| string pageid | Array<int> adid_list |
|---|---|
| "front_page" | 1 |
| "front_page" | 2 |
| "front_page" | 3 |
| "contact_page" | 3 |
| "contact_page" | 4 |
| "contact_page" | 5 |
接下來就是一個聚合的統(tǒng)計：
SELECT adid, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;
| int adid | count(1) |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
多個lateral view語句
一個FROM語句后可以跟多個lateral view語句粘优，后面的lateral view語句能夠引用它前面的所有表和列名仇味。 以下面的表為例：
| Array<int> col1 | Array<int> adid_list |
|---|---|
| [1, 2] | [a", "b", "c"] |
| [3, 4] | [d", "e", "f"] |
SELECT myCol1, col2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1;
執(zhí)行結(jié)果為：
| int mycol1 | Array<string> col2 |
|---|---|
| 1 | [a", "b", "c"] |
| 2 | [a", "b", "c"] |
| 3 | [d", "e", "f"] |
| 4 | [d", "e", "f"] |
加上一個lateral view：
SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;
| int myCol1 | int myCol1 |
|---|---|
| 1 | "a" |
| 1 | "b" |
| 1 | "c" |
| 2 | "a" |
| 2 | "b" |
| 2 | "c" |
| 3 | "d" |
| 3 | "e" |
| 3 | "f" |
| 4 | "d" |
| 4 | "e" |
| 4 | "f" |
它的執(zhí)行結(jié)果為：
| int myCol1 | int myCol1 |
|---|---|
| 1 | "a" |
| 1 | "b" |
| 1 | "c" |
| 2 | "a" |
| 2 | "b" |
| 2 | "c" |
| 3 | "d" |
| 3 | "e" |
| 3 | "f" |
| 4 | "d" |
| 4 | "e" |
| 4 | "f" |
