← Kursa Dön
📄 Text · 30 min

LAG, LEAD, FIRST_VALUE, LAST_VALUE

Giriş — "Bir Önceki Aya Göre Ne Kadar Arttı?"

Raporlama dünyasında en sık sorulan sorulardan biri budur: "Bu ayın satışları geçen aya göre nasıl?" Ya da: "Bu siparişin tutarı müşterinin bir önceki siparişinden fazla mı?" Bu tür sorular, bir satırın değerini başka bir satırla karşılaştırmayı gerektirir.

Normal SQL'de bu oldukça zor: self-join yaparsın, correlated subquery yazarsın, kodun bir spagettiye döner. Ama window function'ların değer fonksiyonları (value functions) bunu tek satırda çözer.

🎯 Analoji: Bir kitabı okurken parmağını sayfanın arasında tutarsın — "az önce ne demişti?" diye geri bakarsın veya "bir sonraki bölümde ne olacak?" diye ileriye göz atarsın. LAG geriye bakmak, LEAD ileriye bakmaktır. Parmağını kaldırmadan, sayfaları karıştırmadan, bulunduğun satırdan başka satırlara erişirsin.

LAG() — Önceki Satıra Bakmak

LAG() fonksiyonu, belirtilen sayıda önceki satırın değerini döndürür. En yaygın kullanımı: zaman serileri karşılaştırması.

Syntax

LAG(sütun, offset, varsayılan_değer) OVER (
    [PARTITION BY bölüm_sütunu]
    ORDER BY sıralama_sütunu
)

  • sütun: Hangi sütunun önceki değeri?

  • offset: Kaç satır geriye bak? (varsayılan: 1)

  • varsayılan_değer: Önceki satır yoksa ne dönsün? (varsayılan: NULL)

Temel Kullanım

-- Her çalışanın maaşını, bir önceki çalışanın maaşıyla karşılaştır
SELECT 
    first_name,
    salary,
    LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary,
    salary - LAG(salary) OVER (ORDER BY salary DESC) AS diff
FROM employees;
+------------+--------+-------------+-------+
| first_name | salary | prev_salary | diff  |
+------------+--------+-------------+-------+
| Can        |  15000 |        NULL |  NULL |  ← İlk satır, önceki yok
| Ece        |  14000 |       15000 | -1000 |
| Deniz      |  13000 |       14000 | -1000 |
| Mehmet     |  12000 |       13000 | -1000 |
| Ayşe       |  10000 |       12000 | -2000 |
| Ali        |   8000 |       10000 | -2000 |
+------------+--------+-------------+-------+

İlk satırda prev_salary NULL çünkü Can'dan önce kimse yok. Varsayılan değer belirtebilirsin:

-- İlk satır için 0 döndür
LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS prev_salary
-- Can'ın prev_salary'si 0 olur, diff = 15000 olur

Aylık Satış Karşılaştırması — Klasik Kullanım

