postgresql - 仓库订单拣选

Question

在为电子商务商店实现仓库管理系统时，我尝试为仓库 worker 创建一个拣货 list ，他们将在仓库中走动，从不同货架上挑选订单中的产品。

一种商品可以放在不同的货架上，每个货架上可以有很多同种商品。

如果一个订单中有许多相同的产品，有时拣货员必须从多个货架上挑选才能获得一个订单中的所有商品。

更棘手的是，有时产品也会缺货。

我的数据模型如下所示(简化):

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 中执行此操作。

感谢您的帮助!

Answer 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. 我们为 product_on_shelf 中的每一行分配一个编号 order_among_product，以便货架上的每个对象都知道其在相同产品中的顺序。
3. 我们为 order_product 中的每一行分配一个对称数 order_among_product。
4. 对于 order_product 中的每一行，我们尝试在货架上找到具有相同 order_among_product 的产品。如果我们找不到任何货架，则意味着我们已经用完任何货架上的产品。

旁注 #1:从货架上取货是一项并发操作。您应该确保，无论是在应用程序端还是通过智能锁在数据库端，货架上的任何产品都可以归因于一个订单。在应用程序端处理每一行 product_order 可能是处理并发的最佳选择。

旁注 #2:为了清晰起见，我使用 CTE 编写了此查询。为了提高性能，请考虑改用子查询。确保运行 EXPLAIN ANALYZE