ORDER BY 句には、次のいずれかを指定できます。
- 式のリスト (例:
ORDER BY visits, search_phrase) SELECT句内のカラムを参照する番号のリスト (例:ORDER BY 2, 1)SELECT句のすべてのカラムを意味するALL(例:ORDER BY ALL)
ALL によるソートを無効にするには、設定 enable_order_by_all = 0 を指定します。
ORDER BY 句には、ソート方向を決定する DESC (降順) または ASC (昇順) の修飾子を付けることができます。
明示的にソート順を指定しない場合は、デフォルトで ASC が使用されます。
ソート方向はリスト全体ではなく、個々の式に適用されます。たとえば ORDER BY Visits DESC, SearchPhrase です。
また、ソートでは大文字と小文字が区別されます。
ソート式の値が同じ行は、任意かつ非決定論的な順序で返されます。
SELECT ステートメントで ORDER BY 句を省略した場合も、行の順序は任意かつ非決定論的です。
特殊な値のソート
NaN と NULL のソート順には、2 つの方法があります。
- デフォルト、または
NULLS LAST修飾子を使用する場合: まず値、次にNaN、最後にNULL。 NULLS FIRST修飾子を使用する場合: まずNULL、次にNaN、その後にその他の値。
例
SELECT * FROM t_null_nan ORDER BY y NULLS FIRST を実行します。
照合順序のサポート
ORDER BY SearchPhrase COLLATE 'tr' - これは、文字列が UTF-8 でエンコードされていることを前提に、トルコ語アルファベットを使用し、大文字と小文字を区別せずに、キーワードを昇順でソートすることを意味します。COLLATE は、ORDER BY 内の各 式 に対して個別に指定することも、指定しないこともできます。ASC または DESC を指定する場合は、その後に COLLATE を指定します。COLLATE を使用すると、ソートは常に大文字と小文字を区別しません。
COLLATE は LowCardinality、Nullable、Array、Tuple でサポートされています。
COLLATE を使ったソートは通常のバイト単位のソートよりも効率が低いため、COLLATE は少数の行に対する最終的なソートにのみ使用することを推奨します。
照合順序の例
Query
Response
Query
Response
Query
Response
Query
Response
Response
Query
Response
実装の詳細
ORDER BY に加えて十分に小さい LIMIT を指定すると、使用する RAM を抑えられます。そうでない場合、ソートに必要なメモリ量は、ソート対象のデータ量に比例します。分散クエリ処理では、GROUP BY を省略すると、ソートはリモートサーバー上で部分的に実行され、その結果はリクエスト元のサーバーでマージされます。つまり、分散ソートでは、ソート対象のデータ量が単一サーバーのメモリ量を上回ることがあります。
RAM が不足している場合は、外部メモリを使ってソートを実行できます (ディスク上に一時ファイルを作成します) 。この目的には max_bytes_before_external_sort 設定を使用します。これが 0 (デフォルト) に設定されている場合、外部ソートは無効です。有効にすると、ソート対象のデータ量が指定したバイト数に達した時点で、収集済みのデータがソートされ、一時ファイルにダンプされます。すべてのデータを読み取った後、ソート済みのすべてのファイルがマージされ、結果が出力されます。ファイルは設定内の /var/lib/clickhouse/tmp/ ディレクトリに書き込まれます (デフォルトではこの場所ですが、tmp_path パラメータを使って変更できます) 。また、クエリがメモリ制限を超えた場合にのみディスクへのスピルを使用することもできます。たとえば、max_bytes_ratio_before_external_sort=0.6 とすると、クエリがメモリ制限 (ユーザー/サーバー) の 60% に達した時点でのみディスクへのスピルが有効になります。
クエリの実行では、max_bytes_before_external_sort より多くのメモリを使用することがあります。そのため、この設定値は max_memory_usage より十分に小さくする必要があります。たとえば、サーバーに 128 GB の RAM があり、単一のクエリを実行する必要がある場合は、max_memory_usage を 100 GB、max_bytes_before_external_sort を 80 GB に設定します。
外部ソートは、RAM 内でのソートに比べて大幅に効率が低下します。
Data の読み取りの最適化
ORDER BY 式にテーブルの sorting key と一致するプレフィックスがある場合は、optimize_read_in_order 設定を使用してクエリを最適化できます。
optimize_read_in_order 設定が有効な場合、ClickHouse server はテーブルの索引を使用し、ORDER BY キーの順序でデータを読み取ります。これにより、LIMIT が指定されている場合に、すべてのデータを読み取らずに済みます。そのため、少ない limit で大規模なデータを対象とするクエリは、より高速に処理されます。
この最適化は ASC と DESC の両方で機能しますが、GROUP BY 句および FINAL 修飾子 とは併用できません。
optimize_read_in_order 設定が無効な場合、ClickHouse server は SELECT クエリの処理中にテーブルの索引を使用しません。
ORDER BY 句があり、LIMIT が大きく、さらに目的のデータが見つかるまでに大量のレコードを読み取る必要がある WHERE 条件を含むクエリを実行する場合は、optimize_read_in_order を手動で無効にすることを検討してください。
この最適化は、次の table engines でサポートされています。
- MergeTree (materialized views を含む) 、
- Merge、
- Buffer
MaterializedView-engine テーブルでは、この最適化は SELECT ... FROM merge_tree_table ORDER BY pk のような view で機能します。ただし、view のクエリに ORDER BY 句がない場合、SELECT ... FROM view ORDER BY pk のようなクエリではサポートされません。
ORDER BY Expr WITH FILL 修飾子
WITH FILL 修飾子は、必要に応じて FROM expr、TO expr、STEP expr パラメータを指定して、ORDER BY expr の後に記述できます。
expr カラムの欠落している値はすべて順次補完され、他のカラムにはデフォルト値が設定されます。
複数のカラムを補完するには、ORDER BY 句の各フィールド名の後に、必要に応じてオプションのパラメータ付きで WITH FILL 修飾子を追加します。
Query
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 は数値型では as is、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 を適用して直前のフィールド値に基づいて補完されます。expr が指定されていない場合は、直前の値が繰り返されます。リストを省略すると、使用可能なすべてのカラムが対象になります。
WITH FILL を使用しないクエリの例:
Query
Response
WITH FILL 修飾子を適用した同じクエリ:
Query
Response
ORDER BY field2 WITH FILL, field1 WITH FILL の場合、補完の順序は ORDER BY 句内のフィールドの並び順に従います。
例:
Query
Response
d1 は補完されず、デフォルト値が使用されます。これは、d2 の値が繰り返されていないため、d1 のシーケンスを適切に計算できないからです。
以下は、ORDER BY のフィールドを変更したクエリです。
Query
Response
d1 の各補完データに対して、1 日の INTERVAL データ型を使用します:
Query
Response
STALENESS を指定しないクエリの例:
Query
Response
STALENESS 3 を適用した後の同じクエリ：
Query
Response
INTERPOLATE を使用しないクエリの例:
Query
Response
INTERPOLATE 適用後の同じクエリ:
Query
Response
ソートプレフィックスごとにグループ化して補完する
timestamp のソートプレフィックスとして sensor_id カラムを使用します。
value カラムを 9999 で補間しています。
この動作は use_with_fill_by_sorting_prefix の設定で制御されます (デフォルトで有効です)
- ブログ: ClickHouseで時系列データを扱う