MariaDB ve MySQL’de LAG ve LEAD Fonksiyonları ile Önceki ve Sonraki Satır Verisi Alma

Tarafındankomut
0 Yorum

Veritabanlarında pencere fonksiyonları, SQL dünyasının en güçlü ama aynı zamanda en az kullanılan özelliklerinden biri. Özellikle LAG ve LEAD fonksiyonları, zaman serisi analizi, trend takibi ve ardışık satır karşılaştırmaları için inanılmaz derecede kullanışlı. Çoğu sysadmin ve veritabanı yöneticisi hâlâ bu işlemleri subquery veya self-join ile yapıyor, ama bu yaklaşım hem yavaş hem de karmaşık sorgular üretiyor. Bu yazıda MariaDB ve MySQL üzerinde LAG ve LEAD fonksiyonlarını gerçek dünya senaryolarıyla ele alacağız.

LAG ve LEAD Nedir?

LAG ve LEAD, SQL’de pencere fonksiyonları (window functions) kategorisine girer. Temel amacı, bir satırın önceki veya sonraki satırdaki değerine erişmek.

  • LAG: Mevcut satırdan önceki satırın değerini getirir
  • LEAD: Mevcut satırdan sonraki satırın değerini getirir

Bu iki fonksiyon MySQL 8.0 ve MariaDB 10.2 sürümleriyle birlikte geldi. Eğer daha eski sürüm kullanıyorsanız, önce sürümünüzü kontrol etmenizi öneririm.

-- Versiyon kontrolü
SELECT VERSION();

-- MariaDB için
SELECT @@version;

Syntax yapısı şu şekilde:

LAG(kolon, offset, varsayilan_deger) OVER (PARTITION BY gruplama ORDER BY siralama)
LEAD(kolon, offset, varsayilan_deger) OVER (PARTITION BY gruplama ORDER BY siralama)

Parametreleri açıklayalım:

  • kolon: Değerini almak istediğiniz sütun
  • offset: Kaç satır geri veya ileri gideceğiniz (varsayılan: 1)
  • varsayilan_deger: Önceki/sonraki satır yoksa döndürülecek değer (varsayılan: NULL)
  • PARTITION BY: Verileri gruplamak için kullanılır (opsiyonel)
  • ORDER BY: Satırların hangi sıraya göre değerlendirileceği (zorunlu)

Temel Kullanım: Basit Örneklerle Başlayalım

Önce basit bir tablo oluşturup temel kullanımı gösterelim. Diyelim ki bir e-ticaret sisteminin günlük satış verilerini tutuyoruz.

-- Test tablosu oluşturma
CREATE TABLE gunluk_satis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tarih DATE NOT NULL,
    urun_kategori VARCHAR(50),
    satis_miktari DECIMAL(10,2),
    siparis_adedi INT
);

-- Örnek veri ekleme
INSERT INTO gunluk_satis (tarih, urun_kategori, satis_miktari, siparis_adedi) VALUES
('2024-01-01', 'Elektronik', 15000.00, 45),
('2024-01-02', 'Elektronik', 18500.00, 52),
('2024-01-03', 'Elektronik', 12000.00, 38),
('2024-01-04', 'Elektronik', 22000.00, 67),
('2024-01-05', 'Elektronik', 19500.00, 58),
('2024-01-01', 'Giyim', 8000.00, 120),
('2024-01-02', 'Giyim', 9500.00, 145),
('2024-01-03', 'Giyim', 7200.00, 108),
('2024-01-04', 'Giyim', 11000.00, 165),
('2024-01-05', 'Giyim', 10500.00, 158);

Şimdi her günün bir önceki güne göre satışını karşılaştıralım:

SELECT 
    tarih,
    urun_kategori,
    satis_miktari,
    LAG(satis_miktari, 1, 0) OVER (
        PARTITION BY urun_kategori 
        ORDER BY tarih
    ) AS onceki_gun_satis,
    LEAD(satis_miktari, 1, 0) OVER (
        PARTITION BY urun_kategori 
        ORDER BY tarih
    ) AS sonraki_gun_satis,
    satis_miktari - LAG(satis_miktari, 1, 0) OVER (
        PARTITION BY urun_kategori 
        ORDER BY tarih
    ) AS gun_farki
FROM gunluk_satis
ORDER BY urun_kategori, tarih;

