Сформировать правильный SQL запрос или поменять структуру таблиц?

Здравствуйте. Пишу сервис для "подготовки" контактов клиентов к 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).

Привожу таблицу с тестовыми данными:
62bc45ca778cf654955146.png

Пытался разными способами получать данные, вот этот самый удачный:
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;
  • Вопрос задан
  • 81 просмотр
Решения вопроса 1
iMedved2009
@iMedved2009
Не люблю людей
select 
category_contact.contact_id, 
LAST_VALUE(category_contact.category_id) over(order by total_points) as category_id
from category_contact
group by category_contact.contact_id;


если версия старая и оконных функций нет - то можно покостылировать
select 
category_contact.contact_id, 
substring_index(group_concat(category_contact.category_id order by total_points desc), ',', 1)
from category_contact
group by category_contact.contact_id


Но помнится у мускула было кажется ограничение на количество group_concat - можно влететь в него. Но помню смутно
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы