Здравствуйте. Пишу сервис для "подготовки" контактов клиентов к email-рассылкам. Менеджеры компании хотят получать список всех контактов по определенной категории. Контакты и категории контактов приходят из CRM, обрабатываются и должны прилететь в базу. Для этих целей существует три таблицы: contacts, categories, category_contact.
Таблица contacts:
id,
contact_id (из CRM),
name,
email;
Таблица categories:
id,
category_id (из CRM),
name_category;
Таблица category_contact:
id,
contact_id,
category_id,
total_points.
Когда в базу загружаются новые контакты, работает две таблицы:
1. contacts (сюда попадают контакты);
2. category_contact. Когда сюда попадает контакт, у него берем id контакта и id всех категорий, в которых он совершил покупки. Если у контакта 2 разные категории заказов, то создается две записи с этими категориями. Если в таблице уже есть такой контакт, то к категории, в которой он приобрел товар прибавляется единица.
Возникла проблема с таблицей
category_contact. Извне будет прилетать только id категории, которая нужна, а SQL должен определить к какой категории относится контакт. Категория контакта определяется максимальным количеством покупок в категории (total_points). То есть если у контакта три категории с total_points 2, 0 и 8 соответственно, то категория этого контакта - 3 (с total_points 8).
Привожу таблицу с тестовыми данными:
Пытался разными способами получать данные, вот этот самый удачный:
SELECT *, MAX(total_points) as MAX FROM `category_contact` WHERE category_id = 2 && total_points <> 0 GROUP BY contact_id;
Возможно, стоит переделать структуру таблиц. Рассмотрю все идеи, заранее спасибо, коллеги.
UPD: добавляю экспорт таблицы в SQL
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Структура таблицы `category_contact`
--
CREATE TABLE `category_contact` (
`id` bigint(20) UNSIGNED NOT NULL,
`contact_id` bigint(20) UNSIGNED NOT NULL,
`category_id` tinyint(3) UNSIGNED NOT NULL,
`total_points` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Дамп данных таблицы `category_contact`
--
INSERT INTO `category_contact` (`id`, `contact_id`, `category_id`, `total_points`) VALUES
(1, 0, 4, 17),
(2, 0, 5, 21),
(3, 2, 2, 24),
(4, 3, 1, 12),
(5, 3, 5, 9),
(6, 5, 1, 5),
(7, 5, 2, 11),
(8, 5, 5, 20),
(9, 6, 0, 24),
(10, 6, 3, 15),
(11, 6, 5, 6),
(12, 7, 1, 22),
(13, 7, 4, 15),
(14, 8, 0, 11),
(15, 9, 2, 15),
(16, 10, 0, 16),
(17, 10, 1, 5),
(18, 10, 5, 12),
(19, 11, 0, 16),
(20, 11, 2, 18),
(21, 11, 3, 15),
(22, 11, 4, 4),
(23, 12, 4, 20),
(24, 12, 5, 21),
(25, 13, 1, 20),
(26, 13, 3, 9),
(27, 13, 4, 11),
(28, 14, 1, 4),
(29, 14, 2, 7),
(30, 14, 3, 9),
(31, 15, 3, 22),
(32, 16, 5, 6),
(33, 17, 0, 6),
(34, 17, 4, 6),
(35, 18, 2, 15),
(36, 18, 2, 22),
(37, 18, 3, 5),
(38, 18, 4, 3),
(39, 19, 0, 11),
(40, 19, 1, 8);
--
-- Индексы таблицы `category_contact`
--
ALTER TABLE `category_contact`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT для таблицы `category_contact`
--
ALTER TABLE `category_contact`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41;
COMMIT;
Простой
