Клаузула 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, если дополнительно к ORDER BY указан достаточно малый LIMIT. В противном случае объем потребляемой памяти пропорционален объему данных для сортировки. Для распределенной обработки запросов, если 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 ГБ оперативной памяти, и вам нужно выполнить один запрос, установите 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, требующее чтения огромного количества записей до нахождения запрашиваемых данных.
Оптимизация поддерживается в следующих движках таблиц:
- MergeTree (включая материализованные представления),
- Merge,
- Buffer
В таблицах движка 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 интерпретируется как есть для числовых типов, как дни для типа 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 (включена по умолчанию).
