La contabilità dell'inventario richiede un tracciamento preciso dei costi mentre le merci scorrono attraverso un magazzino. FIFO (First-In-First-Out) presuppone che gli articoli acquistati più vecchi vengano venduti per primi, fondamentale per i prodotti deperibili o in ambienti inflazionistici. A differenza della valutazione media dei costi, FIFO richiede di abbinare ogni vendita a lotti di acquisto storici specifici, creando una sfida di relazione molti-a-molti che precede gli standard moderni di SQL.
Data due tabelle—purchases (lot_id, quantity, unit_cost, received_at) e sales (sale_id, quantity, sold_at)—dobbiamo allocare ogni quantità di vendita all'inventario invenduto più vecchio disponibile. Questo crea tre complessità: una singola vendita può consumare più lotti parziali, un singolo lotto può estendersi su più vendite e l'allocazione deve rispettare l'ordine cronologico senza cicli procedurali. Gli approcci tradizionali con JOIN falliscono perché non possono tracciare lo stato di esaurimento dei singoli lotti attraverso le righe.
Utilizza funzioni di finestra per calcolare somme cumulative, trasformando quantità discrete in intervalli contigui. Converti gli acquisti in intervalli cumulativi [prior_cumulative+1, current_cumulative] e le vendite in intervalli simili. Un JOIN su intervalli che si sovrappongono identifica quali lotti alimentano quali vendite. La lunghezza dell'intersezione moltiplicata per il unit_cost del lotto produce il costo di allocazione. Questo approccio teorico insiemistico evita la ricorsione e opera interamente all'interno di SQL ANSI.
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 distributore farmaceutico tiene traccia dei lotti di farmaci con prezzi all'ingrosso variabili a causa delle fluttuazioni dei fornitori. Le normative della FDA richiedono una tracciabilità esatta dei costi per ogni pillola venduta, necessitando un'attribuzione di costo per unità piuttosto che una valutazione media dei costi. Il magazzino elabora migliaia di transazioni giornaliere su centinaia di SKU, con lotti di acquisto che arrivano a intervalli e prezzi imprevedibili.
L'approccio iniziale utilizzava un CURSOR in una procedura memorizzata, iterando le vendite sequenzialmente e decrementando i saldi dei lotti riga per riga. Sebbene fosse funzionalmente corretto, questo metodo causava una grave contesa di lock durante le ore di punta poiché tratteneva i lock sulle tabelle di inventario per periodi prolungati. Inoltre, la logica procedurale non superava i test di conformità ACID sotto operazioni INSERT concorrenti, risultando in letture fantasma e doppia spesa di lotti di inventario.
Il team ha brevemente considerato l'utilizzo di triggers per mantenere una tabella di saldo in esecuzione che si aggiornasse automaticamente ad ogni vendita. Tuttavia, questo ha introdotto errori di tabella mutante in Oracle e complessi problemi di gestione dei vincoli rimandabili in PostgreSQL, causando latenza nel sistema OLTP. L'approccio del trigger ha anche complicato le tracce di audit, oscurando la logica di allocazione esatta all'interno dei metadati del database piuttosto che nel codice di query esplicito.
La soluzione scelta ha implementato il metodo di sovrapposizione degli intervalli utilizzando funzioni di finestra per precalcolare i confini cumulativi. Questo ha permesso all'ottimizzatore del database di utilizzare join di tipo sort-merge piuttosto che join a ciclo annidato, riducendo il calcolo del costo per un report di vendita di 10.000 unità da 45 secondi a 200 millisecondi. Il risultato ha permesso reportistica in tempo reale del costo delle merci vendute durante la chiusura finanziaria di fine mese senza bloccare le transazioni di inventario, raggiungendo la piena conformità all'isolamento SERIALE.
Come gestisci il caso limite in cui gli eventi di acquisto e vendita condividono esattamente lo stesso timestamp, garantendo un ordinamento FIFO deterministico?
I candidati spesso presumono che ORDER BY sold_at sia sufficiente. Tuttavia, quando i timestamp collidono, l'ordine di allocazione diventa non deterministico e potrebbe variare tra le esecuzioni delle query. La soluzione richiede una colonna di tie-breaker—tipicamente la chiave primaria o una sequenza auto-incrementante—nell'ORDER BY della funzione di finestra. Senza questo ordinamento rigoroso, due vendite che avvengono simultaneamente potrebbero erroneamente consumare la stessa quantità di lotto due volte a causa di condizioni di corsa nel piano di esecuzione dell'ottimizzatore di query, violando l'integrità dell'inventario.
Perché l'utilizzo di FLOAT o DOUBLE PRECISION per le colonne di quantità corrompe i risultati di allocazione FIFO?
Molti candidati usano tipi a virgola mobile per i calcoli monetari o di quantità, ignari che la rappresentazione a virgola mobile IEEE 754 non può rappresentare con precisione frazioni decimali come 0.1. Questa imprecisione causa errori di somma cumulativa che si accumulano attraverso migliaia di righe, risultando in un lotto che ci si aspetta contenga esattamente 100 unità registrando 99.999999 o 100.000001. Di conseguenza, la matematica della sovrapposizione degli intervalli può mancare sovrapposizioni valide o creare allocazioni negative fantasma. La soluzione impone tipi DECIMAL o NUMERIC con precisione esplicita per tutte le colonne di quantità e costo per garantire aritmetica esatta e prevenire discrepanze finanziarie.
Come correggi gli errori di arrotondamento accumulati quando una vendita consuma centesimi frazionari su più lotti con costi unitari diversi?
Quando una vendita si divide su tre lotti prezzi a $0.33, $0.33 e $0.34, un arrotondamento ingenuo di ogni voce potrebbe causare la somma dei costi allocati a divergere dal valore totale atteso della vendita di un centesimo. I candidati spesso calcolano allocated_quantity * unit_cost direttamente senza considerare il contesto di arrotondamento o i resti residui. La soluzione robusta applica l'arrotondamento bancario (arrotondamento a metà verso pari) o mantiene valori non arrotondati in una sottoquery, quindi applica un algoritmo di correzione nella query esterna. Questo aggiustamento aggiunge la differenza residua alla linea di allocazione più grande, forzando la somma a corrispondere esattamente al valore totale della vendita mantenendo la precisione della traccia di audit.