如何在保持累積值的同時從多行中減去消耗值？

從多批中減去消耗價值

此問題涉及在多個庫存批次 (Pooled_Lots) 之間分配消耗資源 (QuantityConsumed)，同時追蹤累積數量、剩餘需求以及任何盈餘或赤字。

資料結構：

我們有兩張桌子：

• Pooled_Lots：包含每個庫存批次的詳細資訊（池、批次、數量）。
• Pool_Conclusion：指定每個池消耗的總數（PoolId、QuantityConsumed）。

期望的結果：

查詢應該為Pooled_Lots中的每個批次產生一個結果集，包括：

• Pool、Lot、Quantity、QuantityConsumed、RunningQuantity、RemainingDemand、SurplusOrDeficit。

解決方法：

遞歸公用表表達式（CTE）提供了一個有效的解決方案。邏輯如下：

1. 使用每個池的第一批來初始化 CTE。
2. 對於每個池中的後續批次：
   • 如果批次的 RunningQuantity 超過剩餘的 QuantityConsumed，則透過減去 Quantity 來更新 QuantityConsumed。
   • 將 RemainingDemand 計算為 QuantityConsumed 與 RunningQuantity 總和與目前批次 Quantity 總和之間的差值。
   • 對於每個池中的最後一批，計算 SurplusOrDeficit 作為 RunningQuantity 和 RemainingDemand 之間的差異。

SQL 查詢（說明性）：

提供的查詢不完整，包含一些邏輯不一致的地方。 強大的解決方案需要仔細處理邊緣情況（例如，QuantityConsumed為零或超過池中所有批次的總數）。 需要基於特定的資料庫系統（例如 SQL Server、PostgreSQL、MySQL）來設計修正且更有效率的查詢。 以下是更精確方法的概念摘要：

WITH RecursiveCTE AS (
    -- Anchor member: Select the first lot for each pool
    SELECT 
        PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed, 
        PL.Quantity AS RunningQuantity, 
        CASE WHEN PC.QuantityConsumed IS NULL THEN PL.Quantity ELSE PC.QuantityConsumed - PL.Quantity END AS RemainingDemand, 
        0 AS SurplusOrDeficit,
        ROW_NUMBER() OVER (PARTITION BY PL.Pool ORDER BY PL.Lot) as rn
    FROM Pooled_Lots PL
    LEFT JOIN Pool_Consumption PC ON PL.Pool = PC.PoolId
    WHERE PL.Lot = 1 --First lot

    UNION ALL

    -- Recursive member: Process subsequent lots
    SELECT 
        PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed,
        CASE 
            WHEN r.RemainingDemand > PL.Quantity THEN r.RunningQuantity - PL.Quantity
            ELSE r.RunningQuantity - r.RemainingDemand
        END AS RunningQuantity,
        CASE 
            WHEN r.RemainingDemand > PL.Quantity THEN r.RemainingDemand - PL.Quantity
            ELSE 0
        END AS RemainingDemand,
        CASE WHEN r.rn = (SELECT MAX(rn) FROM RecursiveCTE WHERE Pool = PL.Pool) THEN r.RunningQuantity - r.RemainingDemand ELSE 0 END AS SurplusOrDeficit,
        r.rn + 1
    FROM Pooled_Lots PL
    INNER JOIN RecursiveCTE r ON PL.Pool = r.Pool AND PL.Lot = r.rn + 1
    LEFT JOIN Pool_Consumption PC ON PL.Pool = PC.PoolId
)
SELECT * FROM RecursiveCTE
ORDER BY Pool, Lot;

結果：

輸出將顯示每批的詳細信息，包括累計RunningQuantity、剩餘RemainingDemand以及分配後的任何SurplusOrDeficit。 RunningQuantity、RemainingDemand 和 SurplusOrDeficit 計算的準確性取決於遞歸 CTE 中實現的精確邏輯來處理所有可能的場景。 完整且經過測試的解決方案需要了解特定的資料庫系統以及用於驗證的潛在樣本資料。

以上是如何在保持累積值的同時從多行中減去消耗值？的詳細內容。更多資訊請關注PHP中文網其他相關文章！