Bu sorgu size her kategoride günlük satış trendini verecek. PARTITION BY urun_kategori kullanarak Elektronik ve Giyim kategorilerinin kendi içinde sıralandığından emin oluyoruz. Bu olmadan, farklı kategorilerin satışları birbirine karışır.

Gerçek Dünya Senaryosu 1: Sunucu Kaynak Kullanımı Analizi

Sysadmin olarak en sık ihtiyaç duyduğunuz şeylerden biri sunucu metriklerinin trend analizi. Diyelim ki her 5 dakikada bir CPU ve RAM kullanımını kayıt altına alıyorsunuz ve ani artışları tespit etmek istiyorsunuz.

CREATE TABLE sunucu_metrik (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sunucu_adi VARCHAR(100),
    olcum_zamani DATETIME,
    cpu_yuzde DECIMAL(5,2),
    ram_yuzde DECIMAL(5,2),
    disk_io DECIMAL(10,2)
);

-- Ani CPU artışlarını tespit etme sorgusu
SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde AS mevcut_cpu,
    LAG(cpu_yuzde, 1) OVER (
        PARTITION BY sunucu_adi 
        ORDER BY olcum_zamani
    ) AS onceki_cpu,
    cpu_yuzde - LAG(cpu_yuzde, 1) OVER (
        PARTITION BY sunucu_adi 
        ORDER BY olcum_zamani
    ) AS cpu_artisi,
    LEAD(cpu_yuzde, 1) OVER (
        PARTITION BY sunucu_adi 
        ORDER BY olcum_zamani
    ) AS sonraki_cpu
FROM sunucu_metrik
WHERE olcum_zamani >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
HAVING cpu_artisi > 20
ORDER BY cpu_artisi DESC;

Bu sorgu size son 24 saat içinde CPU kullanımı 20 puanın üzerinde ani artış gösteren durumları getirecek. HAVING kullanarak hesaplanan sütuna göre filtreleme yapabiliyoruz. Böylece “önceki ölçümden bu yana 20% puan artış var mı?” sorusunu tek bir sorguda yanıtlıyoruz.

Önemli not: MariaDB’de pencere fonksiyonlarını WHERE ile doğrudan filtreleyemezsiniz, bu yüzden HAVING ya da alt sorgu kullanmanız gerekir.

Gerçek Dünya Senaryosu 2: Log Analizi ve Oturum Takibi

Web sunucusu veya uygulama loglarınızı veritabanında tutuyorsanız, kullanıcı oturum sürelerini hesaplamak için LAG ve LEAD son derece işe yarar.

CREATE TABLE kullanici_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    kullanici_id INT,
    islem_tipi ENUM('LOGIN', 'LOGOUT', 'ACTION'),
    islem_zamani DATETIME,
    ip_adresi VARCHAR(45),
    sayfa VARCHAR(255)
);

-- Kullanıcıların oturumları arasındaki süreyi hesapla
SELECT 
    kullanici_id,
    islem_zamani AS giris_zamani,
    LEAD(islem_zamani, 1) OVER (
        PARTITION BY kullanici_id 
        ORDER BY islem_zamani
    ) AS sonraki_islem,
    LEAD(islem_tipi, 1) OVER (
        PARTITION BY kullanici_id 
        ORDER BY islem_zamani
    ) AS sonraki_islem_tipi,
    TIMESTAMPDIFF(
        MINUTE,
        islem_zamani,
        LEAD(islem_zamani, 1) OVER (
            PARTITION BY kullanici_id 
            ORDER BY islem_zamani
        )
    ) AS dakika_farki
FROM kullanici_log
WHERE islem_tipi = 'LOGIN'
ORDER BY kullanici_id, islem_zamani;

Bu sorgu her kullanıcının giriş zamanları arasındaki farkı dakika cinsinden hesaplıyor. Bunu kullanarak “bir kullanıcı 30 dakika içinde kaç kez giriş yaptı?” veya “şüpheli hızlı tekrar girişler var mı?” gibi güvenlik sorularını yanıtlayabilirsiniz.

Offset Parametresi ile Birden Fazla Satır Geriye/İleriye Gitmek

LAG ve LEAD’in en güçlü özelliklerinden biri, sadece bir önceki değil, birkaç satır öncesinin veya sonrasının verisine erişebilmek. Haftanın aynı günü ile karşılaştırma yapmak için mükemmel.

