Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

ARRAY JOIN

View as MarkdownEdit this pageReport an issueSet up docs MCP

配列カラムを含むテーブルでは、元のカラム内の各配列要素ごとに 1 行を持つ新しいテーブルを生成し、その際に他のカラムの値を複製するという操作がよく行われます。これは 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 を適用した後、各配列に対して対象を1回だけカウントできるようになります。例:

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 を使用して配列要素を集計する場合に便利です。

この例では、各 goal ID について、コンバージョン数 (ネストされた Goals データ構造の各要素は到達した goal を表し、これをコンバージョンと呼びます) と session 数を計算しています。ARRAY JOIN がなければ、session 数は sum(Sign) として数えられます。しかし、このケースでは行がネストされた Goals 構造によって増やされているため、その後に各 session を 1 回だけ数えるには、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]             │
└───────┴─────────┴───┴─────┴─────────────────────┘

サイズが異なる複数のArrayも、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 などの特定の関数における複雑な式の実行を最適化する機能です。これにより、これらの関数の実行中に、0 による除算のような例外が発生するのを防げます。

arrayJoin は常に実行されるため、短絡関数評価には対応していません。これは、arrayJoin がクエリ分析および実行時に、ほかの関数とは別に処理される特殊な関数であり、短絡関数評価では機能しない追加ロジックを必要とするためです。結果の行数は arrayJoin の結果に依存するため、arrayJoin の遅延実行を実装するのは複雑すぎるうえ、コストも高くなります。

関連コンテンツ

Navigation