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

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

      Merhabalar. 

      Kaç zamandır ortadoğu ve balkanların en tembel yazılımcısı olarak bloğumun adını tembel-programci olarak değiştirmeyi düşünüyorum ama çok üşeniyorum:) Neyse 2 buçuk yıllık İstanbul gezmelerinde yapıp geldikten sonra biz İzmir yazısı yazayım dedim. İstanbul'daki iş hayatımda türlü türlü sql sorgu taklaları attıktan sonra olaya sql yazısı ile geri döneyim dedim. sql sorguları yazarken guruplama ve gurup içindeki en küçük yada en büyük elemanı bulmak çok sık kullandığımız bir işlem. Bu işlemi genelde group by ile çözüyoruz ama bazen gurup içindeki en büyük değeri almak yetmiyor en büyük yada en küçük değerin olduğu tüm satırı almak gerekebiliyor. 

      Mesela veritabanındaki tüm müşterilerin son alışverişlerinin tutarlarını listelemek gibi. Bu gibi durumlarda imdadımıza partition by ifadesi yetişiyor. Konuyu daha anlaşılır kılmak için basit bir örnek yapmak iyi olur sanırım. Bu arada elimin altında hazır olduğu için örnekleri MS Sql Server üzerinde yazacağım. Diğer sql sunucuları içinde sorgular çok farklı değil. 

    SELECT musteri_adi, musteri_no, fatura_tarih, fatura_toplam 

    FROM (

        SELECT m.musteri_adi, m.musteri_no, f.fatura_tarih, f.fatura_toplam, 

            ROW_NUMBER() OVER(PARTITION BY m.musteri_id ORDER BY f.fatura_tarihi DESC) rn 

        FROM fatura f

            INNER JOIN musteri m ON f.musteri_id = m.musteri_id

        WHERE fatura_iptal = 0

    ) fl

    WHERE fl.rn = 1

      Örneği kısaca anlatarak anlamayı kolaylaştırayım. içerideki sorguda müşterilerin tüm faturaları listeleniyor. partition by ile sorgu sonucu musteri_id alanındaki degerlere göre parçalanıyor ve fatura_tarihi alanına göre ters sıra ile her satır rn alanında numaralandırılıyor. Yani her müşteri için sıra 1'den başlayarak her faturası için ilerliyor. Dıştaki sorguda rn = 1 şartı ile her müşteri faturası için sadece ilk kaydın seçilmesi sağlanıyor. 

     Aslında bugün farklı bir konu anlatmayı düşünüyordum. Bu hafta içinde iş yerinde önemli bir sorunu cursor kullanmak yerine dense_rank ile çok harika bir şekilde çözmüş ve bununla ilgili bir örnek anlatacaktım ama konuyu geriden alınca saat geç oldu. Artık yarın yada bir kaç yıl sonra onuda yazarım...