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

Оператор 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:

Запрос:

Результат:

Детали реализации​

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

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

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

Внешняя сортировка работает значительно менее эффективно, чем сортировка в оперативной памяти.

Оптимизация чтения данных​

Если выражение 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 modifier.

Модификатор 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 интерпретируется как таковое для числовых типов, как дни для типов Date, как секунды для типов 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 (включено по умолчанию).

Связанный контент​