如何编写SQL累计查询判断销售订单是否有可用库存（旧单优先）

现有表结构与数据 #

sales表 #

stock_balance表 #

预期输出结果 #

原查询问题分析 #

原查询存在两个核心问题：

1. balance_start直接使用初始库存值，未考虑前面订单占用后的剩余余额，导致所有同商品订单的balance_start都相同，不符合业务逻辑。
2. 预留量reserved的计算逻辑错误，原逻辑错误地将初始库存与历史订单总和做比较，没有按旧订单优先的规则逐步扣减库存，导致订单104的可用库存判断错误。

修正后的SQL查询 #

WITH sales_with_initial AS (
    SELECT
        s.sale_id,
        s.item_id,
        s.quantity,
        s.sale_date,
        sb.balance AS initial_balance
    FROM sales s
    JOIN stock_balance sb ON s.item_id = sb.item_id
),
ranked_sales AS (
    SELECT
        *,
        -- 计算当前订单之前的累计需求量
        SUM(quantity) OVER (PARTITION BY item_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cumulative_demand_before
    FROM sales_with_initial
)
SELECT
    sale_id,
    item_id,
    quantity,
    sale_date,
    -- 当前订单开始时的库存余额：初始库存减去之前已预留的总量
    GREATEST(initial_balance - COALESCE(cumulative_demand_before, 0), 0) AS balance_start,
    -- 判断是否有足够库存
    (GREATEST(initial_balance - COALESCE(cumulative_demand_before, 0), 0) >= quantity) AS has_balance,
    -- 实际预留量：取需求和剩余库存的较小值，不足则为0
    LEAST(quantity, GREATEST(initial_balance - COALESCE(cumulative_demand_before, 0), 0)) AS reserved,
    -- 订单处理后的库存余额
    GREATEST(initial_balance - COALESCE(cumulative_demand_before, 0), 0) - LEAST(quantity, GREATEST(initial_balance - COALESCE(cumulative_demand_before, 0), 0)) AS balance_end
FROM ranked_sales
ORDER BY item_id, sale_date;

修正说明 #

1. balance_start计算：通过窗口函数计算当前订单之前的累计已预留量，用初始库存减去该值得到当前订单处理前的实际余额。
2. reserved计算：对比当前订单需求量和剩余余额，若余额足够则预留全部需求量，否则预留0（完全匹配预期输出逻辑）。
3. 旧订单优先规则：所有窗口函数均按item_id分组、sale_date排序，确保旧订单先处理库存扣减。

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