Lateral Join
列を行に変換するためにLateral Joinを使用する
列を行に変換することは、ETL処理において一般的な操作です。Lateralは、行と内部サブクエリまたはテーブル関数を関連付ける特殊なJoinキーワードです。Lateralをunnest()と組み合わせることで、1つの行を複数の行に展開することができます。詳細については、unnestを参照してください。
制限事項
- 現在、Lateral Joinはunnest()を使用して列を行に変換するためにのみ使用されます。他のテーブル関数やUDTFは後でサポートされる予定です。
- 現在、Lateral Joinはサブクエリをサポートしていません。
Lateral Joinの使用方法
構文:
from table_reference join [lateral] table_reference;
例:
SELECT student, score
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t (score);
SELECT student, score
FROM tests, UNNEST(scores) AS t (score);
ここでの2番目の構文は、Lateralキーワードを省略することができる最初の構文の短縮版です。UNNESTキーワードは、配列を複数の行に変換するテーブル関数です。Lateral Joinと組み合わせることで、一般的な行の展開ロジックを実現することができます。
注意
複数の列でunnestを実行する場合は、各列に別名を指定する必要があります。例えば、
select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test, unnest(v2) t1, unnest(v3) t2;のようにします。
StarRocksは、BITMAP、STRING、ARRAY、およびColumnの間の型変換をサポートしています。
使用例
unnest()と組み合わせることで、次の列から行への変換機能を実現できます:
文字列を複数の行に展開する
-
テーブルを作成し、データを挿入します。
CREATE TABLE lateral_test2 (
`v1` bigint(20) NULL COMMENT "",
`v2` string NULL COMMENT ""
)
DUPLICATE KEY(v1)
DISTRIBUTED BY HASH(`v1`)
PROPERTIES (
"replication_num" = "3",
"storage_format" = "DEFAULT"
);
INSERT INTO lateral_test2 VALUES (1, "1,2,3"), (2, "1,3"); -
展開前のデータをクエリします。
select * from lateral_test2;
+------+-------+
| v1 | v2 |
+------+-------+
| 1 | 1,2,3 |
| 2 | 1,3 |
+------+-------+ -
v2を複数の行に展開します。-- 単一の列に対してunnestを実行します。
select v1,unnest from lateral_test2, unnest(split(v2, ","));
+------+--------+
| v1 | unnest |
+------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
+------+--------+
-- 複数の列に対してunnestを実行します。各操作には別名を指定する必要があります。
select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test2, unnest(split(v2, ",")) t1, unnest(split(v3, ",")) t2;
+------+------+------+
| v1 | v2 | v3 |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 1 | 3 | 2 |
| 2 | 1 | 1 |
| 2 | 1 | 3 |
| 2 | 3 | 1 |
| 2 | 3 | 3 |
+------+------+------+
配列を複数の行に展開する
v2.5以降、unnest()は異なるタイプと長さの複数の配列を受け取ることができます。 詳細については、unnest()を参照してください。
-
テーブルを作成し、データを挿入します。
CREATE TABLE lateral_test (
`v1` bigint(20) NULL COMMENT "",
`v2` ARRAY NULL COMMENT ""
)
DUPLICATE KEY(v1)
DISTRIBUTED BY HASH(`v1`)
PROPERTIES (
"replication_num" = "3",
"storage_format" = "DEFAULT"
);
INSERT INTO lateral_test VALUES (1, [1,2]), (2, [1, null, 3]), (3, null); -
展開前のデータをクエリします。
select * from lateral_test;
+------+------------+
| v1 | v2 |
+------+------------+
| 1 | [1,2] |
| 2 | [1,null,3] |
| 3 | NULL |
+------+------------+ -
v2を複数の行に展開します。select v1,v2,unnest from lateral_test , unnest(v2) ;
+------+------------+--------+
| v1 | v2 | unnest |
+------+------------+--------+
| 1 | [1,2] | 1 |
| 1 | [1,2] | 2 |
| 2 | [1,null,3] | 1 |
| 2 | [1,null,3] | NULL |
| 2 | [1,null,3] | 3 |
+------+------------+--------+
Bitmapデータを展開する
-
テーブルを作成し、データを挿入します。
CREATE TABLE lateral_test3 (
`v1` bigint(20) NULL COMMENT "",
`v2` Bitmap BITMAP_UNION COMMENT ""
)
AGGREGATE KEY(v1)
DISTRIBUTED BY HASH(`v1`);
INSERT INTO lateral_test3 VALUES (1, bitmap_from_string('1, 2')), (2, to_bitmap(3)); -
展開前のデータをクエリします。
select v1, bitmap_to_string(v2) from lateral_test3;
+------+------------------------+
| v1 | bitmap_to_string(`v2`) |
+------+------------------------+
| 1 | 1,2 |
| 2 | 3 |
+------+------------------------+ -
新しい行を挿入します。
insert into lateral_test3 values (1, to_bitmap(3));
select v1, bitmap_to_string(v2) from lateral_test3;
+------+------------------------+
| v1 | bitmap_to_string(`v2`) |
+------+------------------------+
| 1 | 1,2,3 |
| 2 | 3 |
+------+------------------------+ -
v2のデータを複数の行に展開します。select v1,unnest from lateral_test3 , unnest(bitmap_to_array(v2));
+------+--------+
| v1 | unnest |
+------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
+------+--------+