Звіт показує сто двадцять клієнтів. Керівник каже, що їх приблизно вісімдесят. Обидва мають рацію: у базі справді сто двадцять рядків, і за ними справді вісімдесят людей.
Це не дублі в звичному сенсі. Записи різні, замовлення справжні, нічого не зламано. Проблема в тому, за чим саме запит визначає, що два замовлення належать одній людині.
#Чому ім’я й телефон не годяться
Здається логічним групувати за всім одразу: пошта, ім’я, телефон. Міркування таке: збігається все — отже, одна людина.
Насправді виходить навпаки. Ім’я й телефон у формі оплати вводить сам покупець, щоразу заново. Одна й та сама людина за рік напише:
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 — поведінка роздільника й порядку сортування
- Взято з власного досвіду