如何使用LateralView拆分行資料並執行彙總操作 - MaxCompute

MaxCompute支援通過LATERAL VIEW結合UDTF（自訂表格值函數）將單行資料拆分為多行。本文介紹如何使用Lateral View。

功能介紹

LATERAL VIEW主要用於處理MaxCompute複雜資料類型（如Array、Map等）的展開操作。標準的SELECT語句為每行輸入返回一行輸出，而UDTF（自訂表格值函數）的輸入與輸出是一對多的關係，所以無法直接在SELECT列表中使用UDTF。為解決這一限制，可以結合使用LATERAL VIEW和UDTF，將一行資料拆成多行資料，並彙總拆分後的資料。

當LATERAL VIEW命令格式中含有OUTER關鍵字（即LATERAL VIEW OUTER ...），自訂的UDTF不輸出任何一行時，對應的輸入行在LATERAL VIEW結果中依然保留，且所有UDTF輸出資料行為NULL。

命令格式

LATERALVIEW: LATERAL VIEW [OUTER] <udtf_name>(<expression>) <table_alias> AS <columnAlias> (',' <columnAlias>) 
fromClause: FROM <baseTable> (LATERALVIEW) [(LATERALVIEW) ...]

參數說明

udtf_name：必填。將一行資料拆成多行資料的UDTF，請參見其他函數。
expression：必填。待拆分行資料所屬列名。
table_alias：必填。UDTF結果集的別名。
columnAlias：必填。拆分後得到的列的別名。
baseTable：必填。資料來源表。

說明

FROM後可以有多個LATERAL VIEW語句，後面的LATERAL VIEW語句能夠引用它前面的所有表和列名，實現對不同列的行資料進行拆分。

樣本資料

表pageAds，有三列資料，第一列是pageid STRING，第二列是col1 ARRAY<INT>，第三列是col2 ARRAY<STRING>，詳細資料如下。

pageid	col1	col2
front_page	[1, 2, 3]	["a", "b", "c"]
contact_page	[3, 4, 5]	["d", "e", "f"]

建表語句如下：

CREATE TABLE pageAds (pageid  STRING,col1 ARRAY<INT>,col2 ARRAY<STRING>);
INSERT INTO pageAds VALUES ('front_page',ARRAY(1,2,3),ARRAY("a","b","c"));
INSERT INTO pageAds VALUES ('contact_page',ARRAY(3,4,5),ARRAY("d","e","f"));

使用樣本

單個LATERAL VIEW語句

樣本1：拆分col1。命令樣本如下：

SELECT pageid, col1_new, col2 FROM pageAds LATERAL VIEW EXPLODE(col1) adTable AS col1_new;

返回結果如下：

+--------------+------------+---------------+
| pageid       | col1_new   | col2          | 
+--------------+------------+---------------+
| front_page   | 1          | ["a","b","c"] | 
| front_page   | 2          | ["a","b","c"] | 
| front_page   | 3          | ["a","b","c"] | 
| contact_page | 3          | ["d","e","f"] | 
| contact_page | 4          | ["d","e","f"] | 
| contact_page | 5          | ["d","e","f"] | 
+--------------+------------+---------------+

樣本2：拆分col1並執行彙總統計。命令樣本如下：

SELECT col1_new, COUNT(1) AS COUNT FROM pageAds  LATERAL VIEW EXPLODE(col1) adTable AS col1_new GROUP BY col1_new;

返回結果如下：

+------------+------------+
| col1_new   | count      |
+------------+------------+
| 1          | 1          |
| 2          | 1          |
| 3          | 2          |
| 4          | 1          |
| 5          | 1          |
+------------+------------+

多個LATERAL VIEW語句

拆分col1和col2。命令樣本如下：

SELECT pageid,mycol1, mycol2 FROM pageAds 
  LATERAL VIEW EXPLODE(col1) myTable1 AS mycol1 
  LATERAL VIEW EXPLODE(col2) myTable2 AS mycol2;

返回結果如下：

+--------------+------------+------------+
| pageid       | mycol1     | mycol2     |
+--------------+------------+------------+
| front_page   | 1          | a          |
| front_page   | 1          | b          |
| front_page   | 1          | c          |
| front_page   | 2          | a          |
| front_page   | 2          | b          |
| front_page   | 2          | c          |
| front_page   | 3          | a          |
| front_page   | 3          | b          |
| front_page   | 3          | c          |
| contact_page | 3          | d          |
| contact_page | 3          | e          |
| contact_page | 3          | f          |
| contact_page | 4          | d          |
| contact_page | 4          | e          |
| contact_page | 4          | f          |
| contact_page | 5          | d          |
| contact_page | 5          | e          |
| contact_page | 5          | f          |
+------------+------------+------------+

MaxCompute：LATERAL VIEW

功能介紹

命令格式

參數說明

樣本資料

使用樣本

單個LATERAL VIEW語句

多個LATERAL VIEW語句

相關參考