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

Клауза 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 модификатором.

Модификатор WITH FILL может быть установлен после ORDER BY expr с необязательными параметрами FROM expr, TO expr и STEP expr. Все пропущенные значения колонки expr будут заполнены последовательно, а другие колонки будут заполнены по умолчанию.

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

WITH FILL может быть применен для полей с числовыми (всеми видами плавающей запятой, десятичными, целыми) или типами 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 день для типа Date и 1 секунда для типа 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 (включена по умолчанию).

Связанное содержание​