PostgreSQL’de Window Functions ile Gelişmiş Sorgular

yazar

Veritabanı sorgularında bir noktaya gelirsiniz: GROUP BY ile aggregate fonksiyonlar artık yetmez, satırlar arasındaki ilişkileri görmek istersiniz ama satırları kaybetmek de istemezsiniz. İşte tam bu noktada PostgreSQL’in window functions özelliği devreye girer. Sıradan SQL yazarken “bunu nasıl çözeceğim” diye düşündüğünüz senaryoların büyük çoğunluğu, window functions ile hem daha temiz hem de çok daha verimli çözülür.

Window Functions Nedir, Neden Kullanmalıyız?

Window functions, bir satır kümesi üzerinde hesaplama yaparken o satırları gruplandırıp tek bir sonuca indirgemez. Yani GROUP BY gibi davranmaz. Her satır kendi kimliğini korur, ama aynı zamanda çevresindeki satırlarla ilgili hesaplamalar da yapılabilir.

Klasik aggregate fonksiyonlarla window functions arasındaki farkı bir örnekle gösterelim. Diyelim ki her çalışanın maaşı ve departman ortalaması aynı satırda olsun istiyorsunuz:

-- Klasik yöntem: Subquery veya JOIN gerekir
SELECT 
    ad,
    departman,
    maas,
    (SELECT AVG(maas) FROM calisanlar c2 WHERE c2.departman = c1.departman) AS dept_ortalama
FROM calisanlar c1;

-- Window function ile: Temiz, okunabilir, verimli
SELECT 
    ad,
    departman,
    maas,
    AVG(maas) OVER (PARTITION BY departman) AS dept_ortalama
FROM calisanlar;

İki sorgu aynı sonucu verir ama ikinci sorgu hem daha okunaklı hem de sorgu planlayıcısının üzerinde çok daha az baskı oluşturur.

Window function sözdiziminin temel yapısı şudur:

fonksiyon_adi(argüman) OVER (
    PARTITION BY bölümleme_kolonu
    ORDER BY sıralama_kolonu
    ROWS/RANGE çerçeve_tanımı
)
  • PARTITION BY: Hesaplamanın hangi grup içinde yapılacağını belirler. GROUP BY gibi düşünülebilir ama satırları kaybetmez.
  • ORDER BY: Pencere içindeki sıralamayı belirler. Bazı fonksiyonlar için zorunludur.
  • ROWS/RANGE: Pencere çerçevesini tanımlar. Varsayılan davranışı değiştirmek için kullanılır.

ROW_NUMBER, RANK ve DENSE_RANK ile Sıralama

Sysadmin hayatında sıkça karşılaştığım senaryo şu: Her departmandaki en yüksek maaşlı 3 kişiyi bulmak. Ya da her sunucudaki son 5 log girişini çekmek. Bu tür “her grup içindeki ilk N” sorguları window functions olmadan oldukça karmaşık hale gelir.

-- Her departmandaki maaş sıralaması
SELECT 
    ad,
    departman,
    maas,
    ROW_NUMBER() OVER (PARTITION BY departman ORDER BY maas DESC) AS sira,
    RANK() OVER (PARTITION BY departman ORDER BY maas DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY departman ORDER BY maas DESC) AS dense_rank
FROM calisanlar
ORDER BY departman, maas DESC;

Bu üç fonksiyon arasındaki fark şudur:

  • ROW_NUMBER(): Her satıra benzersiz bir numara verir. Eşit değerler olsa bile farklı numaralar alır.
  • RANK(): Eşit değerler aynı sırayı alır, ama sonrasında boşluk oluşur. (1, 1, 3, 4…)
  • DENSE_RANK(): Eşit değerler aynı sırayı alır, boşluk oluşmaz. (1, 1, 2, 3…)

Her gruptan ilk N kaydı çekmek için bu yapıyı bir CTE ile birleştirmek en temiz yoldur:

-- Her departmandan en yüksek maaşlı 3 kişi
WITH sirali_calisanlar AS (
    SELECT 
        ad,
        departman,
        maas,
        ROW_NUMBER() OVER (PARTITION BY departman ORDER BY maas DESC) AS sira
    FROM calisanlar
)
SELECT ad, departman, maas
FROM sirali_calisanlar
WHERE sira <= 3
ORDER BY departman, sira;

Bu yapı, monitoring sistemlerinde her sunucu için son N olayı çekmek, e-ticaret sistemlerinde her kategori için en çok satan ürünleri bulmak gibi sayısız gerçek dünya senaryosunda kullanılır.

LAG ve LEAD ile Satırlar Arası Karşılaştırma

