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

Question

[Вадим Мурашкин]

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

Comments

[Дмитрий]

А запрос то какой переделывать надо?

З.Ы. А то что у вас contact_id и category_id эпизодически равны нулю, это норм?

[Вадим Мурашкин]

Дмитрий, действительно забыл вставить запрос, которым пытался получить данные :)

SELECT *, MAX(total_points) as MAX FROM `category_contact` WHERE category_id = 2 && total_points <> 0 GROUP BY contact_id;

По поводу нулей в таблице: это тестовые данные, но даже для боевой базы это норма) с нулей индексируются контакты и категории в CRM

[Антон Антон]

Добавить в contacts поле main_category_id и обновлять при необходимости

[Вадим Мурашкин]

Антон Антон, думал о таком, но все равно остается проблема выявления этой самой main_category_id

[Akina]

Вы бы бы указали точно свою СУБД, включая точную версию, что ли. Потому как задача решается по щелчку пальцев оконной функцией в CTE - но поддерживает ли всё это СУБД?

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

И чё нам с этой "весёлой картинкой" делать? Выложи всё то же, но в виде кода CREATE TABLE + INSERT INTO.

если у контакта три категории с total_points 2, 0 и 8 соответственно, то категория этого контакта - 3 (с total_points 8).

А если 5,1,5? первая? третья? обе? что-то ещё?

[Вадим Мурашкин]

Akina, СУБД: 10.4.24-MariaDB. CREATE TABLE + INSERT INTO добавил к вопросу.
Если количество "баллов" категорий совпадает, то должна выводиться только та, которую запросили (извне прилетает ID категории category_id).

[Akina]

Вадим Мурашкин,

Если количество "баллов" категорий совпадает, то должна выводиться только та, которую запросили (извне прилетает ID категории category_id).

Так тут ещё и параметр затесался? тогда вообще каша и никакой ясности...

В общем, глянь для затравочки: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=77a...
Для каждого юзера получена "максимальная" категория, и помечено, совпадает она с запрошенной или нет.

[Вадим Мурашкин]

Akina, про параметр я писал в вопросе:

Извне будет прилетать только id категории, которая нужна, а SQL должен определить к какой категории относится контакт.

. Посмотрел db fiddle, спасибо. Это почти то, что нужно. Если хотите, можете продублировать сообщение в ответ, я помечу как решение.

[Akina]

Вадим Мурашкин, угу, писал... вот только КАКОЙ контакт? Тебе ж прилетел только ид категории...

Answer 1

[Дмитрий]

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 - можно влететь в него. Но помню смутно

Comments

[Вадим Мурашкин]

Лаконичное решение, огромное спасибо!