阿里云云原生大数据计算服务 MaxComputeLateral View-云淘科技

使用示例

• 单个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方法外，还可以参见行转列及列转行最佳实践。