SQL LAG ve LEAD Nasıl Kullanılır: Satırı Komşusuyla Karşılaştırmak

LAG ve LEAD ne işe yarar?

LAG, bir sütunun önceki satırdaki değerini; LEAD ise sonraki satırdaki değerini geçerli satırın yanına getiren pencere fonksiyonlarıdır. Normalde bir satır yalnızca kendi değerlerini görür. Önceki ay satışıyla karşılaştırma yapmak için tabloyu kendisiyle birleştirmek gerekirdi; LAG ve LEAD bu işi tek tablo geçişiyle ve çok daha okunaklı biçimde yapar.

Tipik kullanım alanları şunlardır:

• Dönemden döneme değişim: bu ayın satışı ile geçen ayın satışı arasındaki fark ve yüzde değişim
• Olaylar arasındaki süre: bir müşterinin ardışık siparişleri arasındaki gün farkı
• Durum değişimi tespiti: sensör, fiyat ya da statü değerinin bir önceki kayıttan farklı olduğu satırlar
• Boşluk ve kopukluk bulma: sıralı numaralarda ya da tarihlerde eksik halkalar
• Bir sonraki adım analizi: kullanıcının bir sonraki eylemi, bir sonraki sipariş tarihi

Bu fonksiyonların ortak mantığı, satırların bir sıraya dizilmesi ve her satırın komşusuna erişmesidir. Dolayısıyla sıralamanın doğru tanımlanması, sonucun doğruluğunu belirler.

Söz dizimi ve ilk örnek

Fonksiyonlar OVER cümlesiyle birlikte yazılır. Sıralama için ORDER BY zorunludur, çünkü "önceki" ya da "sonraki" ancak bir sıra tanımlandığında anlam kazanır:

SELECT ay, tutar, LAG(tutar) OVER (ORDER BY ay) AS onceki_ay_tutari, LEAD(tutar) OVER (ORDER BY ay) AS sonraki_ay_tutari FROM aylik_satis;

İlk satırın önceki satırı, son satırın sonraki satırı yoktur; bu durumda fonksiyon NULL döndürür. İstenirse boş yerine kullanılacak bir varsayılan değer verilebilir. Fonksiyonun ikinci argümanı kaç satır geriye ya da ileriye bakılacağını, üçüncü argümanı ise karşılık bulunmadığında döndürülecek değeri belirler:

LAG(tutar, n, varsayilan_deger) OVER (ORDER BY ay)

Burada n bir tam sayıdır; belirtilmezse fonksiyon hemen yakın satıra bakar. Daha eski bir döneme bakmak gibi durumlarda bu argüman değiştirilir. Bu söz diziminin ayrıntıları ve varsayılan davranış veritabanı ürününe bağlı olduğu için kullandığınız sistemin belgesinden kontrol edilmelidir.

Dönem farkını hesaplamak ise tek ifadedir:

tutar - LAG(tutar) OVER (ORDER BY ay) AS degisim

Gruplar içinde karşılaştırma: PARTITION BY

Gerçek tablolarda satırlar genellikle farklı varlıklara aittir: farklı müşteriler, ürünler, cihazlar. LAG her satırın bir önceki satırına bakar; PARTITION BY olmadan bir müşterinin ilk siparişi, başka bir müşterinin son siparişiyle karşılaştırılır. Bu sessiz bir hatadır, çünkü sorgu hata vermez ve anlamsız sonuç üretir.

Doğru kullanım, varlığa göre bölümlemektir:

SELECT customer_id, siparis_tarihi, siparis_tutari, LAG(siparis_tarihi) OVER ( PARTITION BY customer_id ORDER BY siparis_tarihi ) AS onceki_siparis_tarihi FROM siparisler;

Her müşterinin ilk siparişinde önceki tarih NULL olur. Bu NULL bilgilidir: "bu müşterinin ilk siparişi" anlamına gelir. Sonraki adımda ardışık siparişler arasındaki gün farkı hesaplanır. Gün farkı fonksiyonu veritabanına göre değişir: PostgreSQL'de tarihler çıkarılabilir, SQL Server'da DATEDIFF, MySQL'de DATEDIFF kullanılır; kendi sisteminizin tarih fonksiyonlarını kullanmalısınız.

Gün farkı sütunu üzerinde ortalama alınırsa müşterilerin ortalama sipariş sıklığı bulunur; eşik değerinden uzun aralıklar ise müşteri kaybı riskinin erken işaretidir. Bu tür sorgular için pencere fonksiyonları sonucu CTE'ye alınır ve dışarıdan filtrelenir.

Yüzde değişim ve büyüme hesabı

Dönemden döneme büyüme oranı, LAG'in en sık kullanıldığı hesaptır:

SELECT ay, tutar, (tutar - LAG(tutar) OVER (ORDER BY ay)) * 100.0 / NULLIF(LAG(tutar) OVER (ORDER BY ay), 0) AS degisim_yuzde FROM aylik_satis;

Bu sorguda birkaç dikkat noktası vardır. İlk dönemde önceki değer olmadığı için sonuç NULL olur; bu doğrudur, çünkü hesaplanacak bir değişim yoktur. Önceki dönem değeri sıfırsa NULLIF sıfıra bölme hatasını önler. Çarpanın ondalıklı yazılması tam sayı bölmesini engeller. Pay ve yüzde hesabının ayrıntıları için SQL ile yüzde ve oran hesaplama sayfası ilgilidir.

LAG ifadesi sorguda tekrar yazılır. Okunabilirlik için önce CTE ile önceki değeri bir sütun yapıp ardından hesabı dışarıda yapmak iyi bir alışkanlıktır:

