SQL’de Pencere Fonksiyonları: Veri Analizi İçin Güçlü Bir Araç

SQL’de Pencere Fonksiyonları: Veri Analizi İçin Güçlü Bir Araç

SQL’de karmaşık veri analizleri yaparken bazen standart toplama fonksiyonları yetersiz kalabilir. Peki, bir satırın diğer satırlarla ilişkisini anlayarak daha derinlemesine içgörüler elde etmek mümkün mü? İşte bu noktada SQL’in güçlü aracı olan pencere fonksiyonları devreye giriyor. Bu makalede, pencere fonksiyonlarının ne olduğunu, nasıl kullanıldığını ve gerçek dünya senaryolarında nasıl fark yarattığını adım adım inceleyeceğiz.

Pencere Fonksiyonları Nedir ve Neden Önemlidir?

Pencere fonksiyonları (window functions), SQL’de satır grupları üzerinde hesaplamalar yapmamızı sağlayan özel bir fonksiyondur. Standart toplama fonksiyonlarından (SUM, AVG, COUNT vb.) en büyük farkı, bu fonksiyonların bir satırın değerini, kendi grubundaki diğer satırların değerlerine göre hesaplamasıdır. Ancak bu hesaplama sırasında, pencere fonksiyonları orijinal satırları kümelemez veya tek bir özet satıra indirgemez. Yani, her satır kendi orijinal verisiyle birlikte, pencere fonksiyonunun hesapladığı yeni bir sütuna sahip olur. Bu, veriyi kaybetmeden ek bilgiler elde etmemizi sağlar ki bu da veri analizi için inanılmaz derecede değerlidir. Düşünün ki bir şirketin satış verilerini inceliyorsunuz. Sadece toplam satışları görmek yerine, her bir satışın, o aya ait toplam satış içindeki payını veya o yılın ortalama satışını görmek isteyebilirsiniz. İşte pencere fonksiyonları tam da bu tür senaryolar için tasarlanmıştır.

Geleneksel SQL sorgularında, bir satırın diğer satırlarla ilişkisini anlamak için genellikle self-join (kendini birleştirme) veya alt sorgular kullanılırdı. Bu yöntemler karmaşık sorgulara yol açabilir, performansı düşürebilir ve okunabilirliği azaltabilir. Pencere fonksiyonları ise bu karmaşıklığı ortadan kaldırarak, daha temiz, daha anlaşılır ve daha performanslı sorgular yazmamıza olanak tanır. Özellikle büyük veri kümeleriyle çalışırken, performans artışı göz ardı edilemez bir avantajdır. Bu fonksiyonlar, SQL’in sadece veri çekme ve temel işlemler yapma yeteneğinin ötesine geçerek, gelişmiş analitik yetenekler kazandırmasını sağlar. Veri bilimcileri, analistler ve veritabanı yöneticileri için pencere fonksiyonları, veri setlerinden anlamlı bilgiler çıkarmak adına vazgeçilmez bir araç haline gelmiştir. Bu fonksiyonlar, veriyi daha bağlamsal bir şekilde anlamamıza yardımcı olur ve bu da daha bilinçli iş kararları alınmasına zemin hazırlar. Kısacası, pencere fonksiyonları, SQL’i daha akıllı ve daha yetenekli bir analiz aracına dönüştürür.

Pencere Fonksiyonlarının Temel Bileşenleri: OVER, PARTITION BY, ORDER BY

Bir pencere fonksiyonunun temelini oluşturan üç anahtar kelime vardır: OVER, PARTITION BY ve ORDER BY. Bu üçlü, pencere fonksiyonunun hangi satırlar üzerinde çalışacağını ve bu satırların nasıl gruplandırılıp sıralanacağını belirler. OVER anahtar kelimesi, fonksiyonun bir pencere fonksiyonu olduğunu belirtir ve devamında gelen bölümleme (partitioning) ve sıralama (ordering) seçeneklerini tanımlar. PARTITION BY, veriyi mantıksal gruplara ayırmak için kullanılır. Örneğin, satış verilerinde her bir ürün kategorisi için ayrı ayrı hesaplamalar yapmak istediğimizde, PARTITION BY kategori_adi kullanabiliriz. Bu, her bir kategori için ayrı bir pencere oluşturulmasını sağlar. ORDER BY ise, her bir pencere içindeki satırların belirli bir sıraya göre dizilmesini sağlar. Bu sıralama, özellikle sıralı hesaplamalar gerektiren fonksiyonlar için kritiktir. Örneğin, bir ayın satışlarını, o aya ait önceki ayların satışlarına göre hesaplamak istediğimizde, ORDER BY ay_numarasi kullanırız. Bu sıralama, fonksiyonun hangi satırdan başlayıp hangi satıra kadar ilerleyeceğini belirler.

