メインコンテンツまでスキップ
メインコンテンツまでスキップ

IN 演算子

IN、NOT IN、GLOBAL IN、および GLOBAL NOT IN 演算子は、その機能が非常に豊富であるため、それぞれ独立して説明されています。

演算子の左側は、単一のカラムまたはタプルです。

例：

SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...

左側がインデックスにある単一のカラムで、右側が定数のセットである場合、システムはクエリを処理するためにインデックスを使用します。

明示的にあまり多くの値をリストしないでください（すなわち、百万以上）。データセットが大きい場合は、一時テーブルに入れてください（たとえば、クエリ処理のための外部データのセクションを参照）、次にサブクエリを使用します。

演算子の右側には、定数式のセット、定数式を含むタプルのセット（上記の例に示す）、またはデータベーステーブルの名前や括弧内のSELECTサブクエリを指定できます。

ClickHouseでは、INサブクエリの左側と右側の型が異なることを許可しています。この場合、右側の値は左側の型に変換され、まるでaccurateCastOrNull関数が右側に適用されたかのようになります。

これは、データ型がNullableになり、変換が行えない場合はNULLが返されることを意味します。

例

クエリ：

SELECT '1' IN (SELECT 1);

結果：

┌─in('1', _subquery49)─┐
│                    1 │
└──────────────────────┘

演算子の右側がテーブルの名前（たとえば、UserID IN users）である場合、これはサブクエリUserID IN (SELECT * FROM users)に相当します。これは、クエリと共に送信される外部データを扱う際に使用します。たとえば、クエリは、フィルタリングされるべき「users」一時テーブルに読み込まれたユーザーIDのセットと共に送信できます。

演算子の右側が、Setエンジンを持つテーブル名である場合（常にRAMにある準備されたデータセット）、データセットは各クエリのために再作成されることはありません。

サブクエリでは、タプルのフィルタリングのために複数のカラムを指定できます。

例：

SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...

IN演算子の左側と右側のカラムは同じ型である必要があります。

IN演算子とサブクエリは、集約関数やラムダ関数を含めて、クエリの任意の部分に現れることがあります。 例：

SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC

┌──EventDate─┬────ratio─┐
│ 2014-03-17 │        1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└────────────┴──────────┘

3月17日以降の各日について、3月17日にサイトを訪れたユーザーによって行われたページビューの割合をカウントします。 IN句のサブクエリは、常に単一のサーバーで1回だけ実行されます。依存するサブクエリは存在しません。

NULL 処理​

要求処理中、IN演算子は、NULLとの演算の結果が、演算子の右側または左側にNULLがあっても、常に0と等しいと仮定します。NULL値はどのデータセットにも含まれず、相互に対応せず、transform_null_in = 0の場合に比較することはできません。

t_nullテーブルの例を示します：

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘

クエリSELECT x FROM t_null WHERE y IN (NULL,3)を実行すると、次の結果が得られます：

┌─x─┐
│ 2 │
└───┘

y = NULLの行がクエリ結果から除外されていることがわかります。これは、ClickHouseがNULLが(NULL,3)セットに含まれるかどうかを判断できず、操作の結果として0を返し、SELECTがこの行を最終出力から除外するためです。

SELECT y IN (NULL, 3)
FROM t_null

┌─in(y, tuple(NULL, 3))─┐
│                     0 │
│                     1 │
└───────────────────────┘

分散サブクエリ​

サブクエリを持つIN演算子には、JOIN演算子と似た2つのオプションがあります：通常のIN / JOINとGLOBAL IN / GLOBAL JOINです。これらは、分散クエリ処理の実行方法が異なります。

注記

以下に示すアルゴリズムは、設定のdistributed_product_mode設定によって異なって動作することがあることを忘れないでください。

通常のINを使用する場合、クエリはリモートサーバーに送信され、それぞれがINまたはJOIN句内のサブクエリを実行します。

GLOBAL IN / GLOBAL JOINを使用する場合、まずすべてのサブクエリはGLOBAL IN / GLOBAL JOINのために実行され、結果が一時テーブルに集約されます。その後、一時テーブルは各リモートサーバーに送信され、それを使用してクエリが実行されます。

非分散クエリの場合は、通常のIN / JOINを使用してください。

分散クエリ処理のためにIN / JOIN句内でサブクエリを使用する際には注意が必要です。

いくつかの例を見てみましょう。クラスター内の各サーバーには通常のlocal_tableがあると仮定します。各サーバーには、クラスター内のすべてのサーバーを参照するDistributedタイプのdistributed_tableテーブルもあります。

distributed_tableへのクエリは、すべてのリモートサーバーに送信され、local_tableを使用して実行されます。

たとえば、次のクエリ：

SELECT uniq(UserID) FROM distributed_table

は、すべてのリモートサーバーに次のように送信されます：

SELECT uniq(UserID) FROM local_table

並行して各サーバーで実行され、途中の結果を組み合わせることができる段階に達するまで進行します。次に、中間的な結果は要求サーバーに戻され、マージされ、最終結果がクライアントに送信されます。

