关联4张表基于ID与日期查询订单消耗后临期过期库存的方案咨询

第一步：修正关联逻辑，避免数据膨胀 #

你之前直接用Product表同时左关联库存、订单表的方式会产生笛卡尔积，同一个商品的多库存行、多订单行交叉关联后会导致库存、订单数量计算重复，需要先分别聚合两张表的数据再关联。

第二步：预聚合各批次库存数据 #

按商品+到期日聚合每个批次的总库存，同时用窗口函数计算同商品下各批次库存的累计总容量（用于后续FIFO先进先出扣减订单）：

WITH stock_batch AS (
  SELECT
    ProductID,
    Stock_Expiration_Date,
    SUM(Stock) AS batch_stock,
    SUM(SUM(Stock)) OVER (PARTITION BY ProductID ORDER BY Stock_Expiration_Date ASC) AS cum_stock
  FROM Stock_table
  WHERE Stock_Expiration_Date > CURRENT_DATE -- 过滤已过期的库存
  GROUP BY ProductID, Stock_Expiration_Date
),
-- 预聚合订单数据，计算每个商品的历史已发货订单累计量、下一次待发货订单时间
order_agg AS (
  SELECT
    ProductID,
    SUM(CASE WHEN Order_Date < CURRENT_DATE THEN Quantity ELSE 0 END) AS total_shipped,
    MIN(CASE WHEN Order_Date >= CURRENT_DATE THEN Order_Date ELSE NULL END) AS next_order_date
  FROM Orders_table
  GROUP BY ProductID
)

第三步：FIFO扣减库存，计算临期风险 #

按批次到期顺序优先扣减更早到期的库存，判定剩余库存是否会在下次订单前到期，或无后续订单时到期未消耗：

SELECT
  s.ProductID,
  s.Stock_Expiration_Date,
  -- 计算当前批次剩余的风险库存
  CASE 
    WHEN s.cum_stock <= o.total_shipped THEN 0
    WHEN s.cum_stock - s.batch_stock <= o.total_shipped THEN s.cum_stock - o.total_shipped
    ELSE s.batch_stock
  END AS risk_stock
FROM stock_batch s
LEFT JOIN order_agg o ON s.ProductID = o.ProductID
WHERE
  -- 剩余库存大于0
  (CASE 
    WHEN s.cum_stock <= o.total_shipped THEN 0
    WHEN s.cum_stock - s.batch_stock <= o.total_shipped THEN s.cum_stock - o.total_shipped
    ELSE s.batch_stock
  END) > 0
  AND
  -- 到期日早于下一次订单时间，或没有后续订单
  (o.next_order_date IS NULL OR s.Stock_Expiration_Date < o.next_order_date)

结果验证 #

按你提供的样例数据，最终计算结果会得到：

• Meatballs 2022-04-01 风险库存50
• Meatballs 2022-04-21 风险库存10
  总和60，和示例计算逻辑完全一致。

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