配列カラムを含むテーブルに対して一般的な操作は、新しいテーブルを生成し、そのテーブルには最初のカラムの各個別の配列要素ごとに行があり、他のカラムの値は重複します。これがARRAY JOIN句の基本的な動作です。
その名前は、配列またはネストされたデータ構造に対してJOINを実行することとして見ることができることから来ています。意図は arrayJoin 関数に似ていますが、句の機能はより広範です。
構文:
SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...
サポートされているARRAY JOINのタイプは以下の通りです。
ARRAY JOIN - 基本ケースでは、空の配列はJOINの結果に含まれません。LEFT ARRAY JOIN - JOINの結果には空の配列を持つ行が含まれます。空の配列の値は、配列要素の型のデフォルト値（通常は0、空文字列、またはNULL）に設定されます。
基本ARRAY JOINの例
ARRAY JOINおよびLEFT ARRAY JOIN
以下の例は、ARRAY JOINおよびLEFT ARRAY JOIN句の使用法を示しています。Array型カラムを持つテーブルを作成し、値を挿入しましょう:
CREATE TABLE arrays_test
(
s String,
arr Array(UInt8)
) ENGINE = Memory;
INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
┌─s───────────┬─arr─────┐
│ Hello │ [1,2] │
│ World │ [3,4,5] │
│ Goodbye │ [] │
└─────────────┴─────────┘
以下の例は、ARRAY JOIN句を使用しています:
SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;
┌─s─────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
└───────┴─────┘
次の例は、LEFT ARRAY JOIN句を使用しています:
SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;
┌─s───────────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
│ Goodbye │ 0 │
└─────────────┴─────┘
ARRAY JOINおよびarrayEnumerate関数
この関数は通常ARRAY JOINとともに使用されます。ARRAY JOINを適用した後、各配列の数を一度だけカウントすることを可能にします。例:
SELECT
count() AS Reaches,
countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
GoalsReached,
arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
この例では、Reachesはコンバージョンの数（ARRAY JOINが適用された後に受信された文字列）、Hitsはページビューの数（ARRAY JOINの前の文字列）です。この特定のケースでは、より簡単な方法で同じ結果を得ることができます:
SELECT
sum(length(GoalsReached)) AS Reaches,
count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
ARRAY JOINおよびarrayEnumerateUniq
この関数は、ARRAY JOINを使用して配列要素を集約する際に役立ちます。
この例では、各目標IDごとのコンバージョンの数（Goalsのネストデータ構造内の各要素は到達された目標であり、これをコンバージョンと呼びます）とセッションの数を計算しています。ARRAY JOINなしでは、セッションの数はsum(Sign)としてカウントされます。しかし、この特定のケースでは、行がネストされたGoals構造によって乗算されているため、これを行った後に各セッションを一度だけカウントするために、arrayEnumerateUniq(Goals.ID)関数の値に条件を適用します。
SELECT
Goals.ID AS GoalID,
sum(Sign) AS Reaches,
sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
Goals,
arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10
┌──GoalID─┬─Reaches─┬─Visits─┐
│ 53225 │ 3214 │ 1097 │
│ 2825062 │ 3188 │ 1097 │
│ 56600 │ 2803 │ 488 │
│ 1989037 │ 2401 │ 365 │
│ 2830064 │ 2396 │ 910 │
│ 1113562 │ 2372 │ 373 │
│ 3270895 │ 2262 │ 812 │
│ 1084657 │ 2262 │ 345 │
│ 56599 │ 2260 │ 799 │
│ 3271094 │ 2256 │ 812 │
└─────────┴─────────┴────────┘
エイリアスの使用
ARRAY JOIN句で配列のエイリアスを指定することができます。この場合、配列アイテムにはこのエイリアスを介してアクセスできますが、配列自体は元の名前でアクセスされます。例:
SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;
┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2] │ 1 │
│ Hello │ [1,2] │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴─────────┴───┘
エイリアスを使用すると、外部配列とのARRAY JOINを行うことができます。例えば:
SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;
┌─s───────────┬─arr_external─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ Hello │ 3 │
│ World │ 1 │
│ World │ 2 │
│ World │ 3 │
│ Goodbye │ 1 │
│ Goodbye │ 2 │
│ Goodbye │ 3 │
└─────────────┴──────────────┘
ARRAY JOIN句では、複数の配列をカンマで区切って指定できます。この場合、同時にJOINが行われます（直接的な和であり、直積ではありません）。デフォルトでは、すべての配列は同じサイズである必要があります。例:
SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;
┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2] │ 1 │ 1 │ 2 │
│ Hello │ [1,2] │ 2 │ 2 │ 3 │
│ World │ [3,4,5] │ 3 │ 1 │ 4 │
│ World │ [3,4,5] │ 4 │ 2 │ 5 │
│ World │ [3,4,5] │ 5 │ 3 │ 6 │
└───────┴─────────┴───┴─────┴────────┘
以下の例では、arrayEnumerate関数を使用しています:
SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2] │ 1 │ 1 │ [1,2] │
│ Hello │ [1,2] │ 2 │ 2 │ [1,2] │
│ World │ [3,4,5] │ 3 │ 1 │ [1,2,3] │
│ World │ [3,4,5] │ 4 │ 2 │ [1,2,3] │
│ World │ [3,4,5] │ 5 │ 3 │ [1,2,3] │
└───────┴─────────┴───┴─────┴─────────────────────┘
異なるサイズの複数の配列を結合するには、SETTINGS enable_unaligned_array_join = 1を使用します。例:
SELECT s, arr, a, b
FROM arrays_test ARRAY JOIN arr as a, [['a','b'],['c']] as b
SETTINGS enable_unaligned_array_join = 1;
┌─s───────┬─arr─────┬─a─┬─b─────────┐
│ Hello │ [1,2] │ 1 │ ['a','b'] │
│ Hello │ [1,2] │ 2 │ ['c'] │
│ World │ [3,4,5] │ 3 │ ['a','b'] │
│ World │ [3,4,5] │ 4 │ ['c'] │
│ World │ [3,4,5] │ 5 │ [] │
│ Goodbye │ [] │ 0 │ ['a','b'] │
│ Goodbye │ [] │ 0 │ ['c'] │
└─────────┴─────────┴───┴───────────┘
ネストデータ構造を持つARRAY JOIN
ARRAY JOINは、ネストされたデータ構造でも機能します:
CREATE TABLE nested_test
(
s String,
nest Nested(
x UInt8,
y UInt32)
) ENGINE = Memory;
INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello │ [1,2] │ [10,20] │
│ World │ [3,4,5] │ [30,40,50] │
│ Goodbye │ [] │ [] │
└─────────┴─────────┴────────────┘
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
ARRAY JOINでネストデータ構造の名前を指定する際、その意味はそれが構成するすべての配列要素を持つARRAY JOINと同じです。以下の例が挙げられます:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
このバリエーションも意味があります:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;
┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │ 1 │ [10,20] │
│ Hello │ 2 │ [10,20] │
│ World │ 3 │ [30,40,50] │
│ World │ 4 │ [30,40,50] │
│ World │ 5 │ [30,40,50] │
└───────┴────────┴────────────┘
ネストデータ構造にエイリアスを使用することで、JOINの結果または元の配列のいずれかを選択できます。例:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;
┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘
arrayEnumerate 関数を使用した例:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │ 1 │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │ 2 │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │ 1 │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │ 2 │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │ 3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘
実装の詳細
クエリの実行順序は、ARRAY JOINを実行する際に最適化されます。ARRAY JOINは常にWHERE/PREWHERE句の前に指定しなければなりませんが、技術的にはARRAY JOINの結果がフィルタリングに使用されない限り、どの順序でも実行できます。処理の順序はクエリオプティマイザによって制御されます。
短絡評価との互換性の無さ
短絡関数評価は、if、multiIf、and、orなどの特定の関数における複雑な式の実行を最適化する機能です。これは、これらの関数の実行中にゼロ除算などの潜在的な例外が発生するのを防ぎます。
arrayJoinは常に実行され、短絡関数評価に対応していません。これは、クエリの解析と実行中にすべての他の関数とは別に処理される特異な関数であり、短絡関数実行に対して機能する追加のロジックを必要とするためです。理由は、結果の行数がarrayJoinの結果に依存し、arrayJoinを遅延実行することは非常に複雑でコストがかかるからです。
関連コンテンツ
