İleri SQL: Pencere Fonksiyonları (Window Functions) (1 Görüntüleyici)

Geleneksel toplama fonksiyonları (COUNT(), SUM(), AVG() vb.) GROUP BY ile birlikte kullanıldığında sonuç kümesindeki satırları gruplandırır ve her grup için tek bir özet değer döndürür. Ancak bazen, her bir orijinal satır için bir toplama değeri hesaplamanız gerekebilir veya bir satırı, onunla ilgili diğer satırlarla karşılaştırmanız gerekebilir. İşte tam bu noktada Pencere Fonksiyonları devreye girer.
Pencere Fonksiyonları Nedir? Aggregates vs. Windows
Pencere fonksiyonları, tıpkı toplama fonksiyonları gibi bir dizi satır üzerinde işlem yapar. Ancak temel fark şudur: Pencere fonksiyonları sonuç kümesindeki satırları gruplandırmaz. Bunun yerine, her bir satır için tanımlanmış "pencere"deki (ilgili satırlar kümesi) hesaplamayı gerçekleştirir ve orijinal satırları döndürür.

• 
  
• Toplama Fonksiyonları (GROUP BY ile): Satırları birleştirir ve grup başına tek satır döndürür.
  Kod:Panoya kopyala

   SELECT Kategori, AVG(Fiyat) FROM Urunler GROUP BY Kategori;

  (Bu, her kategori için tek satır ve o kategorinin ortalama fiyatını verir.)
• 
  
• Pencere Fonksiyonları: Her orijinal satır için bir değer döndürür, ancak bu değer ilgili satırlar (pencere) üzerinden hesaplanır.
  Kod:Panoya kopyala

   SELECT UrunAd, Fiyat, Kategori, AVG(Fiyat) OVER (PARTITION BY Kategori) AS KategoriOrtalamaFiyat FROM Urunler;

  (Bu, her ürün satırını döndürür ve her satırın yanında kendi kategorisinin ortalama fiyatını gösterir.)

Pencere Fonksiyonu Sözdizimi: OVER Yan Tümcesi
Pencere fonksiyonları, fonksiyon adından sonra gelen OVER yan tümcesi ile tanımlanır. OVER yan tümcesi, fonksiyonun üzerinde çalışacağı "pencereyi" (satır kümesini) tanımlar.

• 
  
• PARTITION BY: Sonuç kümesini bağımsız bölümlere (partition) ayırır. Pencere fonksiyonu her partition içinde ayrı ayrı uygulanır. GROUP BY gibi düşünebilirsiniz ama satırları birleştirmek yerine her satırı kendi partition'ı içindeki diğer satırlarla ilişkilendirir. Atlanırsa, tüm sonuç kümesi tek bir partition olarak kabul edilir.
• 
  
• ORDER BY: Her partition içindeki satırları belirli bir sütun veya sütunlara göre sıralar. Bu, sıralamaya bağımlı pencere fonksiyonları (sıralama, lider/gecikme gibi) için kritiktir.

Yaygın Pencere Fonksiyonları

1. 
   
2. Sıralama Fonksiyonları (Ranking Functions): Bir partition içindeki her satıra bir sıralama numarası atar.
   • 
     
   • ROW_NUMBER(): Her satıra benzersiz, ardışık bir sıra numarası verir (1, 2, 3...).
   • 
     
   • RANK(): Aynı değerlere sahip satırlara aynı sırayı verir ve sonraki sırayı atlar (1, 2, 2, 4...).
   • 
     
   • DENSE_RANK(): Aynı değerlere sahip satırlara aynı sırayı verir ancak sonraki sırayı atlamaz (1, 2, 2, 3...).
   Örnek: Her kategorideki ürünleri fiyatına göre sıralama.
   
   Kod:Panoya kopyala

    SELECT UrunAd, Fiyat, Kategori, ROW_NUMBER() OVER (PARTITION BY Kategori ORDER BY Fiyat DESC) AS KategoriSırası, RANK() OVER (PARTITION BY Kategori ORDER BY Fiyat DESC) AS KategoriRank, DENSE_RANK() OVER (PARTITION BY Kategori ORDER BY Fiyat DESC) AS KategoriDenseRank FROM Urunler ORDER BY Kategori, Fiyat DESC;

