sql ile taklalar - partition by (dense_rank) kullanımı

      Merhabalar.

      3 gün önce yarın yazarım dediğim yazıyı 3 gün sonunda yazabiliyorum bloğa. Gerçi okuyan olur mu biloğa yarar mı bilmiyorum. :) Önceki yazımda partition by kullanımından kısaca bahsetmiştim ve row_number ile bir örnek yapmıştım. Sql sorgularında partition by benim bildiğim 3 farklı sıralama şekli ile kullanılabiliyor. row_number, rank ve dense_rank. partition by ifadesinin sorgu sonucunu parçaladığını ve parçalar içinde sıralama yaptığından bahsetmiştim. Bu üç sıralama türüde işte bu parçalar içindeki satır numaralarının nasıl artacağına karar vermek için kullanılıyor. Kısaca aralarındaki farkı şöyle anlatabilirim.

• row_number : parça içindeki satırlar order by ifadesindeki kolona göre 1 den itibaren numaralandırıyor.
• dense_rank : parça içindeki satırların numarası sadece order by ifadesindeki kolon değeri değiştikçe arttırılıyor.
• rank :  parça içindeki satırların numarası row_number'da olduğu gibi arttırılıyor ama sadece order by ifadesindeki kolon değeri değiştikçe sonuca yansıtılıyor.

      Aralarındaki farklı daha kolay anlamak için internette rastladığım basit bir örneği paylaşarak anlatayım.

WITH T(StyleID, ID)

     AS (SELECT 1,2 UNION ALL

         SELECT 1,2 UNION ALL

         SELECT 1,2 UNION ALL

         SELECT 1,5)

SELECT *,

       RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS 'RANK',

       ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',

       DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'

FROM   T 

      Sorgu sonucu

      Farkı gösteren örneğin ardından dense_rank kullanımı ile ilgili canlı bir örnek yazarak gerçek hayatta nasıl kullanılabileceğini göstermek istiyorum. Örneğim için şöyle bir senaryo düşünün. Bir özel okulda her sınıftan birden fazla şube (1A, 1B, 2A, 2B gibi) var. Her şube için bir öğretmen seçilmiş ve öğrenciler her şubeye en fazla 20 kişi gelecek şekilde kendi bulunduğu sınıf seviyesinde bir şubeye atanması yapılacak. Normalde bu işlem için her sınıf için öğrenci listesini çekip bir döngü ile 20-20 insert edilmesi gerekiyor ama dense_rank sayesinde bu parçalama ve atama işlemini tek sorgu ile yapabiliriz. Şimdi bu sorunu çözeceğini düşündüğüm sorguyu paylaşayım.

SELECT * FROM (

SELECT a.ogretmen_id, a.ogretmen_adi, a.sinif, a.sube, b.ogrenci_id, b.ogrenci_adi,

DENSE_RANK() OVER(PARTITION BY a.sinif, a.ogretmen_id ORDER BY b.ogrenci_id) rn_ogretmen,

DENSE_RANK() OVER)PARTITION BY o.sinif ORDER BY a.ogretmen_id) rn_ogrenci

FROM ogretmen a 

INNER JOIN ogrenci b ON a.sinif = b.sinif

) c

WHERE c.rn_ogretmen BETWEEN (20 * c.rn_ogrenci) - 19 AND (20 * c.rn_ogrenci) 

      Sorgunun yaptığı işi kısaca anlatmak gerekirse her öğretmen karşısına öğretmenin ders vereceği sınıf seviyesindeki tüm öğrenciler getiriliyor ve her sınıf seviyesi için öğrenci sayısı x öğretmen sayısı kadar satır oluşuyor. Satırlara iki farklı numara veriliyor. Birincisi her öğretmen için bir sıra numarası. İkincisi her öğretmenin karşısındaki her öğrenciye bir satır numarası. Öğretmen sıra numarasını 20 ile çarparak öğrenci sırası ile karşılaştırdığımızda her öğretmen için 20 öğrenci seçmiş oluyoruz. Mesela

• 1. öğretmen için (20 * 1) - 19 ve (20 * 1) = 1..20, 
• 2. öğretmen için (20 * 2) - 19 ve (20 * 2) = 21..40,

şeklinde en fazla 20 öğrenci olacak şekilde tüm öğretmenler ve öğrenciler eşleştiriliyor. Bu sorguya eğer biliniyorsa öğrencilerin önceki yıl yada deneme sınavı puanları veya öğretmenlerin performans puanları eklenerek atama işine öğrenci başarı derecesine göre sınıflandırma ve benzeri şeyler katılabilir. insert into ile sorgu sonucu doğrudan istediğiniz tabloya basılabilir