İleri pencere çerçeveleri: ROWS, RANGE, GROUPS ve EXCLUDE

Üç çerçeve birimi

ROWS ile RANGE farkı: eşitlikler

Aynı fiyattaki ürünlerde yürüyen toplam:

SELECT name, price,
       SUM(price) OVER (ORDER BY price ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_toplam,
       SUM(price) OVER (ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_toplam
FROM products
WHERE category IN ('Kitap', 'Hediye')
ORDER BY price, name;

RANGE, aynı fiyattaki tüm satırları “şu ana kadar” sayar; bu yüzden eşit değerli satırlar aynı toplamı gösterir. ORDER BY yazıp çerçeve yazmazsan varsayılan RANGE'dir; yürüyen toplamda beklenmedik sonuç görürsen nedeni budur.

Değer aralığına göre pencere

“Son 7 günlük sipariş sayısı” gibi takvime dayalı pencerelerde satır sayısı değil tarih aralığı gerekir (bazı günlerde hiç sipariş yoksa ROWS yanlış olur):

-- PostgreSQL, Oracle, MySQL 8
SELECT order_date, gunluk,
       SUM(gunluk) OVER (ORDER BY order_date
                         RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW) AS son7
FROM gunluk_siparis;

-- BigQuery: RANGE yalnızca sayısal; tarihi gün numarasına çevir
SUM(gunluk) OVER (ORDER BY UNIX_DATE(order_date) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW)

-- SQL Server: RANGE yalnızca UNBOUNDED / CURRENT ROW destekler;
-- takvim tablosuyla eksik günleri doldurup ROWS BETWEEN 6 PRECEDING kullanılır.

SQLite'ta da sayısal RANGE vardır; tarihi gün numarasına (julianday) çevirerek deneyelim:

WITH g AS (
  SELECT order_date, COUNT(*) AS gunluk
  FROM orders
  WHERE order_date >= '2024-12-01'
  GROUP BY order_date
)
SELECT order_date, gunluk,
       SUM(gunluk) OVER (ORDER BY julianday(order_date) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS son7
FROM g
ORDER BY order_date;

GROUPS ve EXCLUDE

SELECT name, price,
       SUM(price) OVER (ORDER BY price GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS komsu_gruplar,
       AVG(price) OVER (PARTITION BY category ORDER BY price
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                        EXCLUDE CURRENT ROW) AS digerlerinin_ort
FROM products
WHERE category = 'Kitap'
ORDER BY price;

EXCLUDE CURRENT ROW satırın kendisini pencereden çıkarır: “bu ürün kategorideki diğer ürünlerin ortalamasından ne kadar farklı?” sorusu için idealdir. Diğer seçenekler: EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS.