使用Oracle SQL窗口函数实现多需求分批次填充的技术求助

Got it, let's tackle this resource allocation problem you're facing. The core issue with your current query is that it only tracks consumption within a single NEED_ID, not across multiple needs. That's why later needs end up trying to use fill quantities that were already exhausted by earlier requests.

Here's a corrected single-query solution (no stored procedures or temp tables) that properly tracks cumulative demand and fill supply to calculate exact allocations:

WITH NEED_TABLE AS (
    SELECT 'A' NEED_ID, 123 PARTNO, TO_DATE('01/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 4 NEED_QTY FROM DUAL
    UNION ALL
    SELECT 'B' NEED_ID, 123 PARTNO, TO_DATE('06/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 2 NEED_QTY FROM DUAL
),
FILL_TABLE AS (
    SELECT 'X' FILL_ID, 123 PARTNO, TO_DATE('01/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 2 FILL_QTY FROM DUAL
    UNION ALL
    SELECT 'Y' FILL_ID, 123 PARTNO, TO_DATE('06/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 4 FILL_QTY FROM DUAL
),
-- Step 1: Order needs by date and calculate cumulative demand
ordered_needs AS (
    SELECT 
        nt.*,
        SUM(nt.NEED_QTY) OVER (PARTITION BY nt.PARTNO ORDER BY nt.DATE_NEEDED, nt.NEED_ID) AS cumulative_need,
        SUM(nt.NEED_QTY) OVER (PARTITION BY nt.PARTNO ORDER BY nt.DATE_NEEDED, nt.NEED_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_cumulative_need
    FROM NEED_TABLE nt
),
-- Step 2: Order fills by availability date and calculate cumulative supply
ordered_fills AS (
    SELECT 
        ft.*,
        SUM(ft.FILL_QTY) OVER (PARTITION BY ft.PARTNO ORDER BY ft.DATE_AVAILABLE, ft.FILL_ID) AS cumulative_fill,
        SUM(ft.FILL_QTY) OVER (PARTITION BY ft.PARTNO ORDER BY ft.DATE_AVAILABLE, ft.FILL_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_cumulative_fill
    FROM FILL_TABLE ft
),
-- Step 3: Map needs to fills and calculate actual allocation
need_fill_mapping AS (
    SELECT 
        oned.NEED_ID,
        oned.PARTNO,
        oned.DATE_NEEDED,
        oned.NEED_QTY,
        ofill.FILL_ID,
        ofill.DATE_AVAILABLE,
        ofill.FILL_QTY,
        -- Calculate the exact amount this fill contributes to the current need
        GREATEST(
            0,
            LEAST(oned.cumulative_need, ofill.cumulative_fill) 
            - GREATEST(NVL(oned.prev_cumulative_need, 0), NVL(ofill.prev_cumulative_fill, 0))
        ) AS REAL_FILL
    FROM ordered_needs oned
    JOIN ordered_fills ofill 
        ON oned.PARTNO = ofill.PARTNO
        -- Only match fills that overlap with the need's demand range
        AND ofill.cumulative_fill > NVL(oned.prev_cumulative_need, 0)
        AND oned.cumulative_need > NVL(ofill.prev_cumulative_fill, 0)
)
-- Add explanatory text for the WHY? column
SELECT 
    nm.*,
    CASE 
        WHEN REAL_FILL > 0 THEN 
            nm.NEED_ID || '需' || nm.NEED_QTY || '件，' || 
            (CASE 
                WHEN (SELECT SUM(REAL_FILL) FROM need_fill_mapping WHERE NEED_ID = nm.NEED_ID AND FILL_ID <= nm.FILL_ID) = nm.NEED_QTY 
                THEN '由' || nm.FILL_ID || '填充剩余' || nm.REAL_FILL || '件'
                ELSE '由' || nm.FILL_ID || '填充' || nm.REAL_FILL || '件'
             END)
        ELSE nm.NEED_ID || '需' || nm.NEED_QTY || '件，' || nm.FILL_ID || '已被前面需求耗尽'
    END AS "WHY?"
FROM need_fill_mapping nm
ORDER BY nm.DATE_NEEDED, nm.DATE_AVAILABLE;

How This Works #

1. ordered_needs: Sorts requirements by date and calculates cumulative demand. This tells us how much total quantity is needed up to each request, and how much was needed before the current request.
2. ordered_fills: Sorts fill sources by availability date and calculates cumulative supply. This tracks how much total quantity is available up to each fill source, and how much was available before it.
3. need_fill_mapping: Joins needs and fills, then uses the cumulative values to find overlapping ranges where a fill can contribute to a need. The REAL_FILL calculation isolates the exact quantity from the fill that goes to the current need.
4. Final Select: Adds human-readable explanations for each allocation to match your expected output.

Expected Output #

Running this query will produce exactly the result you're looking for:

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