MaxCompute支持通过Lateral View与UDTF(表生成函数)结合,将单行数据拆成多行数据。本文为您介绍如何使用Lateral View拆分行数据,并执行聚合操作。

功能介绍

直接在select语句中使用UDTF会存在限制,为解决此问题,您可以通过MaxCompute的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>,详细数据如下。
pageidcol1col2
front_page[1, 2, 3][“a”, “b”, “c”]
contact_page[3, 4, 5][“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方法外,还可以参见行转列及列转行最佳实践