In de context van voorraadwaardering en kostlagen, hoe zou je een strikte FIFO (First-In-First-Out) allocatie-algoritme implementeren met alleen ANSI SQL om elke uitgaande verkooptransactie af te stemmen op specifieke inkomende aankooppartijen, waarbij de exacte kostbasis voor elke verkochte eenheid wordt berekend?

Antwoord op de vraag

Geschiedenis van de vraag

Voorraadboekhouding vereist nauwkeurige tracking van kosten terwijl goederen door een magazijn stromen. FIFO (First-In-First-Out) gaat ervan uit dat de oudste aangekochte artikelen als eerste worden verkocht, cruciaal voor bederfelijke waar of inflatoire omgevingen. In tegenstelling tot gemiddelde kostprijssystemen vereist FIFO het afstemmen van elke verkoop op specifieke historische aankooppartijen, wat een uitdaging vormt in een veel-op-veel relatie die de moderne SQL-standaarden voorafgaat.

Het probleem

Gezien twee tabellen—aankopen (partij_id, hoeveelheid, eenheidskost, ontvangen_op) en verkopen (verkoop_id, hoeveelheid, verkocht_op)—moeten we elke verkochte hoeveelheid toewijzen aan de oudste beschikbare ongeschonde voorraad. Dit creëert drie complicaties: een enkele verkoop kan meerdere gedeeltelijke partijen verbruiken, een enkele partij kan zich over meerdere verkopen uitstrekken en de allocatie moet de chronologische volgorde respecteren zonder procedurele loops. Traditionele JOIN-benaderingen falen omdat ze de uitputtingsstatus van individuele partijen over rijen niet kunnen bijhouden.

De oplossing

Gebruik vensterfuncties om cumulatieve sommen te berekenen, waarbij discrete hoeveelheden worden omgevormd tot aaneengeschakelde intervallen. Zet aankopen om in cumulatieve reeksen [vorige_cumulatief+1, huidige_cumulatief] en verkopen in soortgelijke reeksen. Een JOIN op overlappende intervallen identificeert welke partijen welke verkopen voeden. De lengte van de intersectie vermenigvuldigd met de eenheidskost van de partij levert de kostbasis op. Deze set-theoretische aanpak vermijdt recursie en opereert volledig binnen ANSI SQL.

WITH aankoop_cumulatief AS (
    SELECT 
        partij_id,
        eenheidskost,
        ontvangen_op,
        SUM(hoeveelheid) OVER (ORDER BY ontvangen_op, partij_id ROWS UNBOUNDED PRECEDING) - hoeveelheid AS cum_start,
        SUM(hoeveelheid) OVER (ORDER BY ontvangen_op, partij_id ROWS UNBOUNDED PRECEDING) AS cum_end
    FROM aankopen
),
verkoop_cumulatief AS (
    SELECT 
        verkoop_id,
        verkocht_op,
        SUM(hoeveelheid) OVER (ORDER BY verkocht_op, verkoop_id ROWS UNBOUNDED PRECEDING) - hoeveelheid AS cum_start,
        SUM(hoeveelheid) OVER (ORDER BY verkocht_op, verkoop_id ROWS UNBOUNDED PRECEDING) AS cum_end
    FROM verkopen
)
SELECT 
    v.verkoop_id,
    a.partij_id,
    a.eenheidskost,
    LEAST(v.cum_end, a.cum_end) - GREATEST(v.cum_start, a.cum_start) AS toegewezen_hb,
    (LEAST(v.cum_end, a.cum_end) - GREATEST(v.cum_start, a.cum_start)) * a.eenheidskost AS toegewezen_kost
FROM verkoop_cumulatief v
JOIN aankoop_cumulatief a 
    ON v.cum_start < a.cum_end AND v.cum_end > a.cum_start
ORDER BY v.verkoop_id, a.ontvangen_op;

Situatie uit het leven

Een farmaceutische distributeur volgt batches van medicijnen met variërende groothandelsprijzen door fluctuaties bij leveranciers. FDA-voorschriften vereisen exacte kostentracering voor elke verkochte pil, wat toerekening per eenheid noodzakelijk maakt in plaats van gemiddelde kostprijzen. Het magazijn verwerkt duizenden dagelijkse transacties over honderden SKU's, met aankooppartijen die op onvoorspelbare tijdstippen en prijzen aankomen.

