Как в SQL считать уникальные пары значений при повторяющихся user_id и device_id?

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

Умеете ли вы считать уникальность по комбинации колонок, а не по каждой отдельно, и знаете ли, как ведут себя разные диалекты и NULL. Следом обычно просят посчитать то же самое по дням или найти пользователей с несколькими устройствами.

Как отвечать

Переносимый вариант работает в любой СУБД:

SELECT COUNT(*) FROM (SELECT DISTINCT user_id, device_id FROM events) t;

GROUP BY user_id, device_id в подзапросе даёт то же самое. Короткие формы зависят от диалекта:

• MySQL, Spark SQL, Hive: COUNT(DISTINCT user_id, device_id) с несколькими аргументами;
• PostgreSQL: так нельзя, но работает конструктор строки COUNT(DISTINCT (user_id, device_id));
• ClickHouse: uniqExact(user_id, device_id) для точного счёта и uniq для приблизительного.

На миллиардах строк точный счёт дорог, и там берут приблизительные функции на HyperLogLog: approx_count_distinct в Spark, APPROX_COUNT_DISTINCT в BigQuery, approx_distinct в Trino. Для дашборда небольшой погрешности обычно хватает.

Сразу оговорите NULL. COUNT(DISTINCT a, b) в MySQL пропускает строки, где хотя бы одно значение пустое, а SELECT DISTINCT считает (1, NULL) отдельной парой. Результаты разойдутся, и нужно решить, что считается парой по бизнес-смыслу.

Типовые продолжения: пары по дням через GROUP BY event_date, пользователи с несколькими устройствами через GROUP BY user_id HAVING COUNT(DISTINCT device_id) > 1, доля таких пользователей среди всех.

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

Склеивают колонки через CONCAT(user_id, device_id) без разделителя. Пары (1, 23) и (12, 3) дают одну строку 123, а NULL в конкатенации в ряде СУБД превращает в NULL всё значение.

Пишут COUNT(DISTINCT user_id) + COUNT(DISTINCT device_id) или перемножают эти числа. К количеству пар это отношения не имеет.

Пытаются написать COUNT(DISTINCT ...) OVER (...). В PostgreSQL и Spark DISTINCT в оконных функциях не поддерживается, нужен подзапрос, а в Spark ещё выручает size(collect_set(...)).