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、配列、および タプル に対応しています。
COLLATE
は、少数の行の最終ソートにのみ使用することを推奨します。COLLATE
によるソートはバイトによる通常のソートよりも効率が悪いためです。
照合の例
文字列 値のみの例:
入力テーブル:
クエリ:
結果:
Nullable を使用した例:
入力テーブル:
クエリ:
結果:
配列 を使用した例:
入力テーブル:
クエリ:
結果:
LowCardinality 文字列の例:
入力テーブル:
クエリ:
結果:
タプル の例:
クエリ:
結果:
実装の詳細
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
よりも significantly 小さい値を持っている必要があります。例として、サーバに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
を無効にすることを検討してください。
最適化は次のテーブルエンジンでサポートされています。
- MergeTree(マテリアライズドビュー を含む)、
- Merge、
- Buffer
MaterializedView
エンジンテーブルでは、SELECT ... FROM merge_tree_table ORDER BY pk
のようなビューに対して最適化が機能します。ただし、ビュークエリに ORDER BY
句がない場合の SELECT ... FROM view ORDER BY pk
のようなクエリではサポートされません。
ORDER BY 表現 WITH FILL 修飾子
この修飾子は、LIMIT ... WITH TIES 修飾子 と組み合わせて使用することもできます。
WITH FILL
修飾子は ORDER BY expr
の後に設定でき、オプションの FROM expr
、TO expr
および STEP expr
パラメータを使用できます。
expr
カラムの欠落しているすべての値は連続的に埋められ、他のカラムはデフォルト値で埋められます。
複数のカラムを埋めるためには、各フィールド名の後にオプションのパラメータ付き WITH FILL
修飾子を追加します。
WITH FILL
は、Numeric(すべての種類の浮動小数点、デシマル、整数)または Date/DateTime タイプのフィールドに適用できます。String
フィールドに適用される場合、欠落している値は空の文字列で埋められます。
FROM const_expr
が定義されていない場合、埋めの順序は ORDER BY
の最小 expr
フィールド値を使用します。
TO const_expr
が定義されていない場合、埋めの順序は ORDER BY
の最大 expr
フィールド値を使用します。
STEP const_numeric_expr
が定義されている場合、const_numeric_expr
は数値タイプにはそのまま解釈され、Date タイプには days
として、DateTime タイプには seconds
として解釈されます。これは、時間と日付のインターバルを表す INTERVAL データ型もサポートします。
STEP const_numeric_expr
が省略されると、埋めの順序は数値タイプには 1.0
、Date タイプには 1 day
、DateTime タイプには 1 second
が使用されます。
STALENESS const_numeric_expr
が定義されている場合、クエリは前の行からの差が const_numeric_expr
を超えるまで行を生成します。
INTERPOLATE
は、ORDER BY WITH FILL
に参加していないカラムに適用できます。このようなカラムは、前のフィールド値を適用することによって埋められます。expr
が存在しない場合、前の値が繰り返されます。省略されたリストは、許可されているすべてのカラムを含む結果となります。
WITH FILL
を使用しないクエリの例:
結果:
WITH FILL
修飾子を適用した後の同じクエリ:
結果:
複数のフィールド ORDER BY field2 WITH FILL, field1 WITH FILL
のケースでは、埋める順序は ORDER BY
句のフィールドの順序に従います。
例:
結果:
フィールド d1
は埋め込まれず、デフォルト値を使用します。なぜなら、私たちは d2
の値に対して繰り返し値を持っていないため、d1
の順序を正しく計算できないからです。
ORDER BY
のフィールドを変更した次のクエリ:
結果:
次のクエリは、d1
カラムに対して1日の INTERVAL
データ型を使用して、各データを埋めます。
結果:
STALENESS
を使用しないクエリの例:
結果:
STALENESS 3
を適用した後の同じクエリ:
結果:
INTERPOLATE
を使用しないクエリの例:
結果:
INTERPOLATE
を適用した後の同じクエリ:
結果:
ソート接頭辞によるグループ化がされた埋め込み
特定のカラムで同じ値を持つ行を独立して埋めることが便利な場合があります。良い例は、時系列の欠落値を埋めることです。以下のような時系列テーブルを考えます。
各センサーごとに独立して1秒間隔で欠落値を埋めたいと考えています。
これを達成する方法は、sensor_id
カラムをソートの接頭辞として timestamp
カラムを埋めることです:
ここでは、value
カラムは 9999
で補間されており、埋められた行が目立つようにしています。
この動作は、デフォルトで有効な use_with_fill_by_sorting_prefix
設定によって制御されます。