LATERAL VIEW 節
説明
LATERAL VIEW 節は、1 つ以上の行を含む仮想テーブルを生成する EXPLODE などのジェネレータ関数と組み合わせて使用されます。 LATERAL VIEW が各元の出力行に行を適用するようになります。
構文
LATERAL VIEW [ OUTER ] generator_function ( expression [ , ... ] ) [ table_alias ] AS column_alias [ , ... ]
パラメータ
-
OUTER
OUTERが指定されている場合、入力配列/マップが空または null の場合、null が返されます。 -
generator_function
ジェネレーター関数（EXPLODE、INLINE など）を指定します。
-
table_alias
generator_functionのエイリアスで、オプションです。 -
column_alias
generator_functionの列エイリアスのリストで、出力行で使用できます。generator_functionに複数の出力列がある場合は、複数のエイリアスを持つ可能性があります。
例
CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
INSERT INTO person VALUES
(100, 'John', 30, 1, 'Street 1'),
(200, 'Mary', NULL, 1, 'Street 2'),
(300, 'Mike', 80, 3, 'Street 3'),
(400, 'Dan', 50, 4, 'Street 4');
SELECT * FROM person
LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age
LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age;
+------+-------+-------+--------+-----------+--------+--------+
| id | name | age | class | address | c_age | d_age |
+------+-------+-------+--------+-----------+--------+--------+
| 100 | John | 30 | 1 | Street 1 | 30 | 40 |
| 100 | John | 30 | 1 | Street 1 | 30 | 80 |
| 100 | John | 30 | 1 | Street 1 | 60 | 40 |
| 100 | John | 30 | 1 | Street 1 | 60 | 80 |
| 200 | Mary | NULL | 1 | Street 2 | 30 | 40 |
| 200 | Mary | NULL | 1 | Street 2 | 30 | 80 |
| 200 | Mary | NULL | 1 | Street 2 | 60 | 40 |
| 200 | Mary | NULL | 1 | Street 2 | 60 | 80 |
| 300 | Mike | 80 | 3 | Street 3 | 30 | 40 |
| 300 | Mike | 80 | 3 | Street 3 | 30 | 80 |
| 300 | Mike | 80 | 3 | Street 3 | 60 | 40 |
| 300 | Mike | 80 | 3 | Street 3 | 60 | 80 |
| 400 | Dan | 50 | 4 | Street 4 | 30 | 40 |
| 400 | Dan | 50 | 4 | Street 4 | 30 | 80 |
| 400 | Dan | 50 | 4 | Street 4 | 60 | 40 |
| 400 | Dan | 50 | 4 | Street 4 | 60 | 80 |
+------+-------+-------+--------+-----------+--------+--------+
SELECT c_age, COUNT(1) FROM person
LATERAL VIEW EXPLODE(ARRAY(30, 60)) AS c_age
LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age
GROUP BY c_age;
+--------+-----------+
| c_age | count(1) |
+--------+-----------+
| 60 | 8 |
| 30 | 8 |
+--------+-----------+
SELECT * FROM person
LATERAL VIEW EXPLODE(ARRAY()) tableName AS c_age;
+-----+-------+------+--------+----------+--------+
| id | name | age | class | address | c_age |
+-----+-------+------+--------+----------+--------+
+-----+-------+------+--------+----------+--------+
SELECT * FROM person
LATERAL VIEW OUTER EXPLODE(ARRAY()) tableName AS c_age;
+------+-------+-------+--------+-----------+--------+
| id | name | age | class | address | c_age |
+------+-------+-------+--------+-----------+--------+
| 100 | John | 30 | 1 | Street 1 | NULL |
| 200 | Mary | NULL | 1 | Street 2 | NULL |
| 300 | Mike | 80 | 3 | Street 3 | NULL |
| 400 | Dan | 50 | 4 | Street 4 | NULL |
+------+-------+-------+--------+-----------+--------+