PostgreSQL’de Gruplar İçinde LAG Fonksiyonunun Kullanımı

# PostgreSQL’de LAG Fonksiyonunu Gruplar İçinde Kullanmak

PostgreSQL veritabanınızda, bir grubun içindeki önceki satırın değerini almak istiyorsunuz. Örneğin, bir müşterinin önceki ayki satışlarını veya bir ürünün önceki haftanın fiyatını bulmak gibi. Bu durumda, LAG() fonksiyonunu gruplarla birlikte kullanmanız gerekiyor. Bu makalede, PostgreSQL’de LAG() fonksiyonunu gruplar halinde nasıl kullanacağınızı, adım adım, gerçek dünya örnekleriyle ve performans optimizasyon teknikleriyle öğreneceksiniz. Fatih Soysal’ın yazdığı bu rehber, yeni başlayanlardan ileri seviye kullanıcılara kadar herkese hitap ediyor.

PostgreSQL’de LAG Fonksiyonu Nedir?

LAG() fonksiyonu, pencere fonksiyonlarının bir parçasıdır ve bir satırın belirli bir sayıdaki önceki satırın değerini döndürür. Temel olarak, “şu anki satırdan *n* satır önceki satırdaki kolonun değerini getir” der. n değeri varsayılan olarak 1’dir. Ancak LAG() fonksiyonu, tek başına kullanıldığında tüm tablo üzerinde çalışır. Gruplar içindeki önceki satırı bulmak için ise PARTITION BY ve ORDER BY kısımlarını kullanmamız gerekmektedir. Bu, her bir grup içinde bağımsız olarak önceki satırı bulmamızı sağlar.

LAG Fonksiyonunu Gruplar İçinde Nasıl Kullanırım? Temel Kavramlar

LAG() fonksiyonunun temel yapısı şöyledir:

LAG(column_name, offset, default_value) OVER (PARTITION BY group_column ORDER BY order_column)

* column_name: Önceki satırın değerini almak istediğiniz sütunun adı.
* offset: Şu anki satırdan kaç satır önceki satırı almak istediğinizi belirtir (varsayılan değer 1).
* default_value: Eğer offset değeri, mevcut satırdan önceki satır sayısını aşarsa (örneğin ilk satırda), bu değer döndürülür. Varsayılan olarak NULL döndürür.
* PARTITION BY group_column: Verileri gruplara ayırmak için kullanılan sütun. Her grup için LAG() fonksiyonu bağımsız olarak çalışır.
* ORDER BY order_column: Her grup içindeki satırların sıralamasını belirtir. LAG() fonksiyonunun doğru çalışması için mutlaka bir ORDER BY klauzu kullanmalısınız.

Öğrenme Yol Haritası

# Yeni Başlayan: Adım Adım Açıklama ve Basit Kod Örneği

Diyelim ki, aşağıdaki gibi bir satislar tablomuz var:

| tarih | urun_id | miktar |
|————-|———|——–|
| 2024-01-15 | 1 | 10 |
| 2024-01-15 | 2 | 5 |
| 2024-01-22 | 1 | 15 |
| 2024-01-22 | 2 | 8 |
| 2024-01-29 | 1 | 20 |
| 2024-01-29 | 2 | 12 |

Her ürün için önceki haftanın satış miktarını görmek istiyoruz. Aşağıdaki sorguyu kullanabiliriz:

SELECT
    tarih,
    urun_id,
    miktar,
    LAG(miktar, 1, 0) OVER (PARTITION BY urun_id ORDER BY tarih) as onceki_hafta_satis
FROM
    satislar;

Bu sorgu, her ürün için (PARTITION BY urun_id), tarihe göre sıralanmış (ORDER BY tarih) verilerde, önceki haftanın satış miktarını (LAG(miktar, 1, 0)) gösterir. 0 değeri, ilk haftanın önceki haftası için varsayılan değer olarak kullanılır.

# Orta Seviye: Gerçek Hayat Örneği ve Optimizasyon İpuçları

Bir e-ticaret şirketinde çalıştığınızı ve her müşterinin son üç siparişinin tarihini ve toplamını görmek istediğinizi varsayalım. siparisler tablomuz aşağıdaki gibi olsun:

| siparis_id | musteri_id | siparis_tarihi | toplam_tutar |
|————|————-|—————–|—————|
| 1 | 1 | 2024-02-10 | 100 |
| 2 | 1 | 2024-02-15 | 150 |
| 3 | 1 | 2024-02-20 | 200 |
| 4 | 2 | 2024-02-12 | 80 |
| 5 | 2 | 2024-02-25 | 120 |

Aşağıdaki sorgu, her müşteri için son üç siparişin tarihini ve toplamını gösterir:

SELECT
    siparis_id,
    musteri_id,
    siparis_tarihi,
    toplam_tutar,
    LAG(siparis_tarihi, 1) OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi) as onceki_siparis_tarihi,
    LAG(toplam_tutar, 1) OVER (PARTITION BY musteri_id ORDER BY siparis_tarihi) as onceki_siparis_tutari
FROM
    siparisler
ORDER BY musteri_id, siparis_tarihi;

Optimizasyon İpuçları: Büyük tablolar için performansı artırmak için indeksler kullanın. siparis_tarihi ve musteri_id sütunlarına indeks eklemek sorgu hızını önemli ölçüde iyileştirebilir.

