Отчёт показывает сто двадцать клиентов. Руководитель говорит, что их примерно восемьдесят. Правы оба: в базе действительно сто двадцать строк, и за ними действительно восемьдесят человек.
Это не дубли в привычном смысле. Записи разные, заказы настоящие, ничего не сломано. Проблема в том, по чему именно запрос определяет, что два заказа принадлежат одному человеку.
#Почему имя и телефон не годятся
Кажется логичным группировать по всему сразу: почта, имя, телефон. Рассуждение такое: совпадает всё — значит, один человек.
На деле выходит наоборот. Имя и телефон в форме оплаты вводит сам покупатель, каждый раз заново. Один и тот же человек за год напишет:
ivan@mail.com Иван +380671234567
ivan@mail.com иван 0671234567
ivan@mail.com Иван П. +38 067 123 45 67Группировка по трём полям видит здесь три разные комбинации — значит, трёх клиентов. У каждого по одному заказу, каждый выглядит новым, и ни один отчёт о повторных покупках не работает.
#Что взять за идентификатор
Почту. Её человек вводит, чтобы получить билет, чек или доступ, поэтому она почти всегда одна и та же. И, в отличие от имени, её формат можно проверить.
Правило короткое: группируйте по почте и только по почте, приведя её к нижнему регистру.
SELECT LOWER(o.client_email) AS email,
COUNT(*) AS orders,
SUM(o.total) AS revenue
FROM orders o
GROUP BY LOWER(o.client_email)Ivan@Mail.com и ivan@mail.com — это один и тот же человек. Считает ли их одинаковыми база, зависит от правила сравнения: типовые настройки MySQL регистр игнорируют, Postgres — нет. LOWER() здесь не подстраховка, а способ сделать поведение запроса независимым от настройки, о которой вы не думали.
#Откуда тогда брать имя и телефон
Из последнего заказа. Не из группировки, не «какое попадётся первым» — именно из самого нового, потому что там самые свежие данные. Вот колонка, которую для этого добавляют к запросу выше:
SUBSTRING_INDEX(
GROUP_CONCAT(o.client_name ORDER BY o.created_at DESC SEPARATOR 0x1F),
0x1F, 1
) AS client_nameКонструкция непривычная, но делает ровно то, что нужно: сводит все имена в одну строку в порядке от самого нового и берёт первое. Разделитель здесь — служебный символ, а не запятая, потому что запятая вполне может попасться в самом имени.
#Почему нельзя принимать идентификатор с фронтенда
Отдельное правило, у которого нет исключений.
Если страница передаёт на сервер user_id, этим идентификатором может стать что угодно: человек откроет инструменты разработчика и подставит чужой. Дальше он увидит чужие заказы и чужие документы.
Сервер должен определять клиента сам — из проверенного токена или из сессии. Всё, что пришло из браузера, — это данные для показа, а не для выборки.
#Что делать, если почты нет
Бывает: заказы принимают по телефону или в мессенджере. Тогда идентификатором становится телефон, но не в том виде, в каком его ввели.
Приводите номер к цифрам и берите последние девять. Код страны, плюс и пробелы перестают влиять, а +380 67 123 45 67 и 0671234567 становятся одним человеком. Та же нормализация нужна и при сведении дублей.
#Выводы
- Имя и телефон — это поля формы, они не идентифицируют человека.
- Группируйте по почте, приведённой к нижнему регистру.
- Имя и телефон для карточки берите из последнего заказа.
- Идентификатор с фронтенда не принимайте: клиента определяет сервер.
- Если почты нет — телефон, нормализованный до последних девяти цифр.
#Частые вопросы
#Что делать, если один человек заказывает с двух разных почт?
Тогда в отчёте он будет двумя клиентами, и это честнее, чем альтернатива. Свести их можно вручную или по дополнительному совпадению — например, по нормализованному телефону.
#Не лучше ли завести собственный идентификатор клиента?
Лучше, и в системе с регистрацией так и делают. Но его всё равно нужно к чему-то привязать при первом заказе — и привязывают к почте.
#Что делать с уже испорченным отчётом?
Переписывать в данных ничего не нужно. Ошибка живёт в запросе, а не в базе: исправьте группировку, и история пересчитается правильно с первого дня.
#Источники
- MySQL — GROUP_CONCAT — поведение разделителя и порядка сортировки
- Из собственного опыта