Rank, Dense_Rank, First ve Last komutları ile derecelendirme

PaylaşTweet about this on TwitterShare on FacebookEmail this to someoneShare on Google+Share on LinkedInShare on TumblrShare on RedditPin on PinterestDigg thisFlattr the authorShare on StumbleUponBuffer this page

Rank (ve Dense_Rank) fonsiyonları, belirli bir kritere göre sıralayıp bu sıraya göre derecelendirmek istediğimiz veri kümeleri için oldukça kullanışlı fonksiyonlardır. Örneğin bir pazarlama departmanında ayıp elemanını seçiyoruz, kriter o ay en yüksek satış rakamına ulaşmak. Tabiki max ve group by ile basit bir şekilde bu veriyi elde edebiliriz ama Rank ve diğer fonksiyonların kullanımını görünce max ve group by 'ı aklınıza getirdiğinize pişman olacaksınız d:

 Örnekler ile komutların kullanımına bakalım;

Tablomuzde veriler şu şekilde olsun;

Örnekler departman bazında salary'lere göre veri sorgulamak üzerine olacak.

RANK(): 

30 No'lu departman için salarylere göre bir derecelendirma yapalım;

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         RANK () OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY) "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID = 30

Sonuç;

Evet kayıtları salary alanına göre sıralayıp bize bir derecelendirme sundu sorgumuz. Peki burada 30 nolu departmanda salary değerleri hep farklıydı bakalım aynı olduğu zaman RANK fonksiyonu ne yapacak sapıtacak mı görelim (:

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         RANK () OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY) "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID = 60

60 nolu departmana baktığımızda şöyle bir sonuç ile karşılaşıyoruz;

Evet gördüğümüz gibi kaydı derecelendirelim derken başımıza ne işler gelecek kim bilir : p İşte burada Dense_rank() devreye girecek ama az sonra (;

Dense_Rank() foksiyonundan önce

RANK () OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY) "rank"

satırını biraz "kurcalamak" istiyorum bakalım ortaya neler çıkacak.

30 ve 60 nolu departmanları birlikte sorgulasak sonuç nasıl olurdu görelim;

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         RANK () OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY) "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID IN (30, 60)

şimdi bu sonuca göre bahsettiğim kurcalama işlemine başlayalım. Satırda bir "PARTITION BY A.DEPARTMENT_ID" var bu ne işe yarar acaba, olmazsa olmaz mı… bu ifadeyi kaldıralım bakalım;

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         RANK () OVER (ORDER BY A.SALARY) "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID IN (30, 60)

Sonuç;

Olmazsa olmaz değilmiş ve görüyoruz ki daha önce derecelendirme partition by ifadesinde geçen column değerlerine göre kendi içerisinde yapılıyordu artık genel bir sıralamaya dönüştü sorgu sonucumuz. Salary aynı olan kayıtlar için aynı RANK değeri verildiğinide bir daha görmüş olduk…. İfadeyi kaldırdık sorun olmadı peki birden fazla column'a göre kayıt kümeleri oluşturabilirmiyiz buna da bakalım. Partition by ifadesine hire_date'nin yıl kısmınıda ekleyelim ve görelim sonucu;

SELECT   A.DEPARTMENT_ID,
         TO_CHAR (A.HIRE_DATE, 'YYYY') hire_date,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         RANK ()
            OVER (PARTITION BY A.DEPARTMENT_ID, TO_CHAR (A.HIRE_DATE, 'YYYY')
                  ORDER BY A.SALARY)
            "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID IN (30, 60)

Sonuç;

Sonuçta dönün değerler anlamlı olmayabilir ama bizim amacımız anlamlı veri elde etmek değildi zaten (: gördük ki birden fazla kritere göre de partition by ifadesi kullanılabiliyor.

Order by ifadesinde dikkat edilmesi gereken ufak ama önemli bir nokta var ona da bakıp dense_rank() fonksiyonuna geçelim.

Daha önceki örneklerde aynı salary değeri için aynı rank değerleri verildiğini görmüştük ve bu yüzden rank değerleri sıralı bir şekilde gitmiyor arada bazı rank değerleri atlanıyordu. 1 2 2 4 5 gibi. Bakalım order by ifadesini değiştirsek bu yapı değişecekmi.

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         RANK ()
            OVER (PARTITION BY A.DEPARTMENT_ID
                  ORDER BY A.SALARY, A.EMPLOYEE_ID DESC)
            "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID = 60

Sonuç;

Evet Rank değeri daha önce gördüğümüz gibi 1 2 2 4 5 değerlerinden oluşmuyor, RANK değeride bu yeni durumdan etkileniyor. Bu da ufak gibi görünsede önemli bir detay olarak karşımıza çıktı.

RANK syntax'ını değiştirerek sizde değişik sonuçları görüp yorumlar çıkarabilirsiniz. Gelelim DENSE_RANK() fonksiyonuna.

DENSE_RANK(): Burada RANK fonksiyonu ile ayrıldığı noktayı verip aynı denemeleri burada yapmadan geçeceğim.
 Hatırlarsanız aynı salary'ye sahip kayıt olduğu zaman aynı rank değerini alıyor ve rank değerlerinde kullanılmayan değerler ortaya çıkıyordu bakalım DENSE_RANK ile aynı sorgu nasıl bir sonuç döndürecek.

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         DENSE_RANK () OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY)
            "rank"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID = 60

Sonuç;

Evet görüldüğü gibi Rank oranları salary'ye göre dağıtılmış ve değer atlanmadan satırlara rank verilmiş. İhtiyacınız olan veriye göre hangisini isterseniz kullanırsınız ama daha çok DENSE_RANK ın döndürdüğü veri şekli istenilir herhalde.

Birde unutmadan RANK() 8i, 9i, 10g, 11g de kullanılabiliyor, DENSE_RANK() ise 9i, 10g, 11g de kullanılabiliyor.

Gelelim FIRST ve LAST komutlarına…

Bu komutlarda derecelendirdiğimiz grup içerisinde ilk ve son elemanı döndürür yani verdiğimiz kriterler ve RANK değerine göre, ilk ve son gelen kayıt değerlerini verir.

SELECT   A.DEPARTMENT_ID,
         A.FIRST_NAME || ' ' || A.LAST_NAME employee,
         A.SALARY,
         DENSE_RANK () OVER (PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY) "rank",
         MIN (A.SALARY) KEEP (DENSE_RANK FIRST ORDER BY A.SALARY) OVER (PARTITION BY A.DEPARTMENT_ID) "Lowest",
         MAX (A.SALARY) KEEP (DENSE_RANK LAST ORDER BY A.SALARY) OVER (PARTITION BY A.DEPARTMENT_ID) "Highest"
  FROM   employees a
 WHERE   A.DEPARTMENT_ID IN (30, 60)

Sonuç;

Görüldüğü gibi partition kriterimiz olan departmana göre kayıtlar kümeleniyor dense_rank'ında ilk sırada ve son sırada olan kayıtlar first ve last fonksiyonu ile döndürülüyor.

Umarım faydalı olmuştur. Hoşçakalın.

PaylaşTweet about this on TwitterShare on FacebookEmail this to someoneShare on Google+Share on LinkedInShare on TumblrShare on RedditPin on PinterestDigg thisFlattr the authorShare on StumbleUponBuffer this page
Pivot/Unpivot işlemleri
Autonomous Transaction

Your comments

Loading Facebook Comments ...

Leave a Reply