SQL-запрос, включающий сравнение наборов
Задний план
Товары могут продаваться комплектами. Присутствуют следующие таблицы: products, bundles, bundles_products, orders, orders_products.
Можно сказать, что заказ «содержит» набор, если он содержит все продукты пакета.
Проблема
Как можно подсчитывать заказы на пакеты?
Пример
Таблица продукты
id name
1 broom
2 mug
3 spoon
4 candle
Таблица связки
id name
1 dining
2 witchcraft
Таблица наборы_продуктов
bundle_id product_id
1 2
1 3
2 1
2 4
Таблица заказы_продукты
order_id product_id
1000 1
1000 3
1001 1
1001 2
1001 3
Запрос вернет следующую таблицу:
bundle orders
dining 1
witchcraft 0
Примечания
В примере намеренно отсутствует таблица orders, так как не имеет значения, что она содержит.
Конечно, к этому можно было бы подойти императивно, написав некоторый код и собрав данные, но я надеялся, что есть декларативный SQL-способ запросов для таких вещей?
Одна из моих идей заключалась в том, чтобы использовать GROUP_CONCAT, чтобы объединить все продукты в наборе и каким-то образом сравнить это с продуктами каждого заказа. Тем не менее, далеко до ясности.
отмеченный. предоставление примера.
Ответы 2
Вот краткий пример, в котором отсутствуют некоторые части, которые я пропустил, потому что не знаю точной структуры базы данных. Логика такая:
- Генерируется временная таблица, состоящая из 3-х строк - порядок, количество продукты, связанные с набором, количество продуктов в наборе
- Затем мы выбираем из этой таблицы только те заказы, в которых у нас есть те два последних переменные равны
select count(order_id) from orders
left join(
select count(*) from bundles_products as bundle_amount,
sum(case when orders_products in (
select names from bundles_products where bundle_id='1') then 1 else 0) as order_total,
orders.order_id
left join product on bundle_products.product_id = products.product_id
left join orders on products.product_id = orders_products.product_id
where bundle_products.bundle_id ='1'
) as my_table
on orders.order_name = my_table.orders
where my_table.bundle_amount = my_table.order_total
Обновлено: я разместил это как ответ на предыдущую версию вопроса без подробного объяснения.
Edit2: немного исправлен запрос. Это может быть отправной точкой. Логика остается той же, вы можете получить количество заказов для каждого bundle_id, используя его.
Один из способов — использовать два Производные таблицы (подзапросы). В первом подзапросе мы получим общее количество уникальных продуктов для каждого пакета. Во втором подзапросе мы получим общее количество продуктов в заказе для комбинации заказа и пакета.
Мы будем LEFT JOIN их bundle_id, а также сопоставим общее количество продуктов в наборе в них. В конце концов, мы выполним группировку по пакетам и подсчитаем количество успешно совпадающих заказов.
SELECT dt1.id AS bundle_id,
dt1.name AS bundle,
Count(dt2.order_id) AS orders
FROM (SELECT b.id,
b.name,
Count(DISTINCT bp.product_id) AS total_bundle_products
FROM bundles AS b
JOIN bundles_products AS bp
ON bp.bundle_id = b.id
GROUP BY b.id,
b.name) AS dt1
LEFT JOIN (SELECT op.order_id,
bp.bundle_id,
Count(DISTINCT op.product_id) AS order_bundle_products
FROM orders_products AS op
JOIN bundles_products AS bp
ON bp.product_id = op.product_id
GROUP BY bp.bundle_id,
op.order_id) AS dt2
ON dt2.bundle_id = dt1.id
AND dt2.order_bundle_products = dt1.total_bundle_products
GROUP BY dt1.id,
dt1.name
Образцы данных и желаемые результаты помогут. Как обрабатываются перекрытия между пакетами (скажем, «A», «B» и «C» находятся в одном пакете, а «A» и «B» — в другом).