客户同商品退货单据与采购单精准匹配核销的技术实现需求

核心需求 #

针对客户采购数据完成退货单据的匹配核销：删除客户购买**同商品（相同ItemCode、ColorCode）**后，与采购日期最接近的退货发票（IsReturn=1）。

具体规则 #

• 单商品采购后全额退货：同时删除采购单据与退货单据两行数据；
• 同一采购单据含多件商品，其中一件退货：扣减该采购单据对应商品的Qty1与Revenue，仅删除退货单据。

现有问题 #

尝试过sum(Qty1)>0的处理方式，但在大数据量场景下性能无法满足。

输入数据 #

期望输出数据 #

解决方案（适配大数据量场景） #

使用窗口函数精准匹配退货与采购单据，避免全量聚合的性能损耗，步骤如下：

1. 按客户、商品维度分组，为采购/退货单按日期排序，标记序列；
2. 匹配每个退货单到最近的未核销采购单；
3. 计算核销后的采购单数量与金额，过滤掉全额核销的单据。

示例SQL代码（MySQL）：

WITH ranked_invoices AS (
    -- 为采购/退货单按日期排序
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerCode, ItemCode, ColorCode, IsReturn 
            ORDER BY STR_TO_DATE(InvoiceDate, '%d.%m.%Y')
        ) AS rn
    FROM invoices
),
purchase_return_matches AS (
    -- 匹配退货单到最近的前置采购单
    SELECT 
        p.*,
        r.InvoiceNumber AS return_invoice,
        r.Qty1 AS return_qty,
        r.Revenue AS return_rev,
        ROW_NUMBER() OVER (
            PARTITION BY r.InvoiceNumber 
            ORDER BY ABS(DATEDIFF(STR_TO_DATE(p.InvoiceDate, '%d.%m.%Y'), STR_TO_DATE(r.InvoiceDate, '%d.%m.%Y')))
        ) AS match_rank
    FROM ranked_invoices p
    JOIN ranked_invoices r 
        ON p.CustomerCode = r.CustomerCode
        AND p.ItemCode = r.ItemCode
        AND p.ColorCode = r.ColorCode
        AND p.IsReturn = 0
        AND r.IsReturn = 1
        AND STR_TO_DATE(p.InvoiceDate, '%d.%m.%Y') <= STR_TO_DATE(r.InvoiceDate, '%d.%m.%Y')
),
valid_matches AS (
    -- 保留每个退货单的最优匹配
    SELECT * FROM purchase_return_matches WHERE match_rank = 1
),
adjusted_purchases AS (
    -- 计算核销后的采购单数据
    SELECT 
        p.CustomerCode,
        p.InvoiceNumber,
        p.IsReturn,
        p.InvoiceDate,
        p.ItemCode,
        p.ColorCode,
        p.Qty1 + COALESCE(SUM(v.return_qty), 0) AS adjusted_qty,
        p.Revenue + COALESCE(SUM(v.return_rev), 0) AS adjusted_rev
    FROM ranked_invoices p
    LEFT JOIN valid_matches v ON p.InvoiceNumber = v.InvoiceNumber
    WHERE p.IsReturn = 0
    GROUP BY p.CustomerCode, p.InvoiceNumber, p.IsReturn, p.InvoiceDate, p.ItemCode, p.ColorCode, p.Qty1, p.Revenue
)
-- 过滤有效采购单
SELECT 
    CustomerCode,
    InvoiceNumber,
    IsReturn,
    InvoiceDate,
    ItemCode,
    ColorCode,
    adjusted_qty AS Qty1,
    adjusted_rev AS Revenue
FROM adjusted_purchases
WHERE adjusted_qty > 0
ORDER BY CustomerCode, InvoiceDate;

方案优势 #

• 用窗口函数+精准JOIN替代全量聚合，降低大数据量下的计算负载；
• 按日期排序匹配，严格遵循“最近采购单”的核销规则；
• 分步处理逻辑清晰，便于后续优化和维护。

内容的提问来源于stack exchange，提问作者Buğra Varol