Window functions + CTEs: "her müşterinin en son siparişi"

“Her Müşterinin En Son Siparişi Ne Zaman?”

Elif bir sabah uyanıyor ve soruyor: “Hangi müşteri son 1 aydır sipariş vermedi? Hangisi en sadık? Kim bu hafta ilk kez geldi?” Bunlar pencere fonksiyonu (window function) soruları. Normal GROUP BY ile yapamazsın çünkü her satır için grup içi sıralama lazım.

CTE: “Tarifin Adımları”

Önce küçük bir araç: CTE (Common Table Expression). Büyük bir sorguyu adım adım yazarsın, her adımın bir adı olur. Tarifin “Adım 1”, “Adım 2”si gibi.

WITH gunluk_satis AS (
  SELECT
    DATE(siparis_tarihi) AS gun,
    SUM(toplam_tutar)    AS ciro
  FROM {{ source('raw', 'siparisler') }}
  GROUP BY 1
),
haftalik_satis AS (
  SELECT
    DATE_TRUNC(gun, WEEK) AS hafta,
    SUM(ciro)             AS haftalik_ciro
  FROM gunluk_satis
  GROUP BY 1
)
SELECT * FROM haftalik_satis ORDER BY hafta DESC LIMIT 4

İlk CTE günlük ciroyu hesaplar, ikinci CTE onu haftalığa toplar, son sorgu son 4 haftayı getirir. İç içe SELECT yazmaktan çok daha okunur.

OVER (PARTITION BY): “Grup İçi Hesap”

Şimdi asıl konu: window function. OVER() clause’u, fonksiyonun hangi grup üzerinde çalışacağını söyler.

SELECT
  siparis_id,
  musteri_id,
  toplam_tutar,
  -- Aynı müşterinin tüm siparişlerinin toplamı
  SUM(toplam_tutar) OVER (PARTITION BY musteri_id) AS musteri_toplam_harcama,
  -- Aynı müşterinin ortalama siparişi
  AVG(toplam_tutar) OVER (PARTITION BY musteri_id) AS musteri_ortalama
FROM {{ source('raw', 'siparisler') }}

PARTITION BY musteri_id = “her müşteriyi ayrı grup say.” Her satırda, o müşterinin tüm siparişlerinin toplamı görünür.

GROUP BY’dan farkı: GROUP BY her grup için tek satır döner, window function her satırı koruyup grup istatistiğini yanına yazar.

ROW_NUMBER: “Sıralama”

Elif’in en sevdiği pattern: “Her müşterinin en yeni siparişini bul.”

WITH sirali AS (
  SELECT
    siparis_id,
    musteri_id,
    siparis_tarihi,
    toplam_tutar,
    ROW_NUMBER() OVER (
      PARTITION BY musteri_id
      ORDER BY siparis_tarihi DESC
    ) AS sira
  FROM {{ source('raw', 'siparisler') }}
)
SELECT *
FROM sirali
WHERE sira = 1

ROW_NUMBER() OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi DESC) = “her müşteri için siparişleri tarihe göre tersten sırala, 1, 2, 3… numarası ver.” Dış sorgu sira = 1 alır → her müşterinin en son siparişi.

RANK() benzer ama eşit değerlerde aynı sıra (1, 1, 3). DENSE_RANK() ise (1, 1, 2).

LAG: “Önceki Değere Bak”

“Bir önceki siparişinden ne kadar daha fazla harcadı?” sorusu:

SELECT
  musteri_id,
  siparis_tarihi,
  toplam_tutar,
  LAG(toplam_tutar) OVER (
    PARTITION BY musteri_id
    ORDER BY siparis_tarihi
  ) AS onceki_siparis_tutari,
  toplam_tutar - LAG(toplam_tutar) OVER (
    PARTITION BY musteri_id
    ORDER BY siparis_tarihi
  ) AS fark
FROM {{ source('raw', 'siparisler') }}

LAG(sütun) = “bir önceki satırdaki o sütun.” Müşterinin siparişleri kronolojik sırayla, her satırda “önceki siparişin tutarı” görünür, farkı hesaplanır.

LEAD(sütun) tersidir: “bir sonraki satırdaki değer.”

Ne Öğrendik?

• CTE (WITH ... AS) büyük sorguyu adım adım yazmayı sağlar.
• OVER (PARTITION BY ...) her satırda grup istatistiğini gösterir.
• ROW_NUMBER() grup içi benzersiz sıra verir; “her grubun en X’i” için birebir.
• LAG() bir önceki satıra, LEAD() bir sonraki satıra bakar.

Küçük Ödev

1. musteri_siralama modeli yaz: ROW_NUMBER() ile her müşterinin en yeni siparişini işaretle.
2. onceki_ile_fark modeli yaz: LAG() ile bir önceki siparişten farkı hesapla.
3. dbt run çalıştır, modelleri sorgula.

Sıradaki

dbt docs: tarif defterini güzel bir site olarak yayınla →