Комплектация заказа на складе

Внедряя систему управления складом для интернет-магазина, я пытаюсь создать список комплектования для складских рабочих, которые будут ходить по складу, собирая товары по заказам с разных полок.

Один вид товаров может находиться на разных полках, и на каждой полке может быть много товаров одного и того же типа.

Если в одном заказе много одинаковых товаров, иногда сборщику приходится выбирать с нескольких полок, чтобы получить все товары в заказе.

Чтобы еще больше усложнить задачу, иногда товар также заканчивается.

Моя модель данных выглядит так (упрощенно):

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.

Спасибо за любую помощь!

Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
0
162
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

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

Вот так:

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
;

Вот идея:

  1. Мы создаем временную таблицу product_on_shelf, которая аналогична warehouse_placement, за исключением того, что строки дублируются n раз, где n - количество товара на полке.
  2. Мы присваиваем номер order_among_product каждой строке в product_on_shelf, чтобы каждый объект на полке знал свой порядок среди тех же товаров.
  3. Мы присваиваем симметричный номер order_among_product каждой строке в order_product.
  4. Для каждой строки в order_product мы пытаемся найти товар на полке с таким же order_among_product. Если мы не можем их найти, значит, мы исчерпали товары на любой полке.

Боковое примечание # 1: Снятие продуктов с полок - это одновременное действие. Вы должны убедиться, что со стороны приложения или со стороны БД с помощью интеллектуальных замков, любой продукт на полке можно отнести к одному заказу. Обработка каждой строки product_order на стороне приложения может быть лучшим вариантом для борьбы с параллелизмом.

Боковое примечание # 2: Я написал этот запрос, используя CTE для ясности. Чтобы повысить производительность, рассмотрите возможность использования подзапросов. Обязательно запустите EXPLAIN ANALYZE

Отличное решение, приятное объяснение!

S-Man 10.09.2018 11:29

Это прекрасно отвечает на мой вопрос и является действительно хорошим способом решения проблемы. Спасибо огромное, Матье !!

rutchkiwi 10.09.2018 13:16

Re. совпадение, для начала будет только один упаковщик, так что это не будет проблемой. Когда нам нужно, чтобы несколько сборщиков работали одновременно, я планировал заблокировать заказы, чтобы у каждого сборщика был эксклюзивный доступ, чтобы выбрать, скажем, 10 заказов одновременно.

rutchkiwi 10.09.2018 13:18

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