La comptabilité des stocks nécessite un suivi précis des coûts à mesure que les marchandises circulent dans un entrepôt. FIFO (premier entré, premier sorti) suppose que les articles achetés les plus anciens sont vendus en premier, ce qui est crucial pour les produits périssables ou dans des environnements inflationnistes. Contrairement au coût moyen, FIFO exige de relier chaque vente à des lots d'achat historiques spécifiques, créant un défi de relation plusieurs-à-plusieurs qui précède les normes modernes de SQL.
Étant donné deux tables—purchases (lot_id, quantity, unit_cost, received_at) et sales (sale_id, quantity, sold_at)—nous devons allouer chaque quantité de vente à l'inventaire non vendu le plus ancien disponible. Cela crée trois complexités : une seule vente peut consommer plusieurs lots partiels, un seul lot peut s'étendre sur plusieurs ventes, et l'allocation doit respecter l'ordre chronologique sans boucles procédurales. Les approches traditionnelles de JOIN échouent car elles ne peuvent pas suivre l'état d'épuisement des lots individuels à travers les lignes.
Utilisez des fonctions de fenêtre pour calculer des sommes cumulatives, transformant des quantités discrètes en intervalles contigus. Convertissez les achats en plages cumulatives [prior_cumulative+1, current_cumulative] et les ventes en plages similaires. Un JOIN sur des intervalles qui se chevauchent identifie quels lots alimentent quelles ventes. La longueur d'intersection multipliée par le unit_cost du lot donne la base de coût. Cette approche théorique des ensembles évite la récursion et fonctionne entièrement dans ANSI SQL.
WITH purchase_cumulative AS ( SELECT lot_id, unit_cost, received_at, SUM(quantity) OVER (ORDER BY received_at, lot_id ROWS UNBOUNDED PRECEDING) - quantity AS cum_start, SUM(quantity) OVER (ORDER BY received_at, lot_id ROWS UNBOUNDED PRECEDING) AS cum_end FROM purchases ), sales_cumulative AS ( SELECT sale_id, sold_at, SUM(quantity) OVER (ORDER BY sold_at, sale_id ROWS UNBOUNDED PRECEDING) - quantity AS cum_start, SUM(quantity) OVER (ORDER BY sold_at, sale_id ROWS UNBOUNDED PRECEDING) AS cum_end FROM sales ) SELECT s.sale_id, p.lot_id, p.unit_cost, LEAST(s.cum_end, p.cum_end) - GREATEST(s.cum_start, p.cum_start) AS allocated_quantity, (LEAST(s.cum_end, p.cum_end) - GREATEST(s.cum_start, p.cum_start)) * p.unit_cost AS allocated_cost FROM sales_cumulative s JOIN purchase_cumulative p ON s.cum_start < p.cum_end AND s.cum_end > p.cum_start ORDER BY s.sale_id, p.received_at;
Un distributeur pharmaceutique suit des lots de médicaments avec des prix de gros variables en raison de fluctuations des fournisseurs. Les réglementations de la FDA exigent une traçabilité exacte des coûts pour chaque pilule vendue, nécessitant une attribution de coût par unité plutôt qu'un coût moyen. L'entrepôt traite des milliers de transactions quotidiennes à travers des centaines de SKU, avec des lots d'achat arrivant à des intervalles et des prix imprévisibles.
L'approche initiale utilisait un CURSOR dans une procédure stockée, itérant les ventes séquentiellement et diminuant les soldes des lots ligne par ligne. Bien que fonctionnellement correct, cette méthode entraînait une contention de verrouillage sévère pendant les heures de pointe car elle maintenait des verrous sur les tables d'inventaire pendant de longues durées. De plus, la logique procédurale échouait aux tests de conformité ACID sous des opérations INSERT concurrentes, entraînant des lectures fantômes et des doubles dépenses de lots d'inventaire.
L'équipe a brièvement envisagé d'utiliser des triggers pour maintenir une table de solde courante qui se mettait à jour automatiquement après chaque vente. Cependant, cela a introduit des erreurs de table mutante dans Oracle et une gestion complexe des contraintes différables dans PostgreSQL, entraînant une latence dans le système OLTP. L'approche du trigger a également compliqué les pistes de vérification en obscurcissant la logique d'allocation exacte au sein des métadonnées de la base de données plutôt que dans le code de requête explicite.
La solution choisie a implémenté la méthode de chevauchement des intervalles utilisant des fonctions de fenêtre pour pré-calculer les limites cumulatives. Cela a permis à l'optimiseur de base de données d'utiliser des jointures de tri-fusion plutôt que des jointures de boucles imbriquées, réduisant le temps de calcul du coût d'un rapport de vente de 10 000 unités de 45 secondes à 200 millisecondes. Le résultat a permis un reporting en temps réel du coût des marchandises vendues lors de la clôture financière de fin de mois sans bloquer les transactions d'inventaire, atteignant une pleine conformité d'isolation SERIALIZABLE.
Comment gérez-vous le cas limite où les événements d'achat et de vente partagent exactement le même horodatage, garantissant un ordre FIFO déterministe ?
Les candidats supposent souvent que ORDER BY sold_at est suffisant. Cependant, lorsque les horodatages entrent en collision, l'ordre d'allocation devient non déterministe et peut varier entre les exécutions de requête. La solution nécessite une colonne décisive—généralement la clé primaire ou une séquence d'auto-incrémentation—dans la clause ORDER BY de la fonction de fenêtre. Sans cet ordre strict, deux ventes se produisant simultanément pourraient consommer incorrectement la même quantité de lot deux fois en raison de conditions de course dans le plan d'exécution de l'optimiseur de requêtes, violant l'intégrité de l'inventaire.
Pourquoi l'utilisation de FLOAT ou DOUBLE PRECISION pour les colonnes de quantité corrompt-elle les résultats d'allocation FIFO ?
Beaucoup de candidats utilisent des types à virgule flottante pour les calculs monétaires ou de quantité, ignorant que IEEE 754 à virgule flottante ne peut pas représenter avec précision des fractions décimales comme 0.1. Cette imprécision entraîne des erreurs de somme cumulative qui se cumulent à travers des milliers de lignes, rendant un lot censé contenir exactement 100 unités à 99.999999 ou 100.000001. Par conséquent, les mathématiques de chevauchement d'intervalles peuvent soit manquer des chevauchements valides, soit créer des allocations négatives fantômes. La solution exige des types DECIMAL ou NUMERIC avec une précision explicite pour toutes les colonnes de quantité et de coût afin d'assurer une arithmétique exacte sur les entiers et d'éviter des écarts financiers.
Comment corrigez-vous les erreurs d'arrondis accumulées lorsqu'une vente consomme des cents fractionnaires à travers plusieurs lots avec des coûts unitaires différents ?
Lorsqu'une vente se divise entre trois lots au prix de 0,33 $, 0,33 $ et 0,34 $, un arrondi naïf de chaque ligne d'article peut faire en sorte que la somme des coûts alloués diverge de la valeur totale attendue de la vente d'un centime. Les candidats calculent souvent allocated_quantity * unit_cost directement sans tenir compte du contexte d'arrondi ou des restes résiduels. La solution robuste applique l'arrondi bancaire (arrondir moitié vers l'événement) ou préserve les valeurs non arrondies dans une sous-requête, puis applique un algorithme de correction dans la requête externe. Cette correction ajoute la différence résiduelle à la plus grande ligne d'allocation, forçant la somme à correspondre exactement à la valeur totale de la vente tout en maintenant l'exactitude des pistes de vérification.