仓库中的订单拣选

Question

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

一种类型的产品可以放在不同的货架上，每个货架上可以有许多相同类型的产品。

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

为了让事情变得更加棘手，有时产品也会脱销。

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

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，除了行重复n次，n为货架上产品的数量外，与warehouse_placement相同。
2. 我们为order_among_product中的每一行分配一个编号order_among_product，以便货架上的每个对象知道其在相同产品中的顺序。
3. 我们为order_product中的每一行分配一个对称编号order_among_product，我们尝试查找order_among_product相同的order_among_product货架上的产品。如果我们找不到任何货架上的产品，这意味着我们已经用完了所有货架上的产品。

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

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