В моем магазине я могу продавать товары вместе (пакеты с предопределенным списком товаров). Однако на сайте система показывает мне только коды товаров по отдельности, не сообщает мне, когда продажа была ВМЕСТЕ (в пакетном формате).
У меня есть справочная таблица, в которой показаны коды продуктов и количество, составляющих определенный пакет (который я называю 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. Большое спасибо!
Вы можете сравнить максимальное/минимальное соотношение продуктов пакета в билете, чтобы получить соответствующие пары пакет/билет. Не уверен, что вы хотите, если продукт в билете участвует более чем в одном соответствующем пакете.
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)
Добавлен nbr пакетов, смотрите отредактированный ответ
Я не думаю, что это правильный путь ... Разрешение сработало для этого примера (и это здорово, это вопрос, который я изначально задал, так что большое спасибо!). Однако я просто применяю новый пакет, и результат больше не работает.... ############## 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 количество ) #############
.......в дополнение к предыдущему ответу ....... Единственный элемент Z внутри ticketID 153612 уже помечает меня как пакетную продажу (что неправильно).
Вы правы, запрос также должен учитывать количество элементов. Посмотреть отредактированную версию
Большое спасибо за помощь, это разрешение работало почти для каждого случая, который у меня есть в базе данных. Я уже указал ваш ответ как правильный. Однако у меня есть дополнительный случай, когда правило не применяется правильно, я ценю, если вы можете помочь мне и с этим. Если нет, то это тоже хорошо... Представьте, что пакет set123 состоит из 3x 'ItemA' + 1x 'ItemB' (с в комментарии ниже). В этом случае Ticket_ID '625167' должен быть указан как пакетная продажа + один отдельный товар A + один отдельный товар B. Однако запрос не возвращает это.
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 КАК количество )
Один и тот же продукт может находиться в разных упаковках, но СТРУКТУРА упаковок будет разной, поэтому такой проблемы возникнуть не должно... Присланное вами решение возвращает только две строки, однако билет 625167, состоящий из 2 пакета - он возвращает только одну строку - мне нужно, чтобы он возвращал два, потому что есть две продажи пакета...