Rütbeleme Fonksiyonları ile Kayıtları Sıralamak

Rütbeleme(Ranking) fonksiyonları satır(row) fonksiyonları olarak da anılırlar. Rütbeleme fonksiyonlarının temel işlevi, bir sorgu sonucunda dönen sonuç kümesinde sıralayıcı unsur olarak sayıların yer aldığı ek sütunlar türetmektir. Rütbeleme fonksiyonları neticede verileri sıralamaya yönelik değerler ürettikleri için, etkilerini anlamak için örneklerin sonuçlarına göz atmak anlamayı kolaylaştıracaktır.

Bütün rütbeleme fonksiyonları için geçerli genel bir kullanım sentaksı şu şekilde verilebilir:

Rutbeleme_fonksiyonu
OVER([ parcalama cumlesi ] ORDER BY sutun_isimleri )

Rütbeleme fonksiyonları sentaks basitliğinin yanı sıra, etkin bir sütun üretme yöntemidir. SQL Sorgu optimizasyonu yaparken, değerleri türetmek üzere sadece bir defa değerleri tarar. Bu işlemi şayet sıralayıcı unsur üstünde bir indeks varsa, bu indeks üstünden, yoksa da bir kere veriyi tarayıp sıraladıktan sonra türetir.

Bu fonksiyonlardan her birinin işlevlerini ayrıntılarıyla ele alalım:

ROW_NUMBER

Belirtilen ifadeye göre satırları sıraladıktan sonra, her bir satır için artan numaraların yer aldığı bir sütun üretir.

Örnek:

tblUrun tablosundaki ürün numaralarının yanına birden başlayarak aralıksız artacak şekilde bir sütun türetelim:

SELECT ROW_NUMBER() OVER(ORDER BY urunKod) AS satirNo,
urunKod, urunAd, listeFiyat,dovizKod
FROM tblUrun

şekil-1: ROW_NUMBER() fonksiyonu ile urunKod sütunu sırasında, birden başlayan ek bir sütun oluşturulması.

Dikkat edilirse, sorgu sonucu elde edildikten sonra her bir satır için diğerlerinden ayırd edici bir ardışık numara verilmektedir.

DİKKAT:
Satır sırasını sorgu neticelendikten sonra otomatik olarak verebiliyor olmak nerede işe yarayabilir sorusuna verilebilecek pratik cevap, veritabanından çekilen kayıtların sıralandıktan sonra sayfa sayfa gösterilmesi esnasında olabileceği şeklinde bir kanıya yöneltebilir. Ancak bu durum, aynı anda çok kişi tarafından erişilmesi durumlarında performans kısıcı bir etkiye sahiptir.

Satır numaralarını, veritabanından sayfalar halinde veri çekmek için kullanacaksanız, tüm sonucu bir defa üretip, geçici bir tabloda sakladıktan sonra, takip eden bütün sorgular için aynı satır numaraları kullanmak, daha etkin bir yol sağlanmış olur.

Örnek:
tblUrun tablosunda yer alan ürünleri, #tbSayfalanabilirUrun adında geçici bir tabloya, ilk sütunu 1'den başlayan ardışık sayılardan oluşacak şekilde tamponlayalım:

SELECT ROW_NUMBER() OVER(ORDER BY urunKod DESC) AS satirNo,*
INTO #tbSayfalanabilirUrun
FROM urunTest
GO
-- takip eden iki satırı daha sonra anlamlandıracağız.
--CREATE UNIQUE CLUSTERED INDEX idx_uc_satirNo
--ON #tbSayfalanabilirUrun (satirNo)

İPUCU:
Tablolar üstünde indeks tanımlamayı bir sonraki bölümde ele alacağız. Geçici tablolar üstünde de sorgularınızı hızlandırmak üzere indeksler tanımlayabilirsiniz.

İfadesi ile oturum için bir geçici tablo oluşturduktan sonra bu geçici tablo üstünden bir sorgulama için genel formül şu şekilde verilebilir:

SELECT satirNo, urunAd, urunFiyat, dovizKod
FROM #sayfalanabilirUrun
WHERE satirNo BETWEEN ( sayfaNo -1) * sayfaBoyu + 1
AND sayfaNo * SayfaBoyu
ORDER BY dovizKod DESC

İPUCU:

Herhangi bir tablo üstünde, IDENTITY(1,1) ile tanımlı bir sütun da olsa, arada silinmiş kayıtlar olabilir. Bu nedenle IDENTITY sütun üstünden bir sayfalama hesaplaması her zaman doğru olmayabilir.

Parçalı Satır Numaraları Vermek

