与生成器函数(例如 EXPLODE)结合使用,将生成包含一个或多个行的虚拟表。 LATERAL VIEW 将行应用于每个原始输出行。

语法

  1. LATERAL VIEW generator_function ( expression [, ...] ) table_identifier AS column_identifier [, ...]

参数

  • generator_function

    生成器函数(EXPLODE、EXPLODE_SPLIT 等)。

  • table_identifier

    generator_function 的别名。

  • column_identifier

    列出列别名 generator_function,它可用于输出行。列标识符的数目必须与 generator 函数返回的列数匹配。

示例

  1. CREATE TABLE `person` (
  2. `id` int(11) NULL,
  3. `name` text NULL,
  4. `age` int(11) NULL,
  5. `class` int(11) NULL,
  6. `address` text NULL
  7. ) ENGINE=OLAP
  8. UNIQUE KEY(`id`)
  9. COMMENT 'OLAP'
  10. DISTRIBUTED BY HASH(`id`) BUCKETS 1
  11. PROPERTIES (
  12. "replication_allocation" = "tag.location.default: 1",
  13. "in_memory" = "false",
  14. "storage_format" = "V2",
  15. "disable_auto_compaction" = "false"
  16. );
  17. INSERT INTO person VALUES
  18. (100, 'John', 30, 1, 'Street 1'),
  19. (200, 'Mary', NULL, 1, 'Street 2'),
  20. (300, 'Mike', 80, 3, 'Street 3'),
  21. (400, 'Dan', 50, 4, 'Street 4');
  22. mysql> SELECT * FROM person
  23. -> LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age;
  24. +------+------+------+-------+----------+-------+
  25. | id | name | age | class | address | c_age |
  26. +------+------+------+-------+----------+-------+
  27. | 100 | John | 30 | 1 | Street 1 | 30 |
  28. | 100 | John | 30 | 1 | Street 1 | 60 |
  29. | 200 | Mary | NULL | 1 | Street 2 | 30 |
  30. | 200 | Mary | NULL | 1 | Street 2 | 60 |
  31. | 300 | Mike | 80 | 3 | Street 3 | 30 |
  32. | 300 | Mike | 80 | 3 | Street 3 | 60 |
  33. | 400 | Dan | 50 | 4 | Street 4 | 30 |
  34. | 400 | Dan | 50 | 4 | Street 4 | 60 |
  35. +------+------+------+-------+----------+-------+
  36. 8 rows in set (0.12 sec)