Bu üç bileşenin birlikte kullanımı, pencere fonksiyonlarına inanılmaz bir esneklik kazandırır. OVER () ifadesi, pencerenin tüm tabloyu kapsadığını belirtirken, OVER (PARTITION BY ...) belirli sütunlara göre gruplanmış pencereler oluşturur. OVER (ORDER BY ...) ise, her pencere içindeki satırları belirli bir düzene sokar. En güçlü kombinasyon ise OVER (PARTITION BY ... ORDER BY ...) şeklindedir. Bu, veriyi hem gruplara ayırır hem de her grup içindeki satırları sıralayarak, çok daha gelişmiş analizler yapmamızı sağlar. Örneğin, bir e-ticaret sitesinde her bir kullanıcının zaman içindeki harcama eğilimini analiz etmek istediğimizde, önce kullanıcıya göre bölümleme (PARTITION BY kullanici_id) yaparız, ardından her kullanıcının harcamalarını zamana göre sıralarız (ORDER BY siparis_tarihi). Bu sayede, her kullanıcının kendi harcama geçmişindeki konumunu anlayabiliriz. Bu bileşenlerin doğru kullanımı, SQL sorgularının gücünü kat kat artırır ve karmaşık veri problemlerine zarif çözümler sunar.

Yaygın Pencere Fonksiyonları ve Kullanım Alanları

SQL’de birçok farklı pencere fonksiyonu bulunmaktadır. Bunlardan en sık kullanılanları şunlardır:

• ROW_NUMBER(), RANK(), DENSE_RANK()

  Bu fonksiyonlar, bir pencere içindeki satırlara benzersiz bir sıra numarası atamak için kullanılır. ROW_NUMBER() her satıra benzersiz bir numara verir. RANK(), aynı değere sahip satırlara aynı sırayı verir ve sonraki sıralama numarasını atlar. DENSE_RANK() ise aynı değere sahip satırlara aynı sırayı verir ancak sonraki sıralama numarasını atlamaz. Bu fonksiyonlar, sıralamada belirli bir sıradaki veriyi çekmek, en yüksek veya en düşük N değeri alan kayıtları bulmak için kullanılır. Örneğin, her bir departmandaki en yüksek maaşlı 3 çalışanı listelemek için bu fonksiyonları kullanabiliriz.

  Bir örnek düşünelim: Bir öğrenci notları tablosunda, her öğrencinin her ders için aldığı notları sıralamak ve en yüksek notu alan ilk 3 öğrenciyi görmek isteyebiliriz. PARTITION BY ders_adi ORDER BY notu DESC ile her ders için notları büyükten küçüğe sıralarız ve ardından RANK() veya DENSE_RANK() kullanarak ilk 3’ü belirleyebiliriz. Bu, öğretmenlerin öğrencilerin başarı durumunu daha iyi analiz etmelerine yardımcı olur.

• LAG() ve LEAD()

  Bu fonksiyonlar, bir pencere içindeki mevcut satırdan önceki (LAG) veya sonraki (LEAD) satırın değerine erişmek için kullanılır. Bu, zaman serisi verilerinde değişimleri, büyüme oranlarını veya önceki/sonraki döneme ait değerleri karşılaştırmak için oldukça kullanışlıdır. Örneğin, aylık satış verilerinde, her ayın satışını bir önceki aya göre ne kadar arttığını veya azaldığını görmek için LAG(aylik_satis, 1, 0) OVER (ORDER BY ay) şeklinde bir ifade kullanabiliriz. Buradaki 1, bir önceki satıra bakılacağını, 0 ise eğer önceki satır yoksa (ilk satır) varsayılan değerin 0 olacağını belirtir.

  Bir başka senaryo: Bir web sitesinin günlük ziyaretçi sayılarını analiz ederken, bir gün önceki ziyaretçi sayısını veya bir sonraki günün ziyaretçi sayısını görmek isteyebiliriz. LAG(ziyaretci_sayisi, 1, 0) OVER (ORDER BY tarih) ile bir önceki günün ziyaretçi sayısını, LEAD(ziyaretci_sayisi, 1, 0) OVER (ORDER BY tarih) ile de bir sonraki günün ziyaretçi sayısını kolayca elde edebiliriz. Bu, ziyaretçi trafiğindeki ani değişimleri veya eğilimleri tespit etmek için önemlidir.

• SUM(), AVG(), COUNT() (Pencere Fonksiyonu Olarak)

  Bu standart toplama fonksiyonları, pencere fonksiyonları ile birlikte kullanıldığında çok daha güçlü hale gelir. Örneğin, SUM(satis_tutari) OVER (PARTITION BY urun_kategorisi), her bir ürün kategorisinin toplam satış tutarını, her ürün satırı için ayrı ayrı gösterir. Bu, her bir satışın kendi kategorisinin toplam satışına ne kadar katkıda bulunduğunu görmemizi sağlar. Aynı şekilde, AVG(fiyat) OVER (PARTITION BY magaza_adi), her bir mağazanın ortalama ürün fiyatını tüm ürün satırları için hesaplar.

  Bu fonksiyonların bir diğer önemli kullanımı da kümülatif toplamlar (cumulative sums) hesaplamaktır. Örneğin, bir projenin maliyetlerini zaman içinde takip ederken, her bir harcama kaleminin toplam proje maliyetine kümülatif etkisini görmek için SUM(harcama) OVER (ORDER BY harcama_tarihi) ifadesini kullanabiliriz. Bu, projenin bütçe takibini çok daha şeffaf hale getirir.

