Grup Başına En Yüksek N Kayıt: ROW_NUMBER, RANK ve LATERAL Yöntemleri

Sorun nedir ve LIMIT neden yetmez?

Tüm tabloda en yüksek birkaç kaydı getirmek kolaydır: sıralayıp LIMIT koyarsınız. Ama "her kategorinin en çok satan ürünleri", "her müşterinin son siparişleri" ya da "her bölgenin en çok satış yapan temsilciler" gibi sorular grup başına sınır ister. LIMIT sonucun tümüne uygulandığı için grup başına çalışmaz.

Bu kalıba top N per group denir ve raporlamada çok sık karşınıza çıkar. Çözümün ana yolları şunlardır: pencere fonksiyonuyla grup içi sıra numarası vermek, korelasyonlu bir alt sorgu ya da LATERAL birleştirmeyle her gruba ayrı bir sınırlı sorgu çalıştırmak ve koşullu bir birleştirmeyle satırı sayarak elemek. Pratikte en yaygın ve okunaklı olanı pencere fonksiyonudur.

ROW_NUMBER ile temel çözüm

ROW_NUMBER, her pencerede satırlara sıra numarası verir. PARTITION BY ile grupları, ORDER BY ile grup içi sıralamayı belirlersiniz. Sonra dış sorguda numarayı filtrelersiniz. Çünkü pencere fonksiyonu WHERE içinde doğrudan kullanılamaz, bir alt sorguya ya da CTE'ye sarmak gerekir.

Örneğin kategori başına satış tutarı en yüksek ürünler:

WITH ranked AS ( SELECT kategori, product_name, sale_amount, ROW_NUMBER() OVER ( PARTITION BY kategori ORDER BY sale_amount DESC ) AS row_num FROM product_sales ) SELECT kategori, product_name, sale_amount FROM ranked WHERE row_num <= :n;

Buradaki :n, istediğiniz kayıt sayısını taşıyan bir parametredir. Yalnızca ilk sıradaki kaydı isterseniz row_num değerinin ilk sırayı gösterdiği satırı seçin. Bu desen "her müşterinin son siparişi" gibi sorularda da aynıdır; sıralama sütunu tarih olur ve DESC yazılır.

Eşitlik durumu: ROW_NUMBER, RANK ve DENSE_RANK

Aynı değere sahip birden fazla ürün varsa ne olacağını baştan kararlaştırmalısınız. ROW_NUMBER eşit değerlere bile farklı numara verir; hangisinin önce geleceği ORDER BY'a bağlı kalır ve belirsizdir. Sonuç sorgudan sorguya değişebilir. Eşitlikleri deterministik yapmak için ORDER BY'a ek bir sütun, örneğin ürün kimliğini ekleyin.

RANK eşit değerlere aynı sıra numarasını verir ve sonrasındaki numaraları atlar. Eşit olanların hepsini dahil etmek istiyorsanız RANK ile filtre yazmak, birden fazla ürünün aynı sırada olduğu durumda N'den fazla satır döndürebilir. DENSE_RANK de aynı değere aynı sırayı verir ama numara atlamaz.

Karar şu soruya bağlıdır: "tam olarak N satır mı istiyorum, yoksa N'inci sıradaki değerin eşitlerini de görmek mi?" Rapor gereksinimi yazılı değilse iş birimine sorun; yanlış seçim sessizce yanlış sonuç üretir.

LATERAL ve korelasyonlu alt sorgu yaklaşımı

Bazı veritabanları, FROM içinde LATERAL alt sorgusuna izin verir. PostgreSQL belgesine göre LATERAL ile işaretlenmiş bir alt sorgu, kendinden önceki FROM öğelerinin sütunlarına başvurabilir ve her satır için ayrı çalıştırılır. Bu, top N per group için doğal bir ifadedir: önce grupları listeler, sonra her grup için sıralayıp sınırlı sorgu çalıştırırsınız.

SELECT k.kategori, u.product_name, u.sale_amount FROM kategoriler k CROSS JOIN LATERAL ( SELECT product_name, sale_amount FROM product_sales us WHERE us.kategori = k.kategori ORDER BY sale_amount DESC LIMIT :n ) u;

