NTILE, PERCENT_RANK, CUME_DIST ve Frame
Giriş — Verinin İstatistiksel Yüzü
Sıralama fonksiyonlarını, değer fonksiyonlarını ve aggregate OVER'ı öğrendin. Bu derste window function'ların son ama en güçlü parçalarını ele alacağız: verileri gruplara bölmek (NTILE), yüzdelik sıralama (PERCENT_RANK, CUME_DIST) ve pencere çerçevesini (frame) hassas şekilde kontrol etmek.
Bu konular "ileri seviye" olarak etiketlenir ama gerçek dünyada sürekli karşına çıkar: müşterileri harcama segmentlerine ayırmak (NTILE), bir çalışanın maaşının yüzde kaçlık dilimde olduğunu belirlemek (PERCENT_RANK), ya da hareketli ortalama hesaplarken tam olarak hangi satırları dahil edeceğini kontrol etmek (frame specification).
🎯 Analoji: Sınav sonuçlarını düşün. Notun 85 — ama bu iyi mi? Sınıfın en yüksek notu 95, en düşüğü 40 ise, 85 gayet iyi. Ama herkes 90+ almışsa, 85 düşük. PERCENT_RANK sana "sınıfın yüzde kaçından daha iyisin?" sorusunun cevabını verir. NTILE ise "ilk çeyrekte misin, ikinci çeyrekte misin?" der.
NTILE(n) — Satırları Eşit Gruplara Böl
NTILE(n) satırları mümkün olduğunca eşit n gruba böler ve her satıra grup numarasını atar:
-- Çalışanları maaşa göre 4 çeyreğe (quartile) ayır
SELECT
first_name,
salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;+------------+--------+----------+
| first_name | salary | quartile |
+------------+--------+----------+
| Ali | 8000 | 1 | ← Alt çeyrek (Q1)
| Zeynep | 9000 | 1 |
| Ayşe | 10000 | 1 |
| Burak | 10500 | 2 | ← İkinci çeyrek (Q2)
| Fatma | 11000 | 2 |
| Mehmet | 12000 | 2 |
| Selin | 12500 | 3 | ← Üçüncü çeyrek (Q3)
| Deniz | 13000 | 3 |
| Ece | 14000 | 3 |
| Can | 15000 | 4 | ← Üst çeyrek (Q4)
| Hakan | 16000 | 4 |
| Berna | 18000 | 4 |
+------------+--------+----------+12 çalışan 4 gruba bölündü: her grupta 3 kişi. Peki 10 çalışan olsaydı? 10 / 4 = 2.5 — tam bölünmüyor. NTILE bu durumu şöyle çözer: ilk gruplara 1'er fazla satır verir.
10 satır, NTILE(4):
Grup 1: 3 satır (10/4 = 2.5, yukarı yuvarla)
Grup 2: 3 satır
Grup 3: 2 satır
Grup 4: 2 satırMüşteri Segmentasyonu — NTILE'ın En Yaygın Kullanımı
-- Müşterileri harcamalarına göre 5 segmente ayır
SELECT
c.first_name,
c.last_name,
total_spent,
NTILE(5) OVER (ORDER BY total_spent DESC) AS segment,
CASE NTILE(5) OVER (ORDER BY total_spent DESC)
WHEN 1 THEN 'VIP'
WHEN 2 THEN 'Premium'
WHEN 3 THEN 'Standart'
WHEN 4 THEN 'Düşük'
WHEN 5 THEN 'Pasif'
END AS segment_name
FROM (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
) spending
JOIN customers c ON spending.customer_id = c.customer_id;+------------+-----------+-------------+---------+--------------+
| first_name | last_name | total_spent | segment | segment_name |
+------------+-----------+-------------+---------+--------------+
| Ali | Yılmaz | 45000 | 1 | VIP |
| Zeynep | Demir | 38000 | 1 | VIP |
| Mehmet | Kaya | 32000 | 2 | Premium |
| Ayşe | Çelik | 25000 | 2 | Premium |
| ... | ... | ... | ... | ... |
+------------+-----------+-------------+---------+--------------+Bu segmentasyon pazarlama kampanyalarında çok kullanılır: VIP müşterilere özel teklifler, pasif müşterilere geri kazanım kampanyaları.
NTILE ile Decile (10'luk Dilim)
-- Ürünleri fiyata göre 10 dilime ayır
SELECT
product_name,
price,
NTILE(10) OVER (ORDER BY price) AS price_decile
FROM products;
-- Decile 1: en ucuz %10
-- Decile 10: en pahalı %10PERCENT_RANK() — Yüzdelik Sıralama
PERCENT_RANK() bir satırın yüzdelik dilimini döndürür: 0 ile 1 arasında bir değer. "Bu satır, tüm satırların yüzde kaçından büyük?" sorusunu cevaplar.
Formül
PERCENT_RANK = (rank - 1) / (toplam_satır - 1)SELECT
first_name,
salary,
RANK() OVER (ORDER BY salary) AS rnk,
ROUND(PERCENT_RANK() OVER (ORDER BY salary), 3) AS pct_rank
FROM employees;+------------+--------+-----+----------+
| first_name | salary | rnk | pct_rank |
+------------+--------+-----+----------+
| Ali | 8000 | 1 | 0.000 | ← En düşük (0%)
| Zeynep | 9000 | 2 | 0.091 | ← %9.1'lik dilim
| Ayşe | 10000 | 3 | 0.182 |
| Burak | 10500 | 4 | 0.273 |
| ... | ... | ... | ... |
| Berna | 18000 | 12 | 1.000 | ← En yüksek (100%)
+------------+--------+-----+----------+Ali'nin PERCENT_RANK'ı 0: kimsenin altında değil, en düşük. Berna'nın PERCENT_RANK'ı 1: herkesin üstünde.
Maaş Pozisyonu Analizi
-- Her çalışanın departmanındaki yüzdelik dilimi
SELECT
first_name,
department_id,
salary,
ROUND(PERCENT_RANK() OVER (
PARTITION BY department_id
ORDER BY salary
) * 100, 0) AS percentile
FROM employees
ORDER BY department_id, salary;+------------+---------------+--------+------------+
| first_name | department_id | salary | percentile |
+------------+---------------+--------+------------+
| Ali | 1 | 8000 | 0 |
| Zeynep | 1 | 9000 | 50 |
| Fatma | 1 | 12000 | 100 |
| Ayşe | 2 | 10000 | 0 |
| Mehmet | 2 | 12000 | 100 |
+------------+---------------+--------+------------+Zeynep, departmanındaki çalışanların %50'sinden fazla maaş alıyor. Fatma %100'ünden fazla — yani en yüksek maaşlı.
CUME_DIST() — Kümülatif Dağılım
CUME_DIST() bir satırın kümülatif dağılım değerini döndürür: "Bu değere eşit veya daha düşük kaç satır var?" sorusunu cevaplar. Değer 0 ile 1 arasındadır ama asla 0 olmaz (PERCENT_RANK'tan farklı).
Formül
CUME_DIST = (bu değere eşit veya küçük satır sayısı) / (toplam satır sayısı)SELECT
first_name,
salary,
ROUND(PERCENT_RANK() OVER (ORDER BY salary), 3) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY salary), 3) AS cume_dist
FROM employees;+------------+--------+----------+-----------+
| first_name | salary | pct_rank | cume_dist |
+------------+--------+----------+-----------+
| Ali | 8000 | 0.000 | 0.083 | ← PERCENT_RANK: 0, CUME_DIST: 0.083
| Zeynep | 9000 | 0.091 | 0.167 |
| Ayşe | 10000 | 0.182 | 0.250 |
| Burak | 10500 | 0.273 | 0.333 |
| Fatma | 11000 | 0.364 | 0.417 |
| Mehmet | 12000 | 0.455 | 0.583 | ← 12000'den düşük 7/12 = 0.583
| Selin | 12000 | 0.455 | 0.583 | ← Aynı maaş, aynı CUME_DIST
| ... | ... | ... | ... |
| Berna | 18000 | 1.000 | 1.000 |
+------------+--------+----------+-----------+PERCENT_RANK vs CUME_DIST — Fark Nedir?
| Fonksiyon | Formül | Min | Max | Soru |
|---|---|---|---|---|
| PERCENT_RANK | (rank-1)/(n-1) | 0 | 1 | "Yüzde kaçından büyüğüm?" |
| CUME_DIST | count(<=x)/n | >0 | 1 | "Yüzde kaçı benden küçük veya eşit?" |
Pratik kullanımda: bir maaşın "yüzde kaçlık dilimde" olduğunu belirlemek istiyorsan PERCENT_RANK, "maaşı bu kadar veya daha az olan kaç kişi var?" diyorsan CUME_DIST kullan.
Pratik Kullanım: Fiyat Segmentasyonu
-- Ürünleri fiyat yüzdeliklerine göre etiketle
SELECT
product_name,
price,
ROUND(CUME_DIST() OVER (ORDER BY price), 2) AS price_percentile,
CASE
WHEN CUME_DIST() OVER (ORDER BY price) <= 0.25 THEN 'Bütçe Dostu'
WHEN CUME_DIST() OVER (ORDER BY price) <= 0.50 THEN 'Orta Segment'
WHEN CUME_DIST() OVER (ORDER BY price) <= 0.75 THEN 'Üst Segment'
ELSE 'Premium'
END AS price_tier
FROM products
ORDER BY price;Frame Specification — Pencere Çerçevesinin İnce Ayarı
Frame specification, window function'ın hangi satırlar üzerinde çalışacağını tam olarak belirler. Bu, window function'ların en güçlü ama en az anlaşılan kısmı.
Varsayılan Frame
ORDER BY kullanıldığında varsayılan frame:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWORDER BY kullanılmadığında varsayılan frame:
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGFrame Syntax
fonksiyon() OVER (
[PARTITION BY ...]
[ORDER BY ...]
frame_tipi BETWEEN başlangıç AND bitiş
)Frame tipleri:
ROWS— Fiziksel satır sayısı (her zaman net)RANGE— Değer aralığı (eşit değerler birlikte işlenir)GROUPS— Eşit değer grupları (MySQL 8.0.2+)
Başlangıç ve bitiş seçenekleri:
UNBOUNDED PRECEDING— Partition'ın ilk satırıN PRECEDING— N satır/değer/grup öncesiCURRENT ROW— Şu anki satırN FOLLOWING— N satır/değer/grup sonrasıUNBOUNDED FOLLOWING— Partition'ın son satırı
ROWS BETWEEN — En Yaygın Kullanım
-- 3 günlük hareketli ortalama (önceki 2 gün + bugün)
SELECT
order_date,
daily_revenue,
ROUND(AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 0) AS moving_avg_3d
FROM (
SELECT order_date, SUM(total_amount) AS daily_revenue
FROM orders
GROUP BY order_date
) daily;-- Centered moving average (ortada hareketli ortalama)
-- 2 önceki + şu anki + 2 sonraki = 5 satır
SELECT
order_date,
daily_revenue,
ROUND(AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
), 0) AS centered_ma_5d
FROM daily_revenue_view;ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING — ortadaki değeri hem geçmişle hem gelecekle birlikte hesapla. Bu, trend analizinde çok kullanılır.
ROWS vs RANGE Farkı
Bu fark önemli ve çoğu zaman gözden kaçar:
-- ROWS: Fiziksel satır sayısı
-- RANGE: Değer bazlı aralık
-- Örnek: Aynı maaşı alan 2 çalışan var
SELECT
first_name, salary,
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_sum,
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_sum
FROM employees;+------------+--------+----------+-----------+
| first_name | salary | rows_sum | range_sum |
+------------+--------+----------+-----------+
| Ali | 8000 | 8000 | 8000 |
| Zeynep | 9000 | 17000 | 17000 |
| Mehmet | 12000 | 29000 | 41000 | ← ROWS: sadece Mehmet'e kadar
| Fatma | 12000 | 41000 | 41000 | ← RANGE: Fatma da dahil (aynı değer!)
| Deniz | 13000 | 54000 | 54000 |
+------------+--------+----------+-----------+ROWS ile Mehmet ve Fatma farklı running total alır (29000 vs 41000). RANGE ile ikisi de aynı (41000) — çünkü RANGE, aynı maaşı (12000) aynı "değer grubu" olarak işler.
⚠️ Dikkat: ORDER BY kullanıldığında varsayılan frame
RANGE,ROWSdeğil! Bu, eşit değerlerde beklenmedik sonuçlara neden olabilir. Kesin sonuç istiyorsan her zaman ROWS kullan.
Pratik Frame Örnekleri
-- Son 7 günlük satış toplamı
SUM(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
-- Gelecek 3 siparişin toplamı (tahmin için)
SUM(total_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING
)
-- Tüm partition (ORDER BY olsa bile)
AVG(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- Sadece önceki satırlar (şu anki hariç)
SUM(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)Gerçek Dünya Örnekleri
Örnek 1: Kapsamlı Satış Dashboard'u
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(total_amount) AS revenue,
-- Yıl başından bu yana toplam (YTD)
SUM(SUM(total_amount)) OVER (
ORDER BY DATE_FORMAT(order_date, '%Y-%m')
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_revenue,
-- 3 aylık hareketli ortalama
ROUND(AVG(SUM(total_amount)) OVER (
ORDER BY DATE_FORMAT(order_date, '%Y-%m')
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 0) AS ma_3m,
-- Bu ayın yüzdelik dilimi (yıl içinde)
ROUND(PERCENT_RANK() OVER (
ORDER BY SUM(total_amount)
) * 100, 0) AS revenue_percentile,
-- Çeyreklik segment
NTILE(4) OVER (ORDER BY SUM(total_amount)) AS quarter_segment
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;Örnek 2: Maaş Analizi Raporu
SELECT
e.first_name,
d.department_name,
e.salary,
-- Departman içi çeyreklik
NTILE(4) OVER (PARTITION BY e.department_id ORDER BY e.salary) AS salary_quartile,
-- Şirket geneli yüzdelik dilim
ROUND(PERCENT_RANK() OVER (ORDER BY e.salary) * 100, 0) AS company_percentile,
-- Departman ortalamasından sapma
ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 0) AS diff_from_dept_avg,
-- Maaş aralığındaki pozisyon (min-max arası)
ROUND(
(e.salary - MIN(e.salary) OVER (PARTITION BY e.department_id)) * 100.0 /
NULLIF(MAX(e.salary) OVER (PARTITION BY e.department_id) -
MIN(e.salary) OVER (PARTITION BY e.department_id), 0),
0
) AS dept_salary_position_pct
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.salary;Örnek 3: Ürün Fiyat Bandı Analizi
-- Ürünleri kategori içinde fiyat bandlarına ayır ve istatistikleri göster
SELECT
c.category_name,
p.product_name,
p.price,
NTILE(3) OVER (PARTITION BY p.category_id ORDER BY p.price) AS price_band,
CASE NTILE(3) OVER (PARTITION BY p.category_id ORDER BY p.price)
WHEN 1 THEN 'Ekonomik'
WHEN 2 THEN 'Orta'
WHEN 3 THEN 'Premium'
END AS band_name,
ROUND(CUME_DIST() OVER (PARTITION BY p.category_id ORDER BY p.price) * 100, 0) AS price_percentile,
-- Bu ürünün üstünde ve altında kaç ürün var
COUNT(*) OVER (PARTITION BY p.category_id) -
RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS cheaper_count,
RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) - 1 AS more_expensive_count
FROM products p
JOIN categories c ON p.category_id = c.category_id
ORDER BY c.category_name, p.price;Örnek 4: Sipariş Hareketli Toplamı
-- Her müşterinin son 3 siparişinin toplamı (frame kullanarak)
SELECT
c.first_name,
o.order_id,
o.order_date,
o.total_amount,
SUM(o.total_amount) OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS last_3_orders_total,
AVG(o.total_amount) OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS last_3_orders_avg
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY c.first_name, o.order_date;Frame Specification Görsel Özet
UNBOUNDED PRECEDING
↓
┌──────────────────────────────┐
│ Satır -3 (3 PRECEDING) │
│ Satır -2 (2 PRECEDING) │
│ Satır -1 (1 PRECEDING) │ ← ROWS BETWEEN 2 PRECEDING
│ ★ CURRENT ROW ★ │ ← AND CURRENT ROW
│ Satır +1 (1 FOLLOWING) │
│ Satır +2 (2 FOLLOWING) │ ← ROWS BETWEEN CURRENT ROW
│ Satır +3 (3 FOLLOWING) │ ← AND 2 FOLLOWING
└──────────────────────────────┘
↓
UNBOUNDED FOLLOWINGPostgreSQL Farklılıkları
-- PostgreSQL: GROUPS frame tipi daha erken desteklendi
-- MySQL 8.0.2+ da destekler
-- PostgreSQL: EXCLUDE clause
SUM(salary) OVER (
ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
EXCLUDE CURRENT ROW -- Şu anki satırı hariç tut
)
-- MySQL'de EXCLUDE yok
-- PostgreSQL: percentile_cont ve percentile_disc (ordered-set aggregates)
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;
-- MySQL'de median hesabı: PERCENT_RANK veya NTILE ile yapılırÖzet
NTILE(n): Satırları n eşit gruba böler — segmentasyon, çeyreklik analiz için ideal
PERCENT_RANK(): 0-1 arası yüzdelik sıralama — "yüzde kaçından büyüğüm?"
CUME_DIST(): Kümülatif dağılım — "yüzde kaçı benden küçük veya eşit?"
Frame specification: Pencere çerçevesini hassas kontrol — ROWS, RANGE, GROUPS
ROWS BETWEEN N PRECEDING AND CURRENT ROW: Hareketli ortalama, hareketli toplamROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: Tüm partition'ı kapsaROWS fiziksel satır, RANGE değer bazlı — eşit değerlerde farklı sonuç verir
Varsayılan frame ORDER BY varken
RANGE...CURRENT ROW, yokkenRANGE...UNBOUNDED FOLLOWING
Sıkça Yapılan Hatalar
NTILE'ın eşit bölmediğini unutmak — 10 satır 3 gruba bölünürse: 4, 3, 3. Tam eşit değil.
PERCENT_RANK ve CUME_DIST'i karıştırmak — PERCENT_RANK ilk satırda 0, CUME_DIST asla 0 olmaz. Farklı formüller, farklı anlamlar.
Frame specification'da ROWS ve RANGE farkını bilmemek — Eşit değerler varsa RANGE beklenmedik sonuçlar verebilir. Net sonuç için ROWS tercih et.
LAST_VALUE'da frame'i düzeltmeyi unutmak — Varsayılan frame CURRENT ROW'da biter, LAST_VALUE kendi satırını döndürür.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGekle.Frame olmadan hareketli ortalama hesaplamak —
AVG() OVER (ORDER BY date)kümülatif ortalamadır, hareketli ortalama değil! Hareketli ortalama içinROWS BETWEEN N PRECEDING AND CURRENT ROWşart.
AI Asistan
Sorularını yanıtlamaya hazır