WITH oncekili AS ( SELECT ay, tutar, LAG(tutar) OVER (ORDER BY ay) AS onceki_tutar FROM aylik_satis ) SELECT ay, tutar, (tutar - onceki_tutar) * 100.0 / NULLIF(onceki_tutar, 0) AS degisim_yuzde FROM oncekili;

Aynı kalıp yıldan yıla karşılaştırma için de kullanılır; bu durumda LAG ikinci argümanıyla bir yıl geriye gidilir, veri aylık ise geriye gidilecek satır sayısı bir yıldaki dönem sayısına eşit olur ve serinin eksiksiz olması gerekir.

LEAD ile bir sonraki olayı bulmak

LEAD aynı mantığın ileri yöndeki karşılığıdır. Bir kullanıcının eylem günlüğünde her eylemin bir sonrakini görmek, akış analizi için temel bir yapıdır:

SELECT kullanici_id, olay_zamani, olay_tipi, LEAD(olay_tipi) OVER ( PARTITION BY kullanici_id ORDER BY olay_zamani ) AS sonraki_olay FROM olay_gunlugu;

Bu sonuçla "sepete ekledikten sonra ne yapıldı?" ya da "ödeme sayfasından sonra hangi olay geldi?" soruları, gruplanarak sayılabilir. Dönüşüm hunisi analizi bu yapıya dayanır; ayrıntı için SQL ile dönüşüm hunisi sayfası tamamlayıcıdır.

LEAD aynı zamanda aralık hesabında da kullanılır. Geçerlilik tarihli kayıtlarda (bir fiyatın başlangıç tarihi vardır, bitişi yoktur) bitiş tarihi, bir sonraki kaydın başlangıcından türetilir: LEAD(baslangic) OVER (PARTITION BY product ORDER BY baslangic). Son kayıt için NULL gelen bitiş, "hâlâ geçerli" anlamına gelir ve gerekirse varsayılan uzak bir tarih ile doldurulur.

Ardışık olmayan satırlar ve boşluk tespiti

LAG satır sırasına bakar, takvime değil. Bir müşterinin ardışık siparişleri arasında uzun bir süre varsa, LAG bunları yine komşu sayar ve aradaki boş dönemleri hesaba katmaz. Bu davranış sıklıkla istenen şeydir (siparişler arasındaki süreyi ölçmek), ama aylık seride eksik ay varsa "bir önceki satır" ile "bir önceki ay" farklı şeylerdir.

Bu durumda şu yollar kullanılır: eksik dönemleri tamamlayan bir takvim tablosuyla sol birleştirme yapıp boş dönemleri sıfır ya da NULL ile doldurmak, ya da LAG'i yalnızca gerçek satırlar arasındaki farkı ölçmek için kullanıp aralığın büyüklüğünü ayrıca değerlendirmek.

Boşluk tespiti de LAG'in doğal bir kullanımıdır: sıralı numaralı faturalarda, her satırı önceki satırla karşılaştırıp fark bir birimden büyükse eksik numara vardır. Denetim ve veri kalitesi çalışmalarında bu sorgu sıklıkla kullanılır.

Dikkat edilecekler: sıralama sütununda eşit değerler varsa sıra belirsiz olur ve LAG her çalıştırmada farklı komşu bulabilir. ORDER BY içine benzersiz bir ikinci sütun ekleyin. Ayrıca pencere fonksiyonları WHERE içinde doğrudan kullanılamaz; "önceki değerden büyük olanlar" gibi filtreler için sorguyu CTE'ye sarın.

Sık yapılan hatalar ve kontrol listesi

LAG ve LEAD kullanırken tekrarlanan hatalar şunlardır:

• PARTITION BY unutulup farklı varlıkların satırlarının birbiriyle karşılaştırılması
• ORDER BY eksikliği ya da yanlış sıralama sütunu; tarih yerine metin sütununa göre sıralama gibi
• İlk ve son satırdaki NULL'ı hata sanmak ya da hesaplamada hesaba katmamak
• Tam sayı bölmesi ve sıfıra bölme hatasını önlememek
• Pencere fonksiyonunu WHERE içinde kullanmak
• Satır boşluğunu takvim boşluğu sanmak

Doğrulama alışkanlığı olarak, sonucun bazı gruplarını elle inceleyin: her grubun ilk satırında NULL olup olmadığına, tarihin artan sırada gidip gitmediğine bakın. Büyük tablolarda PARTITION BY ve ORDER BY sütunlarına uygun indeks yürütme süresini ciddi biçimde düşürür. Genel pencere çerçevesi için SQL pencere fonksiyonları sayfası, sıra numarası için ROW_NUMBER ve RANK sayfası ilgilidir.

Mindset programlarında bu beceri nerede çalışılır?

Önceki ya da sonraki satırla karşılaştırma; finans, satış ve ürün analitiğinde dönem değişimi, müşteri davranışı ve olay akışı çözümlemesi yapan analistler için bir temel tekniktir. Mindset'in SQL ile Veri Sorgulama (İleri) programında Pencere Fonksiyonları ile İleri Analiz ayrı bir başlıktır; SQL ile İleri Veri Dönüşümü ve Modelleme programının ilk modülü de Pencere Fonksiyonları'dır.

Bu programlar, dönem karşılaştırmalarını ve olay sıralarını elle çoğaltılan sorgularla çıkaran analist ve raporlama uzmanları için uygundur. Kurumsal ekiplerde dönemsel karşılaştırma raporlarını elle hazırlayan çalışanlar, kendi tablolarındaki sorular üzerinden çalışabilir. Kapsam ayrıntısı program sayfalarında yer alır.