-- Aylık gelir ve bir önceki aya göre değişim
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(total_amount) AS monthly_revenue,
    LAG(SUM(total_amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS prev_month,
    ROUND(
        (SUM(total_amount) - LAG(SUM(total_amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')))
        * 100.0 / LAG(SUM(total_amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')),
        1
    ) AS growth_pct
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;
+---------+-----------------+------------+------------+
| month   | monthly_revenue | prev_month | growth_pct |
+---------+-----------------+------------+------------+
| 2024-01 |          245000 |       NULL |       NULL |
| 2024-02 |          268000 |     245000 |        9.4 |
| 2024-03 |          312000 |     268000 |       16.4 |
| 2024-04 |          287000 |     312000 |       -8.0 |
| 2024-05 |          334000 |     287000 |       16.4 |
+---------+-----------------+------------+------------+

Bu tablo, her ayın bir önceki aya göre yüzde kaç büyüdüğünü veya küçüldüğünü gösteriyor. Dashboard'larda çok kullanılan bir rapor türü.

Offset ile 2 Satır Geriye Bakma

-- 2 ay öncesiyle karşılaştır
LAG(SUM(total_amount), 2) OVER (ORDER BY month) AS two_months_ago

-- 12 ay öncesiyle karşılaştır (yıllık karşılaştırma)
LAG(SUM(total_amount), 12) OVER (ORDER BY month) AS same_month_last_year

LEAD() — Sonraki Satıra Bakmak

LEAD() fonksiyonu LAG'ın tam tersidir — sonraki satırın değerini döndürür.

Syntax

LEAD(sütun, offset, varsayılan_değer) OVER (
    [PARTITION BY bölüm_sütunu]
    ORDER BY sıralama_sütunu
)

Temel Kullanım

-- Her siparişin yanında, müşterinin sonraki sipariş tarihini göster
SELECT 
    c.first_name,
    o.order_id,
    o.order_date,
    LEAD(o.order_date) OVER (
        PARTITION BY o.customer_id 
        ORDER BY o.order_date
    ) AS next_order_date,
    DATEDIFF(
        LEAD(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date),
        o.order_date
    ) AS days_until_next
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY c.first_name, o.order_date;
+------------+----------+------------+-----------------+-----------------+
| first_name | order_id | order_date | next_order_date | days_until_next |
+------------+----------+------------+-----------------+-----------------+
| Ali        |     1001 | 2024-01-15 | 2024-03-22      |              67 |
| Ali        |     1045 | 2024-03-22 | 2024-07-10      |             110 |
| Ali        |     1089 | 2024-07-10 | NULL            |            NULL |  ← Son sipariş
| Zeynep     |     1002 | 2024-02-01 | 2024-05-18      |             107 |
| Zeynep     |     1056 | 2024-05-18 | NULL            |            NULL |
+------------+----------+------------+-----------------+-----------------+

Bu sorgu, müşteri sadakatini analiz etmek için harika: iki sipariş arasındaki gün sayısı, müşterinin alışveriş sıklığını gösteriyor.

Churn Analizi — Müşteri Kaybetme Riski

-- 90 günden fazla sipariş vermeyen müşteriler (churn riski)
SELECT * FROM (
    SELECT 
        c.first_name,
        c.last_name,
        o.order_date AS last_order,
        LEAD(o.order_date) OVER (
            PARTITION BY o.customer_id 
            ORDER BY o.order_date
        ) AS next_order,
        DATEDIFF(
            COALESCE(
                LEAD(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date),
                CURDATE()
            ),
            o.order_date
        ) AS gap_days
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
) analysis
WHERE next_order IS NULL AND gap_days > 90;
-- Son siparişinden bu yana 90+ gün geçen müşteriler

LAG ve LEAD'in PARTITION BY ile Kullanımı

PARTITION BY eklendiğinde, LAG/LEAD her partition (grup) içinde çalışır:

-- Her departmanda, çalışanları maaşa göre sırala 
-- ve bir önceki/sonraki çalışanın maaşını göster
SELECT 
    first_name,
    department_id,
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS lower_salary,
    LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary) AS higher_salary
FROM employees;
+------------+---------------+--------+--------------+---------------+
| first_name | department_id | salary | lower_salary | higher_salary |
+------------+---------------+--------+--------------+---------------+
| Ali        |             1 |   8000 |         NULL |          9000 |
| Zeynep     |             1 |   9000 |         8000 |          NULL |  ← Dept 1 bitti
| Ayşe       |             2 |  10000 |         NULL |         12000 |  ← Dept 2 başladı
| Mehmet     |             2 |  12000 |        10000 |          NULL |
+------------+---------------+--------+--------------+---------------+

Her partition'ın başında LAG NULL, sonunda LEAD NULL döner. Partition sınırlarını aşmaz.

FIRST_VALUE() — Penceredeki İlk Değer

FIRST_VALUE() pencere (veya partition) içindeki ilk satırın değerini döndürür.

-- Her çalışanın maaşını departmanın en düşük maaşıyla karşılaştır
SELECT 
    first_name,
    department_id,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary ASC
    ) AS min_salary_in_dept,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary ASC
    ) AS diff_from_min
FROM employees;
+------------+---------------+--------+--------------------+---------------+
| first_name | department_id | salary | min_salary_in_dept | diff_from_min |
+------------+---------------+--------+--------------------+---------------+
| Ali        |             1 |   8000 |               8000 |             0 |
| Zeynep     |             1 |   9000 |               8000 |          1000 |
| Fatma      |             1 |  12000 |               8000 |          4000 |
| Ayşe       |             2 |  10000 |              10000 |             0 |
| Mehmet     |             2 |  12000 |              10000 |          2000 |
+------------+---------------+--------+--------------------+---------------+

Pratik Kullanım: En Yüksek Maaşlı Çalışanın Adı

-- Her departmanda en yüksek maaşlı çalışanın adını göster
SELECT 
    first_name,
    department_id,
    salary,
    FIRST_VALUE(first_name) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS top_earner
FROM employees;
+------------+---------------+--------+------------+
| first_name | department_id | salary | top_earner |
+------------+---------------+--------+------------+
| Fatma      |             1 |  12000 | Fatma      |
| Zeynep     |             1 |   9000 | Fatma      |
| Ali        |             1 |   8000 | Fatma      |
| Mehmet     |             2 |  12000 | Mehmet     |
| Ayşe       |             2 |  10000 | Mehmet     |
+------------+---------------+--------+------------+

Her satırda, o departmanın en yüksek maaşlı çalışanının adını görüyorsun. Bu, GROUP BY ile yapılması çok zor olan bir işlem.

LAST_VALUE() — Penceredeki Son Değer

LAST_VALUE() pencere içindeki son satırın değerini döndürür. Ama burada önemli bir tuzak var!

LAST_VALUE Tuzağı

-- ❌ BEKLENMEDİK SONUÇ!
SELECT 
    first_name,
    department_id,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary ASC
    ) AS max_salary_dept
FROM employees;
+------------+---------------+--------+-----------------+
| first_name | department_id | salary | max_salary_dept |
+------------+---------------+--------+-----------------+
| Ali        |             1 |   8000 |            8000 |  ← 8000?? 12000 olmalıydı!
| Zeynep     |             1 |   9000 |            9000 |  ← 9000?? Hâlâ yanlış!
| Fatma      |             1 |  12000 |           12000 |  ← Sadece son satır doğru
+------------+---------------+--------+-----------------+

Ne oldu? LAST_VALUE, ORDER BY kullanıldığında varsayılan pencere çerçevesi (frame) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW olduğu için, "son değer" = "şu anki satır" oluyor!

-- ✅ DOĞRU: Frame'i açıkça belirt
SELECT 
    first_name,
    department_id,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_salary_dept
FROM employees;
+------------+---------------+--------+-----------------+
| first_name | department_id | salary | max_salary_dept |
+------------+---------------+--------+-----------------+
| Ali        |             1 |   8000 |           12000 |  ← Doğru!
| Zeynep     |             1 |   9000 |           12000 |  ← Doğru!
| Fatma      |             1 |  12000 |           12000 |  ← Doğru!
+------------+---------------+--------+-----------------+

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING demek: "penceredeki tüm satırları dahil et — en baştan en sona." Bu frame konusunu B11-L06'da detaylı göreceğiz.

⚠️ Dikkat: LAST_VALUE kullanırken frame specification eklemeyi unutma! Aksi halde beklenmedik sonuçlar alırsın. FIRST_VALUE'da bu sorun yok çünkü ilk satır varsayılan frame'in içinde.

NTH_VALUE() — N. Satırın Değeri

NTH_VALUE(sütun, n) penceredeki n. satırın değerini döndürür.

-- Her departmanda 2. en yüksek maaş
SELECT 
    first_name,
    department_id,
    salary,
    NTH_VALUE(salary, 1) OVER w AS highest,
    NTH_VALUE(salary, 2) OVER w AS second_highest,
    NTH_VALUE(salary, 3) OVER w AS third_highest
FROM employees
WINDOW w AS (
    PARTITION BY department_id 
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);
+------------+---------------+--------+---------+----------------+---------------+
| first_name | department_id | salary | highest | second_highest | third_highest |
+------------+---------------+--------+---------+----------------+---------------+
| Fatma      |             1 |  12000 |   12000 |           9000 |          8000 |
| Zeynep     |             1 |   9000 |   12000 |           9000 |          8000 |
| Ali        |             1 |   8000 |   12000 |           9000 |          8000 |
| Can        |             3 |  15000 |   15000 |          14000 |         13000 |
+------------+---------------+--------+---------+----------------+---------------+

💡 İpucu: NTH_VALUE'da da LAST_VALUE gibi frame specification eklemen gerekir. Aksi halde henüz erişilmemiş satırlarda NULL döner.

Gerçek Dünya Örnekleri

Örnek 1: Sipariş Değişim Analizi

-- Her siparişin bir önceki siparişe göre artış/azalış yüzdesi
SELECT 
    o.order_id,
    c.first_name,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount) OVER (
        PARTITION BY o.customer_id ORDER BY o.order_date
    ) AS prev_amount,
    CASE 
        WHEN LAG(o.total_amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) IS NULL 
            THEN 'İlk Sipariş'
        WHEN o.total_amount > LAG(o.total_amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) 
            THEN 'Artış ↑'
        WHEN o.total_amount < LAG(o.total_amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) 
            THEN 'Azalış ↓'
        ELSE 'Sabit →'
    END AS trend
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY c.first_name, o.order_date;

Örnek 2: Ürün Fiyat Değişim Geçmişi

-- Ürünlerin fiyat değişim geçmişi (price_history tablosu varsayılıyor)
SELECT 
    p.product_name,
    ph.price AS current_price,
    ph.effective_date,
    LAG(ph.price) OVER (PARTITION BY ph.product_id ORDER BY ph.effective_date) AS old_price,
    ph.price - LAG(ph.price) OVER (PARTITION BY ph.product_id ORDER BY ph.effective_date) AS price_change,
    ROUND(
        (ph.price - LAG(ph.price) OVER (PARTITION BY ph.product_id ORDER BY ph.effective_date)) 
        * 100.0 / LAG(ph.price) OVER (PARTITION BY ph.product_id ORDER BY ph.effective_date),
        1
    ) AS change_pct
FROM price_history ph
JOIN products p ON ph.product_id = p.product_id
ORDER BY p.product_name, ph.effective_date;

Örnek 3: İki Sipariş Arası Ortalama Süre

-- Müşteri bazında ortalama sipariş aralığı
SELECT 
    customer_id,
    first_name,
    AVG(gap_days) AS avg_days_between_orders,
    MIN(gap_days) AS min_gap,
    MAX(gap_days) AS max_gap
FROM (
    SELECT 
        o.customer_id,
        c.first_name,
        o.order_date,
        DATEDIFF(
            o.order_date, 
            LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date)
        ) AS gap_days
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
) gaps
WHERE gap_days IS NOT NULL  -- İlk siparişi hariç tut
GROUP BY customer_id, first_name
HAVING COUNT(*) >= 2  -- En az 3 siparişi olan müşteriler
ORDER BY avg_days_between_orders;

Örnek 4: Ardışık Değişim Algılama

-- Siparişin statusu bir önceki durumdan farklı mı? (durum değişikliği tespiti)
SELECT 
    order_id,
    status,
    updated_at,
    LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at) AS prev_status,
    CASE 
        WHEN LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at) IS NULL THEN 'Yeni'
        WHEN status != LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at) THEN 'Değişti'
        ELSE 'Aynı'
    END AS change_type