3. 
   
4. Toplama (Aggregate) Fonksiyonları (Pencere Fonksiyonu Olarak): Daha önce gördüğünüz toplama fonksiyonlarını OVER yan tümcesi ile kullanarak, gruplama yapmadan her satır için bir toplama değeri elde edebilirsiniz.
   Örnek: Her ürünün yanında, kendi kategorisindeki en yüksek fiyatı gösterme.
   
   Kod:Panoya kopyala

    SELECT UrunAd, Fiyat, Kategori, MAX(Fiyat) OVER (PARTITION BY Kategori) AS KategoriMaksFiyat FROM Urunler;

   Örnek: Sipariş tarihine göre kümülatif (biriken) toplam sipariş miktarını hesaplama.
   
   Kod:Panoya kopyala

    SELECT SiparisID, SiparisTarihi, Miktar, SUM(Miktar) OVER (ORDER BY SiparisTarihi) AS KumulatifMiktar FROM SiparisDetaylari;

   (Burada PARTITION BY kullanılmadı, bu yüzden tüm sonuç kümesi tek bir pencere olarak kabul edildi ve sıralama ORDER BY ile yapıldı.)
5. 
   
6. Analitik Fonksiyonlar (Analytic Functions): Bir penceredeki ilgili satırlara erişim sağlar.
   • 
     
   • LEAD(sutun, offset, default):[/B] Mevcut satırdan sonraki offset sayısı kadar satırdaki sutun değerini getirir.
     [*]
     
     [*]LAG(sutun, offset, default):[/B] Mevcut satırdan önceki offset sayısı kadar satırdaki sutun değerini getirir.
     [*]
     
     [*]FIRST_VALUE(sutun):[/B] Penceredeki ilk satırın sutun değerini getirir.
     [*]
     
     [*]LAST_VALUE(sutun):[/B] Penceredeki son satırın sutun değerini getirir.
     [*]
     
   
   Örnek: Her siparişin yanında, sonraki siparişin tarihini gösterme.
   
   Kod:Panoya kopyala

    SELECT SiparisID, SiparisTarihi, LEAD(SiparisTarihi, 1, 'Yok') OVER (ORDER BY SiparisTarihi) AS SonrakiSiparisTarihi FROM Siparisler;

   Örnek: Her ürünün fiyatını, kendi kategorisindeki en ucuz ürünün fiyatıyla karşılaştırma.
   
   Kod:Panoya kopyala

    SELECT UrunAd, Fiyat, Kategori, FIRST_VALUE(Fiyat) OVER (PARTITION BY Kategori ORDER BY Fiyat ASC) AS KategoriEnUcuzFiyat FROM Urunler;

   [*]
   

Pencere fonksiyonları, özellikle finans, raporlama ve veri analizi gibi alanlarda karmaşık hesaplamaları çok daha basit ve anlaşılır hale getirir. GROUP BY ve JOIN'lerle zor veya imkansız olan birçok görevi kolayca yerine getirebilirsiniz.
Bu bölümde SQL Pencere Fonksiyonlarının ne olduğunu, nasıl kullanıldığını ve yaygın örneklerini gördük. Bu güçlü araçları öğrenmek, SQL sorgulama yeteneklerinizi büyük ölçüde artıracaktır.
SQL'de ustalaşma yolculuğumuz devam ediyor. Bir sonraki adımda, veritabanı tarafında iş mantığını depolamak ve tekrarlayan görevleri otomatikleştirmek için kullanılan Saklı Yordamlar (Stored Procedures) ve Fonksiyonlar (Functions) konusuna giriş yapabiliriz.

---

Bölüm 9: İleri SQL: Pencere Fonksiyonları (Window Functions)​

