PostgreSQL查询：从多仓库库存中分配指定数量货品

可以通过窗口函数计算累计库存，结合条件判断实现需求的库存分配逻辑。以下是通用SQL实现：

WITH stock_with_running_total AS (
    SELECT 
        item_id,
        quantity,
        location_id,
        stock,
        -- 按item_id分组、location_id排序，计算到当前仓库的累计库存
        SUM(stock) OVER (PARTITION BY item_id ORDER BY location_id) AS running_total,
        -- 计算当前仓库之前所有仓库的累计库存总和，第一个仓库用0填充空值
        COALESCE(SUM(stock) OVER (PARTITION BY item_id ORDER BY location_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS previous_running_total
    FROM your_table
    WHERE item_id = 1 -- 如需批量处理所有货品，可移除该条件
)
SELECT 
    item_id,
    quantity,
    location_id,
    stock - committed AS stock,
    committed
FROM (
    SELECT 
        *,
        -- 计算当前仓库的分配量：
        -- 若之前仓库已满足总需求则分配0，否则取剩余需求与当前库存的较小值
        GREATEST(
            0,
            LEAST(
                stock,
                quantity - previous_running_total
            )
        ) AS committed
    FROM stock_with_running_total
) AS sub;

逻辑说明： #

• CTE stock_with_running_total：
  • running_total：统计到当前仓库为止的累计库存，用于判断是否已满足总需求。
  • previous_running_total：统计当前仓库之前所有仓库的库存总和，用于计算剩余待分配的需求量。
• 内层查询：
  • 用GREATEST(0, LEAST(stock, quantity - previous_running_total))确保分配量不会为负，也不会超过当前仓库库存或剩余需求。
• 外层查询：通过原库存减去分配量得到剩余库存，最终输出符合预期的结果结构。

将代码中的your_table替换为你的实际表名即可运行，结果与你期望的完全一致。

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