COUNT DISTINCT по двум столбцам в SQL: считаем пары клиент–товар

Менеджер хочет узнать, сколько сочетаний «клиент–товар» появилось за месяц. Это помогает оценить ширину покупок: один человек приобрёл три разных товара или трижды заказал один. В таблице лежат позиции заказов, поэтому простой подсчёт строк завышает результат. Но и количество уникальных клиентов отвечает на другой вопрос.

Задача становится особенно неприятной, когда привычный COUNT(DISTINCT client_id, sku) не принимается базой. В SQLite для такого подсчёта удобно сначала получить уникальные строки из двух полей, затем сосчитать их. Заодно становится видно, что именно считается единицей наблюдения.

Почему нельзя просто склеить поля

Возьмём текстовые идентификаторы. Пары ('1', '23') и ('12', '3') различны, но при склейке без разделителя обе дадут 123. Разделитель помогает, пока он никогда не встречается внутри значения. Следом появляются экранирование, обработка пустых строк и правила для NULL. В SQL уже есть операция сравнения целых строк, поэтому придумывать собственный формат ключа здесь незачем.

Ни количество, ни цена позиции в уникальность пары не входят. Если добавить их в SELECT DISTINCT, повторная покупка по новой цене станет новой комбинацией. Сначала сформулируйте объект: в нашем отчёте это один известный клиент и один известный артикул внутри заданного периода.

Запрос, который можно выполнить целиком

WITH lines(client_id, sku, qty) AS (
  VALUES ('1', '23', 1),
         ('1', '23', 2),
         ('12', '3', 1),
         ('1', '3', 1),
         (NULL, '3', 1)
), pairs AS (
  SELECT DISTINCT client_id, sku
  FROM lines
  WHERE client_id IS NOT NULL AND sku IS NOT NULL
)
SELECT COUNT(*) AS unique_pairs FROM pairs;

Результат — 3. Повторная покупка первого клиента не увеличила число сочетаний. Пара второго клиента осталась отдельной, хотя склеенный ключ совпал бы с первой. Строка с неизвестным клиентом не участвует: по ней нельзя установить, новый это покупатель или уже известный.

Такое исключение нужно согласовать с владельцем отчёта. Если считать неизвестного клиента отдельной условной группой, уберите соответствующий фильтр и подпишите показатель. Не подменяйте неизвестный идентификатор нулём без проверки: ноль может оказаться настоящим клиентом.

Как превратить расчёт в рабочий отчёт

При переносе на настоящую таблицу фильтр периода поставьте внутри pairs, до устранения дублей. Иначе вы получите пары за всю историю, хотя заголовок отчёта обещает месяц. Для поля с временем удобно задавать полуоткрытый интервал: от начала месяца включительно до начала следующего исключительно.

Если нужен результат по магазинам, включите store_id в уникальную строку и сгруппируйте внешний запрос по нему. Тогда одна пара, встречавшаяся в двух магазинах, попадёт в оба. Сумма магазинных показателей может превысить общий показатель сети — это следствие выбранного объекта подсчёта. Общий итог следует вычислять отдельно, убирая магазин из ключа.

Сохраните рядом ещё два числа: число строк до DISTINCT и число строк с пропущенным ключом. Они объяснят, почему значение изменилось после исправления выгрузки. Если пары резко выросли, сначала проверьте нормализацию артикулов и идентификаторов. Пробел в конце кода способен создать новый товар с точки зрения сравнения строк.

Где закрепить приём

Начните с задач на GROUP BY, HAVING и агрегаты: там можно проверить, как меняют результат NULL и детализация группировки. Попробуйте перед запуском выписать ожидаемые строки руками.

Следующая рабочая трудность — соединить такой показатель с оплатами и возвратами, сохранив смысл каждой строки. В курсе «Продвинутый SQL» вы разбираете сложные JOIN, причины дублей и неверных сумм, сверку отчётов и цельные задания с проверкой запросов. Откройте программу и выберите блок про объединение источников и агрегацию.