如何使用Lateral View拆分行数据并执行聚合操作_云原生大数据计算服务 MaxCompute(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          |
+------------+------------+------------+

功能介绍

命令格式

参数说明

示例数据

使用示例

单个LATERAL VIEW语句

多个LATERAL VIEW语句

相关参考