# İleri Düzey: Performans Analizi ve Edge Case’ler

Çok büyük tablolarla çalışırken, LAG() fonksiyonunun performansını analiz etmek önemlidir. EXPLAIN ANALYZE komutunu kullanarak sorgu planını inceleyebilir ve performans darboğazlarını tespit edebilirsiniz. Gerekirse, indeksleme stratejilerinizi optimize edebilir veya farklı sorgu yaklaşımları deneyebilirsiniz.

Edge Case’ler: LAG() fonksiyonu, ORDER BY klauzu olmadan çalışmaz. ORDER BY klauzu olmadan kullanmaya çalışmak bir hata verecektir. Ayrıca, offset değeri çok büyükse, performans düşebilir veya beklenmedik sonuçlar elde edilebilir. Bu gibi durumlarda, daha verimli alternatifler araştırmalısınız. Örneğin, bir alt sorgu kullanarak önceki satırları filtrelemek daha performanslı olabilir.

PostgreSQL’de LAG ile Zaman Serisi Analizi: Gerçek Dünya Senaryosu

Bir hisse senedinin günlük kapanış fiyatlarını içeren bir tablo düşünün. Her günün kapanış fiyatıyla önceki günün kapanış fiyatı arasındaki farkı hesaplamak istiyoruz. Bu, hisse senedinin günlük değişimini anlamamıza yardımcı olur.

CREATE TABLE hisse_fiyatlari (
    tarih DATE,
    hisse_adi VARCHAR(50),
    kapanis_fiyati NUMERIC
);

INSERT INTO hisse_fiyatlari (tarih, hisse_adi, kapanis_fiyati) VALUES
('2024-03-01', 'AAPL', 150.00),
('2024-03-04', 'AAPL', 152.50),
('2024-03-05', 'AAPL', 155.00),
('2024-03-06', 'AAPL', 153.75),
('2024-03-01', 'MSFT', 250.00),
('2024-03-04', 'MSFT', 255.00),
('2024-03-05', 'MSFT', 252.00),
('2024-03-06', 'MSFT', 260.00);


SELECT
    tarih,
    hisse_adi,
    kapanis_fiyati,
    LAG(kapanis_fiyati, 1, kapanis_fiyati) OVER (PARTITION BY hisse_adi ORDER BY tarih) as onceki_gun_kapanis,
    kapanis_fiyati - LAG(kapanis_fiyati, 1, kapanis_fiyati) OVER (PARTITION BY hisse_adi ORDER BY tarih) as gunluk_degisim
FROM
    hisse_fiyatlari
ORDER BY hisse_adi, tarih;

Bu sorgu, her hisse senedi için günlük kapanış fiyatını, önceki günün kapanış fiyatını ve aralarındaki farkı hesaplar. LAG() fonksiyonunun default_value parametresi, ilk gün için önceki günün kapanış fiyatını kendisiyle karşılaştırarak 0 günlük değişim göstermesini sağlar.

Performans Optimizasyonu için İpuçları

* İndeksler: PARTITION BY ve ORDER BY kısımlarında kullanılan sütunlara indeks eklemek sorgu performansını önemli ölçüde artırabilir.
* WHERE klauzu: Gerekli verileri filtrelemek için WHERE klauzu kullanın. Bu, LAG() fonksiyonunun daha az veri üzerinde çalışmasını sağlayarak performansı iyileştirir.
* Alt sorgular: Karmaşık sorgularda, alt sorgular kullanarak LAG() fonksiyonunu daha küçük veri kümeleri üzerinde çalıştırmayı düşünebilirsiniz.
* Materyalize Görünümler: Sık kullanılan sorgular için materyalize görünümler oluşturmak, performansı iyileştirebilir.

Sonuç

PostgreSQL’de LAG() fonksiyonunu gruplar içinde kullanmak, zaman serileri analizi ve diğer birçok veri analizi görevini kolaylaştırır. Bu makalede, LAG() fonksiyonunun temel kullanımını, gerçek dünya örneklerini ve performans optimizasyon tekniklerini ele aldık. Unutmayın ki, büyük verilerle çalışırken performans optimizasyonu çok önemlidir. Doğru indeksleme stratejileri ve sorgu optimizasyonu, performansı önemli ölçüde iyileştirebilir. Daha fazla ileri seviye PostgreSQL konusu öğrenmek için [Fatih Soysal’ın bloguna](https://fatihsoysal.com) göz atabilirsiniz.

Sıkça Sorulan Sorular:

1. LAG() fonksiyonu LEAD() fonksiyonundan nasıl farklıdır? LAG(), önceki satırın değerini döndürürken, LEAD() sonraki satırın değerini döndürür.

2. offset parametresi negatif olabilir mi? Hayır, offset parametresi negatif olamaz.

3. default_value parametresi neden önemlidir? İlk satırda veya offset değeri geçersiz olduğunda NULL yerine belirli bir değer döndürmek için kullanılır.

4. PARTITION BY klauzu olmadan LAG() fonksiyonu nasıl çalışır? PARTITION BY klauzu olmadan tüm tablo tek bir grup olarak işlem görür.

5. Büyük tablolar için performansı nasıl iyileştirebilirim? İndeksleme, WHERE klauzu kullanımı, alt sorgular ve materyalize görünümler performansı artırabilir.

Yazar: Fatih Soysal