SELECT DISTINCT を指定すると、クエリ結果には一意の行だけが残ります。つまり、結果内で完全に一致する行の組ごとに、1 行だけが残ります。
一意の値を持つ必要があるカラムの一覧は SELECT DISTINCT ON (column1, column2,...) のように指定できます。カラムを指定しない場合は、すべてのカラムが対象になります。
次のテーブルを考えます:
DISTINCT を使用する場合:
DISTINCT を使用する:
DISTINCT と ORDER BY
ClickHouse では、1 つのクエリ内でDISTINCT 句と ORDER BY 句に異なるカラムを指定できます。DISTINCT 句は ORDER BY 句より先に実行されます。
次のテーブルを考えます。
2, 4 はソート前に除外されました。
クエリを記述する際は、この実装上の特性を考慮してください。
NULL の処理
DISTINCT は、NULL を NULL が特定の値であり、NULL==NULL であるかのように扱います。つまり、DISTINCT の結果では、NULL を含む同じ組み合わせは 1 回しか現れません。これは、ほかの多くの文脈での NULL の扱いとは異なります。
代替手段
集約関数を使用しなくても、SELECT 句で指定したものと同じ値の組み合わせに対して GROUP BY を適用すれば、同じ結果を得ることができます。ただし、GROUP BY を使う方法とはいくつか異なる点があります。
DISTINCTはGROUP BYと組み合わせて使用できます。- 外部実行が開始される前であれば、ORDER BY を伴わないクエリは、LIMIT を満たすのに十分な数の異なる行を読み込んだ時点で停止できます。
- 外部実行が開始される前で、かつ
ORDER BYが省略されている場合、ALLを伴わないLIMIT ... AFTER ... UNTILの範囲についても、その範囲が終了した時点でクエリを停止できます。 - データブロックは、外部実行が開始されるまでは、処理されるたびに出力されます。
外部メモリでのDISTINCT
DISTINCT は、メモリに保持しきれないほど大きな一意な値の集合を処理する際に、一時データをディスクへ書き出すことができます。その分ディスクI/Oが増えるため、クエリが遅くなる場合があります。
スピルを開始するタイミングは、次の2つの設定で制御します。
max_bytes_before_external_distinctは、クエリのメモリ使用量合計に対するしきい値をバイト単位で設定します。デフォルトは0(無効) です。max_bytes_ratio_before_external_distinctは、サーバーまたはユーザーの制限下で利用可能なメモリに対する割合を設定します。この値は実行開始時点で測定されます。デフォルトは0.5で、いずれの制限も適用されない場合は効果がありません。
0 にしてください。
max_memory_usage は割合には影響しません。クエリのメモリ制限を基準にスピルを設定するには、その制限より小さい絶対しきい値を指定してください。たとえば次のクエリでは、256 MiBのクエリメモリ制限に対して16 MiBのスピルしきい値を使用しています。
LIMIT が満たされればクエリは早期に完了できます。
いったんスピルが始まると、残りの結果を返す前に入力の残り全体を読み取る必要があります。
クエリに ORDER BY が含まれる場合、それらの結果は要求された順序で返されます。
DISTINCT がそのキーのプレフィックスでソートされた入力を使用する場合、スピルは発生しません。ただし、同じプレフィックスを持つ行のグループが大きい場合は、依然としてかなりのメモリを消費する可能性があります。
optimize_distinct_in_order の場合と同様に、スピルによって、バイナリ表現は異なるが比較上は等しいと判定される浮動小数点値 (0.0 と -0.0 や、ペイロードの異なる NaN 値など) が重複排除されることがあります。