Geleneksel toplama fonksiyonları (COUNT(), SUM(), AVG() vb.) GROUP BY ile birlikte kullanıldığında sonuç kümesindeki satırları gruplandırır ve her grup için tek bir özet değer döndürür. Ancak bazen, her bir orijinal satır için bir toplama değeri hesaplamanız gerekebilir veya bir satırı, onunla ilgili diğer satırlarla karşılaştırmanız gerekebilir. İşte tam bu noktada Pencere Fonksiyonları devreye girer.

Pencere Fonksiyonları Nedir? Aggregates vs. Windows

Pencere fonksiyonları, tıpkı toplama fonksiyonları gibi bir dizi satır üzerinde işlem yapar. Ancak temel fark şudur: Pencere fonksiyonları sonuç kümesindeki satırları gruplandırmaz. Bunun yerine, her bir satır için tanımlanmış "pencere"deki (ilgili satırlar kümesi) hesaplamayı gerçekleştirir ve orijinal satırları döndürür.

• Toplama Fonksiyonları (GROUP BY ile): Satırları birleştirir ve grup başına tek satır döndürür.
  Kod:Panoya kopyala

  SELECT Kategori, AVG(Fiyat)FROM UrunlerGROUP BY Kategori;

  (Bu, her kategori için tek satır ve o kategorinin ortalama fiyatını verir.)
• Pencere Fonksiyonları: Her orijinal satır için bir değer döndürür, ancak bu değer ilgili satırlar (pencere) üzerinden hesaplanır.
  Kod:Panoya kopyala

  SELECT UrunAd, Fiyat, Kategori,AVG(Fiyat) OVER (PARTITION BY Kategori) AS KategoriOrtalamaFiyatFROM Urunler;

  (Bu, her ürün satırını döndürür ve her satırın yanında kendi kategorisinin ortalama fiyatını gösterir.)

Pencere Fonksiyonu Sözdizimi: OVER Yan Tümcesi

Pencere fonksiyonları, fonksiyon adından sonra gelen OVER yan tümcesi ile tanımlanır. OVER yan tümcesi, fonksiyonun üzerinde çalışacağı "pencereyi" (satır kümesini) tanımlar.

Kod:Panoya kopyala

Pencere_Fonksiyonu() OVER ([PARTITION BY sutun1, sutun2, ...] -- Satırları gruplara (partitionlara) ayırır[ORDER BY sutunA, sutunB, ...]     -- Her partition içindeki satırları sıralar[ROWS/RANGE BETWEEN ...]          -- Pencere içindeki satır aralığını tanımlar (daha ileri seviye))

• PARTITION BY: Sonuç kümesini bağımsız bölümlere (partition) ayırır. Pencere fonksiyonu her partition içinde ayrı ayrı uygulanır. GROUP BY gibi düşünebilirsiniz ama satırları birleştirmek yerine her satırı kendi partition'ı içindeki diğer satırlarla ilişkilendirir. Atlanırsa, tüm sonuç kümesi tek bir partition olarak kabul edilir.
• ORDER BY: Her partition içindeki satırları belirli bir sütun veya sütunlara göre sıralar. Bu, sıralamaya bağımlı pencere fonksiyonları (sıralama, lider/gecikme gibi) için kritiktir.

Yaygın Pencere Fonksiyonları

1. Sıralama Fonksiyonları (Ranking Functions): Bir partition içindeki her satıra bir sıralama numarası atar.
   
   • ROW_NUMBER(): Her satıra benzersiz, ardışık bir sıra numarası verir (1, 2, 3...).
   • RANK(): Aynı değerlere sahip satırlara aynı sırayı verir ve sonraki sırayı atlar (1, 2, 2, 4...).
   • DENSE_RANK(): Aynı değerlere sahip satırlara aynı sırayı verir ancak sonraki sırayı atlamaz (1, 2, 2, 3...).
   • Örnek: Her kategorideki ürünleri fiyatına göre sıralama.
     
   
   
   Kod:Panoya kopyala

   SELECT UrunAd, Fiyat, Kategori,ROW_NUMBER() OVER (PARTITION BY Kategori ORDER BY Fiyat DESC) AS KategoriSırası,RANK() OVER (PARTITION BY Kategori ORDER BY Fiyat DESC) AS KategoriRank,DENSE_RANK() OVER (PARTITION BY Kategori ORDER BY Fiyat DESC) AS KategoriDenseRankFROM UrunlerORDER BY Kategori, Fiyat DESC;