Bu yazımda grup sayısı azsa ve (kategori, sale_amount) üzerinde uygun bir indeks varsa, her grup için yalnızca ilk kayıtlar okunur ve tüm tabloyu sıralamak gerekmez. Grup sayısı çok fazlaysa pencere fonksiyonu daha dengeli olabilir. Hangisinin hızlı olduğuna veri dağılımı karar verir. Bu sözdizimi her sistemde yoktur; bazı sistemlerde karşılığı farklı adla bulunur.

Eski tarz alternatif, her satır için aynı gruptan kendisinden daha yüksek kaç kaydın bulunduğunu sayan korelasyonlu alt sorgudur. Çalışır ama okunması zordur ve büyük tablolarda genellikle yavaştır.

Performansı yürütme planıyla denetleme

Pencere fonksiyonlu çözüm, sıralamayı grup bazında yapar; tablo büyükse bu sıralama maliyetlidir. Hızı etkileyen şeyler şunlardır:

• PARTITION BY ve ORDER BY sütunlarını kapsayan bir indeks, sıralama adımını azaltabilir ya da kaldırabilir.
• Süzülebilecek satırları pencere öncesinde filtreleyin: yalnızca son dönem verisi lazımsa tarih filtresini CTE'nin içine koyun.
• Yalnızca gerekli sütunları seçin; geniş satırlar sıralamayı yavaşlatır.
• Yürütme planını (EXPLAIN gibi) alın ve sıralama adımının nerede yapıldığına bakın.

Veritabanları arasında fark vardır: bazı sistemler pencere sonucunu doğrudan filtreleyen QUALIFY gibi bir ifade sunar ve ara CTE yazmayı gereksiz kılar. Kullandığınız sistemde bulunup bulunmadığını belgesinden öğrenin.

Örnek girdi ve beklenen çıktı

Örneğin product_sales tablosunda kategori sütunu "Kırtasiye" olan kalem, defter ve silgi satırları, kategori sütunu "Kitap" olan roman, sözlük ve atlas satırları bulunsun. N değerini küçük tutup yukarıdaki ROW_NUMBER sorgusunu çalıştırırsanız her kategori için yalnızca satış tutarı en yüksek satırlar gelir; Kırtasiye grubunun sıralaması Kitap grubunun sıralamasını etkilemez. Çünkü PARTITION BY gruplar arasında duvar örer.

Doğrulama için şu kontrolleri yapın. Önce sonuçtaki her kategori için satır sayısının N'i aşmadığına bakın. Sonra bir kategoriyi elle sıralayıp çıktıdaki ilk satırın gerçekten en yüksek tutar olduğunu görün. Eşit tutarlı satırları kasıtlı olarak ekleyip hangisinin geldiğini deneyin; bu sınama, ORDER BY'a ek sütun koymak gerekip gerekmediğini hemen gösterir.

Sık yapılan hatalar

• Eşitlikleri düşünmemek: ORDER BY tek sütunluysa aynı değerli satırların hangisinin seçildiği belirsiz kalır; ek bir sütunla sırayı netleştirin.
• ROW_NUMBER yerine RANK kullanıp tam olarak N satır beklemek.
• Pencere fonksiyonunu WHERE içinde doğrudan çağırmaya çalışmak.
• NULL değerlerin sıralamadaki yerini unutmak: azalan sıralamada NULL'ların başa ya da sona gelmesi sisteme göre değişir; NULLS LAST gibi açık ifadeyi destekleyen sistemlerde kullanın.
• Grup anahtarını eksik yazmak: PARTITION BY'da yanlış sütun, tüm sonucu bir gruba çevirir.
• Sonucu doğrulamamak: her grup için dönen satır sayısını COUNT ile kontrol edin; N'den fazla satır dönen grup varsa eşitlik ya da mükerrer kayıt vardır.

Bu beceriyi Mindset programlarında nerede çalışırsınız

Grup içi sıralama pencere fonksiyonlarının en tipik uygulamasıdır. Mindset'in SQL ile İleri Veri Dönüşümü ve Modelleme programının ilk modülü pencere fonksiyonlarına ayrılmıştır; SQL ile Veri Sorgulama (İleri) programında da pencere fonksiyonlarıyla ileri analiz ve alt sorgular ile korelasyon teknikleri bulunur.

Rapor hazırlayan analistler, satış ve ürün ekipleri için en çok satan ürünleri ya da son işlemleri çıkaran veri mühendisleri bu bilgiyi doğrudan kullanır. Kurumsal ekiplerde bu kalıp ortak bir sorgu parçası olarak yazılırsa herkesin eşitlik kuralı aynı olur.