SELECT 
    tarih,
    urun_kategori,
    satis_miktari,
    -- 1 gün önce
    LAG(satis_miktari, 1, 0) OVER (
        PARTITION BY urun_kategori ORDER BY tarih
    ) AS dn_satis,
    -- 7 gün önce (geçen hafta aynı gün)
    LAG(satis_miktari, 7, 0) OVER (
        PARTITION BY urun_kategori ORDER BY tarih
    ) AS gecen_hafta_satis,
    -- Büyüme oranı (geçen haftaya göre)
    ROUND(
        (satis_miktari - LAG(satis_miktari, 7, 0) OVER (
            PARTITION BY urun_kategori ORDER BY tarih
        )) / NULLIF(LAG(satis_miktari, 7, 0) OVER (
            PARTITION BY urun_kategori ORDER BY tarih
        ), 0) * 100, 
    2) AS haftalik_buyume_yuzde
FROM gunluk_satis
ORDER BY urun_kategori, tarih;

Burada NULLIF kullanımına dikkat edin. Sıfıra bölme hatasını önlemek için geçen hafta satışı 0 ise NULL döndürüyoruz.

Gerçek Dünya Senaryosu 3: Veritabanı Yedekleme Takip Sistemi

Birden fazla sunucunuzun yedek durumunu takip ediyorsanız ve yedekler arasındaki süreyi analiz etmek istiyorsanız, bu senaryo tam size göre.

CREATE TABLE yedekleme_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sunucu_adi VARCHAR(100),
    veritabani_adi VARCHAR(100),
    yedek_baslangic DATETIME,
    yedek_bitis DATETIME,
    yedek_boyutu_mb DECIMAL(12,2),
    durum ENUM('BASARILI', 'HATA', 'KISMI')
);

-- Her sunucu için yedekler arası süre ve boyut değişimini analiz et
SELECT 
    sunucu_adi,
    veritabani_adi,
    yedek_baslangic,
    durum,
    yedek_boyutu_mb,
    LAG(yedek_boyutu_mb, 1) OVER (
        PARTITION BY sunucu_adi, veritabani_adi 
        ORDER BY yedek_baslangic
    ) AS onceki_boyut_mb,
    ROUND(yedek_boyutu_mb - LAG(yedek_boyutu_mb, 1) OVER (
        PARTITION BY sunucu_adi, veritabani_adi 
        ORDER BY yedek_baslangic
    ), 2) AS boyut_farki_mb,
    LAG(durum, 1) OVER (
        PARTITION BY sunucu_adi, veritabani_adi 
        ORDER BY yedek_baslangic
    ) AS onceki_durum,
    TIMESTAMPDIFF(
        HOUR,
        LAG(yedek_baslangic, 1) OVER (
            PARTITION BY sunucu_adi, veritabani_adi 
            ORDER BY yedek_baslangic
        ),
        yedek_baslangic
    ) AS yedekler_arasi_saat,
    TIMESTAMPDIFF(MINUTE, yedek_baslangic, yedek_bitis) AS sure_dakika
FROM yedekleme_log
ORDER BY sunucu_adi, veritabani_adi, yedek_baslangic;

Bu sorgu ile:

  • Veritabanı boyutundaki ani artışları tespit edebilirsiniz
  • Yedekleme sürelerindeki beklenmedik uzamayı görebilirsiniz
  • Önceki yedekleme hata verdiyse ve şimdiki başarılıysa bunu işaretleyebilirsiniz
  • Yedekler arasındaki süre beklenenin üzerindeyse alarm üretebilirsiniz

Alt Sorgu ile Filtreleme

Daha önce bahsettiğim gibi, pencere fonksiyonlarını doğrudan WHERE ile filtreleyemezsiniz. Bunun için alt sorgu kullanmanız gerekiyor. İşte doğru yaklaşım:

-- Yanlış yaklaşım (hata verir)
-- SELECT ... FROM tablo WHERE LAG(kolon) OVER (...) > 100