2. Toplama (Aggregate) Fonksiyonları (Pencere Fonksiyonu Olarak): Daha önce gördüğünüz toplama fonksiyonlarını OVER yan tümcesi ile kullanarak, gruplama yapmadan her satır için bir toplama değeri elde edebilirsiniz.
   
   Örnek: Her ürünün yanında, kendi kategorisindeki en yüksek fiyatı gösterme.
   
   
   Kod:Panoya kopyala

   SELECT UrunAd, Fiyat, Kategori,MAX(Fiyat) OVER (PARTITION BY Kategori) AS KategoriMaksFiyatFROM Urunler;

   
   Örnek: Sipariş tarihine göre kümülatif (biriken) toplam sipariş miktarını hesaplama.
   
   
   Kod:Panoya kopyala

   SELECT SiparisID, SiparisTarihi, Miktar,SUM(Miktar) OVER (ORDER BY SiparisTarihi) AS KumulatifMiktarFROM SiparisDetaylari;

   (Burada PARTITION BY kullanılmadı, bu yüzden tüm sonuç kümesi tek bir pencere olarak kabul edildi ve sıralama ORDER BY ile yapıldı.)
3. Analitik Fonksiyonlar (Analytic Functions): Bir penceredeki ilgili satırlara erişim sağlar.
   
   • LEAD(sutun, offset, default):[/B] Mevcut satırdan sonraki offset sayısı kadar satırdaki sutun değerini getirir.
     [*]LAG(sutun, offset, default):[/B] Mevcut satırdan önceki offset sayısı kadar satırdaki sutun değerini getirir.
     [*]FIRST_VALUE(sutun):[/B] Penceredeki ilk satırın sutun değerini getirir.
     [*]LAST_VALUE(sutun):[/B] Penceredeki son satırın sutun değerini getirir.
     [*]Örnek: Her siparişin yanında, sonraki siparişin tarihini gösterme.
     
     
   
   
   
   Kod:Panoya kopyala

   SELECT SiparisID, SiparisTarihi,LEAD(SiparisTarihi, 1, 'Yok') OVER (ORDER BY SiparisTarihi) AS SonrakiSiparisTarihiFROM Siparisler;

   
   Örnek: Her ürünün fiyatını, kendi kategorisindeki en ucuz ürünün fiyatıyla karşılaştırma.
   
   
   Kod:Panoya kopyala

   SELECT UrunAd, Fiyat, Kategori,FIRST_VALUE(Fiyat) OVER (PARTITION BY Kategori ORDER BY Fiyat ASC) AS KategoriEnUcuzFiyatFROM Urunler;

Pencere fonksiyonları, özellikle finans, raporlama ve veri analizi gibi alanlarda karmaşık hesaplamaları çok daha basit ve anlaşılır hale getirir. GROUP BY ve JOIN'lerle zor veya imkansız olan birçok görevi kolayca yerine getirebilirsiniz.

Bu bölümde SQL Pencere Fonksiyonlarının ne olduğunu, nasıl kullanıldığını ve yaygın örneklerini gördük. Bu güçlü araçları öğrenmek, SQL sorgulama yeteneklerinizi büyük ölçüde artıracaktır.

SQL'de ustalaşma yolculuğumuz devam ediyor. Bir sonraki adımda, veritabanı tarafında iş mantığını depolamak ve tekrarlayan görevleri otomatikleştirmek için kullanılan Saklı Yordamlar (Stored Procedures) ve Fonksiyonlar (Functions) konusuna giriş yapabiliriz.

---