Büyük sorgu 6: SCD 2 fiyat boyutu ve nokta-zaman birleştirme

Fiyat geçmişinden geçerlilik aralıkları kurmak, satışları satış anındaki fiyatla eşleştirmek ve sapmaları ölçmek.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Soru

Finans ekibi soruyor: “Satış satırlarındaki birim fiyat, o gün geçerli liste fiyatıyla tutarlı mı?” Ürün tablosu yalnızca güncel fiyatı tutar; geçmişi price_updates tablosundaki güncellemelerden geri kurmak gerekir. Bu, veri ambarlarında “yavaş değişen boyut Tip 2” ve “nokta-zaman (as-of) birleştirme” olarak bilinir.

Sorgu

Büyük sorgu nasıl okunur?Yukarıdan aşağı, her CTE'yi ayrı bir tablo gibi düşün. Bir adımı anlamadıysan o CTE'nin adını en sondaki SELECT'e yazıp yalnızca onu çalıştır: SELECT * FROM adim_adi LIMIT 20;

WITH olay AS (           -- 1) Fiyat olayları: başlangıç fiyatı + her güncelleme
  SELECT product_id, price AS fiyat, '2000-01-01 00:00:00' AS baslangic, 0 AS kaynak
  FROM products
  WHERE product_id IN (SELECT product_id FROM price_updates)
  UNION ALL
  SELECT product_id, new_price, updated_at, 1
  FROM price_updates
),
aralik AS (              -- 2) Her fiyatın geçerli olduğu aralık: bir sonraki olayın başlangıcına kadar
  SELECT product_id, fiyat, baslangic,
         LEAD(baslangic) OVER (PARTITION BY product_id ORDER BY baslangic) AS bitis
  FROM olay
),
boyut AS (               -- 3) SCD 2 boyutu: açık uçlu aralığa uzak bir tarih ver, güncel bayrağı ekle
  SELECT product_id, fiyat, baslangic,
         COALESCE(bitis, '9999-12-31 00:00:00') AS bitis,
         CASE WHEN bitis IS NULL THEN 1 ELSE 0 END AS guncel
  FROM aralik
),
satis AS (               -- 4) Ekim–Aralık 2024 satış satırları
  SELECT oi.product_id, o.order_date, oi.unit_price, oi.quantity
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.order_date >= '2024-10-01'
)
SELECT s.product_id,          -- 5) Nokta-zaman birleştirme: satış anında geçerli fiyat satırı
       COUNT(*) AS satir,
       ROUND(AVG(s.unit_price), 2) AS ort_satis_fiyati,
       ROUND(AVG(b.fiyat), 2) AS ort_liste_fiyati,
       SUM(CASE WHEN b.guncel = 1 THEN 1 ELSE 0 END) AS guncel_fiyatla
FROM satis s
JOIN boyut b ON b.product_id = s.product_id
            AND s.order_date >= b.baslangic AND s.order_date < b.bitis
GROUP BY s.product_id
ORDER BY s.product_id;

Adım adım

Adım	Ne yapar?	Çıktının bir satırı
`olay`	Her fiyatın başladığı an: başlangıç fiyatı (çok eski bir tarih) ve her güncelleme	31, 520, 2000-01-01 / 31, 550, 2024-10-11
`aralik`	LEAD ile bir sonraki olayın başlangıcı = bu fiyatın bitişi	31, 520, 2000-01-01 → 2024-10-11
`boyut`	Açık uçlu aralığa 9999 tarihi, güncel satıra bayrak	31, 550, 2024-10-11 → 9999-12-31, 1
`satis`	Ekim 2024 sonrası satış satırları	31, 2024-10-20, 549, 1
`son SELECT`	Satışı, tarihi aralığın içine düşen boyut satırıyla birleştirir	31, 4, 548.75, 535, 3

Yarı açık aralık burada da kritik

Birleştirme koşulu s.order_date >= b.baslangic AND s.order_date < b.bitis şeklindedir. Bitişi dahil etseydin (<=), tam güncelleme anına denk gelen bir satış iki fiyat satırıyla eşleşir ve satır çoğalırdı.

Bilerek bırakılmış bir basitleştirmeBaşlangıç fiyatı, products tablosundaki güncel fiyattan türetilemez; gerçek bir sistemde ilk fiyat ayrı bir kaynaktan gelir. Bu örnekte ilk aralık için güncel fiyat kullanıldı; alıştırmalardan biri bunu düzeltiyor.