基于多行数值的运行量扣减SQL实现问题及多类型扩展需求

单类型场景修复与实现 #

假设存在两张核心表：

• marble_inventory：存储弹珠库存，字段包括marble_type（弹珠类型）、total_stock（总库存）
• basket_demands：存储篮子需求，字段包括basket_id（篮子ID）、marble_type（所需弹珠类型）、demand_qty（需求数量）

问题根源 #

原CTE方案错误是因为未通过累计需求与库存的对比区分可满足的篮子范围，直接对所有行执行扣减操作，导致库存不足时逻辑混乱。正确逻辑应为：按需求数量从小到大排序，优先满足需求最少的篮子，直至库存耗尽。

修复后的SQL查询 #

WITH ranked_demands AS (
    SELECT 
        basket_id,
        marble_type,
        demand_qty,
        -- 按需求升序计算累计需求，确保优先处理小需求
        SUM(demand_qty) OVER (ORDER BY demand_qty, basket_id) AS cumulative_demand
    FROM basket_demands
    WHERE marble_type = 'Green Marble' -- 筛选单类型需求
),
inventory_data AS (
    SELECT total_stock FROM marble_inventory WHERE marble_type = 'Green Marble'
)
SELECT 
    rd.basket_id,
    rd.marble_type,
    rd.demand_qty,
    -- 计算实际可满足数量
    CASE
        WHEN rd.cumulative_demand <= (SELECT total_stock FROM inventory_data) THEN rd.demand_qty
        WHEN rd.cumulative_demand - rd.demand_qty < (SELECT total_stock FROM inventory_data) THEN (SELECT total_stock FROM inventory_data) - (rd.cumulative_demand - rd.demand_qty)
        ELSE 0
    END AS fulfilled_qty,
    -- 剩余需求 = 原需求 - 已满足数量
    rd.demand_qty - CASE
        WHEN rd.cumulative_demand <= (SELECT total_stock FROM inventory_data) THEN rd.demand_qty
        WHEN rd.cumulative_demand - rd.demand_qty < (SELECT total_stock FROM inventory_data) THEN (SELECT total_stock FROM inventory_data) - (rd.cumulative_demand - rd.demand_qty)
        ELSE 0
    END AS remaining_demand
FROM ranked_demands rd;

逻辑说明 #

1. ranked_demands：按需求数量升序计算累计需求，明确每个篮子在队列中的位置。
2. CASE分支判断：
   • 累计需求≤库存：该篮子需求可被完全满足
   • 累计需求>库存，但扣除当前篮子需求后的累计值<库存：仅能满足部分需求，满足量为库存减去之前的累计需求
   • 其他情况：完全无法满足，满足量为0

多类型扩展实现 #

针对多类型弹珠，只需通过窗口函数的分区功能，让每种类型的库存独立处理对应类型的篮子需求即可。

扩展后的SQL查询 #

WITH ranked_demands AS (
    SELECT 
        basket_id,
        bd.marble_type,
        demand_qty,
        mi.total_stock,
        -- 按弹珠类型分区，计算单类型下的累计需求
        SUM(demand_qty) OVER (PARTITION BY bd.marble_type ORDER BY demand_qty, basket_id) AS cumulative_demand
    FROM basket_demands bd
    JOIN marble_inventory mi ON bd.marble_type = mi.marble_type
)
SELECT 
    basket_id,
    marble_type,
    demand_qty,
    total_stock AS initial_stock,
    CASE
        WHEN cumulative_demand <= total_stock THEN demand_qty
        WHEN cumulative_demand - demand_qty < total_stock THEN total_stock - (cumulative_demand - demand_qty)
        ELSE 0
    END AS fulfilled_qty,
    demand_qty - CASE
        WHEN cumulative_demand <= total_stock THEN demand_qty
        WHEN cumulative_demand - demand_qty < total_stock THEN total_stock - (cumulative_demand - demand_qty)
        ELSE 0
    END AS remaining_demand
FROM ranked_demands
ORDER BY marble_type, demand_qty, basket_id;

逻辑说明 #

1. ranked_demands：通过PARTITION BY bd.marble_type为每种弹珠类型单独分组，计算该类型下的累计需求，并关联对应类型的库存。
2. 沿用单类型的CASE判断逻辑，确保不同弹珠类型的库存与需求互不干扰，各自按优先级完成扣减。

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