De initiële aanpak gebruikte een CURSOR in een opgeslagen procedure, waarbij verkopen sequentieel werden geïtereerd en partijbalansen rij-voor-rij werden verlaagd. Hoewel functioneel correct, veroorzaakte deze methode ernstige vergrendelingsoverkruising tijdens piekuren omdat het vergrendelingen op voorraadtabellen voor langere tijd vasthield. Bovendien voldeed de procedurele logica niet aan de ACID-compatibiliteitstests onder gelijktijdige INSERT-bewerkingen, wat resulteerde in spooklezingen en dubbele uitgaven van voorraadpartijen.

Het team overwoog kort om triggers te gebruiken om een lopende balans tabel te onderhouden die automatisch werd bijgewerkt bij elke verkoop. Dit introduceerde echter fouten met muterende tabellen in Oracle en complexe uitstelbare constraintbeheer in PostgreSQL, wat tot vertraging in het OLTP-systeem leidde. De triggerbenadering bemoeilijkte ook audits door de exacte allocatielogica binnen de database-metadata te verhullen in plaats van in expliciete querycode.

De gekozen oplossing implementeerde de intervallovertapping-methode met behulp van vensterfuncties om cumulatieve grenzen vooraf te berekenen. Dit stelde de database-optimizer in staat om sorteer-samengevoegde joins te gebruiken in plaats van geneste loopjoins, waardoor de kostenberekening voor een 10.000 eenheden verkooprapport van 45 seconden naar 200 milliseconden werd teruggebracht. Het resultaat stelde real-time rapportage van kosten van verkochte goederen mogelijk tijdens de maandelijkse financiële afsluiting zonder het blokkeren van voorraadtransacties, met volledige naleving van SERIALIZABLE isolatie.

Wat kandidaten vaak missen

Hoe ga je om met de randgeval waar aankoop- en verkoopgebeurtenissen exact dezelfde tijdstempel delen, en zorg je voor een deterministische FIFO-volgorde?

Kandidaten gaan vaak ervan uit dat ORDER BY verkocht_op voldoende is. Wanneer tijdstempels echter samenvallen, wordt de allocatievolgorde niet-deterministisch en kan deze variëren tussen query-uitvoeringen. De oplossing vereist een tie-breaker kolom—typisch de primaire sleutel of een auto-incrementerende volgorde—binnen de ORDER BY clausule van de vensterfunctie. Zonder deze strikte ordening kunnen twee verkopen die gelijktijdig plaatsvinden mogelijk dezelfde partijhoeveelheid twee keer ten onrechte verbruiken vanwege racecondities in het uitvoeringsplan van de queryoptimalisator, wat de integriteit van de voorraad schendt.

Waarom verstoort het gebruik van FLOAT of DOUBLE PRECISION voor hoeveelheid kolommen de FIFO-allocatieresultaten?

Veel kandidaten gebruiken drijvende-komma types voor financiële of hoeveelheidberekeningen, zich niet realiserend dat IEEE 754 drijvende-komma niet precies decimale fracties zoals 0.1 kan weergeven. Deze onnauwkeurigheid veroorzaakt cumulatieve somfouten die zich ophopen over duizenden rijen, wat resulteert in een partij waarvan verwacht wordt dat deze exact 100 eenheden bevat die registreert als 99.999999 of 100.000001. Gevolg hiervan is dat de interval-overtapping wiskunde ofwel geldige overlappen mist, of opduikende negatieve toewijzingen creëert. De oplossing vereist DECIMAL of NUMERIC types met expliciete precisie voor alle hoeveelheid en kosten kolommen om integer-exacte berekeningen te waarborgen en financiële afwijkingen te voorkomen.

Hoe corrigeer je cumulatieve afrondingsfouten wanneer een verkoopFractionele centen consumeert over meerdere partijen met verschillende eenheidskosten?

Wanneer een verkoop zich splitst over drie partijen geprijsd op $0.33, $0.33 en $0.34, kan naïeve afronding van elk lijnitem ertoe leiden dat de som van toegewezen kosten afwijkt van de totale verwachte waarde van de verkoop met een cent. Kandidaten berekenen vaak toegewezen_hb * eenheidskost direct zonder rekening te houden met de afrondingscontext of resterende restwaarden. De robuuste oplossing past banker's rounding toe (afronden halverwege naar even) of behoudt niet-afgeronde waarden in een subquery, en past dan een correctie-algoritme toe in de buitenste query. Deze aanpassing voegt het resterende verschil toe aan de grootste toewijzingregel, waardoor de som exact overeenkomt met de totale verkoopwaarde, terwijl de nauwkeurigheid van de audittrail wordt behouden.