-- Doğru yaklaşım: Alt sorgu kullanımı
SELECT *
FROM (
    SELECT 
        sunucu_adi,
        olcum_zamani,
        cpu_yuzde,
        ram_yuzde,
        LAG(cpu_yuzde, 1) OVER (
            PARTITION BY sunucu_adi 
            ORDER BY olcum_zamani
        ) AS onceki_cpu,
        LAG(ram_yuzde, 1) OVER (
            PARTITION BY sunucu_adi 
            ORDER BY olcum_zamani
        ) AS onceki_ram,
        cpu_yuzde - LAG(cpu_yuzde, 1) OVER (
            PARTITION BY sunucu_adi 
            ORDER BY olcum_zamani
        ) AS cpu_delta,
        ram_yuzde - LAG(ram_yuzde, 1) OVER (
            PARTITION BY sunucu_adi 
            ORDER BY olcum_zamani
        ) AS ram_delta
    FROM sunucu_metrik
    WHERE olcum_zamani >= DATE_SUB(NOW(), INTERVAL 6 HOUR)
) AS metrik_analiz
WHERE cpu_delta > 15 OR ram_delta > 10
ORDER BY olcum_zamani DESC;

Bu yaklaşım çok daha temiz ve performanslı. Dış sorgu içteki alt sorgunun ürettiği cpu_delta ve ram_delta değerlerine göre filtreleme yapıyor.

Performans İpuçları ve Dikkat Edilmesi Gerekenler

LAG ve LEAD kullanırken bazı performans konularına dikkat etmek gerekiyor.

INDEX kullanımı çok önemli. PARTITION BY ve ORDER BY’da kullandığınız sütunlar için uygun index olduğundan emin olun:

-- Sunucu metrikleri için bileşik index
CREATE INDEX idx_sunucu_zaman ON sunucu_metrik (sunucu_adi, olcum_zamani);

-- Yedekleme logu için bileşik index
CREATE INDEX idx_yedek_sunucu_db_zaman 
ON yedekleme_log (sunucu_adi, veritabani_adi, yedek_baslangic);

-- EXPLAIN ile sorgu planını kontrol et
EXPLAIN SELECT 
    sunucu_adi,
    olcum_zamani,
    cpu_yuzde,
    LAG(cpu_yuzde) OVER (
        PARTITION BY sunucu_adi 
        ORDER BY olcum_zamani
    ) AS onceki_cpu
FROM sunucu_metrik;

Performansla ilgili dikkat etmeniz gereken başlıca noktalar:

  • WHERE koşulunu alt sorgu içinde kullanın: Dıştaki WHERE pencere fonksiyonuna erişemez, dolayısıyla veriyi önce filtreleyin
  • PARTITION BY boyutunu küçük tutun: Çok fazla partition olması bellek kullanımını artırır
  • Büyük tablolarda tarih aralığı sınırlayın: Milyonlarca satır üzerinde çalışırken WHERE ile tarihi sınırlandırın
  • Gereksiz OVER() tekrarından kaçının: Aynı OVER() ifadesini birçok kez yazmak yerine CTE (WITH) kullanın

CTE ile Daha Temiz Sorgular Yazmak

Karmaşık analizlerde Common Table Expression (WITH) kullanmak hem okunabilirliği artırır hem de bakımı kolaylaştırır. MariaDB 10.2.1 ve MySQL 8.0 ile CTE desteği geldi.

-- CTE ile gelişmiş trend analizi
WITH satis_trend AS (
    SELECT 
        tarih,
        urun_kategori,
        satis_miktari,
        siparis_adedi,
        LAG(satis_miktari, 1, 0) OVER (
            PARTITION BY urun_kategori ORDER BY tarih
        ) AS dn_satis,
        LAG(satis_miktari, 7, 0) OVER (
            PARTITION BY urun_kategori ORDER BY tarih
        ) AS gecen_hafta_satis,
        LAG(siparis_adedi, 1, 0) OVER (
            PARTITION BY urun_kategori ORDER BY tarih
        ) AS dn_siparis
    FROM gunluk_satis
),
trend_hesap AS (
    SELECT 
        tarih,
        urun_kategori,
        satis_miktari,
        siparis_adedi,
        dn_satis,
        gecen_hafta_satis,
        CASE 
            WHEN dn_satis = 0 THEN NULL
            WHEN satis_miktari > dn_satis THEN 'YUKSELIS'
            WHEN satis_miktari < dn_satis THEN 'DUSUS'
            ELSE 'STABIL'
        END AS gunluk_trend,
        ROUND((satis_miktari - dn_satis) / NULLIF(dn_satis, 0) * 100, 2) AS gunluk_degisim_yuzde,
        ROUND((satis_miktari - gecen_hafta_satis) / NULLIF(gecen_hafta_satis, 0) * 100, 2) AS haftalik_degisim_yuzde
    FROM satis_trend
)
SELECT *
FROM trend_hesap
WHERE ABS(gunluk_degisim_yuzde) > 20
ORDER BY tarih DESC, urun_kategori;

