Pencere fonksiyonları

Öğretmen her ders için bir liste istiyor: her öğrencinin puanı, yanında da dersin ortalaması ve öğrencinin o dersteki sırası. GROUP BY bunu yapamaz; grubu tek bir satıra indirir ve adlar kaybolur. Pencere fonksiyonları ise her satırı yerinde tutar ve ona komşu satırlardan hesaplanan bir değer ekler. Sıralamalar, kümülatif toplamlar, “geçen aya göre değişim”: analitiğin büyük bölümü bu araçla yazılır.

OVER: satırlardan bir “pencere”

Sıradan bir toplama fonksiyonunun arkasına OVER yazdığın anda o bir pencere fonksiyonuna dönüşür. Aşağıda her kaydın yanında kendi dersinin ortalaması görünür: satırlar birleştirilmez, ortalama ise her ders için ayrı hesaplanır.

SELECT e.course_id, e.student_id, e.score,
       ROUND(AVG(e.score) OVER (PARTITION BY e.course_id), 1) AS course_avg
FROM enrollments AS e
WHERE e.course_id IN (1, 3)
ORDER BY e.course_id, e.score DESC;

course_id | student_id | score | course_avg
1 | 11 | 97 | 87.3
1 | 1 | 92 | 87.3
1 | 5 | 85 | 87.3
1 | 2 | 75 | 87.3
3 | 11 | 94 | 80
3 | 2 | 81 | 80
3 | 6 | 77 | 80
3 | 4 | 68 | 80

ROW_NUMBER, RANK ve DENSE_RANK

Üç sıralama fonksiyonu yalnızca eşit değerlerde farklılaşır. Öğrencileri yaşa göre sıralayalım: üç kişi 17, üç kişi 16 yaşında. ROW_NUMBER eşitliğe bakmaz ve 1, 2, 3 verir; bu yüzden sonucun sabit olması için benzersiz ikinci bir sıralama sütunu (id) ekliyoruz.

SELECT first_name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC, id) AS row_num,
       RANK()       OVER (ORDER BY age DESC)     AS rnk,
       DENSE_RANK() OVER (ORDER BY age DESC)     AS dense
FROM students
ORDER BY age DESC, id
LIMIT 7;

first_name | age | row_num | rnk | dense
Elvin | 17 | 1 | 1 | 1
Tural | 17 | 2 | 1 | 1
Fidan | 17 | 3 | 1 | 1
Murad | 16 | 4 | 4 | 2
Rəşad | 16 | 5 | 4 | 2
Səbinə | 16 | 6 | 4 | 2
Aysel | 15 | 7 | 7 | 3

PARTITION BY: her grubun kendi sıralaması

PARTITION BY course_id her dersi ayrı bir pencereye dönüştürür ve numaralandırma her derste 1'den başlar. “Her dersin en iyi öğrencisi” sorusu böyle çözülür: önce sıraları bir CTE içinde hesaplar, sonra pos = 1 olan satırları bırakırız.

WITH ranked AS (
  SELECT c.title, s.first_name, e.score,
         RANK() OVER (PARTITION BY e.course_id ORDER BY e.score DESC) AS pos
  FROM enrollments AS e
  JOIN students AS s ON s.id = e.student_id
  JOIN courses  AS c ON c.id = e.course_id
)
SELECT title, first_name, score
FROM ranked
WHERE pos = 1
ORDER BY title;

title | first_name | score
Algebra | Fidan | 97
English B1 | Leyla | 90
Geometry | Leyla | 95
Mechanics | Fidan | 94
Organic Chemistry | Elvin | 72
Python Basics | Rəşad | 99
World History | Nigar | 93

Kümülatif toplam: SUM() OVER (ORDER BY ...)

OVER içine ORDER BY yazıldığında pencere “baştan bu satıra kadar” olur. Bu yüzden SUM kümülatif toplam verir: her ayın geliri önceki ayların toplamına eklenir. Aşağıda önce aylık geliri bir CTE ile hesaplıyor, sonra onun üzerinde bir pencere fonksiyonu çalıştırıyoruz.

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

month | revenue | running_total
2025-01 | 1691 | 1691
2025-02 | 963.99 | 2654.99
2025-03 | 124.98 | 2779.97
2025-04 | 935.99 | 3715.96
2025-05 | 42 | 3757.96
2025-06 | 152.1 | 3910.06
2025-07 | 32 | 3942.06

Bir incelik var: ORDER BY varken varsayılan çerçeve RANGE kipindedir ve sıralama anahtarı aynı olan satırları (eşdeğer satırları) birlikte alır. İki sipariş aynı tarihteyse ikisi de o günün toplamının tamamını gösterir. Satır satır büyüyen bir toplam için benzersiz bir anahtar ekle ya da çerçeveyi açıkça yaz: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SELECT id, order_date, quantity,
       SUM(quantity) OVER (ORDER BY order_date)     AS by_range,
       SUM(quantity) OVER (ORDER BY order_date, id) AS by_row
FROM orders
WHERE order_date < '2025-05-01'
ORDER BY order_date, id;

id | order_date | quantity | by_range | by_row
1 | 2025-01-15 | 1 | 3 | 1
2 | 2025-01-15 | 2 | 3 | 3
3 | 2025-02-02 | 20 | 23 | 23
4 | 2025-02-10 | 1 | 24 | 24
5 | 2025-03-05 | 1 | 25 | 25
6 | 2025-03-18 | 2 | 27 | 27
7 | 2025-04-09 | 1 | 31 | 28
8 | 2025-04-09 | 3 | 31 | 31

LAG ve LEAD: komşu satırla karşılaştırma

LAG(x) penceredeki önceki satırın değerini, LEAD(x) ise sonraki satırın değerini döndürür. Bunlarla “geçen aya göre değişim” tek bir sorguyla hesaplanır. İlk ayın bir öncesi olmadığı için LAG orada NULL verir. LAG(x, 2) iki satır geriye bakar, LAG(x, 1, 0) ise NULL yerine 0 döndürür.

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

month | revenue | prev_revenue | change
2025-01 | 1691 | NULL | NULL
2025-02 | 963.99 | 1691 | -727.01
2025-03 | 124.98 | 963.99 | -839.01
2025-04 | 935.99 | 124.98 | 811.01
2025-05 | 42 | 935.99 | -893.99
2025-06 | 152.1 | 42 | 110.1
2025-07 | 32 | 152.1 | -120.1

SELECT category, name, price
       -- add price_rank here
FROM products
ORDER BY category, price DESC;

category | name | price | price_rank
Accessories | Backpack | 55 | 1
Accessories | Water bottle | 12 | 2
Electronics | Laptop | 1450 | 1
Electronics | Smartphone | 899.99 | 2
Electronics | Monitor | 310 | 3
Electronics | Headphones | 120.5 | 4
Games | Chess set | 42 | 1
Home | Desk lamp | 34.99 | 1
Stationery | Pen set | 7.9 | 1
Stationery | Notebook | 3.2 | 2

SELECT customer_id, order_date, quantity
       -- order_no and running_qty
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, order_date, id;

customer_id | order_date | quantity | order_no | running_qty
1 | 2025-01-15 | 1 | 1 | 1
1 | 2025-01-15 | 2 | 2 | 3
1 | 2025-07-07 | 10 | 3 | 13
2 | 2025-02-02 | 20 | 1 | 20
2 | 2025-06-30 | 1 | 2 | 21
3 | 2025-02-10 | 1 | 1 | 1
3 | 2025-05-21 | 1 | 2 | 2