索赔交易与采购订单关联的SQL查询优化求助

我需要基于库存交易数据生成库存报表，核心是识别和采购订单（PurchaseOrder）关联的索赔交易（Claim）。目前遇到几个棘手的场景处理不了：

• 因为包装规格的订购特性，单条索赔的数量可能要拆分关联到不同采购订单
• 存在交易冲销的情况：
  • C0索赔执行后在下单前被冲销，完全不能纳入关联结果
  • C3索赔后续被冲销，但原C3交易仍要关联O1、O2订单，冲销的C3记录要忽略
  • C8的部分数量得因为C3的冲销而排除

我写的初始SQL搞不定这些场景，下面是样本数据脚本、预期结果和当前查询代码，求帮忙修正。

1. 先过滤出有效索赔 #

首先得把原始索赔和冲销索赔区分开，算出每个索赔的净有效数量：

• 按索赔ID分组，汇总原始索赔（正数量）和冲销交易（负数量）的差值
• 直接过滤掉净数量为0的索赔（比如C0），只保留净数量大于0的索赔及其有效数量

2. 处理索赔与采购订单的数量拆分 #

对于需要拆分数量关联多个PO的情况，用累计匹配的方式来处理：

• 先算出每个采购订单还剩多少未匹配的数量
• 按索赔的有效数量，依次分配到对应PO，直到索赔数量用完或者PO的待匹配数量耗尽

修正后的SQL示例（兼容主流SQL方言） #

-- 第一步：计算有效索赔（过滤冲销后无剩余的记录）
WITH ValidClaims AS (
    SELECT 
        claim_id,
        product_id,
        SUM(quantity) AS net_quantity,
        MAX(transaction_date) AS latest_trans_date -- 用于判断冲销时间逻辑
    FROM inventory_transactions
    WHERE transaction_type IN ('Claim', 'ClaimReversal')
    GROUP BY claim_id, product_id
    HAVING SUM(quantity) > 0
),
-- 第二步：获取采购订单的待匹配剩余数量
PO_Remaining AS (
    SELECT 
        po.po_id,
        po.product_id,
        po.order_quantity,
        COALESCE(SUM(cm.claimed_qty), 0) AS matched_qty,
        po.order_quantity - COALESCE(SUM(cm.claimed_qty), 0) AS remaining_qty
    FROM purchase_orders po
    LEFT JOIN existing_claim_matches cm ON po.po_id = cm.po_id
    GROUP BY po.po_id, po.product_id, po.order_quantity
    HAVING remaining_qty > 0
),
-- 第三步：关联有效索赔和PO，拆分数量匹配
Claim_PO_Matches AS (
    SELECT 
        vc.claim_id,
        pr.po_id,
        LEAST(vc.net_quantity, pr.remaining_qty) AS claimed_qty,
        vc.product_id
    FROM ValidClaims vc
    JOIN PO_Remaining pr ON vc.product_id = pr.product_id
    -- 按业务规则排序，比如索赔时间、PO下单时间，确保匹配顺序正确
    ORDER BY vc.latest_trans_date, pr.po_date
)
-- 最终关联结果
SELECT 
    cpm.claim_id,
    cpm.po_id,
    cpm.claimed_qty,
    vc.net_quantity AS total_claim_qty,
    pr.order_quantity AS po_total_qty,
    pr.remaining_qty - cpm.claimed_qty AS po_remaining_qty
FROM Claim_PO_Matches cpm
JOIN ValidClaims vc ON cpm.claim_id = vc.claim_id
JOIN PO_Remaining pr ON cpm.po_id = pr.po_id;

关键细节说明 #

• 针对C3的冲销场景：ValidClaims会自动计算C3原始数量减去冲销数量的净值，冲销记录被合并过滤，只保留有效部分参与关联
• 针对C8的部分排除：如果C8的冲销是关联C3的，可在ValidClaims中加入reference_claim_id关联逻辑，调整净数量的计算规则（比如扣减对应C3的冲销影响）
• 数量拆分通过LEAST函数实现，确保每个PO只匹配剩余未关联的数量，索赔数量耗尽后不再继续匹配

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