ORDER BY 句
ORDER BY 句には以下が含まれます:
- 式のリスト、例:
ORDER BY visits, search_phrase SELECT句のカラムを参照する数値のリスト、例:ORDER BY 2, 1、またはALLこれはSELECT句のすべてのカラムを意味し、例:ORDER BY ALL
カラム番号によるソートを無効にするには、設定 enable_positional_arguments を 0 に設定します。 ALL によるソートを無効にするには、設定 enable_order_by_all を 0 に設定します。
ORDER BY 句にはソートの方向を決定する DESC（降順）または ASC（昇順）の修飾子を付けることができます。明示的なソート順が指定されない限り、デフォルトでは ASC が使用されます。ソートの順序は単一の式に適用され、リスト全体には適用されません。例: ORDER BY Visits DESC, SearchPhrase。また、ソートは大文字と小文字を区別します。
ソートされた式の同一の値を持つ行は、任意かつ非決定的な順序で返されます。SELECT 文で ORDER BY 句が省略されると、行の順序も任意かつ非決定的です。
特殊値のソート
NaN と NULL のソート順には二つのアプローチがあります:
- デフォルトまたは
NULLS LAST修飾子を使用する場合: 最初に値、次にNaN、最後にNULL。 NULLS FIRST修飾子を使用する場合: 最初にNULL、次にNaN、その後に他の値。
例
テーブル用のデータ:
クエリ SELECT * FROM t_null_nan ORDER BY y NULLS FIRST を実行すると:
浮動小数点数がソートされるとき、NaN は他の値から分離されます。ソートの順序に関わらず、NaN は最後に来ます。言い換えれば、昇順のソートにおいては他の数字よりも大きいように位置付けられ、一方で降順のソートでは他の数値よりも小さいように位置付けられます。
照合サポート
文字列 値によるソートのために、照合（比較）を指定できます。例: ORDER BY SearchPhrase COLLATE 'tr' - トルコ語アルファベットを使用して、ケースを無視して昇順にキーワードでソートする。COLLATE は、ORDER BY の各式に独立して指定することができます。ASC または DESC が指定されている場合、その後に COLLATE を指定します。COLLATE を使用する場合、ソートは常に大文字と小文字を区別しません。
照合は、LowCardinality、Nullable、Array、および Tuple においてサポートされています。
COLLATE を使用するのは、少ない行数の最終ソートでの使用を推奨します。照合を使用したソートは、バイトによる通常のソートよりも効率が低いためです。
照合の例
文字列 のみを含む例:
入力テーブル:
クエリ:
結果:
Nullable を含む例:
入力テーブル:
クエリ:
結果:
Array を含む例:
入力テーブル:
クエリ:
結果:
LowCardinality 文字列を含む例:
入力テーブル:
クエリ:
結果:
Tuple を含む例:
クエリ:
結果:
実装の詳細
ORDER BY に加えて、小さな LIMIT を指定すると、使用される RAM は減ります。それ以外の場合、消費されるメモリ量はソート対象データの量に比例します。分散クエリ処理の場合、GROUP BY が省略されている場合は、ソートがリモートサーバーで部分的に行われ、結果がリクエスターサーバーでマージされます。これは、分散ソートのために、ソートするデータの量が単一サーバーのメモリ量を超える可能性があることを意味します。
RAM が不足している場合、外部メモリでソートを行うことが可能です（ディスクに一時ファイルを作成します）。この目的のために設定 max_bytes_before_external_sort を使用します。これが 0（デフォルト）に設定されている場合、外部ソートは無効です。有効になっている場合、ソートするデータの量が指定されたバイト数に達した時点で、収集されたデータがソートされ、一時ファイルに書き出されます。すべてのデータが読み込まれた後、すべてのソートされたファイルがマージされ、結果が出力されます。ファイルは設定で /var/lib/clickhouse/tmp/ ディレクトリに書き込まれます（デフォルトですが、tmp_path パラメーターを使用してこの設定を変更できます）。
クエリを実行すると、max_bytes_before_external_sort よりも多くのメモリを使用することがあります。このため、この設定の値は max_memory_usage よりもかなり小さくする必要があります。例として、サーバーに 128 GB の RAM があり、単一のクエリを実行する必要がある場合、max_memory_usage を 100 GB に、max_bytes_before_external_sort を 80 GB に設定します。
外部ソートは RAM 内のソートよりも効率が大幅に低下します。
データ読み取りの最適化
ORDER BY 式がテーブルのソートキーと一致する接頭辞を持っている場合、optimize_read_in_order 設定を使用することでクエリを最適化できます。
optimize_read_in_order 設定が有効になっている場合、ClickHouse サーバーはテーブルインデックスを使用し、ORDER BY キーの順序でデータを読み取ります。特定された LIMIT の場合、すべてのデータを読み取ることを回避できます。そのため、大きなデータに対して小さなリミットのクエリは迅速に処理されます。
最適化は ASC と DESC の両方で機能し、GROUP BY 句および FINAL 修飾子と同時に機能しません。
optimize_read_in_order 設定が無効である場合、ClickHouse サーバーは SELECT クエリの処理中にテーブルインデックスを使用しません。
ORDER BY 句、リミットが大きい LIMIT、そして大量のレコードを読み取る必要がある WHERE 条件を持つクエリを実行する際は、手動で optimize_read_in_order を無効にすることを検討してください。
最適化は次のテーブルエンジンでサポートされています:
MaterializedView エンジンのテーブルでは、最適化は SELECT ... FROM merge_tree_table ORDER BY pk のようなクエリに対して機能します。しかし、SELECT ... FROM view ORDER BY pk のように、ビューのクエリに ORDER BY 句がない場合、サポートされません。
ORDER BY Expr WITH FILL 修飾子
この修飾子は、LIMIT ... WITH TIES 修飾子 と組み合わせることもできます。
WITH FILL 修飾子は ORDER BY expr の後に設定でき、オプションの FROM expr、TO expr、および STEP expr パラメータを指定できます。
欠失した expr カラムのすべての値は順次埋められ、他のカラムはデフォルトの値で埋められます。
複数のカラムを埋めるには、ORDER BY セクションの各フィールド名の後に、オプションのパラメータを持つ WITH FILL 修飾子を追加します。
WITH FILL は、Numeric（すべての種類の float、decimal、int）または Date/DateTime タイプのフィールドに適用できます。String フィールドに適用される場合、欠失した値は空の文字列で埋められます。
FROM const_expr が定義されていない場合、埋める順序は ORDER BY の最小の expr フィールド値を使用します。
TO const_expr が定義されていない場合、埋める順序は ORDER BY の最大の expr フィールド値を使用します。
STEP const_numeric_expr が定義されている場合、const_numeric_expr は numeric タイプに対してそのまま解釈され、Date タイプに対しては days、DateTime タイプに対しては seconds として解釈されます。また、時間と日付の間隔を表す INTERVAL データ型もサポートしています。
STEP const_numeric_expr が省略されると、埋める順序は numeric タイプに対しては 1.0、Date タイプに対しては 1 day、DateTime タイプに対しては 1 second となります。
STALENESS const_numeric_expr が定義されている場合、クエリは元のデータの前の行との違いが const_numeric_expr を超えるまで行を生成します。
INTERPOLATE は ORDER BY WITH FILL に参加していないカラムに適用できます。このようなカラムは、前のフィールドの値に基づいて expr を適用することによって埋められます。expr が存在しない場合は、前の値を繰り返します。省略されたリストは許可されたすべてのカラムを含めることになります。
WITH FILL を使用しないクエリの例:
結果:
WITH FILL 修飾子を適用した後の同じクエリ:
結果:
複数フィールドを持つケースで ORDER BY field2 WITH FILL, field1 WITH FILL の順序は、ORDER BY 句のフィールドの順序に従って埋められます。
例:
結果:
フィールド d1 は埋めずデフォルト値を使用します。これは d2 の値の繰り返しがないため、d1 の順序を正しく計算できないからです。
次の ORDER BY 句のフィールドを変更したクエリ:
結果:
次のクエリでは、columnd1 に対して各データを埋めるために 1 デイの INTERVAL データ型を使用します:
結果:
STALENESS がないクエリの例:
結果:
STALENESS 3 を適用した同じクエリ:
結果:
INTERPOLATE を使用しないクエリの例:
結果:
INTERPOLATE を適用した同じクエリ:
結果:
ソートプレフィックスによる埋め込み
特定のカラムに同じ値を持つ行を独立して埋めるのが有用なことがあります - 時系列データの欠失値を埋める良い例です。 以下のような時系列テーブルがあると仮定します:
各センサーの欠失値を 1 秒間隔で埋めたいとします。
これを実現する方法は、sensor_id カラムを使用して timestamp カラムのソートプレフィックスを指定することです:
ここでは、value カラムが埋められた行を目立たせるために 9999 で補間されました。この動作はデフォルトで有効な設定 use_with_fill_by_sorting_prefix によって制御されます。