Her Grupta En Yüksek N Kayıt: SQL'de Top-N per Group

"Her kategoride en pahalı üç ürün", "her müşterinin en son siparişi", "her şubede en çok satan ürün". Hepsi aynı kalıbın bir çeşitlemesidir: bir gruba göre böl, her grubun içinde sırala, en tepedeki N tanesini al. Adı "top-N per group" ve düz bir ORDER BY ... LIMIT ile çözülmez, çünkü LIMIT tüm sonuçtan keser, her gruptan değil.

Neden basit LIMIT yetmiyor?

ORDER BY fiyat DESC LIMIT 3 size tüm tablodaki en pahalı üç ürünü verir; hepsi tek bir kategoriden olabilir. Siz ise her kategori için ayrı ayrı ilk üçü istiyorsunuz. İşte pencere fonksiyonları tam da bunun için var.

ROW_NUMBER ile kalıp nasıl kurulur?

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) her grup içinde 1'den başlayan bir sıra numarası üretir. Sonra bu numarayı dışarıdan filtrelersiniz. Her kategoride en pahalı üç ürün:

SELECT kategori, ad, fiyat
FROM (
  SELECT kategori, ad, fiyat,
         ROW_NUMBER() OVER (
           PARTITION BY kategori
           ORDER BY fiyat DESC
         ) AS sira
  FROM urunler
) t
WHERE sira <= 3
ORDER BY kategori, sira;

PARTITION BY kategori "her kategori kendi içinde" demektir; ORDER BY fiyat DESC en pahalıya 1 numarayı verir. İç sorguda numarayı üretir, dış sorguda sira <= 3 ile ilk üçü süzersiniz. Numarayı doğrudan WHERE'de kullanamazsınız; bu yüzden iki katman gerekir.

ROW_NUMBER, RANK, DENSE_RANK: hangisi?

Üçü de sıralar ama beraberliklerde farklı davranır. ROW_NUMBER eşit değerlere bile farklı numara verir (kesinlikle N satır istersiniz). RANK eşitlere aynı numarayı verip sonrasını atlar (1,1,3). DENSE_RANK atlama yapmaz (1,1,2). "En pahalı 3 fiyat" (beraberlikler dahil olsun) istiyorsanız DENSE_RANK; "tam 3 satır" istiyorsanız ROW_NUMBER doğru seçimdir.

MySQL 5.7 gibi pencere fonksiyonu olmayan yerlerde ne yaparım?

Pencere fonksiyonları MySQL'e 8.0 ile geldi. Daha eski bir sürümde kaldıysanız, ilişkili (correlated) bir alt sorguyla "bu satırdan daha pahalı, aynı kategoride kaç ürün var?" diye sayar ve bu sayı N'den küçük olanları alırsınız:

SELECT u.kategori, u.ad, u.fiyat
FROM urunler u
WHERE (
  SELECT COUNT(*)
  FROM urunler x
  WHERE x.kategori = u.kategori
    AND x.fiyat > u.fiyat
) < 3
ORDER BY u.kategori, u.fiyat DESC;

Bu yöntem küçük tablolarda gayet iyi çalışır; büyük tablolarda pencere fonksiyonu hem daha hızlı hem daha okunurdur. Bir ayrıntı: eşit değerler aynı sayımı paylaştığı için beraberlik olduğunda N'den fazla satır dönebilir; kesin N satır gerekiyorsa pencere fonksiyonlu kalıp şart. Mümkünse veritabanını güncelleyip ilk kalıba geçin.

Bu iki katmanlı kalıbı ezberlemek yerine PerSight'a "her kategoride en pahalı 3 ürünü göster" ya da "her müşterinin son siparişini getir" diyebilirsiniz. Doğru pencere fonksiyonunu kendisi seçer, kurduğu sorguyu da gösterir.