FROM order_status_history
ORDER BY order_id, updated_at;

PostgreSQL Farklılıkları

LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE tüm standart SQL fonksiyonlarıdır ve MySQL ile PostgreSQL'de aynı çalışır. Küçük farklılıklar:

-- PostgreSQL: NTH_VALUE'da FROM FIRST/FROM LAST seçeneği
NTH_VALUE(salary, 2) OVER (... ORDER BY salary DESC) -- MySQL ve PostgreSQL
-- PostgreSQL'de ek olarak FROM FIRST (varsayılan) veya FROM LAST belirtilebilir

-- PostgreSQL: FILTER clause ile koşullu pencere
-- MySQL'de yok, CASE WHEN ile çözersin

Özet

  • LAG(sütun, n): n satır öncesinin değerini döndürür — zaman serisi karşılaştırmaları için ideal

  • LEAD(sütun, n): n satır sonrasının değerini döndürür — gelecek tahmin ve boşluk analizi

  • FIRST_VALUE(sütun): Penceredeki ilk satırın değeri — partition'daki min/max ile karşılaştırma

  • LAST_VALUE(sütun): Penceredeki son satırın değeri — frame specification zorunlu!

  • NTH_VALUE(sütun, n): Penceredeki n. satırın değeri — 2., 3. en yüksek gibi sorgular

  • Hepsi PARTITION BY ile grup bazlı çalışabilir

  • Varsayılan değer parametresi (3. parametre) NULL yerine anlamlı değer döndürür

Sıkça Yapılan Hatalar

  1. LAST_VALUE'da frame specification unutmak — Varsayılan frame CURRENT ROW'da biter, bu yüzden LAST_VALUE kendi satırını döndürür. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING eklemeyi unutma.

  2. LAG/LEAD'de ORDER BY unutmak — ORDER BY olmadan satır sırası belirsizdir, sonuçlar tutarsız olur. Her zaman ORDER BY belirt.

  3. NULL dönen ilk/son satırları yönetmemek — LAG ilk satırda, LEAD son satırda NULL döner. Varsayılan değer parametresini kullan veya COALESCE ile yönet.

  4. Partition sınırlarını düşünmemek — LAG/LEAD partition sınırını aşmaz. Bir partition'ın son satırında LEAD, bir sonraki partition'a geçmez — NULL döner.

  5. Aynı window tanımını tekrar tekrar yazmak — WINDOW clause kullanarak tekrarı azalt. Hem okunabilirlik hem performans için daha iyi.