Die Lagerbuchhaltung erfordert eine präzise Verfolgung der Kosten, während Waren durch ein Lagerhaus fließen. FIFO (First-In-First-Out) geht davon aus, dass die ältesten gekauften Artikel zuerst verkauft werden, was entscheidend für verderbliche Waren oder inflationsanfällige Umgebungen ist. Im Gegensatz zur Durchschnittskostenrechnung erfordert FIFO die Zuordnung jedes Verkaufs zu bestimmten historischen Kaufpartien, was eine Herausforderung für eine viele-zu-viele-Beziehung darstellt, die modernen SQL-Standards vorausgeht.
Gegeben sind zwei Tabellen—purchases (lot_id, quantity, unit_cost, received_at) und sales (sale_id, quantity, sold_at)—müssen wir jede Verkaufsmenge der ältesten verfügbaren unverkauften Lagerbestände zuweisen. Dies schafft drei Komplexitäten: Ein einzelner Verkauf kann mehrere Teilmengen verbrauchen, eine einzelne Partie kann sich über mehrere Verkäufe erstrecken, und die Zuteilung muss die chronologische Reihenfolge respektieren, ohne prozedurale Schleifen zu verwenden. Traditionelle JOIN-Ansätze scheitern, da sie den Verbrauchszustand einzelner Partien über Zeilen hinweg nicht verfolgen können.
Verwenden Sie Fensterfunktionen, um kumulierte Summen zu berechnen, wodurch diskrete Mengen in zusammenhängende Intervalle umgewandelt werden. Wandeln Sie Käufe in kumulierte Bereiche [vorher_kumuliert+1, aktuell_kumuliert] und Verkäufe in ähnliche Bereiche um. Ein JOIN auf sich überschneidenden Intervallen identifiziert, welche Partien welche Verkäufe speisen. Die Länge der Schnittmenge multipliziert mit den Stückkosten der Partie ergibt die Kostenbasis. Dieser mengenlehre Ansatz vermeidet Rekursion und arbeitet vollständig innerhalb von 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;
Ein pharmazeutischer Distributor verfolgt Chargen von Medikamenten mit unterschiedlichen Großhandelspreisen aufgrund von Lieferantenfluktuationen. FDA-Vorschriften erfordern eine genaue Kostennachverfolgbarkeit für jede verkaufte Pille, was eine Zuordnung der Kosten pro Einheit anstelle der Durchschnittskosten erfordert. Das Lager verarbeitet täglich Tausende von Transaktionen über Hunderte von SKUs, wobei Kaufpartien zu unvorhersehbaren Zeiten und Preisen ankommen.
Der ursprüngliche Ansatz verwendete einen CURSOR in einer gespeicherten Prozedur, um Verkäufe sequenziell durchzugehen und die Bestandswerte zeilenweise zu verringern. Obwohl funktional korrekt, führte diese Methode während der Spitzenzeiten zu schweren Sperrkonkurrenzen, da sie die Sperren auf den Lagerbestands Tabellen über längere Zeiträume hielt. Darüber hinaus scheiterte die prozedurale Logik an den ACID-Compliance-Tests unter gleichzeitigen INSERT-Operationen, was zu phantom reads und doppeltem Verbrauch von Lagerpartien führte.
Das Team erwog kurzzeitig die Verwendung von Triggers, um eine laufende Bilanztabelle zu führen, die sich automatisch bei jedem Verkauf aktualisierte. Dies führte jedoch zu sich verändernden Tabellenfehlern in Oracle und zu komplexem verwaltbarem Einschränkungsmanagement in PostgreSQL, was die Latenz im OLTP-System verschärfte. Der Triggeransatz erschwerte auch die Prüfpfade, indem er die genaue Zuteilungslogik innerhalb der Metadaten der Datenbank statt im expliziten Abfragecode verschleierte.
Die gewählte Lösung implementierte die Intervallüberlappungsmethode unter Verwendung von Fensterfunktionen, um kumulierte Grenzen vorab zu berechnen. Dies ermöglichte dem Datenbankoptimierer, Sortier-Merge-Joins anstelle von geschachtelten Schleifen-Joins zu nutzen, wodurch die Kostenberechnung für einen Verkaufsbericht über 10.000 Einheiten von 45 Sekunden auf 200 Millisekunden gesenkt wurde. Das Ergebnis ermöglichte eine Echtzeitberichterstattung über die Kosten der verkauften Waren während des Monatsabschlusses, ohne die Lagerbestands Transaktionen zu blockieren, und erreichte die volle SERIALIZABLE-Isolations-Compliance.
Wie gehen Sie mit dem Randfall um, in dem Kauf- und Verkaufsereignisse denselben Zeitstempel teilen, um die deterministische FIFO-Reihenfolge zu gewährleisten?
Kandidaten nehmen oft an, dass ORDER BY sold_at ausreicht. Wenn die Zeitstempel jedoch kollidieren, wird die Zuteilungsreihenfolge nicht deterministisch und kann zwischen Abfrageausführungen variieren. Die Lösung erfordert eine Tiebreaker-Spalte—typischerweise der Primärschlüssel oder eine automatisch inkrementierende Sequenz—innerhalb der ORDER BY-Klausel der Fensterfunktion. Ohne diese strikte Reihenfolge könnten zwei gleichzeitige Verkäufe fälschlicherweise dieselbe Partiemenge zweimal verbrauchen, was gegen die Integrität des Bestands verstößt.
Warum führt die Verwendung von FLOAT oder DOUBLE PRECISION für Mengen Spalten zu einer Korruption der FIFO-Zuteilungsergebnisse?
Viele Kandidaten verwenden Fließkommatypen für monetäre oder Mengenberechnungen, ohne zu wissen, dass IEEE 754 Fließkommazahlen dezimale Brüche wie 0,1 nicht genau darstellen können. Diese Ungenauigkeit verursacht kumulierte Summenfehler, die sich über Tausende von Zeilen hinweg summieren und dazu führen, dass eine Partie, die genau 100 Einheiten enthalten sollte, als 99.999999 oder 100.000001 registriert wird. Folglich verpasst die Mathematik der Intervallüberlappung entweder gültige Überlappungen oder erzeugt phantom negative Zuteilungen. Die Lösung erfordert DECIMAL oder NUMERIC-Typen mit expliziter Genauigkeit für alle Mengen- und Kosten Spalten, um eine integer-exakte Arithmetik sicherzustellen und finanzielle Diskrepanzen zu vermeiden.
Wie korrigieren Sie kumulierte Rundungsfehler, wenn ein Verkauf fractional cents über mehrere Partien mit unterschiedlichen Stückkosten verbraucht?
Wenn ein Verkauf sich über drei Partien verteilt, die mit 0,33 USD, 0,33 USD und 0,34 USD bepreist sind, kann naives Runden jedes Einzelpostens dazu führen, dass die Summe der zugewiesenen Kosten um einen Cent von dem insgesamt erwarteten Wert des Verkaufs abweicht. Kandidaten berechnen oft allocated_quantity * unit_cost direkt, ohne den Rundungszusammenhang oder verbleibende Reste zu berücksichtigen. Die robuste Lösung wendet Banker's Rounding (runde halb zu gerade) an oder bewahrt die nicht gerundeten Werte in einer Unterabfrage, um dann einen Korrekturalgorithmus in der äußeren Abfrage anzuwenden. Diese Anpassung fügt die verbleibende Differenz der größten Zuteilungszeile hinzu, wodurch die Summe genau mit dem Gesamtwert des Verkaufs übereinstimmt und gleichzeitig die Genauigkeit der Prüfspuren gewahrt bleibt.