累積値を維持しながら、複数の行から減少する値を減算する方法は?

複数のロットから減少する価値を減算する

この問題には、累積数量、残りの需要、余剰または不足を追跡しながら、枯渇しつつあるリソース (QuantityConsumed) を複数の在庫ロット (Pooled_Lots) に割り当てることが含まれます。

データ構造:

テーブルが 2 つあります:

• Pooled_Lots: 各在庫ロット (プール、ロット、数量) の詳細が含まれます。
• Pool_Consumption: 各プールで消費される合計量 (PoolId、QuantityConsumed) を指定します。

望ましい結果:

クエリは、

内のロットごとに次のような結果セットを生成する必要があります。Pooled_Lots

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

解決策のアプローチ:

再帰的な Common Table Expression (CTE) は効果的なソリューションを提供します。ロジックは次のとおりです:

各プールの最初のロットで CTE を初期化します。
各プール内の後続のロットの場合:
2. ロットの
   • が残りの RunningQuantity を超える場合、QuantityConsumed を減算して Quantity を更新します。QuantityConsumed
   • と、RemainingDemandと現在のロットのQuantityConsumedの合計との差としてRunningQuantityを計算します。Quantity
   各プールの最後のロットについて、
   • と SurplusOrDeficit の差として RunningQuantity を計算します。RemainingDemand

SQL クエリ (例):

提供されたクエリは不完全で、論理的な矛盾がいくつか含まれています。 堅牢なソリューションには、エッジ ケース (たとえば、

がゼロであるか、プール内のすべてのロットの合計数量を超えているなど) を慎重に処理する必要があります。 修正されたより効率的なクエリは、特定のデータベース システム (SQL Server、PostgreSQL、MySQL など) に基づいて作成する必要があります。 以下は、より正確なアプローチの概念的な概要です:QuantityConsumed

<code class="language-sql">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;</code>

結果:

出力には、累積 RunningQuantity、残りの RemainingDemand、割り当て後の SurplusOrDeficit など、各ロットの詳細が表示されます。 RunningQuantity、RemainingDemand、および SurplusOrDeficit 計算の精度は、考えられるすべてのシナリオを処理するために再帰的 CTE に実装された正確なロジックに依存します。 完全でテスト済みのソリューションを実現するには、特定のデータベース システムと、場合によっては検証用のサンプル データについて知っている必要があります。

以上が累積値を維持しながら、複数の行から減少する値を減算する方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。