Оператор 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 всегда оказываются в конце. Другими словами, при сортировке по возрастанию они ведут себя так, как будто больше всех остальных чисел, а при сортировке по убыванию — так, как будто меньше всех остальных.
Поддержка collation
Для сортировки по значениям типа String вы можете указать collation (правила сравнения). Пример: ORDER BY SearchPhrase COLLATE 'tr' — сортировка по ключевому слову по возрастанию с использованием турецкого алфавита, без учета регистра, при условии, что строки закодированы в UTF-8. COLLATE может быть указан или не указан для каждого выражения в ORDER BY независимо. Если указано ASC или DESC, то COLLATE указывается после него. При использовании COLLATE сортировка всегда выполняется без учета регистра.
Collation поддерживается для типов LowCardinality, Nullable, Array и Tuple.
Мы рекомендуем использовать COLLATE только для окончательной сортировки небольшого количества строк, так как сортировка с COLLATE менее эффективна, чем обычная сортировка по байтам.
Примеры сравнения строк
Пример только со значениями String:
Входная таблица:
Запрос:
Результат:
Пример с типом данных Nullable:
Входная таблица:
Запрос:
Результат:
Пример с типом Array:
Входная таблица:
Запрос:
Результат:
Пример со строкой типа LowCardinality:
Входная таблица:
Запрос:
Результат:
Пример с типом Tuple:
Запрос:
Результат:
Детали реализации
ОЗУ расходуется меньше, если помимо ORDER BY указано достаточно маленькое значение LIMIT. В противном случае объём используемой памяти пропорционален объёму данных для сортировки. При распределённой обработке запросов, если GROUP BY опущен, сортировка частично выполняется на удалённых серверах, а результаты объединяются на сервере, инициировавшем запрос. Это означает, что при распределённой сортировке объём данных для сортировки может превышать объём памяти одного сервера.
Если ОЗУ недостаточно, сортировку можно выполнять во внешней памяти (с созданием временных файлов на диске). Для этого используйте настройку 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 ГБ.
Внешняя сортировка работает значительно менее эффективно, чем сортировка в ОЗУ.
Оптимизация чтения данных
Если выражение 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 интерпретируется без преобразования для числовых типов, как количество дней (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 (включен по умолчанию).
