Перейти к основному содержимому
Перейти к основному содержимому

Оператор 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. Также сортировка выполняется с учетом регистра.

Строки с идентичными значениями для выражений сортировки возвращаются в произвольном и недетерминированном порядке. Если оператор ORDER BY опущен в операторе SELECT, порядок строк также произвольный и недетерминированный.

Сортировка специальных значений​

Существует два подхода к порядку сортировки NaN и NULL:

  • По умолчанию или с модификатором NULLS LAST: сначала значения, затем NaN, затем NULL.
  • С модификатором NULLS FIRST: сначала NULL, затем NaN, затем другие значения.

Пример​

Для таблицы

Выполните запрос SELECT * FROM t_null_nan ORDER BY y NULLS FIRST, чтобы получить:

Когда числа с плавающей точкой сортируются, NaN отделены от других значений. Независимо от порядка сортировки, NaN располагаются в конце. Другими словами, при сортировке по возрастанию они расположены так, как если бы они были больше всех других чисел, а при сортировке по убыванию — так, как если бы они были меньше остальных.

Поддержка колляции​

Для сортировки по значениям String вы можете указать колляцию (сравнение). Пример: ORDER BY SearchPhrase COLLATE 'tr' - для сортировки по ключевому слову по возрастанию, используя турецкий алфавит, нечувствительно к регистру, предполагая, что строки закодированы в UTF-8. COLLATE может быть указан или нет для каждого выражения в ORDER BY независимо. Если указан ASC или DESC, COLLATE указывается после него. При использовании COLLATE сортировка всегда нечувствительна к регистру.

Колляция поддерживается в LowCardinality, Nullable, Array и Tuple.

Мы рекомендуем использовать COLLATE только для окончательной сортировки небольшого количества строк, так как сортировка с COLLATE менее эффективна, чем обычная сортировка по байтам.

Примеры колляции​

Пример только с String значениями:

Вводная таблица:

Запрос:

Результат:

Пример с Nullable:

Вводная таблица:

Запрос:

Результат:

Пример с Array:

Вводная таблица:

Запрос:

Результат:

Пример со строкой LowCardinality:

Вводная таблица:

Запрос:

Результат:

Пример с Tuple:

Запрос:

Результат:

Подробности реализации​

Используется меньше RAM, если указан достаточно небольшой LIMIT в дополнение к ORDER BY. В противном случае количество памяти, которое будет потрачено, пропорционально объему данных для сортировки. Для распределенной обработки запросов, если GROUP BY опущен, сортировка частично выполняется на удаленных серверах, а результаты объединяются на сервере запросов. Это означает, что для распределенной сортировки объем данных для сортировки может превышать объем памяти на одном сервере.

Если не хватает RAM, можно выполнить сортировку во внешней памяти (создавая временные файлы на диске). Для этой цели используйте параметр max_bytes_before_external_sort. Если он установлен в 0 (по умолчанию), внешняя сортировка отключена. Если она включена, когда объем данных для сортировки достигает указанного числа байтов, собранные данные сортируются и сбрасываются во временный файл. После того как все данные считаны, все отсортированные файлы объединяются, и результаты выводятся. Файлы записываются в каталог /var/lib/clickhouse/tmp/ в конфигурации (по умолчанию, но вы можете использовать параметр tmp_path, чтобы изменить эту настройку).

Выполнение запроса может использовать больше памяти, чем max_bytes_before_external_sort. По этой причине это значение должно иметь значение значительно меньшее, чем max_memory_usage. Например, если у вашего сервера 128 ГБ RAM и вам нужно выполнить один запрос, установите max_memory_usage на 100 ГБ, а max_bytes_before_external_sort на 80 ГБ.

Внешняя сортировка работает значительно менее эффективно, чем сортировка в 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.

Рекомендуется отключить optimize_read_in_order вручную при выполнении запросов, которые имеют оператор ORDER BY, большой LIMIT и условие WHERE, которое требует чтения огромного количества записей перед нахождением запрашиваемых данных.

Оптимизация поддерживается в следующих движках таблиц:

В таблицах движка 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 будут заполнены последовательно, и другие столбцы будут заполнены по умолчанию.

Чтобы заполнить несколько столбцов, добавьте модификатор WITH FILL с необязательными параметрами после каждого имени поля в разделе ORDER BY.

WITH FILL может применяться к полям с числовыми (всеми видами float, decimal, int) или Date/DateTime типами. При применении к полям String отсутствующие значения заполняются пустыми строками. Когда FROM const_expr не определен, последовательность заполнения использует минимальное значение поля expr из ORDER BY. Когда TO const_expr не определен, последовательность заполнения использует максимальное значение поля expr из ORDER BY. Когда STEP const_numeric_expr определен, то const_numeric_expr интерпретируется как есть для числовых типов, как days для типа Date, как seconds для типа DateTime. Он также поддерживает тип данных INTERVAL, представляющий временные и датированные интервалы. Когда STEP const_numeric_expr опущен, последовательность заполнения использует 1.0 для числового типа, 1 day для типа Date и 1 second для типа DateTime. Когда 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:

Результат:

Следующий запрос использует тип данных INTERVAL в 1 день для каждого заполненного данных в столбце d1:

Результат:

Пример запроса без STALENESS:

Результат:

Тот же запрос после применения STALENESS 3:

Результат:

Пример запроса без INTERPOLATE:

Результат:

Тот же запрос после применения INTERPOLATE:

Результат:

Заполнение, сгруппированное по префиксу сортировки​

Полезно заполнять строки, имеющие одинаковые значения в определенных столбцах, независимо - хорошим примером является заполнение отсутствующих значений во временных рядах. Предположим, есть следующая таблица временных рядов:

И мы хотим заполнить отсутствующие значения для каждого датчика независимо с интервалом в 1 секунду. Сделать это можно, используя столбец sensor_id в качестве префикса сортировки для заполнения столбца timestamp:

Здесь столбец value был интерполирован значением 9999, чтобы сделать заполненные строки более заметными. Это поведение контролируется установкой use_with_fill_by_sorting_prefix (включена по умолчанию).

Связанные материалы​