Внедряя систему управления складом для интернет-магазина, я пытаюсь создать список комплектования для складских рабочих, которые будут ходить по складу, собирая товары по заказам с разных полок.
Один вид товаров может находиться на разных полках, и на каждой полке может быть много товаров одного и того же типа.
Если в одном заказе много одинаковых товаров, иногда сборщику приходится выбирать с нескольких полок, чтобы получить все товары в заказе.
Чтобы еще больше усложнить задачу, иногда товар также заканчивается.
Моя модель данных выглядит так (упрощенно):
CREATE TABLE order_product (
id SERIAL PRIMARY KEY,
product_id integer,
order_id text
);
INSERT INTO "public"."order_product"("id","product_id","order_id")
VALUES
(1,1,'order1'),
(2,1,'order1'),
(3,1,'order1'),
(4,2,'order1'),
(5,2,'order2'),
(6,2,'order2');
CREATE TABLE warehouse_placement (
id SERIAL PRIMARY KEY,
product_id integer,
shelf text,
quantity integer
);
INSERT INTO "public"."warehouse_placement"("id","product_id","shelf","quantity")
VALUES
(1,1,E'A',2),
(2,2,E'B',2),
(3,1,E'C',2);
Возможно ли в postgres сгенерировать список выбора инструкций, подобных следующему:
order_id product_id shelf quantity_left_on_shelf
order1 1 A 1
order1 1 A 0
order1 2 B 1
order1 1 C 1
order2 2 B 0
order2 2 NONE null
В настоящее время я делаю это в коде приложения, но это кажется довольно неуклюжим, и мне почему-то кажется, что должен быть способ сделать это непосредственно в SQL.
Спасибо за любую помощь!
Вот так:
WITH product_on_shelf AS (
SELECT warehouse_placement.*,
generate_series(1, quantity) AS order_on_shelf,
quantity - generate_series(1, quantity) AS quantity_left_on_shelf
FROM warehouse_placement
)
, product_on_shelf_with_product_order AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY quantity, shelf, order_on_shelf
) AS order_among_product
FROM product_on_shelf
)
, order_product_with_order_among_product AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY id
) AS order_among_product
FROM order_product
)
SELECT order_product_with_order_among_product.id,
order_product_with_order_among_product.order_id,
order_product_with_order_among_product.product_id,
product_on_shelf_with_product_order.shelf,
product_on_shelf_with_product_order.quantity_left_on_shelf
FROM order_product_with_order_among_product
LEFT JOIN product_on_shelf_with_product_order
ON order_product_with_order_among_product.product_id = product_on_shelf_with_product_order.product_id
AND order_product_with_order_among_product.order_among_product = product_on_shelf_with_product_order.order_among_product
ORDER BY order_product_with_order_among_product.id
;
Вот идея:
product_on_shelf
, которая аналогична warehouse_placement
, за исключением того, что строки дублируются n раз, где n - количество товара на полке.order_among_product
каждой строке в product_on_shelf
, чтобы каждый объект на полке знал свой порядок среди тех же товаров.order_among_product
каждой строке в order_product
.order_product
мы пытаемся найти товар на полке с таким же order_among_product
. Если мы не можем их найти, значит, мы исчерпали товары на любой полке.Боковое примечание # 1: Снятие продуктов с полок - это одновременное действие. Вы должны убедиться, что со стороны приложения или со стороны БД с помощью интеллектуальных замков, любой продукт на полке можно отнести к одному заказу. Обработка каждой строки product_order
на стороне приложения может быть лучшим вариантом для борьбы с параллелизмом.
Боковое примечание # 2: Я написал этот запрос, используя CTE для ясности. Чтобы повысить производительность, рассмотрите возможность использования подзапросов. Обязательно запустите EXPLAIN ANALYZE
Это прекрасно отвечает на мой вопрос и является действительно хорошим способом решения проблемы. Спасибо огромное, Матье !!
Re. совпадение, для начала будет только один упаковщик, так что это не будет проблемой. Когда нам нужно, чтобы несколько сборщиков работали одновременно, я планировал заблокировать заказы, чтобы у каждого сборщика был эксклюзивный доступ, чтобы выбрать, скажем, 10 заказов одновременно.
Отличное решение, приятное объяснение!