Gerçek Dünya Senaryoları ve Vaka Analizleri

Pencere fonksiyonlarının gücünü en iyi şekilde anlamak için gerçek dünya senaryolarına göz atalım.

Vaka Analizi 1: E-Ticaret Satış Analizi

Bir e-ticaret şirketi, satış verilerini analiz ederek en popüler ürünleri, en çok gelir getiren kategorileri ve her bir ürünün kendi kategorisindeki satış payını anlamak istiyor. Aşağıdaki gibi bir satislar tablosu olduğunu varsayalım:

Bu verilerle, her bir satışın kendi kategorisindeki toplam satış içindeki payını hesaplamak için aşağıdaki sorguyu kullanabiliriz:

  
SELECT
    siparis_id,
    urun_adi,
    kategori,
    satis_tutari,
    siparis_tarihi,
    SUM(satis_tutari) OVER (PARTITION BY kategori) AS kategori_toplam_satis,
    (satis_tutari * 100.0 / SUM(satis_tutari) OVER (PARTITION BY kategori)) AS kategori_satis_payi
FROM
    satislar;
  
  

Bu sorgu, her bir satış satırı için hem ait olduğu kategorinin toplam satışını (kategori_toplam_satis) hem de o satışın kategorinin toplam satışına oranını (kategori_satis_payi) hesaplayacaktır. Bu bilgi, pazarlama stratejilerini belirlemede, hangi kategorilere daha fazla odaklanılması gerektiğini anlamada ve ürün performansını değerlendirmede kritik rol oynar. Örneğin, Giyim kategorisinde Tişört Z’nin satışı 30 TL iken, Giyim kategorisinin toplam satışı 80 TL ise, Tişört Z’nin payı yaklaşık %37.5 olacaktır. Bu tür detaylı analizler, sadece toplam satış rakamlarına bakarak elde edilemez.

Vaka Analizi 2: Çalışan Performans Değerlendirmesi

Bir insan kaynakları departmanı, çalışanlarının maaşlarını ve performanslarını analiz ederek, departman ortalamasına göre maaş sıralaması yapmak ve her çalışanın kendi departmanındaki maaş sıralamasını görmek istiyor. Aşağıdaki gibi bir calisanlar tablosu olduğunu varsayalım:

Bu senaryoda, her çalışanın kendi departmanındaki maaş sıralamasını ve sıralamadaki yerini belirlemek için RANK() fonksiyonunu kullanabiliriz:

  
SELECT
    calisan_id,
    ad_soyad,
    departman,
    maas,
    RANK() OVER (PARTITION BY departman ORDER BY maas DESC) AS departman_maas_siralamasi
FROM
    calisanlar;
  
  

Bu sorgu, her çalışanın kendi departmanı içindeki maaş sıralamasını (en yüksek maaş 1. sırada olacak şekilde) gösterecektir. Örneğin, Satış departmanında Ali Yılmaz (5000 TL) 3. sırada, Ayşe Demir (5500 TL) 1. sırada, Fatma Çelik (5200 TL) ise 2. sırada yer alacaktır. Bu, yöneticilerin departmanlarındaki maaş dağılımını ve potansiyel eşitsizlikleri daha iyi anlamalarına yardımcı olur. Hatta, DENSE_RANK() kullanarak aynı maaşa sahip çalışanların sıralamasını nasıl etkilediğini de gözlemleyebiliriz. Bu tür analizler, adil ücretlendirme politikaları oluşturmada ve performans bazlı prim sistemlerini tasarlamada temel oluşturur.

İleri Düzey Teknikler ve Optimizasyon

Pencere fonksiyonları ile çalışırken performans ve esnekliği artırmak için bazı ileri düzey teknikler mevcuttur. Bunlardan biri, pencere çerçevelerini (window frames) daha hassas bir şekilde tanımlamaktır. Varsayılan olarak, bir pencere fonksiyonu, OVER (PARTITION BY ... ORDER BY ...)` ifadesinde belirtilen satırları kapsar. Ancak, ROWS BETWEEN ... AND ... veya RANGE BETWEEN ... AND ... gibi ifadelerle pencere çerçevesini daha daraltabiliriz. Örneğin, bir hareketli ortalama (moving average) hesaplarken, sadece son 3 satırı dikkate almak isteyebiliriz. Bu durumda, AVG(deger) OVER (ORDER BY zaman ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) gibi bir ifade kullanabiliriz. Bu, her satır için sadece kendisi ve ondan önceki iki satırın ortalamasını hesaplar.