Sorunun Cevabı

Sorunun Tarihçesi

Envanter muhasebesi, malların bir depodan akışına göre maliyetlerin hassas bir şekilde izlenmesini gerektirir. FIFO (İlk Gelen İlk Gider), en eski satın alınan maddelerin önce satıldığını varsayar; bu, bozulabilen ürünler veya enflasyonist ortamlar için hayati önemdedir. Ortalama maliyetle karşılaştırıldığında, FIFO her satışı belirli tarihi satın alma partilerine eşleştirmeyi gerektirir, bu da modern SQL standartlarını önceden gören çoklu ilişkiler oluşturur.

Problem

purchases (lot_id, quantity, unit_cost, received_at) ve sales (sale_id, quantity, sold_at) adlı iki tablo verildiğinde, her satış miktarını en eski mevcut satılmamış envantere tahsis etmemiz gerekiyor. Bu, birden fazla kısmi partinin tek bir satışta tüketilmesi, tek bir partinin birkaç satışta yer alması ve tahsisin kronolojik sıralamayı dikkate alması gibi üç karmaşıklık yaratır. Geleneksel JOIN yaklaşımları, satırların arasındaki bireysel partilerin tüketim durumunu takip edemedikleri için başarısız olur.

Çözüm

Pencere fonksiyonları kullanarak birikimli toplamları hesaplayın ve ayrı miktarları sürekli aralıklara dönüştürün. Satın alımları birikimli aralıklara [önceki_birikimli+1, mevcut_birikimli] ve satışları benzer aralıklara dönüştürün. Üst üste binen aralıklar üzerinde bir JOIN, hangi partilerin hangi satışları beslediğini tanımlar. Kesişim uzunluğu, partinin birim maliyeti ile çarpıldığında maliyet temelini verir. Bu küme teorik yaklaşımı, yinelemeyi önler ve tamamen ANSI SQL içinde çalışır.

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;

Hayattan Bir Durum

Bir ilaç dağıtımcısı, tedarikçi dalgalanmaları nedeniyle değişken toptan fiyatlarla ilaç partilerini izler. FDA düzenlemeleri, her satılan tabletin tam maliyet izlenebilirliğini gerektirir; bu, ortalama maliyet yerine birim maliyet atamasını zorunlu kılar. Depo, öngörülemeyen aralıklarda ve fiyatlarla gelen satın alma partileriyle birlikte, yüzlerce SKU üzerinde günlük binlerce işlem gerçekleştirir.

İlk yaklaşım, satışları sırayla yineleyen ve lot bakiyelerini satır satır azaltan bir saklanan prosedürde KURSÖR kullanıyordu. İşlevsel olarak doğru olmasına rağmen, bu yöntem yoğun saatlerde envanter tablolarında uzun süreli kilitler tutarak ciddi kilit rekabetine neden oldu. Ayrıca, prosedürel mantık, eşzamanlı INSERT işlemleri altında ACID uyumluluğu testlerini geçemedi ve hayalet okumalar ve envanter lotlarının çift harcamasına neden oldu.

Ekip, her satıştan sonra otomatik olarak güncellenen bir sürekli bakiye tablosu tutmak için tetikleyicileri kullanmayı kısa süreli düşünmüştü. Ancak bu, Oracle'da değişken tablo hataları ve PostgreSQL'de karmaşık ertelenebilir kısıtlama yönetimi sorunları yaratmıştı ve OLTP sistemine gecikmeler yüklemişti. Tetikleyici yaklaşımı aynı zamanda, veritabanı metadata'sı içinde açık sorgu kodu yerine tam tahsis mantığını gizleyerek denetim izlerini karmaşıklaştırdı.

Seçilen çözüm, pencere fonksiyonları kullanarak aralık örtüşme yöntemini uyguladı ve birikimli sınırları önceden hesapladı. Bu, veritabanı optimizasyoncunun iç içe geçmiş döngü birleştirmeleri yerine sıralı birleştirmeleri kullanmasını sağladı ve 10.000 birimlik bir satış raporunun maliyet hesaplamasını 45 saniyeden 200 milisaniyeye düşürdü. Sonuç, ay sonu finansal kapatma sırasında envanter işlemlerini engellemeden, gerçek zamanlı maliyet raporu sunmayı sağladı ve tam SERIALIZABLE izolasyon uyumunu sağladı.

Adayların Sıklıkla Gözden Kaçırdığı Noktalar

Aynı zaman damgasını paylaşan satın alma ve satış olaylarıyla nasıl başa çıkarsınız ve deterministik FIFO sıralamasını sağlarsınız?

Adaylar genellikle ORDER BY sold_at ifadesinin yeterli olduğunu varsayarlar. Ancak zaman damgaları çakıştığında, tahsis sırası belirsiz hale gelir ve sorgu yürütmeleri arasında değişkenlik gösterebilir. Çözüm, pencere fonksiyonunun ORDER BY ifadesinde genellikle birincil anahtar veya otomatik artan bir diziyi kullanarak bir tie-breaker sütunu gerektirir. Bu katı sıralama olmadan, eşzamanlı olarak gerçekleşen iki satış, sorgu optimizasyoncunun yürütme planında yarı konumlar nedeniyle aynı lot miktarını yanlış bir şekilde iki kez tüketebilir ve envanter bütünlüğünü ihlal edebilir.

Neden miktar sütunları için FLOAT veya DOUBLE PRECISION kullanmak FIFO tahsis sonuçlarını bozuyor?

Birçok aday, miktar veya maliyet hesaplamaları için kayan nokta türlerini kullanarak, IEEE 754 kayan noktanın 0.1 gibi ondalık kesirleri tam olarak temsil edemediğini farkında değildir. Bu, birikimli toplam hatalarına neden olur ve bu hatalar binlerce satır boyunca büyüyerek tam olarak 100 birim içermesi beklenen bir lotun 99.999999 veya 100.000001 olarak kaydedilmesine sebep olur. Sonuç olarak, aralık örtüşme matematiği ya geçerli örtüşmeleri atlar ya da hayali negatif tahsisler yaratır. Çözüm, tüm miktar ve maliyet sütunları için tam sayı-hassas aritmetiği sağlamak ve finansal tutarsızlıkları önlemek adına DECIMAL veya NUMERIC türlerini açık bir hassasiyetle zorunlu kılar.

Farklı birim maliyetleri olan birden fazla lot arasında bir satışı kesen biriken yuvarlama hatalarını nasıl düzeltirsiniz?

Bir satış, $0.33, $0.33 ve $0.34 fiyatlandırılan üç lotla bölündüğünde, her bir satır öğesinin basit yuvarlanması, tahsis edilen maliyetlerin toplamını, satışın toplam beklenen değerinden bir kuruş saptırabilir. Adaylar genellikle allocated_quantity * unit_cost hesaplamalarını doğrudan yapar ve yuvarlama bağlamı veya kalıntı kalıntılarını göz önünde bulundurmazlar. Sağlam çözüm, banker yuvarlama (yarısını çift yap) uygular veya dış sorguda bir düzeltme algoritması uygulamadan önce yuvarlanmamış değerleri alt sorguda korur. Bu düzeltme, artık farkı en büyük tahsis hattına ekleyerek toplamı tam olarak satış değerine eşit hale getirirken, denetim izi doğruluğunu korur.