Comment soustraire une valeur épuisante de plusieurs lignes tout en conservant les valeurs cumulées ?

Soustraire une valeur épuisante de plusieurs lots

Ce problème implique l'allocation d'une ressource qui s'épuise (QuantityConsumed) sur plusieurs lots de stock (Pooled_Lots) tout en suivant les quantités cumulées, la demande restante et tout excédent ou déficit.

Structures de données :

Nous avons deux tables :

• Pooled_Lots : Contient les détails de chaque lot d'inventaire (Pool, Lot, Quantité).
• Pool_Consumption : Spécifie la quantité totale consommée pour chaque pool (PoolId, QuantityConsumed).

Résultat souhaité :

La requête doit générer un ensemble de résultats pour chaque lot de Pooled_Lots, comprenant :

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

Approche de la solution :

Une expression de table commune (CTE) récursive fournit une solution efficace. La logique est la suivante :

1. Initialisez le CTE avec le premier lot pour chaque pool.
2. Pour les lots suivants au sein de chaque pool :
   • Mettre à jour RunningQuantity en soustrayant QuantityConsumed si le Quantity du lot dépasse le QuantityConsumed restant.
   • Calculez RemainingDemand comme la différence entre QuantityConsumed et la somme de RunningQuantity et du lot actuel Quantity.
   • Pour le dernier lot de chaque pool, calculez SurplusOrDeficit comme la différence entre RunningQuantity et RemainingDemand.

Requête SQL (illustration) :

La requête fournie est incomplète et contient des incohérences logiques. Une solution robuste nécessite une gestion minutieuse des cas extrêmes (par exemple, QuantityConsumed étant nul ou dépassant la quantité totale de tous les lots d'un pool). Une requête corrigée et plus efficace devrait être conçue sur la base d'un système de base de données spécifique (par exemple, SQL Server, PostgreSQL, MySQL). Ce qui suit est un aperçu conceptuel d'une approche plus précise :

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;

Résultats :

La sortie affichera les détails de chaque lot, y compris le RunningQuantity cumulé, le RemainingDemand restant et tout SurplusOrDeficit après allocation. La précision des calculs RunningQuantity, RemainingDemand et SurplusOrDeficit dépend de la logique précise mise en œuvre dans le CTE récursif pour gérer tous les scénarios possibles. Une solution complète et testée nécessiterait de connaître le système de base de données spécifique et éventuellement des échantillons de données pour validation.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!