COUNT(DISTINCT) in SQL: Counting Unique Values and Its Cost

COUNT(DISTINCT col) выглядит как простая метрика, но в аналитике это один из самых дорогих вопросов к базе. «Сколько уникальных пользователей купили», «сколько стран было в заказах», «сколько уникальных пар пользователь-страна» — все эти задачи требуют не просто посчитать строки, а убрать повторы.

Базовый подсчёт уникальных значений

COUNT(*) считает строки. COUNT(DISTINCT ...) сначала строит множество разных значений, а уже потом считает размер этого множества.

-- Total order rows
SELECT COUNT(*) FROM orders;

-- How many distinct customers actually ordered
SELECT COUNT(DISTINCT user_id) FROM orders;

-- Distinct countries among registered users
SELECT COUNT(DISTINCT country) FROM users;

Поведение, на котором часто строятся отчёты:

• NULL не считается отдельным значением: COUNT(DISTINCT col) его игнорирует;
• с GROUP BY функция считает уникальные значения внутри каждой группы.

SELECT status, COUNT(DISTINCT user_id) AS unique_buyers
FROM orders
GROUP BY status;

Для бизнес-метрик это обычно именно то, что нужно: не «сколько заказов», а «сколько разных покупателей участвовало в каждом статусе».

Почему это дорого на масштабе

Обычный счётчик строк можно выполнять потоково. COUNT(DISTINCT) обязан помнить уже встреченные значения: через сортировку или через хеш-таблицу. Чем выше кардинальность, тем больше памяти и временных файлов.

• Индекс по user_id не превращается автоматически в готовый ответ, особенно после фильтров и джойнов.
• Если уникальных значений миллионы, рабочая структура может не влезть в work_mem, и PostgreSQL уйдёт на диск.
• Несколько COUNT(DISTINCT) в одном запросе обычно считаются как несколько отдельных дедупликаций.

-- Each DISTINCT is computed independently -> heavy
SELECT
  COUNT(DISTINCT user_id)  AS buyers,
  COUNT(DISTINCT country)  AS countries
FROM orders o
JOIN users u ON u.id = o.user_id;

Грабли по диалектам: в MySQL COUNT(DISTINCT a, b) валиден. В PostgreSQL такой формы нет; используйте кортеж ROW(...) или отдельный подзапрос с GROUP BY.

DISTINCT по нескольким столбцам

Когда уникальность задаётся парой или тройкой колонок, в PostgreSQL удобно считать distinct по кортежу.

-- Unique (user, country) pairs in PostgreSQL
SELECT COUNT(DISTINCT (user_id, country)) AS uniq_pairs
FROM orders o
JOIN users u ON u.id = o.user_id;

Более явная альтернатива — сначала получить уникальные строки, потом посчитать их.

SELECT COUNT(*) AS uniq_pairs
FROM (
  SELECT DISTINCT user_id, country
  FROM orders o
  JOIN users u ON u.id = o.user_id
) t;

Не склеивайте значения строкой вроде user_id || '-' || country, если можно не склеивать. Коллизии на разделителях, разные приведения типов и пустые значения легко дадут неверный счёт. Кортеж или подзапрос читаются скучнее, зато не ломают данные.

DISTINCT внутри других агрегатов

DISTINCT можно применять не только в COUNT, но и внутри SUM, AVG, array_agg, string_agg и других агрегатов.

-- Sum of distinct salaries per department (dedupes equal salaries first)
SELECT dept, SUM(DISTINCT salary) AS sum_unique_salaries
FROM employees
GROUP BY dept;

-- Comma-separated list of distinct departments per manager
SELECT manager_id, string_agg(DISTINCT dept, ', ') AS depts
FROM employees
GROUP BY manager_id;

Но это не «удалить дубли в отчёте» на автомате. SUM(DISTINCT salary) складывает разные размеры зарплат, а не зарплаты разных сотрудников. Две выплаты по 1000 схлопнутся в одну. Используйте DISTINCT внутри агрегата только там, где одинаковые значения действительно являются повтором одной и той же сущности.

Приближённые альтернативы: APPROX и HLL

На миллиардах строк точный COUNT(DISTINCT) иногда экономически не нужен. Для дашборда трендов обычно достаточно оценки с погрешностью в 1-2%, зато в фиксированной памяти. Классический инструмент — HyperLogLog.

• ClickHouse: uniq() и uniqHLL12() дают приближённую оценку, uniqExact() — точный аналог COUNT(DISTINCT).
• BigQuery и Snowflake: APPROX_COUNT_DISTINCT(col).
• PostgreSQL в ядре такого агрегата не имеет, но есть расширение postgresql-hll.

-- ClickHouse: fast approximate distinct
SELECT uniq(user_id) AS approx_buyers FROM orders;

-- BigQuery / Snowflake style
SELECT APPROX_COUNT_DISTINCT(user_id) AS approx_buyers FROM orders;

Сильная сторона HLL — сливаемость. Можно хранить дневные скетчи и получать уникальных пользователей за месяц без повторного прохода по сырым событиям. Для биллинга и юридической отчётности оставляйте точный COUNT(DISTINCT); для мониторинга продукта часто честнее выбрать быструю оценку и явно указать её погрешность. COUNT(DISTINCT) часто становится узким местом не потому, что база «плохо считает», а потому что вопрос действительно тяжёлый. Точный ответ требует помнить множество уже встреченных значений. На сырых событиях с миллиардами строк это может быть дороже всех остальных метрик отчёта вместе взятых, особенно если таких счётчиков несколько и каждый смотрит на свой столбец.

Хорошая практика — заранее разделять точные и оценочные сценарии. Для расчётов денег, лимитов, юридической отчётности и дедупликации сущностей нужен точный результат. Для графика активной аудитории, мониторинга воронки или сравнения недель часто достаточно приближённой оценки, если погрешность известна и стабильна. Самая дорогая ошибка — молча заменить одно другим без подписи в витрине. Перед оптимизацией COUNT(DISTINCT) полезно уточнить кардинальность и зерно данных. Уникальные пользователи по событиям, по заказам и по дневной витрине — разные запросы с разной ценой. Иногда достаточно предварительно дедуплицировать данные на более раннем этапе ETL и считать уже меньшую таблицу. Это не меняет SQL-функцию, но радикально меняет объём работы, которую ей приходится делать.