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

SQL’in En Güçlü Silahı

GROUP BY her grup için tek satır döner. Ama çoğu analitik soru satır koruyup grup istatistiği ister: “her müşterinin yanına toplam harcamasını yaz”, “her siparişin yanına bir önceki siparişin tutarını yaz”. İşte bu iş window function (OVER) işidir.

Production analytics’in %30’u window function + CTE ile yazılır. Bu ders patron olmak için şart.

CTE: Sorguyu Parçala

Büyük bir sorgu iç içe SELECT’lerle yazılınca okunmaz. CTE (WITH ... AS) her parçayı isimlendirip üst üste kurar:

WITH
temiz_siparis AS (
  SELECT
    siparis_id,
    musteri_id,
    CAST(siparis_tarihi AS TIMESTAMP) AS ts,
    toplam_tutar
  FROM {{ ref('stg_siparisler') }}
  WHERE siparis_tarihi >= '2024-01-01'
),
musteri_gun AS (
  SELECT
    musteri_id,
    DATE(ts) AS gun,
    SUM(toplam_tutar) AS gunluk_toplam,
    COUNT(*)          AS gunluk_adet
  FROM temiz_siparis
  GROUP BY 1, 2
)
SELECT
  gun,
  COUNT(DISTINCT musteri_id)           AS aktif_musteri,
  SUM(gunluk_toplam)                   AS toplam_ciro,
  AVG(gunluk_toplam)                   AS ortalama_sepet
FROM musteri_gun
WHERE gun BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY gun
ORDER BY gun;

Okuma akışı yukarıdan aşağı: ham → temiz → günlük özet → son rapor. Her CTE test edilebilir (dbt run --select musteri_gun).

OVER (PARTITION BY …): Grup İçi Hesap

SELECT
  siparis_id,
  musteri_id,
  siparis_tarihi,
  toplam_tutar,
  -- Aynı müşterinin tüm siparişleri toplamı (her satırda görünür)
  SUM(toplam_tutar) OVER (PARTITION BY musteri_id)               AS musteri_toplam,
  -- Aynı müşterinin ortalama siparişi
  AVG(toplam_tutar) OVER (PARTITION BY musteri_id)               AS musteri_ortalama,
  -- Aynı müşterinin en büyük siparişi
  MAX(toplam_tutar) OVER (PARTITION BY musteri_id)               AS musteri_max,
  -- Tüm verideki sıralaması (yüzde kaçlık dilimde)
  PERCENT_RANK() OVER (ORDER BY toplam_tutar)                    AS yuzdelik_dilim
FROM {{ ref('stg_siparisler') }}

PARTITION BY = görünmez GROUP BY. Her satır korunur ama SUM/AVG/MAX o partition (grup) üzerinden hesaplanır.

OVER () boş bırakılırsa partition tüm tablo olur: SUM(toplam_tutar) OVER () = toplam ciro, her satırda tekrar eder.

ORDER BY eklemek kümülatif hesap yapar:

SUM(toplam_tutar) OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi) AS kumulatif

Her satırda, o satıra kadar olan kümülatif toplam. Running total.

ROW_NUMBER, RANK, DENSE_RANK

Üçü de “sıralama” yapar, farkı eşit değerlerde:

Fonksiyon	1, 2, 2, 3 için	Ne zaman
ROW_NUMBER()	1, 2, 3, 4	Benzersiz sıra istiyorsan
RANK()	1, 2, 2, 4	Aynı değer aynı sıra, atlar
DENSE_RANK()	1, 2, 2, 3	Aynı değer aynı sıra, atlamaz

Klasik pattern: “her müşterinin en son siparişi”:

WITH sirali AS (
  SELECT
    siparis_id,
    musteri_id,
    siparis_tarihi,
    toplam_tutar,
    ROW_NUMBER() OVER (
      PARTITION BY musteri_id
      ORDER BY siparis_tarihi DESC, siparis_id DESC
    ) AS rn
  FROM {{ ref('stg_siparisler') }}
)
SELECT * FROM sirali WHERE rn = 1

İkinci ORDER BY (siparis_id DESC) tie-breaker: aynı tarihte iki sipariş varsa son eklenen “en yeni” sayılır. Bu detayı yazmazsan testler bazen yanlış satırı seçer.

LAG ve LEAD: Komşu Satırlar

LAG(sütun, offset): belirtilen offset kadar gerideki satırın değeri. LEAD(sütun, offset): ilerideki satırın değeri.

WITH trend AS (
  SELECT
    musteri_id,
    siparis_tarihi,
    toplam_tutar,
    LAG(toplam_tutar)  OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi) AS onceki_tutar,
    LEAD(toplam_tutar) OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi) AS sonraki_tutar,
    toplam_tutar - LAG(toplam_tutar) OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi) AS fark_onceki
  FROM {{ ref('stg_siparisler') }}
)
SELECT * FROM trend WHERE onceki_tutar IS NOT NULL

Default offset = 1, istersen LAG(toplam_tutar, 3) ile “3 sipariş önce” bakabilirsin.

LAG(..., 1, 0) ile default değer ver: eğer önceki yoksa 0 döner.

FIRST_VALUE ve LAST_VALUE: Pencere İçi İlk/Son

SELECT
  musteri_id,
  siparis_tarihi,
  toplam_tutar,
  FIRST_VALUE(toplam_tutar) OVER (
    PARTITION BY musteri_id
    ORDER BY siparis_tarihi
  ) AS ilk_siparis_tutari,
  LAST_VALUE(toplam_tutar) OVER (
    PARTITION BY musteri_id
    ORDER BY siparis_tarihi
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS son_siparis_tutari
FROM {{ ref('stg_siparisler') }}

LAST_VALUE’nun default frame’i “partition başından şu ana kadar” — bu yüzden UNBOUNDED FOLLOWING eklemek şart, yoksa son satırda bile sadece kendi değerini döner.

NTILE: Yüzdelik Dilim

SELECT
  musteri_id,
  toplam_harcama,
  NTILE(10) OVER (ORDER BY toplam_harcama DESC) AS decile
FROM musteri_ozet

Decile 1 = en çok harcayan %10, decile 10 = en az. RFM analizi için ideal.

Ne Öğrendik?

• CTE (WITH) büyük sorguyu okunabilir adımlara böler.
• OVER (PARTITION BY ...) her satırda grup istatistiği gösterir.
• ROW_NUMBER() grup içi benzersiz sıra; en X pattern’i için.
• LAG/LEAD komşu satırlara bakar; trend ve fark analizi için.
• RANK/DENSE_RANK/NTILE sıralama ve segmentasyon.
• OVER (ORDER BY ...) kümülatif toplam verir (running total).

Küçük Ödev

1. fct_musteri_son_siparis modeli: ROW_NUMBER() ile her müşterinin en son siparişini seç.
2. fct_musteri_trend modeli: LAG() ile “bu sipariş bir öncekinden büyük mü” kolonu ekle.
3. fct_musteri_decile modeli: NTILE(10) ile harcama decile’ı hesapla.
4. Her üç modele description yaz, dbt build ile geçir.

Sıradaki

dbt docs: otomatik veri kataloğu + portfolyo →