Zaman serisi verilerinde bir önceki veya sonraki satıra bakma ihtiyacı çok sık çıkar. Sunucu metriklerini izliyorsanız, CPU kullanımının bir önceki ölçüme göre ne kadar değiştiğini hesaplamak istersiniz. Satış verilerinde bir önceki aya göre değişimi görmek istersiniz.

-- Sunucu CPU metriklerinde bir önceki ölçüme göre değişim
SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde,
    LAG(cpu_yuzde) OVER (PARTITION BY sunucu_adi ORDER BY olcum_zamani) AS onceki_olcum,
    cpu_yuzde - LAG(cpu_yuzde) OVER (PARTITION BY sunucu_adi ORDER BY olcum_zamani) AS degisim,
    LEAD(cpu_yuzde) OVER (PARTITION BY sunucu_adi ORDER BY olcum_zamani) AS sonraki_olcum
FROM sunucu_metrikleri
WHERE olcum_zamani >= NOW() - INTERVAL '24 hours'
ORDER BY sunucu_adi, olcum_zamani;

LAG ve LEAD fonksiyonlarının üç parametresi vardır:

  • Birinci parametre: Hangi kolona bakılacağı
  • İkinci parametre: Kaç satır ileri veya geri gidileceği (varsayılan: 1)
  • Üçüncü parametre: Değer yoksa (NULL olduğunda) kullanılacak varsayılan değer
-- 3 ölçüm öncesiyle karşılaştırma, NULL yerine 0 kullan
SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde,
    LAG(cpu_yuzde, 3, 0) OVER (PARTITION BY sunucu_adi ORDER BY olcum_zamani) AS uc_olcum_oncesi
FROM sunucu_metrikleri;

Pratikte bu yapıyı anormal değer tespiti için de kullanabilirsiniz. Bir önceki ölçüme göre CPU’nun yüzde 30’dan fazla artması durumunda uyarı tetiklemek gibi:

-- Anormal CPU artışı tespiti
WITH cpu_degisim AS (
    SELECT 
        sunucu_adi,
        olcum_zamani,
        cpu_yuzde,
        LAG(cpu_yuzde) OVER (PARTITION BY sunucu_adi ORDER BY olcum_zamani) AS onceki_cpu
    FROM sunucu_metrikleri
)
SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde,
    onceki_cpu,
    cpu_yuzde - onceki_cpu AS artis
FROM cpu_degisim
WHERE (cpu_yuzde - onceki_cpu) > 30
ORDER BY artis DESC;

SUM ve AVG ile Kümülatif Hesaplamalar

Window functions ile aggregate fonksiyonları birleştirdiğinizde çok güçlü bir araç elde edersiniz. Kümülatif toplam (running total), hareketli ortalama (moving average) ve bunlar gibi hesaplamalar tam bu kategoriye girer.