Bu yapı çok daha okunaklı. İlk CTE ham verileri hesaplıyor, ikinci CTE bu verileri yorumluyor, dış sorgu ise sadece 20% üzeri değişimleri filtreliyor.

Varsayılan Değer Parametresinin Önemi

LAG ve LEAD fonksiyonunun üçüncü parametresi olan varsayılan değer, genellikle göz ardı edilir ama çok önemli. İlk satır için LAG, son satır için LEAD her zaman NULL döndürür ve bu hesaplamalarınızı bozabilir.

-- Varsayılan değer olmadan NULL sorunları
SELECT 
    tarih,
    satis_miktari,
    LAG(satis_miktari) OVER (ORDER BY tarih) AS onceki_satis,
    -- İlk satırda: NULL - NULL işlemi yine NULL döner
    satis_miktari - LAG(satis_miktari) OVER (ORDER BY tarih) AS fark_nullable,
    -- Varsayılan değer ile: İlk satırda 0 kullanılır
    satis_miktari - LAG(satis_miktari, 1, 0) OVER (ORDER BY tarih) AS fark_sifir,
    -- COALESCE ile de çözebilirsiniz
    satis_miktari - COALESCE(LAG(satis_miktari) OVER (ORDER BY tarih), satis_miktari) AS fark_coalesce
FROM gunluk_satis
WHERE urun_kategori = 'Elektronik'
ORDER BY tarih;

Hangi yaklaşımı seçeceğiniz iş kurallarına bağlı:

  • Sıfır varsayılan: “İlk günün değişimi tüm satış gibi görünsün” diyorsanız
  • COALESCE ile kendisi: “İlk günün değişimi sıfır görünsün” diyorsanız
  • NULL bırakma: “İlk gün için hesaplama yapma, raporda boş kalsın” diyorsanız

Sık Yapılan Hatalar ve Çözümleri

Yıllar içinde en çok karşılaştığım hatalar:

  • ORDER BY olmadan OVER(): ORDER BY kullanmadan LAG/LEAD çalışsa da sonuçlar tutarsız olur. Her zaman ORDER BY ekleyin
  • PARTITION BY olmadan farklı grupları karıştırmak: Birden fazla sunucu veya kategori varsa PARTITION BY şart
  • Pencere fonksiyonunu GROUP BY ile karıştırmak: Önce GROUP BY işlemi yapın, sonra pencere fonksiyonunu alt sorgu veya CTE içinde kullanın
  • Yanlış sıralama: LAG ve LEAD’in sonuçları tamamen ORDER BY’a bağlı. Yanlış sıralama yanlış analiz demek
  • Büyük offset değerleri: Çok eski versiyonlarda büyük offset değerleri performans sorununa yol açabilir

Sonuç

LAG ve LEAD fonksiyonları, özellikle zaman serisi verileri üzerinde çalışan sysadmin ve veritabanı yöneticileri için vazgeçilmez araçlar. Sunucu metrik analizi, log inceleme, yedekleme takibi ve satış trend raporlaması gibi onlarca gerçek dünya senaryosunda bu iki fonksiyon sayesinde daha önce karmaşık subquery veya uygulama katmanında yaptığınız işlemleri tek bir SQL sorgusuyla halledebiliyorsunuz.

Önemli olan noktaları özetlersek:

  • MySQL 8.0 ve MariaDB 10.2 veya üzeri sürüm şart
  • PARTITION BY ile gruplarınızı doğru tanımlayın
  • ORDER BY’ı asla atlamamayın
  • WHERE filtrelemesi için alt sorgu veya CTE kullanın
  • INDEX’lerinizi PARTITION BY ve ORDER BY sütunlarına göre oluşturun
  • Karmaşık sorgularda CTE tercih edin, okunabilirlik artar

Eğer hâlâ eski yöntemlerle self-join veya subquery yazıyorsanız, ilk fırsatta bu fonksiyonları denemenizi şiddetle tavsiye ederim. Sorgu süreleriniz dramatik biçimde düşecek, kod karmaşıklığınız azalacak ve analizleriniz çok daha güvenilir hale gelecek.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir