Yukon ile gelen T-SQL Yenilikleri
T-SQL Window Fonksiyonları (Window Functions)
SQL Server 2005 (Yukon) ile gelen T-SQL yeniliklerinden bir kısmını da Window fonksiyonları (Window Table Functions) oluşturmaktadır.
Window fonksiyonları sql sorgusu ile elde edilen sonuç setini her fonksiyonun kendi karakterine göre parçalara ayırarak yine bu parçalara kendi fonksiyonlarına göre değer üretirler. Bu değerler SELECT listesinde veya ORDER BY sıralama kriterleri içinde kullanılabilirler.
Ancak bu fonksiyonlar (window functions) WHERE cümleciği içinde kullanılamazlar. Çünkü window fonksiyonlarının uygulanacağı kayıt setini SELECT sorgusu üzerindeki WHERE bölümündeki koşullar belirler.
Window fonksiyonları kullanılırken OVER anahtarı ile kayıt setinin parçalara bölünmesi sağlanır.
Yukon yani SQL Server 2005 Beta 2 ile iki tip window fonksiyonu tanımlanmıştır.
Bunlar Ranking Window Functions ve Aggregate Window Functions 'tır. Kısaca Ranking Window Functions, kayıt seti içinde derecelendirme fonksiyonlarıdır. Bunlar:
ROW_NUMBER
RANK
DENSE_RANK
ve
NTILE fonksiyonlarıdır.
Ranking (derecelendirme) Window Functions non-deterministic fonksiyonlardır. Yukarıdaki fonksiyonlar kayıt setinin karakteristiğine göre OVER cümleciği ile bölümlendirildiğinde aynı değerleri döndürebilirler.
ROW_NUMBER
ROW_NUMBER fonksiyonu kayıt setinin herbir bölümüne atamak üzere 1 'den başlayarak sıralı giden bir sayı üretir.
Örneklemek gerekirse aşağıdaki SELECT cümlesi çalıştırıldığında,
SELECT
ROW_NUMBER() OVER (ORDER BY Substring(Ad,1,1)) SiraNo,
Substring(Ad,1,1) ilk_harf,
Ad
FROM Adlar
elde edilecek sonuç şöyledir.
Örnekteki SELECT ifadesinde Adlar tablosundaki tüm değerler seçilmiş ve bu değerler OVER anahtarından sonra gelen ORDER BY içindeki kritere göre (yani ad kolonundaki metin değerin ilk harfi) bölümlendirilmiş. Bu kriteri ayrıca SELECT listesi içinde bir ilk_harf kolon ismi ile görüntüledik. ROW_NUMBER fonksiyonunun bu sıralamaya göre bigint tipinde 1'den başlayarak sıralı değerler ürettiğini görebilirsiniz.
ROW_NUMBER fonksiyonunun söz dizimi aşağıdaki gibidir.
ROW_NUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
İkinci bir örnek olarak aşağıdaki SELECT cümleciğiniz verebiliriz. Burada ROW_NUMBER fonksiyonu GROUP BY içinde de kullanılmıştır.
SELECT
ROW_NUMBER() OVER (ORDER BY Substring(Ad,1,1)) SiraNo,
Substring(Ad,1,1) ilk_harf,
Ad
FROM Adlar
ORDER BY
ROW_NUMBER() OVER (ORDER BY Len(Ad))
Şimdi de sayfalama yaptığımızı varsayalım. Her sayfada 5 kayıt göstermek üzere 2. sayfadaki kayıtlarımızı seçeceğimiz SELECT ifadesini yazmaya çalışalım.
Önce parametrelerimizi tanımlayalım ve örneğimiz için 2. sayfa ve her sayfada 5 kayıt için gerekli değerleri parametrelerimize atayalım.
Bir Common Table Expressions (CTE) oluşturarak bu görüntü içinde ROW_NUMBER kullanarak kayıtlarımızı sıralayabiliriz. Ve Common Table Expression (CTE) ardından gelen SELECT ifadesinin koşul bölümünde ikinci sayfada yeralacak 6 ve 10 dahil bu iki sayı arasında numaralandırılmış kayıtları seçebiliriz.
DECLARE @SayfaNo int, @SayfadakiKayit int
SELECT @SayfaNo = 2, @SayfadakiKayit = 5;
WITH AlfabetikAdListesiCTE
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY Ad) SiraNo,
Ad
FROM Adlar
)
SELECT
SiraNo - @SayfadakiKayit,
Ad
FROM AlfabetikAdListesiCTE
WHERE
SiraNo BETWEEN (@SayfaNo - 1) * @SayfadakiKayit + 1 AND @SayfadakiKayit * @SayfaNo
İkinci sayfamız aşağıdaki kayıtlardan oluştu.
RANK ve DENSE_RANK
RANK fonksiyonu ile bölümlendirilmiş kayıt setindeki her bir bölüme bir rank numarası verilir. Bu kayıt setinde alt bölüm içindeki her kayıt aynı rank numarasına sahiptir. Bu numaralandırma yine 1'den başlar. Ancak ROW_NUMBER fonksiyonunda olduğu gibi ardarda gelen bir sıralandırma ile gitmek zorunda değildir. Bir bölüm içinde birden fazla kayıt varsa rank numaralarında atlamalar olacaktır.
Örneğin;
SELECT
RANK() OVER (ORDER BY Substring(ad,1,1)) RankNo,
Substring(Ad,1,1) ilk_harf,
Ad
FROM Adlar
sorgusunun sonucu aşağıdaki gibidir.
Eğer herbir bölümlendirilmiş kısma ardarda sıralı bir numaralandırma uygulanması isteniyorsa DENSE_RANK fonksiyonu kullanılmalıdır.
SELECT
DENSE_RANK() OVER (ORDER BY Substring(ad,1,1)) RankNo,
Substring(Ad,1,1) ilk_harf,
Ad
FROM Adlar
SQL NTILE Fonksiyonu
NTILE fonksiyonu SELECT ifadenizde WHERE koşuluna uyan kayıtları OVER ve ORDER BY ile belirtilen sıralamaya göre dizilmiş şekilde sizin parametre olarak geçeceğiniz bir sayıya bölerek her bölüme bir sıra numarası verir.
NTILE fonksiyonunun söz dizimi diğer Window Functions (Row_Number, Rank, Dense_Rank) sözdizimlerinden farklıdır.
NTILE (integer_expression) OVER ( [ < partition_by_clause > ] < order_by_clause > )
Burada integer_expression pozitif bir tam sayı olmalıdır. Örneğin SELECT ifadeniz size 40 kayıt döndürsün. NTILE fonksiyonuna da 5 parametresini geçtiniz. Bu durumda ilk 8 kayıt için bölüm numarası olarak 1, ikinci sekiz kayıt için bölüm numarası 2 olacak ve son 8 kayıt için de 5 bölüm numarası olacaktır.
Örneklerimiz için kullandığımız tabloya dönersek,
SELECT
NTILE(5) OVER (ORDER BY Ad) TileNo,
Ad
FROM Adlar
sorgusu sonucunda aşağıdaki sonucu elde ettiğimizi görebilirsiniz. Modüler bölünme sonucunda artan değerler ilk gruptan başlayarak sıra ile her gruba 1 kayıt sayısı daha eklenerek dağıtılır.
13 = 3 + 3 + 3 + 2 + 2
