LATERAL VIEW を使用して行データを分割し、集約操作を実行する方法 - MaxCompute

MaxCompute では、LATERAL VIEW 句をユーザー定義のテーブル値関数 (UDTF) とともに使用して、1 行のデータを複数行に分割できます。この Topic では、LATERAL VIEW の使用方法について説明します。

概要

LATERAL VIEW は、主に MaxCompute で Array や Map などの複雑なデータ型を処理するために使用されます。標準の SELECT 文は、入力行ごとに出力行を 1 行返します。しかし、ユーザー定義のテーブル値関数 (UDTF) は、入力と出力の間に 1 対多の関係があります。このため、SELECT リストで UDTF を直接使用することはできません。この制限を克服するために、LATERAL VIEW を UDTF とともに使用して、1 行のデータを複数行に分割し、その結果のデータを集約できます。

LATERAL VIEW OUTER ... のように、LATERAL VIEW 文に OUTER キーワードが含まれており、UDTF が行を返さない場合、元の入力行は結果セットに保持されます。UDTF によって生成されるすべての列は NULL に設定されます。

コマンドのフォーマット

LATERALVIEW: LATERAL VIEW [OUTER] <udtf_name>(<expression>) <table_alias> AS <columnAlias> (',' <columnAlias>) 
fromClause: FROM <baseTable> (LATERALVIEW) [(LATERALVIEW) ...]

パラメーター

udtf_name: 必須。1 行のデータを複数行に分割するために使用される UDTF。詳細については、「その他の関数」をご参照ください。
expression: 必須。分割するデータを含む列の名前。
table_alias: 必須。UDTF 結果セットのエイリアス。
columnAlias: 必須。行分割によって生成された列のエイリアス。
baseTable: 必須。ソーステーブル。

説明

FROM 句の後に複数の LATERAL VIEW 文を使用できます。後続の LATERAL VIEW 文は、その前にあるすべてのテーブルと列名を参照できます。これにより、異なる列のデータを分割できます。

サンプルデータ

pageAds テーブルには、pageid STRING、col1 ARRAY<INT>、col2 ARRAY<STRING> の 3 つの列が含まれています。データは次のとおりです。

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 文を使用して、行と列の間でデータを入れ替えることができます。詳細については、「行と列を入れ替えるためのベストプラクティス」をご参照ください。