mirrors/ClickHouse
0
0 Fork 0
mirror of https://github.com/ClickHouse/ClickHouse.git synced 2026-10-28 16:18:23 +08:00
Code Activity
Files
master
ClickHouse/docs/ja/reference/statements/select/array-join.mdx
T

395 lines
16 KiB
Plaintext
Raw Permalink Blame History

---
description: 'ARRAY JOIN 句のドキュメント'
sidebarTitle: 'ARRAY JOIN'
slug: /sql-reference/statements/select/array-join
title: 'ARRAY JOIN'
doc_type: 'reference'
---
{/*AUTOGENERATED_START*/}
配列カラムを含むテーブルでは、元のカラム内の各配列要素ごとに 1 行を持つ新しいテーブルを生成し、その際に他のカラムの値を複製するという操作がよく行われます。これは `ARRAY JOIN` 句が行う処理の基本的なケースです。
この名前は、配列またはネストされたデータ構造に対して `JOIN` を実行するものと見なせることに由来します。意図としては [arrayJoin](/ja/reference/functions/regular-functions/array-join) 関数に似ていますが、句の機能のほうがより広範です。
<Note>
PostgreSQL の `FROM unnest(...)`、`CROSS JOIN UNNEST(...)`、および `LATERAL unnest(...)` といったテーブルソース形式はサポートされていません。代わりに `ARRAY JOIN` を使用してください。`unnest` という名前 (バージョン 26.5 以降) は `arrayJoin` (`SELECT unnest(arr)`) の関数呼び出し別名であり、テーブル関数ではありません。相関サブクエリに対する `JOIN LATERAL <subquery>` は、実験的機能として別途サポートされています ([`JOIN`](/ja/reference/statements/select/join#lateral-join) を参照)。
</Note>
構文:
```sql
SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...
```
サポートされている `ARRAY JOIN` の種類は以下のとおりです。
* `ARRAY JOIN` - 基本的なケースでは、空のArrayは `JOIN` の結果に含まれません。
* `LEFT ARRAY JOIN` - `JOIN` の結果には、空のArrayを持つ行が含まれます。空の配列の値には、配列の要素型のデフォルト値 (通常は 0、空文字列、または NULL) が設定されます。
## ARRAY JOIN の基本的な例 {#basic-array-join-examples}
### ARRAY JOIN と LEFT ARRAY JOIN {#array-join-left-array-join-examples}
以下の例では、`ARRAY JOIN` 句と `LEFT ARRAY JOIN` 句の使用方法を示します。[Array](/ja/reference/data-types/array) 型のカラムを持つテーブルを作成し、値を挿入してみましょう。
```sql
CREATE TABLE arrays_test
(
s String,
arr Array(UInt8)
) ENGINE = Memory;
INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
```
```response
┌─s───────────┬─arr─────┐
│ Hello │ [1,2] │
│ World │ [3,4,5] │
│ Goodbye │ [] │
└─────────────┴─────────┘
```
次の例では、`ARRAY JOIN`句を使用します。
```sql
SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;
```
```response
┌─s─────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
└───────┴─────┘
```
次の例では、`LEFT ARRAY JOIN` 句を使用します。
```sql
SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;
```
```response
┌─s───────────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
│ Goodbye │ 0 │
└─────────────┴─────┘
```
### ARRAY JOIN と arrayEnumerate 関数 {#array-join-arrayEnumerate}
この関数は通常、`ARRAY JOIN` と組み合わせて使用されます。`ARRAY JOIN` を適用した後、各配列に対して対象を1回だけカウントできるようになります。例:
```sql
SELECT
count() AS Reaches,
countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
GoalsReached,
arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10
```
```text
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
```
この例では、Reaches はコンバージョン数 (`ARRAY JOIN` を適用した後の文字列数) 、Hits はページビュー数 (`ARRAY JOIN` を適用する前の文字列数) を表します。このケースでは、もっと簡単な方法で同じ結果を得ることができます。
```sql
SELECT
sum(length(GoalsReached)) AS Reaches,
count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)
```
```text
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
```
### ARRAY JOIN と arrayEnumerateUniq {#array_join_arrayEnumerateUniq}
この関数は、`ARRAY JOIN` を使用して配列要素を集計する場合に便利です。
この例では、各 goal ID について、コンバージョン数 (ネストされた Goals データ構造の各要素は到達した goal を表し、これをコンバージョンと呼びます) と session 数を計算しています。`ARRAY JOIN` がなければ、session 数は sum(Sign) として数えられます。しかし、このケースでは行がネストされた Goals 構造によって増やされているため、その後に各 session を 1 回だけ数えるには、`arrayEnumerateUniq(Goals.ID)` 関数の値に条件を適用します。
```sql
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
```
```text
┌──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 │
└─────────┴─────────┴────────┘
```
## 別名の使用 {#using-aliases}
`ARRAY JOIN`句では、配列に別名を指定できます。この場合、配列の要素にはその別名でアクセスできますが、配列自体には元の名前でアクセスします。例:
```sql
SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;
```
```response
┌─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` を実行できます。たとえば：
```sql
SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;
```
```response
┌─s───────────┬─arr_external─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ Hello │ 3 │
│ World │ 1 │
│ World │ 2 │
│ World │ 3 │
│ Goodbye │ 1 │
│ Goodbye │ 2 │
│ Goodbye │ 3 │
└─────────────┴──────────────┘
```
複数の配列を `ARRAY JOIN` 句内でカンマ区切りで指定できます。この場合、それらに対して `JOIN` が同時に実行されます (デカルト積ではなく、直和です) 。なお、デフォルトではすべての配列のサイズが同じである必要があります。例:
```sql
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;
```
```response
┌─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](/ja/reference/functions/regular-functions/array-functions#arrayEnumerate) 関数を使用します。
```sql
SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
```
```response
┌─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` を使用すると結合できます。例:
```sql
SELECT s, arr, a, b
FROM arrays_test ARRAY JOIN arr AS a, [['a','b'],['c']] AS b
SETTINGS enable_unaligned_array_join = 1;
```
```response
┌─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-with-nested-data-structure}
`ARRAY JOIN` は、[ネストされたデータ構造](/ja/reference/data-types/nested-data-structures/index)でも使用できます。
```sql
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', [], []);
```
```response
┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello │ [1,2] │ [10,20] │
│ World │ [3,4,5] │ [30,40,50] │
│ Goodbye │ [] │ [] │
└─────────┴─────────┴────────────┘
```
```sql
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;
```
```response
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
```
`ARRAY JOIN` でネストされたデータ構造の名前を指定した場合、その意味は、そのデータ構造を構成するすべての配列要素に対して `ARRAY JOIN` を適用するのと同じです。以下に例を示します。
```sql
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;
```
```response
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
```
この書き方でも問題ありません:
```sql
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;
```
```response
┌─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` の結果とソース配列のどちらを選択するかを指定するために別名を使用できます。例:
```sql
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;
```
```response
┌─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](/ja/reference/functions/regular-functions/array-functions#arrayEnumerate) 関数の使用例:
```sql
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
```
```response
┌─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 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘
```
## 実装の詳細 {#implementation-details}
`ARRAY JOIN` の実行時には、クエリの実行順序が最適化されます。`ARRAY JOIN` はクエリ内で常に [WHERE](/ja/reference/statements/select/where)/[PREWHERE](/ja/reference/statements/select/prewhere) 句より前に指定する必要がありますが、`ARRAY JOIN` の結果をフィルタリングに使用しない限り、技術的にはこれらはどの順序でも実行できます。処理順序はクエリオプティマイザによって制御されます。
### 短絡関数評価との非互換性 {#incompatibility-with-short-circuit-function-evaluation}
[短絡関数評価](/ja/reference/settings/session-settings/short-circuit-function-evaluation#short_circuit_function_evaluation) は、`if`、`multiIf`、`and`、`or` などの特定の関数における複雑な式の実行を最適化する機能です。これにより、これらの関数の実行中に、0 による除算のような例外が発生するのを防げます。
`arrayJoin` は常に実行されるため、短絡関数評価には対応していません。これは、`arrayJoin` がクエリ分析および実行時に、ほかの関数とは別に処理される特殊な関数であり、短絡関数評価では機能しない追加ロジックを必要とするためです。結果の行数は `arrayJoin` の結果に依存するため、`arrayJoin` の遅延実行を実装するのは複雑すぎるうえ、コストも高くなります。
## 関連コンテンツ {#related-content}
* ブログ: [ClickHouseで時系列データと関数を扱う](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse)
{/*AUTOGENERATED_END*/}
View Git Blame Copy Permalink