为何无法用LAG函数实现CustQty的前后向消耗计算？

测试数据 #

CREATE TABLE TEST_ORDER (
    Item VARCHAR2(100),
    Loc VARCHAR2(100),
    Nedddate DATE,
    Qty NUMBER,
    CustQty NUMBER
);

INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/13/2024', 'MM/DD/YYYY'), 0, 4.8, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/14/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/15/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/16/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/19/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/20/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/21/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/22/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/23/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/26/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/27/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/28/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
INSERT INTO SCPOMGR.TEST_FCSTORDER VALUES ('ABC', 'XYZ', TO_DATE('2/29/2024', 'MM/DD/YYYY'), 0, 0.6, 3);
COMMIT;

抵扣规则 #

以2024年2月26日为当前日期，执行以下逻辑：

1. 优先处理当前日期行：用CustQty抵扣Qty，将Qty置为0，剩余CustQty更新为原CustQty - 原Qty
2. 场景1：向前遍历更早日期的记录，重复抵扣操作，直到CustQty耗尽。示例输出：

Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/13/2024', 'MM/DD/YYYY'), 0, 4.8, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/14/2024', 'MM/DD/YYYY'), 0, 0.6, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/15/2024', 'MM/DD/YYYY'), 0, 0.6, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/16/2024', 'MM/DD/YYYY'), 0, 0.6, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/19/2024', 'MM/DD/YYYY'), 0, 0.6, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/20/2024', 'MM/DD/YYYY'), 0, 0, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/21/2024', 'MM/DD/YYYY'), 0, 0, 0.6);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/22/2024', 'MM/DD/YYYY'), 0, 0, 1.2);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/23/2024', 'MM/DD/YYYY'), 0, 0, 1.8);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/26/2024', 'MM/DD/YYYY'), 0, 0, 2.4);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/27/2024', 'MM/DD/YYYY'), 0, 0.6, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/28/2024', 'MM/DD/YYYY'), 0, 0.6, 0);
Insert into SCPOMGR.TEST_FCSTORDER Values ('ABC', 'XYZ', TO_DATE('2/29/2024', 'MM/DD/YYYY'), 0, 0.6, 0);

3. 场景2：若向前遍历至最早日期后CustQty仍未耗尽，则从当前日期的下一条记录开始，向后遍历更晚日期的记录继续抵扣。

实现方案 #

方案1：PL/SQL过程（逐行实时处理） #

适合需要精准控制遍历顺序和边界条件的场景，逻辑直观易调试：

DECLARE
    v_current_date DATE := TO_DATE('2/26/2024', 'MM/DD/YYYY');
    v_remaining_custqty NUMBER;
    v_deduct_qty NUMBER;
BEGIN
    -- 初始化剩余抵扣额度：处理当前日期行
    SELECT CustQty - Qty INTO v_remaining_custqty
    FROM SCPOMGR.TEST_FCSTORDER
    WHERE Nedddate = v_current_date;

    UPDATE SCPOMGR.TEST_FCSTORDER
    SET Qty = 0, CustQty = v_remaining_custqty
    WHERE Nedddate = v_current_date;

    -- 向前遍历更早日期，依次抵扣
    FOR rec IN (SELECT Nedddate, Qty FROM SCPOMGR.TEST_FCSTORDER
                WHERE Nedddate < v_current_date
                ORDER BY Nedddate DESC) LOOP
        IF v_remaining_custqty <= 0 THEN
            EXIT;
        END IF;

        v_deduct_qty := LEAST(rec.Qty, v_remaining_custqty);
        UPDATE SCPOMGR.TEST_FCSTORDER
        SET Qty = Qty - v_deduct_qty,
            CustQty = CustQty - v_deduct_qty
        WHERE Nedddate = rec.Nedddate;

        v_remaining_custqty := v_remaining_custqty - v_deduct_qty;
    END LOOP;

    -- 若仍有剩余额度，向后遍历更晚日期
    IF v_remaining_custqty > 0 THEN
        FOR rec IN (SELECT Nedddate, Qty FROM SCPOMGR.TEST_FCSTORDER
                    WHERE Nedddate > v_current_date
                    ORDER BY Nedddate ASC) LOOP
            IF v_remaining_custqty <= 0 THEN
                EXIT;
            END IF;

            v_deduct_qty := LEAST(rec.Qty, v_remaining_custqty);
            UPDATE SCPOMGR.TEST_FCSTORDER
            SET Qty = Qty - v_deduct_qty,
                CustQty = CustQty - v_deduct_qty
            WHERE Nedddate = rec.Nedddate;

            v_remaining_custqty := v_remaining_custqty - v_deduct_qty;
        END LOOP;
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

方案2：纯SQL（分析函数批量计算） #

适合批量处理场景，性能更优，通过累计值推导最终结果：

WITH base_data AS (
    SELECT 
        Item,
        Loc,
        Nedddate,
        Qty,
        CustQty,
        -- 定义处理顺序：当前行优先，然后向前降序，最后向后升序
        ROW_NUMBER() OVER (ORDER BY 
            CASE WHEN Nedddate = TO_DATE('2/26/2024', 'MM/DD/YYYY') THEN 0
                 WHEN Nedddate < TO_DATE('2/26/2024', 'MM/DD/YYYY') THEN -Nedddate
                 ELSE Nedddate END) AS process_seq
    FROM SCPOMGR.TEST_FCSTORDER
    WHERE Item = 'ABC' AND Loc = 'XYZ'
),
cumulative_calc AS (
    SELECT 
        *,
        SUM(Qty) OVER (ORDER BY process_seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_deductable,
        -- 初始剩余抵扣额度：当前行的CustQty - 当前行的Qty
        FIRST_VALUE(CustQty - Qty) OVER (ORDER BY process_seq) AS remaining_custqty
    FROM base_data
),
final_result AS (
    SELECT 
        Item,
        Loc,
        Nedddate,
        -- 计算最终Qty：若累计可抵扣量<=剩余额度则置0，否则计算剩余未抵扣量
        CASE 
            WHEN total_deductable <= remaining_custqty THEN 0
            WHEN total_deductable - Qty <= remaining_custqty THEN remaining_custqty - (total_deductable - Qty)
            ELSE Qty 
        END AS final_qty,
        -- 计算最终CustQty：剩余额度减去已抵扣量
        CASE 
            WHEN total_deductable <= remaining_custqty THEN CustQty - Qty
            ELSE CustQty - LEAST(total_deductable, remaining_custqty) + (total_deductable - Qty)
        END AS final_custqty
    FROM cumulative_calc
)
-- 更新原表
UPDATE SCPOMGR.TEST_FCSTORDER t
SET Qty = f.final_qty, CustQty = f.final_custqty
FROM final_result f
WHERE t.Item = f.Item AND t.Loc = f.Loc AND t.Nedddate = f.Nedddate;

COMMIT;

方案对比 #

• PL/SQL方案：逻辑清晰，便于处理多Item/Loc分组、异常分支等复杂场景，调试成本低
• 纯SQL方案：批量处理效率高，适合数据量较大的场景，但需要确保排序逻辑完全匹配需求的遍历顺序

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