DISTINCT

Документация по предложению DISTINCT

Если указан SELECT DISTINCT, в результате запроса останутся только уникальные строки. Иными словами, из каждого набора полностью совпадающих строк в результате останется только одна строка.

Можно указать список столбцов, значения в которых должны быть уникальными: SELECT DISTINCT ON (column1, column2,...). Если столбцы не указаны, учитываются все.

Рассмотрим таблицу:

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

Использование DISTINCT без указания столбцов:

SELECT DISTINCT * FROM t1;

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

Использование DISTINCT с указанными столбцами:

SELECT DISTINCT ON (a,b) * FROM t1;

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

​

DISTINCT и ORDER BY

ClickHouse поддерживает использование предложений DISTINCT и ORDER BY для разных столбцов в одном запросе. Предложение DISTINCT выполняется до предложения ORDER BY.

Рассмотрим таблицу:

┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘

Выборка данных:

SELECT DISTINCT a FROM t1 ORDER BY b ASC;

┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘

Выборка данных при другом направлении сортировки:

SELECT DISTINCT a FROM t1 ORDER BY b DESC;

┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘

Строка 2, 4 была отброшена до сортировки.

Учитывайте эту особенность реализации при составлении запросов.

​

Обработка NULL

DISTINCT работает с NULL так, как будто NULL — это конкретное значение и NULL==NULL. Иными словами, в результатах DISTINCT разные комбинации с NULL встречаются только один раз. Это отличается от обработки NULL в большинстве других случаев.

​

Альтернативы

Того же результата можно добиться, применив GROUP BY к тому же набору значений, что указан в предложении SELECT, не используя при этом агрегатные функции. Однако по сравнению с подходом GROUP BY есть несколько отличий:

• DISTINCT можно использовать вместе с GROUP BY.
• До начала внешнего выполнения запрос без ORDER BY может остановиться сразу, как только прочитает достаточно различных строк для выполнения условия LIMIT.
• До начала внешнего выполнения и при опущенном ORDER BY диапазон LIMIT ... AFTER ... UNTIL без ALL также может остановить запрос после окончания диапазона.
• Блоки данных выводятся по мере обработки до начала внешнего выполнения.

​

DISTINCT во внешней памяти

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

Момент начала сброса на диск определяется двумя настройками:

• max_bytes_before_external_distinct задаёт порог в байтах от общего объёма памяти запроса. Значение по умолчанию — 0 (отключено).
• max_bytes_ratio_before_external_distinct задаёт долю доступной памяти в рамках ограничений сервера или пользователя, измеряемую в начале выполнения. Значение по умолчанию — 0.5; настройка не действует, если ни одно из этих ограничений не применяется.

Если применимы оба порога, используется меньший из них. Чтобы отключить сброс на диск, задайте для обеих настроек значение 0.

max_memory_usage на эту долю не влияет. Чтобы настроить сброс на диск относительно ограничения памяти запроса, задайте абсолютный порог ниже этого ограничения. Например, в этом запросе используется порог сброса 16 MiB при ограничении памяти запроса 256 MiB:

SELECT DISTINCT number % 1000000 AS id
FROM numbers(2000000)
SETTINGS
    max_bytes_before_external_distinct = 16777216,
    max_bytes_ratio_before_external_distinct = 0,
    max_memory_usage = 268435456;

Эти пороговые значения не ограничивают использование памяти. Оставляйте запас для обработки других запросов и для самого сброса на диск. Кроме того, при нехватке памяти сброс на диск может начаться раньше.

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

Когда DISTINCT работает с входными данными, отсортированными по префиксу своих ключей, сброс на диск не выполняется. При этом большая группа строк с одинаковым префиксом всё равно может занимать значительный объём памяти.

Как и в случае с optimize_distinct_in_order, при сбросе на диск может выполняться дедупликация значений с плавающей запятой, которые имеют разные двоичные представления, но равны при сравнении, включая 0.0 и -0.0 или значения NaN с разной полезной нагрузкой.