Проверка набора элементов в таблице и пересечение с другой структурой | SQL

В моем магазине я могу продавать товары вместе (пакеты с предопределенным списком товаров). Однако на сайте система показывает мне только коды товаров по отдельности, не сообщает мне, когда продажа была ВМЕСТЕ (в пакетном формате).

У меня есть справочная таблица, в которой показаны коды продуктов и количество, составляющих определенный пакет (который я называю set_products):

WITH ReferenceTable AS (
  SELECT 'set123' AS set_products, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 'set123' AS set_products, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemZ' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemY' AS product_id, 1 AS quantity
)
SELECT * FROM ReferenceTable

В этом примере я знаю, что пакет «set123» состоит из 2 продуктов с кодом «itemA» плюс 1 продукт с кодом «itemB». И пакет «set456» состоит из 1 продукта с кодом «ItemZ» плюс 1 продукт с кодом «ItemY».

В моей таблице продаж у меня есть идентификатор билета (транзакции) и список продуктов, продаваемых в каждом из них.

WITH MyTable AS (
  SELECT 153612 AS ticket_id, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemZ' AS product_id, 3 AS quantity UNION ALL

  SELECT 542652 AS ticket_id, 'itemA' AS product_id, 1 AS quantity UNION ALL
  SELECT 542652 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  
  SELECT 625167 AS ticket_id, 'itemA' AS product_id, 4 AS quantity UNION ALL
  SELECT 625167 AS ticket_id, 'itemB' AS product_id, 2 AS quantity
)
SELECT * FROM MyTable

В этом примере у меня есть 3 разных транзакции. Первый (153612) продавал набор продуктов (2x ItemA + 1x ItemB), но имел дополнительный товар, продаваемый по отдельности (ItemZ). В этом случае ожидаемым результатом будет идентификатор пакета в первых двух строках, но не в третьей.

Вторая транзакция (542652) содержит два элемента, составляющих пакет, но их количество не совпадает, поэтому ожидаемый результат равен NULL.

И, наконец, третья транзакция (625167), которая представляет собой продажу ровно 2 полных пакетов (эти случаи кажутся немного более сложными для идентификации).

Я пытаюсь идентифицировать эти случаи в огромной таблице, где ожидаемым результатом является идентификация кода набора продуктов. Что-то вроде этого:

Я использую BigQuery, но принимаю предложения в любом другом SQL. Большое спасибо!

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
78
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы можете сравнить максимальное/минимальное соотношение продуктов пакета в билете, чтобы получить соответствующие пары пакет/билет. Не уверен, что вы хотите, если продукт в билете участвует более чем в одном соответствующем пакете.

WITH ReferenceTable AS (
  SELECT 'set123' AS set_products, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 'set123' AS set_products, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemZ' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemY' AS product_id, 1 AS quantity ),

),
MyTable AS (
  SELECT 153612 AS ticket_id, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemZ' AS product_id, 3 AS quantity UNION ALL

  SELECT 542652 AS ticket_id, 'itemA' AS product_id, 1 AS quantity UNION ALL
  SELECT 542652 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  
  SELECT 625167 AS ticket_id, 'itemA' AS product_id, 4 AS quantity UNION ALL
  SELECT 625167 AS ticket_id, 'itemB' AS product_id, 2 AS quantity
)
SELECT rt.set_products, mt.ticket_id, max(mt.quantity * 1.0 / rt.quantity) nmbrOfPkgs
from ReferenceTable rt
left join MyTable mt on mt.product_id = rt.product_id
group by mt.ticket_id, rt.set_products
having max(mt.quantity * 1.0 / rt.quantity) = min(mt.quantity * 1.0 / rt.quantity)
   and count(*) = (select count(*) 
              from ReferenceTable rt1  
              where rt1.set_products = rt.set_products)

Один и тот же продукт может находиться в разных упаковках, но СТРУКТУРА упаковок будет разной, поэтому такой проблемы возникнуть не должно... Присланное вами решение возвращает только две строки, однако билет 625167, состоящий из 2 пакета - он возвращает только одну строку - мне нужно, чтобы он возвращал два, потому что есть две продажи пакета...

laosnd 10.01.2023 12:18

Добавлен nbr пакетов, смотрите отредактированный ответ

Serg 10.01.2023 12:24

Я не думаю, что это правильный путь ... Разрешение сработало для этого примера (и это здорово, это вопрос, который я изначально задал, так что большое спасибо!). Однако я просто применяю новый пакет, и результат больше не работает.... ############## WITH ReferenceTable AS ( SELECT 'set123' AS set_products, 'itemA' AS product_id, 2 AS количество UNION ВСЕ ВЫБЕРИТЕ 'set123' КАК set_products, 'itemB' КАК product_id, 1 КАК количество UNION ALL SELECT 'set456' КАК set_products, 'itemZ' КАК product_id, 1 КАК количество UNION ALL SELECT 'set456' КАК set_products, 'itemY' КАК product_id, 1 AS количество ) #############

laosnd 10.01.2023 12:34

.......в дополнение к предыдущему ответу ....... Единственный элемент Z внутри ticketID 153612 уже помечает меня как пакетную продажу (что неправильно).

laosnd 10.01.2023 12:35

Вы правы, запрос также должен учитывать количество элементов. Посмотреть отредактированную версию

Serg 10.01.2023 14:37

Большое спасибо за помощь, это разрешение работало почти для каждого случая, который у меня есть в базе данных. Я уже указал ваш ответ как правильный. Однако у меня есть дополнительный случай, когда правило не применяется правильно, я ценю, если вы можете помочь мне и с этим. Если нет, то это тоже хорошо... Представьте, что пакет set123 состоит из 3x 'ItemA' + 1x 'ItemB' (с в комментарии ниже). В этом случае Ticket_ID '625167' должен быть указан как пакетная продажа + один отдельный товар A + один отдельный товар B. Однако запрос не возвращает это.

laosnd 10.01.2023 16:35

WITH ReferenceTable AS ( SELECT 'bundle123' AS set_products, 'item A' AS product_id, 3 AS number UNION ALL SELECT 'bundle123' AS set_products, 'item B' AS product_id, 1 AS number UNION ALL SELECT 'bundleABC' AS set_products, ' item Y' КАК product_id, 1 КАК количество UNION ALL SELECT 'bundleABC' КАК set_products, 'item Z' КАК product_id, 1 КАК количество )

laosnd 10.01.2023 16:35

Другие вопросы по теме