-- Aylık satış ve kümülatif toplam
SELECT 
    ay,
    aylik_satis,
    SUM(aylik_satis) OVER (ORDER BY ay) AS kumulatif_toplam,
    SUM(aylik_satis) OVER (ORDER BY ay ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS uc_aylik_ortalama_toplam,
    AVG(aylik_satis) OVER (ORDER BY ay ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS uc_aylik_hareketli_ort
FROM aylik_satislar
ORDER BY ay;

Burada ROWS BETWEEN ifadesi pencere çerçevesini tanımlar:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Baştan şimdiye kadar (kümülatif toplam için varsayılan)
  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: 2 satır öncesinden şimdiye
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 1 öncesi, şimdi ve 1 sonrası (3 satırlık pencere)
  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: Tüm pencere

Gerçek bir log analizi senaryosunu düşünelim. Web sunucusu loglarında her dakika kaç hata oluştuğunu ve son 5 dakikanın hareketli ortalamasını hesaplayalım:

-- Web sunucusu hata analizi: Hareketli ortalama
SELECT 
    dakika,
    hata_sayisi,
    AVG(hata_sayisi) OVER (
        ORDER BY dakika 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS bes_dakika_ort,
    MAX(hata_sayisi) OVER (
        ORDER BY dakika 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS bes_dakika_maks,
    SUM(hata_sayisi) OVER (ORDER BY dakika) AS toplam_hata
FROM dakiklik_hatalar
ORDER BY dakika;

NTILE ile Yüzdelik Dilimlere Ayırma

NTILE(n) fonksiyonu, satırları n eşit gruba böler. Performans analizi, müşteri segmentasyonu veya veri dağılımı analizi için çok kullanışlıdır.

-- Müşterileri harcama miktarına göre 4 gruba ayır (çeyrekler)
SELECT 
    musteri_id,
    ad_soyad,
    toplam_harcama,
    NTILE(4) OVER (ORDER BY toplam_harcama DESC) AS ceyrek,
    CASE NTILE(4) OVER (ORDER BY toplam_harcama DESC)
        WHEN 1 THEN 'Platin'
        WHEN 2 THEN 'Altin'
        WHEN 3 THEN 'Gumus'
        WHEN 4 THEN 'Bronz'
    END AS musteri_segmenti
FROM musteriler
ORDER BY toplam_harcama DESC;

Sunucu performans analizi için de kullanabilirsiniz. Yanıt sürelerini yüzdelik dilimlere göre sınıflandırmak:

-- API endpoint yanıt sürelerini yüzdelik dilimlere göre sınıflandır
SELECT 
    endpoint,
    yanit_suresi_ms,
    NTILE(100) OVER (PARTITION BY endpoint ORDER BY yanit_suresi_ms) AS percentile,
    NTILE(10) OVER (PARTITION BY endpoint ORDER BY yanit_suresi_ms) AS decile
FROM api_loglari
WHERE istek_zamani >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY endpoint, yanit_suresi_ms;

FIRST_VALUE, LAST_VALUE ve NTH_VALUE

Bu fonksiyonlar, pencere içindeki belirli sıradaki değere erişmek için kullanılır.

-- Her departmanda en yüksek ve en düşük maaş ile kendi maaşı arasındaki fark
SELECT 
    ad,
    departman,
    maas,
    FIRST_VALUE(maas) OVER (
        PARTITION BY departman 
        ORDER BY maas DESC
    ) AS en_yuksek_maas,
    LAST_VALUE(maas) OVER (
        PARTITION BY departman 
        ORDER BY maas DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS en_dusuk_maas,
    maas - LAST_VALUE(maas) OVER (
        PARTITION BY departman 
        ORDER BY maas DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS en_dusukten_fark
FROM calisanlar
ORDER BY departman, maas DESC;

LAST_VALUE kullanırken dikkat edilmesi gereken önemli bir nokta var: Varsayılan pencere çerçevesi ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW şeklindedir. Bu yüzden LAST_VALUE beklendiği gibi çalışmaz. Yukarıdaki örnekte olduğu gibi ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ile çerçeveyi açıkça tanımlamanız gerekir.

Pencere Tanımlarını Yeniden Kullanmak: WINDOW Clause

Aynı pencere tanımını birden fazla yerde kullandığınızda sorgu uzayıp karmaşık hale gelir. PostgreSQL’in WINDOW cümlesi bu problemi çözer:

-- WINDOW clause ile temiz ve tekrarsız sorgu
SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde,
    bellek_yuzde,
    disk_yuzde,
    AVG(cpu_yuzde) OVER w AS ort_cpu,
    MAX(cpu_yuzde) OVER w AS maks_cpu,
    MIN(cpu_yuzde) OVER w AS min_cpu,
    AVG(bellek_yuzde) OVER w AS ort_bellek,
    AVG(disk_yuzde) OVER w AS ort_disk,
    ROW_NUMBER() OVER (PARTITION BY sunucu_adi ORDER BY olcum_zamani) AS olcum_no
FROM sunucu_metrikleri
WINDOW w AS (PARTITION BY sunucu_adi ORDER BY olcum_zamani ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
ORDER BY sunucu_adi, olcum_zamani;

Bu yapı hem okunabilirliği artırır hem de sorgu bakımını kolaylaştırır.

Performans Konuları ve İndeks Stratejisi

Window functions güçlü ama yanlış kullanıldığında sorgu planlarını berbat edebilir. Birkaç kritik noktaya dikkat etmek gerekir.

İlk olarak, EXPLAIN ANALYZE ile sorgu planını inceleyin:

-- Sorgu planını incele
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde,
    AVG(cpu_yuzde) OVER (
        PARTITION BY sunucu_adi 
        ORDER BY olcum_zamani 
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS hareketli_ort
FROM sunucu_metrikleri
WHERE olcum_zamani >= NOW() - INTERVAL '7 days';

Window functions için kritik indeks noktaları:

  • PARTITION BY + ORDER BY kolonlarına bileşik indeks: Hem sıralama hem de bölümleme işlemini hızlandırır
  • WHERE filtresini öne al: CTE veya subquery ile önce filtreleme yapıp sonra window function uygula
  • Gereksiz pencereleri birleştir: Aynı PARTITION BY ve ORDER BY yapısını kullanan hesaplamaları tek bir geçişte yap
-- İndeks önerisi
CREATE INDEX idx_sunucu_metrikleri_partition ON sunucu_metrikleri 
    (sunucu_adi, olcum_zamani DESC);

-- Önce filtrele, sonra window function uygula
WITH son_veriler AS (
    SELECT sunucu_adi, olcum_zamani, cpu_yuzde
    FROM sunucu_metrikleri
    WHERE olcum_zamani >= NOW() - INTERVAL '7 days'
),
hesaplamalar AS (
    SELECT 
        sunucu_adi,
        olcum_zamani,
        cpu_yuzde,
        AVG(cpu_yuzde) OVER (
            PARTITION BY sunucu_adi 
            ORDER BY olcum_zamani 
            ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
        ) AS hareketli_ort,
        MAX(cpu_yuzde) OVER (
            PARTITION BY sunucu_adi 
            ORDER BY olcum_zamani 
            ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
        ) AS hareketli_maks
    FROM son_veriler
)
SELECT * FROM hesaplamalar
WHERE cpu_yuzde > hareketli_ort * 1.5;  -- Ortalamanın 1.5 katından yüksek olanlar

Gerçek Dünya Senaryosu: Kullanıcı Oturum Analizi

Bir web uygulamasının oturum verilerini analiz ettiğinizi düşünelim. Her kullanıcının oturumları, önceki oturumdan bu yana geçen süre ve oturum süresi gibi metrikleri hesaplayalım:

-- Kullanıcı oturum analizi
WITH oturum_analiz AS (
    SELECT 
        kullanici_id,
        oturum_baslangic,
        oturum_bitis,
        EXTRACT(EPOCH FROM (oturum_bitis - oturum_baslangic)) / 60 AS sure_dakika,
        LAG(oturum_bitis) OVER (
            PARTITION BY kullanici_id 
            ORDER BY oturum_baslangic
        ) AS onceki_oturum_bitis,
        ROW_NUMBER() OVER (
            PARTITION BY kullanici_id 
            ORDER BY oturum_baslangic
        ) AS oturum_no,
        COUNT(*) OVER (PARTITION BY kullanici_id) AS toplam_oturum
    FROM kullanici_oturumlari
),
bekleme_sureli AS (
    SELECT 
        *,
        EXTRACT(EPOCH FROM (oturum_baslangic - onceki_oturum_bitis)) / 3600 AS bekleme_saat,
        AVG(sure_dakika) OVER (
            PARTITION BY kullanici_id 
            ORDER BY oturum_baslangic
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS kumulatif_ort_sure
    FROM oturum_analiz
)
SELECT 
    kullanici_id,
    oturum_no,
    toplam_oturum,
    ROUND(sure_dakika::numeric, 2) AS sure_dakika,
    ROUND(bekleme_saat::numeric, 2) AS bekleme_saat,
    ROUND(kumulatif_ort_sure::numeric, 2) AS kumulatif_ort_sure
FROM bekleme_sureli
ORDER BY kullanici_id, oturum_no;

Bu sorgu, bir kullanıcının hangi oturumunun en uzun olduğunu, oturumlar arası ortalama bekleme süresini ve her oturumla birlikte değişen ortalama oturum süresini tek sorguda verir.

Sık Yapılan Hatalar

Window functions kullanırken bazı hatalar tekrarlanır:

  • WHERE içinde window function kullanmak: Window functions, WHERE cümlesinde kullanılamaz. Bunun yerine CTE veya subquery kullanılmalıdır.
  • LAST_VALUE için çerçeve tanımlamamak: Varsayılan çerçeve LAST_VALUE’yu bozar, her zaman açık tanımlayın.
  • Gereksiz tekrarlayan pencere tanımları: WINDOW clause kullanarak tekrardan kaçının.
  • Çok büyük veri setlerinde sıralama performansı: PARTITION BY kolonuna indeks eklemeden büyük tablolarda window function kullanmak sorguyu yavaşlatır.

Sonuç

PostgreSQL’in window functions özelliği, veri analizi ve raporlama sorgularında gerçek bir oyun değiştirici. Subquery karmaşasından kurtulursunuz, kümülatif hesaplamalar tek geçişte yapılır, satırlar arası karşılaştırmalar temiz bir sözdizimle ifade edilir.

Bir sysadmin veya veritabanı yöneticisi olarak bu fonksiyonları özellikle monitoring verilerini analiz ederken, log tabanlı raporlamalarda ve performans metriklerini yorumlarken sürekli kullanıyorum. ROW_NUMBER, LAG/LEAD ve kümülatif SUM/AVG ikilisi işlerin büyük çoğunluğunu halleder. Daha karmaşık senaryolar için NTILE, FIRST_VALUE ve LAST_VALUE devreye girer.

Başlangıç için şunu öneririm: Mevcut sorgularınızda correlated subquery veya self-join gördüğünüzde, o sorguyu window function ile yeniden yazmayı deneyin. Genellikle hem daha kısa hem daha hızlı bir çözüme ulaşırsınız. EXPLAIN ANALYZE ile her iki versiyonu karşılaştırın, farkı kendi gözlerinizle görün.

Yorum yapın