次に、INを使用したクエリを検討します：

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
  • 2つのサイトのオーディエンスの交差点の計算。

このクエリは、すべてのリモートサーバーに次のように送信されます：

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

言い換えれば、IN句内のデータセットは、各サーバーが独立して収集し、各サーバーに保存されているローカルデータのみに対して行われます。

これは、データがクラスターサーバー間でスプレッドされており、単一のUserIDのデータが完全に単一のサーバーに存在する場合に正しくかつ最適に機能します。この場合、すべての必要なデータが各サーバーでローカルに利用可能です。そうでなければ、結果は不正確になります。このバリエーションのクエリを「ローカルIN」と呼びます。

データがクラスターサーバー間でランダムに分散されている場合、クエリが正しく機能するように、サブクエリ内でdistributed_tableを指定することができます。クエリは次のようになります：

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

このクエリは、すべてのリモートサーバーに次のように送信されます：

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

サブクエリは各リモートサーバーで実行され始めます。サブクエリが分散テーブルを使用しているため、各リモートサーバーにあるサブクエリは次のようにすべてのリモートサーバーに再送されます：

SELECT UserID FROM local_table WHERE CounterID = 34

たとえば、100台のサーバーのクラスターがある場合、クエリ全体を実行するには10,000の基本要求が必要になり、これは一般的に受け入れられないと見なされます。

そのような場合には、常にINの代わりにGLOBAL INを使用する必要があります。クエリに対する動作を見てみましょう：

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

要求サーバーはサブクエリを実行します：

SELECT UserID FROM distributed_table WHERE CounterID = 34

結果はRAM内の一時テーブルに置かれます。その後、要求は次のように各リモートサーバーに送信されます：

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1

一時テーブル_data1は、すべてのリモートサーバーにクエリと共に送信されます（一時テーブルの名前は実装により異なります）。

これは、通常のINを使用するよりも最適です。ただし、以下の点に注意してください。

  1. 一時テーブルを作成する際、データはユニークにされません。ネットワーク上で送信されるデータ量を減らすために、サブクエリにDISTINCTを指定してください。（通常のINにはこれを行う必要はありません。）
  2. 一時テーブルはすべてのリモートサーバーに送信されます。送信はネットワークトポロジーを考慮しません。たとえば、要求サーバーに比べて非常に遠いデータセンターに10台のリモートサーバーがある場合、データはリモートデータセンターにチャンネルを介して10回送信されます。GLOBAL INを使用する際には大規模なデータセットを避けるようにしてください。
  3. リモートサーバーへのデータ転送時にネットワーク帯域幅に対する制限は設定できません。ネットワークが過負荷になる可能性があります。
  4. データをサーバー間で分散させて、定期的にGLOBAL INを使用する必要がないようにしてください。
  5. GLOBAL INを頻繁に使用する必要がある場合は、ClickHouseクラスターの位置を計画し、単一のグループのレプリカが1つのデータセンターにのみ存在し、その間に高速ネットワークがあるようにしてください。これにより、クエリが単一のデータセンター内で完全に処理できるようになります。

また、GLOBAL IN句内にローカルテーブルを指定することも意味があります。このローカルテーブルは要求サーバーのみに利用可能であり、リモートサーバーでそれを使用したい場合です。

分散サブクエリと max_rows_in_set​

max_rows_in_setおよびmax_bytes_in_setを使用して、分散クエリ中に転送されるデータ量を制御できます。

これは特に、GLOBAL INクエリが大量のデータを返す場合に重要です。次のSQLを考えてみてください：

SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE <some_predicate>)

some_predicateが十分に選択的でない場合、大量のデータが返され、パフォーマンスの問題を引き起こす可能性があります。そのような場合には、ネットワーク上のデータ転送を制限することが賢明です。また、set_overflow_modeがthrowに設定されているため（デフォルトで）、これらの制限に達した場合に例外が発生します。

分散サブクエリと max_parallel_replicas​

max_parallel_replicasが1より大きい場合、分散クエリはさらに変換されます。

たとえば、次のクエリ：

SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3

は、各サーバーで次のように変換されます：

SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=M

ここで、Mは1と3の間の値であり、ローカルクエリがどのレプリカで実行されているかによって異なります。

これらの設定は、クエリ内のすべてのMergeTreeファミリーテーブルに影響し、各テーブルに対してSAMPLE 1/3 OFFSET (M-1)/3を適用するのと同じ効果があります。

したがって、max_parallel_replicas設定を追加すると、正しい結果は両方のテーブルが同じレプリケーションスキームを持ち、UserIDまたはそのサブキーによってサンプリングされる場合にのみ生成されます。特に、local_table_2にサンプリングキーがない場合は、不正確な結果が生成されます。同じルールがJOINにも適用されます。

local_table_2が要件を満たさない場合の一つの解決策は、GLOBAL INまたはGLOBAL JOINを使用することです。

テーブルにサンプリングキーがない場合、parallel_replicas_custom_keyの柔軟なオプションを使用して、異なるより最適な動作を生成することができます。