Подбор товара в SQL: как потребовать все обязательные характеристики

Клиенту нужен прибор с Wi-Fi и аккумулятором. Запрос с условием «характеристика входит в список» находит устройства хотя бы с одним из этих свойств. Менеджер получает много вариантов и вручную отбрасывает неподходящие. Проблема не в неполном справочнике: условие отбора отвечает на вопрос «есть что-нибудь из списка», хотя требуется «есть всё из списка».

Сформулировать правило через отсутствующее

Удобно перевернуть проверку: товар подходит, если не существует обязательной характеристики, которой у него нет. Первая проверка перебирает требования, вторая ищет подтверждение конкретного свойства у конкретного товара. Если хотя бы одно требование остаётся без подтверждения, внешняя NOT EXISTS отклоняет товар. Такое выражение называют реляционным делением.

Для примера создадим три товара. A имеет оба свойства, причём аккумулятор записан дважды. B имеет только Wi-Fi. У C вообще нет характеристик. Дубликат специально оставлен во входе: он показывает, почему простого количества строк бывает недостаточно. Две записи Wi-Fi не должны заменять пару разных обязательных свойств.

Пример выполняется в SQLite; функции и синтаксис ниже относятся к этому диалекту.

WITH required(feature) AS (VALUES ('wifi'),('battery')),
products(id) AS (VALUES ('A'),('B'),('C')),
features(product,feature) AS (VALUES ('A','wifi'),('A','battery'),('A','battery'),('B','wifi'))
SELECT p.id FROM products p
WHERE NOT EXISTS (
 SELECT 1 FROM required r
 WHERE NOT EXISTS (
  SELECT 1 FROM features f WHERE f.product=p.id AND f.feature=r.feature
 )
)
ORDER BY p.id;

Разобрать результат по кандидатам

В результате остаётся A. У B внутренний запрос не находит battery, поэтому существует невыполненное требование. У C таких требований два. У A подтверждение есть для каждой строки required; повтор battery ничего не меняет, поскольку EXISTS проверяет сам факт существования. Так запрос сохраняет правильный смысл даже до очистки дублирующего справочника.

Альтернатива — соединить характеристики со списком требований, сгруппировать по товару и сравнить COUNT DISTINCT найденных свойств с количеством уникальных требований. Такой вариант тоже работает при аккуратной обработке дублей и пустого списка. Но двойной NOT EXISTS обычно легче проверить логически: нет ни одного проваленного требования. Выбирайте форму, которую сможет сопровождать команда, и сравнивайте планы на своих данных.

Что означает пустой список

Если клиент пока не выбрал ни одного фильтра, required пуст. Тогда нет невыполненного требования, и запрос возвращает все товары. Для каталога это часто ожидаемо. Если интерфейс обязан требовать хотя бы один параметр, проверка должна стоять на входе поиска; искусственно ломать математический смысл запроса ради этого ограничения неудобно.

NULL не стоит использовать как название неизвестной характеристики. Сравнение обычным равенством с NULL не подтверждает совпадение. Разделите отсутствие информации и конкретное свойство с кодом: например, feature_id должен ссылаться на справочник. Неизвестные значения полезно вывести в контроль качества, иначе товар может ошибочно выпадать из подбора и выглядеть коммерчески неконкурентным.

Когда свойства имеют значения

Для памяти не меньше 16 ГБ и массы не больше 2 кг одной таблицы булевых признаков уже недостаточно. Требование должно включать оператор, единицу и порог. Не храните строку «16 GB» как универсальную числовую характеристику: преобразуйте значение в согласованную единицу и сравнивайте число. Сертификат или совместимость с конкретной платформой, напротив, часто остаются точным категориальным свойством.

В реальном каталоге проверка полноты технических требований обычно предшествует коммерческим ограничениям. Сначала получите подходящие модели, затем присоедините остаток и цену для нужного склада и даты. Если соединение остатков создаёт несколько строк на модель, это уже другой уровень детализации. Не устраняйте их случайным DISTINCT, когда клиенту нужны разные предложения поставщиков.

Для рабочей проверки составьте четыре контрольных кандидата: все свойства, одно отсутствует, лишнее свойство и дубли обязательного свойства. Добавьте пустой набор требований. Индекс по product и feature поможет искать подтверждения, но его пользу оценивают на фактической базе. Основная приёмка остаётся смысловой: ни один выбранный товар не должен иметь невыполненного обязательного условия.

Следующий шаг

Дальше такой подбор можно соединить с историческими ценами и остатками, сохранив правильную детализацию результата. В платном «Продвинутом SQL» вы отработаете сложные соединения, подзапросы и цельные многошаговые задачи с автоматической проверкой. Это полезное продолжение после уверенных SELECT и JOIN. Откройте программу курса.