Odpowiedź na pytanie

Historia pytania

Księgowość zapasów wymaga precyzyjnego śledzenia kosztów w miarę przepływu towarów przez magazyn. FIFO (First-In-First-Out) zakłada, że najstarsze zakupione przedmioty są sprzedawane w pierwszej kolejności, co jest kluczowe dla produktów łatwo psujących się lub w warunkach inflacyjnych. W przeciwieństwie do średnich kosztów, FIFO wymaga dopasowania każdej sprzedaży do konkretnych Historycznych partii zakupu, co stwarza wyzwanie w postaci związku wiele-do-wielu, które wyprzedza współczesne standardy SQL.

Problem

Dysponując dwoma tabelami—purchases (lot_id, quantity, unit_cost, received_at) oraz sales (sale_id, quantity, sold_at)—musimy przypisać każdą ilość sprzedaży do najstarszego dostępnego niesprzedanego zapasu. Tworzy to trzy złożoności: jedna sprzedaż może pochłonąć wiele częściowych partii, jedna partia może obejmować kilka sprzedaży, a alokacja musi respektować chronologiczny porządek bez pętli proceduralnych. Tradycyjne podejścia JOIN zawodzą, ponieważ nie potrafią śledzić stanu wyczerpania poszczególnych partii w wierszach.

Rozwiązanie

Użyj funkcji okiennych do obliczenia sum skumulowanych, przekształcając dyskretne ilości w ciągłe zakresy. Przekształć zakupy w zakresy skumulowane [prior_cumulative+1, current_cumulative] i sprzedaż w podobne zakresy. JOIN na nakładających się przedziałach identyfikuje, które partie zasilają które sprzedaże. Długość przecięcia pomnożona przez jednostkowy koszt partii daje podstawę kosztów. To podejście teoretyczne unika rekurencji i działa całkowicie wewnątrz 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;

Sytuacja z życia

Dystrybutor farmaceutyczny śledzi partie leków o różnych cenach hurtowych w wyniku fluktuacji dostawców. Przepisy FDA wymagają dokładnego śledzenia kosztów dla każdej sprzedanej tabletki, co wymaga przypisania kosztów na jednostkę zamiast kosztów średnich. Magazyn przetwarza tysiące transakcji dziennie na setki SKU, z partiami zakupów przychodzącymi w nieprzewidywalnych odstępach czasu i cenach.

Początkowe podejście używało KURSORA w procedurze składowanej, iterując przez sprzedaże sekwencyjnie i dekrementując salda partii wiersz po wierszu. Choć funkcjonalnie poprawne, ta metoda powodowała poważne problemy z blokowaniem w czasie szczytu, ponieważ trzymała zablokowane tabele zapasów przez dłuższe okresy. Dodatkowo, logika proceduralna nie przeszła testów zgodności ACID pod kątem jednoczesnych operacji INSERT, co skutkowało odczytami fantomowymi i podwójnym wydawaniem partii zapasów.

Zespół krótko rozważał użycie wyzwalaczy do utrzymania stołu z bieżącym bilansem, który aktualnie by się aktualizował po każdej sprzedaży. Jednak to wprowadziło błędy w mutujących tabelach w Oracle i złożone zarządzanie opóźnionymi ograniczeniami w PostgreSQL, co wprowadzało opóźnienia do systemu OLTP. Podejście z wyzwalaczami również skomplikowało ścieżki audytu, ukrywając dokładną logikę alokacji w metadanych bazy danych zamiast w explicite kodzie zapytania.

Wybrane rozwiązanie zaimplementowało metodę nakładania się przedziałów przy użyciu funkcji okiennych w celu wstępnego obliczenia granic skumulowanych. Umożliwiło to optymalizatorowi bazy danych wykorzystanie połączeń sort-merge zamiast zagnieżdżonych, co zmniejszyło czas obliczeń kosztów dla raportu sprzedaży wynoszącego 10 000 jednostek z 45 sekund do 200 milisekund. Wynik umożliwił raportowanie kosztów sprzedanych towarów w czasie rzeczywistym podczas końca miesiąca bez blokowania transakcji zapasów, osiągając pełną zgodność z izolacją SERIALIZABLE.

Co kandydaci często pomijają

Jak poradziłeś sobie z przypadkiem brzegowym, w którym zdarzenia zakupu i sprzedaży podzielają dokładnie ten sam znacznik czasu, zapewniając deterministyczny porządek FIFO?

Kandydaci często zakładają, że ORDER BY sold_at jest wystarczające. Jednak gdy znaczniki czasu kolidują, porządek alokacji staje się niedeterministyczny i może się różnić pomiędzy wykonaniami zapytania. Rozwiązanie wymaga kolumny tie-breaker—typowo klucza głównego lub rosnącej sekwencji—w klauzuli ORDER BY funkcji okna. Bez tego ścisłego porządku, dwie sprzedaże odbywające się jednocześnie mogą błędnie wykorzystać tę samą ilość partii dwa razy z powodu warunków wyścigu w planie wykonania optymalizatora zapytań, naruszając integralność zapasów.

Dlaczego używanie FLOAT lub DOUBLE PRECISION dla kolumn ilościowych psuje wyniki alokacji FIFO?

Wielu kandydatów używa typów zmiennoprzecinkowych do obliczeń monetarnych lub ilościowych, nie zdając sobie sprawy, że IEEE 754 nie może dokładnie reprezentować ułamków dziesiętnych takich jak 0.1. Ta niedokładność powoduje błędy sum skumulowanych, które kumulują się w tysiącach wierszy, co skutkuje partią, która ma mieć dokładnie 100 jednostek, a rejestruje 99.999999 lub 100.000001. W konsekwencji matematyka nakładania przedziałów albo przeocza ważne nakładki, albo tworzy fałszywe ujemne alokacje. Rozwiązanie wymaga typów DECIMAL lub NUMERIC z explicite dokładnością dla wszystkich kolumn ilości i kosztów, aby zapewnić arytmetykę dokładnych liczb całkowitych i zapobiec rozbieżnościom finansowym.

Jak skorygować nagromadzone błędy zaokrągleń, gdy sprzedaż obejmuje ułamkowe centy z różnych partii o różnych kosztach jednostkowych?

Gdy sprzedaż dzieli się na trzy partie wycenione na 0,33 USD, 0,33 USD i 0,34 USD, naiwne zaokrąglanie każdego wiersza może spowodować, że suma przypisanych kosztów odbiega od całkowitej oczekiwanej wartości sprzedaży o centa. Kandydaci często obliczają allocated_quantity * unit_cost bezpośrednio, nie uwzględniając kontekstu zaokrąglania lub pozostałych reszt. Solidne rozwiązanie stosuje zaokrąglanie bankowe (zaokrąglaj w połowie do parzystych) lub zachowuje niezaokrąglone wartości w podzapytaniu, a następnie stosuje algorytm korekcyjny w zapytaniu zewnętrznym. Ta poprawka dodaje różnicę do największej linii alokacyjnej, zmuszając sumę do dokładnego dopasowania do całkowitej wartości sprzedaży, jednocześnie zachowując dokładność ścieżki audytu.