Bazı durumlarda, belli bir değere göre, satır numaralamanın yeniden başlamasını isteyebiliriz. Örneğin, her bir markadaki ürünleri ayrı ayrı satır numaralarıyla sıralamak gerekirse nasıl bir yöntem izlememiz gerekir.

Örnek:
Her bir marka kodu için yeniden başlayan bir satır numarası üretmek gerekmektedir. İlgili sonucu üretecek sorguyu oluşturalım.

SELECT urunKod, ROW_NUMBER() 
	OVER(PARTITION BY markaKod ORDER BY urunKod DESC) as MarkayaBagliUrunSirasi,
  markaKod,urunAd,listeFiyat,dovizKod
FROM tblUrun

RANK() ve DENSE_RANK() Fonksiyonları

RANK() ve DENSE_RANK() fonksiyonları ile verilen bir sütuna göre her bir grup için aynı sayı değeri üretilerek rütbeleme yapılır. Aralarındaki temel fark, RANK() fonksiyonu, değişen her grup için başlangıç satırının satır numarası ile bir rütbeleme yaparken, DENSE_RANK() fonksiyonu gruplar arası geçişte bir artan sayı üretir. Bu iki fonksiyon, aynı değere sahip satırları aynı sayı ile rütbelendirmek gerektiğinde kullanılıdır.

Örnek:

SELECT urunKod,
	ROW_NUMBER() OVER(ORDER BY urunKod) AS [ROW_NUM],
	RANK() OVER(ORDER BY markaKod) AS [RANK],
	DENSE_RANK() OVER(ORDER BY markaKod) AS [DENSE_RANK],
	markaKod,urunAd,listeFiyat,dovizKod
FROM tblUrun
WHERE markaKod IN(1,93,94,97,200)
ORDER BY markaKod 

şekil-3: markaKod sütunu bazında satır korumalı ve satır korumasız rütbeleme işlemi sonuçları

DİKKAT:
RANK ile DENSE_RANK sütunları arasındaki temel fark: RANK sütunundaki her bir grubun ilk satırının rütbe numarasının, aynı satırın satır numarası ile aynı olduğuna dikkat edin. Bu, bir çeşit basamak fonksiyonu gibi düşünülebilir. DENSE_RANK 'da ise her grup için artan numaralar alır, satır numarasından bağımsızdır.

İPUCU:
RANK ve DENSE_RANK fonksiyonları ROW_NUMBER() fonksiyonunun aksine her zaman deterministiktir. Çünkü aynı satırlar için her zaman aynı değerler üretirler. ROW_NUMBER() fonksiyonu sadece ORDER BY cümleciğinin bütün satırlar için tam bir sıra tayin edebiliyor olması halinde deterministiktir.

Sonuçları n Gruba Ayırmak: NTILE( ifade )

NTILE fonksiyonu ile verilen bir sıralamaya göre, satırlardan ifade kadar birbirine en yakın sayyda eleman içeren grup çıkartılarak rütbe ile sabitlenir. Çalışma prensibi şu şekildedir.

n:Satır Sayısı
ifade:grup sayısı olmak üzere

önce n/ifade işlemi yapılır. Buradan elde edilen kalan 0 ise, n satırdan n/ifade kadar elemandan oluşan ifade kadar grup oluşturulur. Ama bölme işleminde kalan 0'dan farklı ise bu fark ilk baştaki gruplarda fazladan eleman olacak şekilde dağıtılır.

Örnek:

Ürünleri ürün kodlaryna göre syraladıktan sonra, 200 farklı gruba dağıtacak sorgu:

SELECT urunKod ,
--ROW_NUMBER() OVER(ORDER BY urunKod DESC) AS rownum,
NTILE ( 200 ) OVER ( ORDER BY urunKod DESC) AS [GRUPKOD] ,
markaKod, urunAd, dovizKod, ListeFiyat
FROM tblUrun 

şekil-4: Son gruplarda(75 nolu gruba kadar 5 ürün, 76 ve daha sonrakilerde 4 ürün bulunmakta) daha az ürün olacak şekilde bir düzenleme yapıldığına dikkat edin.

İfadesinin sonucunda 875 satır olduğundan, 875/200 işleminin sonucu kalan 75 ve bölüm 4'tür. Neticede ilk 75 grup 5 satırdan, takip eden diğer gruplar da 4 satırdan oluşacaktır.

Sonuç ve Değerlendirme

Bazen sıralayıcı unsur olarak, sorgu sonuçlarında ek sütunlara ihtiyaç duyduğumuzda IDENTITY() fonksiyonu işe yaramayabilir. Bu tür durumlarda satır rütbeleme fonksiyonlarından yararlanabiliriz.