Как в SQL сгруппировать данные, чтобы не потерять уникальные значения и избежать дублирования?

Что проверяют

Понимаете ли вы, откуда в агрегатах берутся дубли, и умеете ли выбрать инструмент: GROUP BY, DISTINCT, агрегат по уникальным значениям или оконную функцию. Следом часто дают задачу с JOIN, после которого сумма «внезапно» удвоилась.

Как отвечать

Разделите вопрос на три ситуации:

• уникальные значения внутри группы: COUNT(DISTINCT product_id), array_agg(DISTINCT tag) или string_agg(DISTINCT tag, ', ') в PostgreSQL, groupUniqArray в ClickHouse. Строки схлопываются, а уникальные значения сохраняются счётчиком или массивом;
• одна строка на группу целиком: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) и фильтр rn = 1. В PostgreSQL короче через DISTINCT ON (customer_id), в Snowflake, BigQuery и DuckDB через QUALIFY;
• соединение без размножения строк: если к заказам присоединить и позиции, и оплаты, каждая оплата повторится столько раз, сколько позиций в заказе, и SUM(amount) станет больше реального. Решение: агрегировать каждую таблицу до нужного уровня в отдельном CTE и соединять уже агрегаты.

Про DISTINCT и GROUP BY: без агрегатов они дают одинаковый результат, DISTINCT применяется ко всей строке выборки. HAVING фильтрует после группировки, WHERE до неё.

На больших объёмах назовите цену: точный COUNT(DISTINCT) держит в памяти все значения, поэтому в аналитических системах есть приближённые варианты: APPROX_COUNT_DISTINCT в BigQuery, uniq в ClickHouse, approx_count_distinct в Spark.

Где спотыкаются

Лечат задвоение после JOIN через SUM(DISTINCT amount). Результат ломается, как только две разные оплаты совпадут по сумме.

Выбирают колонку, которой нет в GROUP BY и которая не под агрегатом. PostgreSQL выдаст ошибку (если колонка не зависит от первичного ключа из группировки), а MySQL с выключенным ONLY_FULL_GROUP_BY молча вернёт значение из произвольной строки.

Забывают про NULL: COUNT(col) и COUNT(DISTINCT col) их не считают, а GROUP BY собирает все NULL в одну группу. На грязных данных это даёт расхождения с COUNT(*).