Window Fonksiyonları 2 – Sıralama Fonksiyonları – ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, NTILE

Ranking(Sıralama) Fonksiyonları bir window fonksiyonu tipidir. Tüm pencere fonksiyonları gibi OVER() ifadesi ile kullanılırlar.

Genel Syntax aşağıdaki gibidir.

RANKING_FUNCTIONS ()         –buraya fonksiyon adı gelir.

OVER (

[PARTITION BY alan1,alan2,.. ]    –isteğe bağlı

ORDER BY alan1,alan2,.

)

ROW_NUMBER

Tablo içinde satırları belirli gruplara bağlayarak ilgili satırlara 1 den başlayarak özel sıra numarası atanmasını sağlar.

Row_Number() fonksiyonu OVER ifadesi ile kullanılır. OVER’dan sonra parantez içinde gruplanacak alanlar ya da sıralamayı belirleyecek alanlar eklenir.

Sıralama fonksiyonları için PARTITION BY kullanılması mecburi değildir. Partition by kullanılarak veri kümesi küçük gruplara bölünebilir ve sıralama bu gruplara özel oluşturulabilir. Kullanılmadığı zaman veri kümesini tek bir grup olarak algılar.

Bu bir sıralama fonksiyonu olduğu için ORDER BY kullanılması mecburidir. Böylece fonksiyon satırın konumunu belirleyecektir.

DESC azalan, ASC artan sırada demektir. Fonksiyon varsayılan olarak ASC sıralama yapar. Azalan sırada görebilmek için DESC yazılmalıdır.

(Örneklerde Northwind veritabanı kullanılmıştır.)

(Veri kümesinin daha anlaşılır olması için where ProductID=’1′ and EmployeeID=’3′

Eklenerek satır sayısını azalttık.)

Aşağıdaki örnekte Quantity alanına göre en yüksek sayıdan başlayıp azalan bir sıralama oluşturuldu.

SELECT b.ShipCountry,Quantity,ROW_NUMBER() OVER (ORDER BY Quantity desc) AS RN

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

Aşağıdaki örnekte artan sıralama yaptı. Asc varsayılan olduğu için yazmaya gerek duymadık.

SELECT b.ShipCountry,Quantity,ROW_NUMBER() OVER (ORDER BY Quantity ASC) AS RN

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

Yukarıdaki örnekte gruplamak için herhangi bir alan eklenmedi. Bu yüzden tüm veriyi bir grup olarak algıladı.  Örneğin USA alanı aynı şehir ama 3 farklı satır sayısına sahip.

Veriyi şehirlere göre gruplayarak sıralayalım. Bunun için PARTITION BY ekleyeceğiz. USA ülkesi kendi içinde sıralandı.

SELECT b.ShipCountry,Quantity,ROW_NUMBER() OVER (PARTITION BY b.ShipCountry ORDER BY Quantity ASC) AS RN

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

Aşağıda hem ülke hem şehire göre grupladık.  Şehirler birbirinden farklı olduğu için sıra numaraları her satır için 1 atandı.

SELECT b.ShipCountry,b.ShipCity,Quantity,ROW_NUMBER() OVER (PARTITION BY b.ShipCountry,b.ShipCity ORDER BY Quantity ASC) AS RN

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

RANK()

1den başlayarak satırlara özel sıra numarası verilmesini sağlar. Çoklayan satırlarda aynı numara üzerinden devam eder. Çoklayan satırlardan sonra sıra numarasında boşluklar oluşur çünkü çoklayan kayıt sayısı kadar numara atlar.

Aynı örnek tipi üzerinden devam edelim. Aşağıdaki örnekte şehire göre artan(şehir metin olduğu için alfabetik) bir sıralama yaptı. USA tekrarladığı için 3 satırı da 5.sıra olarak atadı. Çoklayan kayıt sayısı kadar numara atladığı için USA’den sonra gelen kayıt 6 değil 8 olarak devam etti.

SELECT b.ShipCountry,RANK() OVER (ORDER BY b.ShipCountry ASC) AS RN

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

DENSE_RANK()

1den başlayarak satıralara özel sıra numarası verilmesini sağlar. Çoklayan satırlarda aynı numara üzerinden devam eder. RANK ile karşılaştırıldığında tek fark çoklayan satırlardan sonra kaldığı numara üzerinden devam eder.

SELECT b.ShipCountry,DENSE_RANK() OVER (ORDER BY b.ShipCountry ASC) AS RN

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

PERCENT_RANK()

Percent_Rank() fonksiyonu isminden de anlaşılacağı gibi veriyi yüzdelik olarak sıralar. 0 ile 1 arasında değerler alır. 0 dan başlayarak 1 e doğru giderken aşağıdaki formül üzerinden dağılım yapar. Rank değeri 1den başladığı için İlk kayıt her zaman 0dır.

(rank – 1) / (toplam satır – 1)

SELECT A.OrderID,b.ShipCountry,

RANK() OVER (ORDER BY b.ShipCountry DESC) AS Rank,

PERCENT_RANK() OVER (ORDER BY b.ShipCountry DESC) AS Percent_Rank

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

ÇIKTI:

NTILE

NTILE fonksiyonu veri kümesini belirtilen bir parametreye göre gruplara böler.

NTILE(bölünecek grup sayısı) OVER (

PARTITION BY alan1, alan2,..ORDER BY alan1, alan2,.. (asc/desc?)

)

Aşağıdaki sorgudan gelen kayıtları 3 gruba böldük. Sorgudan dönecek ilk kayıttan başlayarak grup sırasına göre satır numaraları atandı. Gruplardaki satır sayıları eşit olması gerekmediği için 3.grupta sadece 2 satır kaldı.

SELECT A.OrderID,b.ShipCountry,NTILE(3) OVER (ORDER BY b.ShipCountry DESC) AS NTILE

FROM [Order Details] A

INNER JOIN Orders B on a.OrderID=b.OrderID

where ProductID='1' and EmployeeID='3'

4 gruba bölünmek istenseydi toplamda 8 kayıttan 2şerli 4 tane grup oluşacaktı.

Bir sonraki yazıda Window fonksiyonlarından Analytic (Value) Functions ile devam edeceğiz.

İlham alınan kaynaklar:

http://www.sqlekibi.com/sql-server/window-fonksiyon-turleri.html/

SQL PERCENT_RANK

http://www.kodyaz.com/articles/article.aspx?articleid=19

https://jaceklaskowski.gitbooks.io/mastering-spark-sql/content/spark-sql-functions-windows.html