Как написать SQL запрос для выборки записей, который учитывает кол-во существующих в таблице записей с таким же ключом?

Question

[eugene159]

Есть таблица Товары (столбцы: ключ товара, поля товара (несколько других неважных столбцов)).
Есть таблица Характеристики (столбцы: ключ характеристики, поля характеристики (несколько других неважных столбцов)).
Есть таблица Товар-Характеристика-ЗначениеХарактеристики (столбцы: ключ товара, ключ характеристики, значение характеристики).

Задача - к одному товару подобрать сопутствующие товары. Это такие товары, у которых значения определенных характеристик точно такие же.

Важный момент! В таблице Товар-Характеристика-ЗначениеХарактеристики может быть несколько строк с одинаковыми ключом товара и ключом характеристики. Таким образом в системе сделана возможность указать список значений какой-либо характеристики. То есть допускается, что у какой-либо характеристики может быть несколько значений.

Как мне выбрать записи из таблицы Товар-Характеристика-ЗначениеХарактеристики, чтобы в них значения характеристик было одинаковым. И количество значений - тоже.

Вот пример.
Есть 2 краски.
У первой характеристика Применение имеет значения: для металла, для дерева, для камня.
У второй характеристика Применение имеет значения: для металла, для дерева.
ЭТО НЕ СОПУТСТВУЮЩИЕ ТОВАРЫ, так как у первой применение шире. То есть такие строки надо как-то избегать.

Как мне написать такой запрос?

Comments

[Danny Arty]

А входные данные какие? Т.е. подобрать для определенного товара по всем характеристикам, или для определенного товара по определенной характеристике, или все сопутствующие товары по определенной характеристике?

[mayton2019]

Ану сделай

SHOW COLUMNS FROM ....

для таблиц Товар-Характеристика-ЗначениеХарактеристики, Товары , Характеристики

[Akina]

Ну обычное же реляционное деление... или, если поиск ведётся для строго одного товара, то фасетный поиск (впрочем, какая разница, ибо первое - один из методов второго).

[eugene159]

Спасибо за попытки помочь. Задача решается за счет поиска в таблице хеша конкатенированных строк (ключей характеристик и их значений), MD5, например.

[Сергей c0re]

бывает )

Answer 1

[Алексей Ваганов]

В песочнице я создал таблицу Товар-Характеристика-ЗначениеХарактеристики и добавил в нее характеристики 3-х товаров. Товары с id=1 и id=3 являются сопутствующими друг другу.

Поиграться можно тут.

Comments

[Сергей c0re]

- кол-во характеристик товара и сами характеристики не известны, берем просто какой-то товар.
- характеристики должны соответствовать все, в том числе и по количеству.

Задача - к одному товару подобрать сопутствующие товары. Это такие товары, у которых значения определенных характеристик точно такие же.

если в третий напихать еще 10к характеристик различных, он все равно выберется, потому что есть 3 совпадающие.

[Алексей Ваганов]

Скорректировал свой запрос, теперь если добавить еще характеристик, то не все равно все будет корректно

[Сергей c0re]

Алексей Ваганов, дополни данными например по продукту 1, добавь в insert
(1,1,7),
(1,1,8),
(1,2,9)

И запрос все равно выберет как соответствующий - продукт 3
Но это неверно, т.к. должно быть строгое соответствие свойств.

[Алексей Ваганов]

Сергей c0re, Если приглядишься к комментам в запросе, то там есть комментарий относительно кол-ва свойств исходного товара. Если кроме добавления 3-х предложенных свойств изменить их кол-во, в запросе, то все работает как надо.

[Сергей c0re]

Алексей Ваганов, а если:

- кол-во характеристик товара и сами характеристики не известны, берем просто какой-то товар (id)

[Алексей Ваганов]

Сергей c0re, это ваши измышления, в вопросе нет такого условия

[Сергей c0re]

Алексей Ваганов, ок

Answer 2

[Сергей c0re]

т.к. набор характеристик может быть совершенно разнообразным, а известен нам только товар (его id), то сопоставлять необходимо по набору характеристик и их кол-ву, т.е. объединяем таблицу саму с собой, из первой выбираем наш продукт и по его набору характеристик соединяем со второй, при этом исключаем исходный товар из второй, как-то так:

WITH param AS (
  SELECT 5 AS prod_id -- ИД продукта которому ищем сопутствующие
)
SELECT p2.prod_id
  FROM param, prod_ch_val p1, prod_ch_val p2
  WHERE p1.prod_id = param.prod_id   -- ИД продукта
    AND p1.ch_id=p2.ch_id
    AND p1.val = p2.val
    AND p1.prod_id != p2.prod_id
    AND (SELECT count(1) FROM prod_ch_val t WHERE t.prod_id=p2.prod_id) = (SELECT count(1) FROM prod_ch_val t WHERE t.prod_id=p1.prod_id)
GROUP BY p2.prod_id

пример
идентичные товары:
1 и 3
2 и 5
4 - не идентичен ни с кем.

PS: выражение WITH можно убрать и подставлять ID товара непосредственно в запрос вместо param.prod_id
сделал для удобства