Rütbeleme Fonksiyonları ( row_number(), rank(), dense_rank() )
25 Ekim 2016 Salı
Rütbeleme fonksiyonları sql 2005 ile birlikte gelmiştir ve en genel ifadeyle verileri sıralamaya yönelik değerler üretirler.
En basit haliyle yazım şekli şöyledir:
ROW_NUMBER()
OVER( [PARTITION BY value_expression, ... [n]] order_by_clause )
Etkilerini anlamak için örnekler yapmakta fayda var. Uygulama için AdventureWorks2012 veritabanını kullanacağım.
Row_Number() Özellikleri:
* 1 den başlayan sıralı bir numara üretirler,
* ORDER BY deyimini kullanmak zorunludur,
* PARTITION BY deyimi isteğe bağlı kullanılır,
* PARTITION BY deyimi kullanıldı ise sıralama her bir gruplama sütunu için 1 den başlar.
Dikkat ettiyseniz, SQL'de sorgulama yaparken sonuç kümesinin en sol tarafında sıralı olarak sayılar görünür. İşte row_number() ın yaptığı iş bu sıralı sayıları sorgu sonucunun içine dahil ederek, bunu farklı sorgulamalarda kullanmaktır. Örnek vermek gerekirse; kimi zaman identity(1,1) kolonunuzda(PK) aradaki bazı satırlar silinmiş olabilir, ancak sizinde buna göre sorgulama ihtiyacınız olabilir. Mesela parça parça sayfalama gibi.. Ve yahut tablonuzda bir şekilde "duplike" yani mükerrer kayıt olabilir ROW_NUMBER() kullanarak bu kayıtları tekilleştirebilirsiniz.
Canlı örnek üzerinden ROW_NUMBER() kullanımına bakalım:
Bakınız Resim-1.
Örnek resimde görüldüğü gibi sorgu sonucunda her bir satır için ardışık bir numara üretildi.
ROW_NUMBER() ın PARTITION ile Birlikte Kullanımı :
Bazen satır numaralarının yeniden başlamasını isteyebilirsiniz. Mesela şehir isimlerini satış oranlarına göre artan veya azalan sırada numaralandırmak istersek ne yapmamız gerekir? Örneğimize bakacak olursak:
Resim-2'deki sorgu sonucundan örnek bir küme alıp yorumlamak gerekirse; Northwest'e bakalım.. Bugüne kadar olan satış rakamları(SalesYTD) büyükten küçüğe sıralanmış ve en büyük rakamın başlangıç değeri, 'satır' sütununda 1 den başlayacak şekilde 3'e kadar numara atanması yapılmış.
RANK() ve DENSE_RANK() Kullanımı
Bu ikisi arasında çok ince bir ayrım vardır. Konuyu daha net kavramak için evvela bir örnek yapmak istiyorum. Sonrasında bu örnek üzerinden konuşuyor olacağız.
Aşağıdaki script'i DB nize kurmanızı rica ediyorum zira örnekleri bu tablo üzerinden anlatıyor olacağım.
create table Employees
(
ID int,
Name varchar(50),
Gender varchar(50),
Salary int
)
GO
INSERT INTO Employees VALUES(1, 'Mark', 'Male', 8000)
INSERT INTO Employees VALUES(2, 'John', 'Male', 8000)
INSERT INTO Employees VALUES(3, 'Pam', 'Female', 5000)
INSERT INTO Employees VALUES(4, 'Sara', 'Female', 4000)
INSERT INTO Employees VALUES(5, 'Todd', 'Male', 3500)
INSERT INTO Employees VALUES(6, 'Mary', 'Female', 6000)
INSERT INTO Employees VALUES(7, 'Ben', 'Male', 6500)
INSERT INTO Employees VALUES(8, 'Jodi', 'Female', 4500)
INSERT INTO Employees VALUES(9, 'Tom', 'Male', 7000)
INSERT INTO Employees VALUES(10, 'Ron', 'Male', 6800)
Resim-3'deki örnek'de henüz PARTITION kullanmadım. Bu haliyle inceleyecek olursak: ilk 2 satır kendi içinde "salary" kolonu üzerinden bir grup oluşturmuş. Değişimi fark edebilmek için seçmiş olduğum sütunların devamına bakalım yani 3. satıra..RANK() fonksiyonu satır numarasının kaldığı yerden sırasına devam ediyor ancak DENSE_RANK() fonksiyonu ise gruplar arası geçişte +1 sayı üreterek yoluna devam ediyor..
Anahtar Not: RANK() ve DENSE_RANK() fonksiyonları aynı değere sahip satırları, aynı sayı ile rütbelendirmenin gerektiği durumlarda oldukça kullanışlıdır.
Good>Better>Best felsefesi uyarınca konuya hakimiyetimizi artırmak amacıyla şimdi'de PARTITION BY kullanımına bakalım.
Bakınız Resim-4.
Bu örnektede görüldüğü gibi 4. satırdan sonra her iki fonksiyonda kendini sıfırlar ve satır tekrar bir'den başlar. Gruplama içeren "Gender" alanından sonra ise RANK satırı, bir üst'de anlattığım örnekteki gibi kaldığı yerden devam eder, DENSE_RANK'da ise bir önceki satırın üzerine +1 eklenerek rütbelendirme işlemi kaldığı yerden devam eder.
Bu 3 fonksiyonun özetini geçmek gerekirse:
Aşağıdaki script'i çalıştırmanızı rica ederim.
Tablomuzda mükerrer kayıt yaratmak amacıyla bir önceki tabloyu silip yerine yeni kayıtlar ekliyoruz.
delete from Employees
GO
insert into Employees values (1, 'Mark', 'Male', 8000)
insert into Employees values(2, 'John', 'Male', 8000)
insert into Employees values(3, 'Pam', 'Female', 8000)
insert into Employees values(4, 'Sara', 'Female', 4000)
insert into Employees values(5, 'Todd', 'Male', 3500)
Bakınız Resim-5.
Özetle; Row_number, arada mükerrer kayıt olsada olmasada 1 den başlayıp 1 er ardışık sırayla sayı ataması yapar. Rank, 1 den başlayan ve artan oranda sayı ataması yapar. Şayet grup içeren kolon var ise kendi arasında hiyerarşik düzende sayı ataması yapar, grup sonlandığında ise baştan kaçıncı satırda kaldı ise ordan devam eder. Dense_Rank, diğer fonksiyonlar gibi 1 den başlar ancak grup sonlandığında kendi sütun grubundaki sayıya +1 ekleyerek yoluna devam eder.
Bu son yapmış olduğum örneği PARTITION BY ekleyerek sonucu görmenizi tavsiye ediyorum.
Umarım yararlı bir yazı olmuştur. Bir sonraki makalemde görüşmek üzere.İyi çalışmalar herkese.