基于订单日期优先级的缺货待入库商品订单履约日期查询

问题背景 #

现有分日期的缺货订单：

• 2021-04-20：16件
• 2021-04-29：20件
• 2021-05-02：8件
• 2021-05-10：11件
• 2021-05-19：4件

需按订单日期先后顺序，用入库库存依次满足：首批库存耗尽后用下一批，最终为每笔订单匹配对应的履约入库日期。

假设表结构 #

先定义两个核心业务表：

1. 缺货订单表 backorders

2. 入库计划表 incoming_stock

解决方案SQL #

以下以PostgreSQL为例，核心思路是通过累计量区间匹配订单与入库批次：

WITH ordered_backorders AS (
    -- 按订单日期排序，计算到当前订单的累计缺货需求
    SELECT
        order_date,
        order_qty,
        SUM(order_qty) OVER (ORDER BY order_date) AS cumulative_demand
    FROM backorders
),
cumulative_stock AS (
    -- 按入库日期排序，计算到当前批次的累计入库供给，同时记录上一批累计量
    SELECT
        stock_date,
        stock_qty,
        SUM(stock_qty) OVER (ORDER BY stock_date) AS cumulative_supply,
        COALESCE(SUM(stock_qty) OVER (ORDER BY stock_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_cumulative_supply
    FROM incoming_stock
)
-- 匹配每个订单对应的履约入库日期
SELECT
    ob.order_date,
    ob.order_qty,
    cs.stock_date AS fulfillment_date
FROM ordered_backorders ob
JOIN cumulative_stock cs
    -- 订单累计需求落在当前入库批次的覆盖区间内
    ON ob.cumulative_demand > cs.prev_cumulative_supply
    AND ob.cumulative_demand <= cs.cumulative_supply
ORDER BY ob.order_date;

逻辑说明 #

1. ordered_backorders 子查询：按订单日期排序，计算累计缺货量，明确每个订单在需求序列中的位置。
2. cumulative_stock 子查询：按入库日期排序，计算累计入库量，同时标记上一批的累计量，确定每个入库批次能覆盖的需求范围。
3. 最终关联：通过累计量的区间匹配，找到能完全覆盖该订单累计需求的最早入库批次，即为该订单的履约日期。

如果存在单个订单跨多个入库批次的情况（如某笔订单量过大，需多批次库存才能满足），可调整逻辑拆分订单，将订单拆分为对应不同入库批次的子记录，分别匹配入库日期。

内容的提问来源于stack exchange，提问作者Youfah Mizzum