Window Function'lar 2: Running Total, ROWS vs RANGE Frame'leri, LAG/LEAD ve Gaps and Islands
ORDER BY'lı bir window aggregate'in varsayılan frame'i RANGE UNBOUNDED PRECEDING'dir; aynı değerli row'lar aynı toplamı alır ve SQL Server worktable kullanır. Kümülatif toplamı, ROWS ile RANGE'in logical read farkını, eksik günlerle hareketli ortalamayı, LAG/LEAD'i, LAST_VALUE tuzağını, PERCENTILE_CONT ile medyanı ve gaps and islands'ı Bookstore verisinde gerçek çıktıyla gösteriyoruz.
Sürüm notu: Bu yazıdaki SQL (Structured Query Language) örnekleri SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) üzerinde serinin Bookstore veritabanıyla çalıştırılmıştır; PostgreSQL karşılaştırmaları PostgreSQL 18.6 ile denenmiştir.
Önce Tahmin Et
20–23 Aralık 2025 arasındaki siparişleri tarihe göre sıralayıp "o ana kadar kaç sipariş geldi" sayısını yazdırıyoruz. 21 Aralık'ta iki, 22 Aralık'ta dört, 23 Aralık'ta bir sipariş var. running_orders column'u hangi sayıları gösterir?
SELECT CAST(ordered_at AS date) AS order_date,
order_id,
COUNT(*) OVER (ORDER BY CAST(ordered_at AS date)) AS running_orders
FROM dbo.orders
WHERE ordered_at >= '2025-12-20' AND ordered_at < '2025-12-24'
ORDER BY order_id;1, 2, 3, 4, 5, 6, 7 bekliyorsan yalnız değilsin. Gerçek çıktı:
order_date order_id running_orders
---------- -------- --------------
2025-12-21 247 2
2025-12-21 248 2
2025-12-22 249 6
2025-12-22 250 6
2025-12-22 251 6
2025-12-22 252 6
2025-12-23 253 721 Aralık'taki iki siparişin ikisi de 2, 22 Aralık'taki dördünün dördü de 6. Bu bir bug değil, standardın tanımladığı varsayılan davranış: ORDER BY yazıp frame belirtmediğinde window'un sınırı RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW olur ve RANGE, sıralama değeri aynı olan bütün row'ları "current row" sayar. Bu yazı window'u row row hareket ettiren tarafı ele alıyor: frame'ler, kümülatif toplam, hareketli ortalama, LAG/LEAD, medyan ve gaps and islands.
Varsayılan Frame: RANGE UNBOUNDED PRECEDING
Bir önceki yazıda window'u PARTITION BY ile böldük ve sıralama function'larıyla numaraladık. Aggregate window function'lar (SUM, COUNT, AVG, MIN, MAX) bir adım daha ileri gider: ORDER BY verildiğinde her row için partition'ın bir alt kümesine, frame'e bakarlar. Frame iki şekilde tanımlanır:
ROWS: fiziksel row sayısıyla.ROWS BETWEEN 2 PRECEDING AND CURRENT ROWtam olarak üç row'dur.RANGE: sıralama değeriyle.CURRENT ROW, aynı sıralama değerine sahip bütün row'lar demektir.
Microsoft Learn'deki OVER dokümanı bunu açıkça yazar: ORDER BY var, ROWS/RANGE yoksa varsayılan RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'dur. Açılıştaki sorguda sıralama yalnız tarihe göre olduğu için aynı gündeki bütün siparişler tek bir "current row" grubuna girdi. Düzeltme, frame'i ROWS olarak açıkça yazmak ve sıralamayı benzersiz yapmak:
SELECT CAST(ordered_at AS date) AS order_date,
order_id,
COUNT(*) OVER (ORDER BY CAST(ordered_at AS date), order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_orders
FROM dbo.orders
WHERE ordered_at >= '2025-12-20' AND ordered_at < '2025-12-24'
ORDER BY order_id;order_date order_id running_orders
---------- -------- --------------
2025-12-21 247 1
2025-12-21 248 2
2025-12-22 249 3
2025-12-22 250 4
2025-12-22 251 5
2025-12-22 252 6
2025-12-23 253 7Kuralı ezberle: aggregate'i OVER (ORDER BY ...) ile kullanıyorsan frame'i her zaman açıkça yaz. Aynı günün siparişlerini gerçekten tek adım saymak istediğin durumlar da olabilir (gün sonu bakiyesi gibi); o zaman RANGE'i bilerek yaz ki okuyan bunun niyet olduğunu anlasın.
Kümülatif Toplam
En sık yazılan frame sorgusu yılbaşından bugüne (YTD (Year to Date)) toplam. Önce ayları hesaplayıp sonra üzerinde kümülatif toplam alıyoruz:
WITH monthly AS (
SELECT DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1) AS month_start,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'Cancelled' AND o.ordered_at >= '2025-01-01' AND o.ordered_at < '2026-01-01'
GROUP BY DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1)
)
SELECT month_start, revenue,
SUM(revenue) OVER (ORDER BY month_start ROWS UNBOUNDED PRECEDING) AS ytd_revenue
FROM monthly
ORDER BY month_start;month_start revenue ytd_revenue
----------- -------- -----------
2025-01-01 8959.20 8959.20
2025-02-01 13727.50 22686.70
2025-03-01 20367.60 43054.30
2025-04-01 18488.20 61542.50
2025-05-01 13679.60 75222.10
2025-06-01 15414.30 90636.40
2025-07-01 12866.70 103503.10
2025-08-01 12681.50 116184.60
2025-09-01 33349.23 149533.83
2025-10-01 27489.90 177023.73
2025-11-01 20325.80 197349.53
2025-12-01 41866.20 239215.73ROWS UNBOUNDED PRECEDING, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'un kısa yazımı. Window function olmadan bunu ya correlated subquery ile (her ay için önceki ayları toplayarak, row sayısının karesiyle büyüyen bir maliyetle) ya da uygulama tarafında döngüyle yapardın. Her ülke için ayrı YTD istiyorsan PARTITION BY country_code eklemen yeter; toplam her partition'da sıfırdan başlar.
ROWS ile RANGE'in Performans Farkı
Sonuç aynı olsa bile ROWS ile RANGE aynı maliyette değildir. order_items üzerinde benzersiz bir anahtarla (order_item_id) kümülatif toplam alıp iki frame'i SET STATISTICS IO ON ile karşılaştırdım. Sıralama anahtarı benzersiz olduğu için iki sorgu aynı sonucu üretir:
SET STATISTICS IO ON;
SELECT SUM(x.running) AS checksum
FROM (SELECT SUM(oi.quantity) OVER (ORDER BY oi.order_item_id) AS running
FROM dbo.order_items AS oi) AS x;
SET STATISTICS IO OFF;checksum
--------
281845
Table 'Worktable'. Scan count 681, logical reads 4087, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'order_items'. Scan count 1, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.SET STATISTICS IO ON;
SELECT SUM(x.running) AS checksum
FROM (SELECT SUM(oi.quantity) OVER (ORDER BY oi.order_item_id ROWS UNBOUNDED PRECEDING) AS running
FROM dbo.order_items AS oi) AS x;
SET STATISTICS IO OFF;checksum
--------
281845
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'order_items'. Scan count 1, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.Aynı checksum, ama RANGE sürümü Worktable üzerinde 681 scan ve 4087 logical read yaptı, ROWS sürümü sıfır. SQL Server, RANGE frame'i için aynı değere sahip row'ların hepsini görebilmek amacıyla tempdb'de bir worktable (window spool) kullanır; ROWS UNBOUNDED PRECEDING ise bellekteki hızlı yolla hesaplanır. 680 row'da bile fark açık; milyonlarca row'da bu, raporun saniyeler mi dakikalar mı süreceğini belirler. Varsayılan frame'e güvenmek hem yanlış sonuç hem de yavaş sorgu riski taşıyor. (Batch mode'da çalışan Window Aggregate operator'ü bu spool'u kullanmaz; hangi koşulda devreye girdiği SQL Server Derinlemesine serisinin konusu.)
Hareketli Ortalama ve Eksik Günler
7 günlük hareketli ortalama için akla ilk gelen ROWS BETWEEN 6 PRECEDING AND CURRENT ROW:
WITH daily AS (
SELECT CAST(o.ordered_at AS date) AS d, COUNT(*) AS orders
FROM dbo.orders AS o
WHERE o.ordered_at >= '2025-12-01' AND o.ordered_at < '2026-01-01'
GROUP BY CAST(o.ordered_at AS date)
),
averaged AS (
SELECT d, orders,
CAST(AVG(orders * 1.0) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(5,2)) AS avg_7_rows
FROM daily
)
SELECT d, orders, avg_7_rows
FROM averaged
WHERE d >= '2025-12-20'
ORDER BY d;d orders avg_7_rows
---------- ------ ----------
2025-12-21 2 1.43
2025-12-22 4 1.86
2025-12-23 1 1.71
2025-12-24 2 1.86
2025-12-25 1 1.86
2025-12-26 1 1.86
2025-12-27 1 1.71
2025-12-28 1 1.57
2025-12-29 1 1.14
2025-12-30 1 1.14
2025-12-31 2 1.14Window'u bir CTE (Common Table Expression) içinde bütün Aralık üzerinde hesaplayıp filtreyi dışarıda uyguladım (bunun neden önemli olduğuna birazdan döneceğim). Sonuç makul görünüyor ama yanlış. 14, 16 ve 20 Aralık'ta hiç sipariş yok, bu yüzden daily'de o günlere ait row da yok. ROWS frame'i row sayar, gün saymaz: 21 Aralık'taki "son 7 row" aslında 12 Aralık'a kadar uzanıyor. Siparişi olmayan her gün window'u bir gün daha geriye esnetiyor. Düzeltme, önce eksiksiz bir takvim üretip ona LEFT JOIN yapmak:
WITH days AS (
SELECT DATEADD(day, value, CAST('2025-12-01' AS date)) AS d
FROM GENERATE_SERIES(0, 30)
),
daily AS (
SELECT days.d, COUNT(o.order_id) AS orders
FROM days
LEFT JOIN dbo.orders AS o
ON o.ordered_at >= days.d AND o.ordered_at < DATEADD(day, 1, days.d)
GROUP BY days.d
),
averaged AS (
SELECT d, orders,
CAST(AVG(orders * 1.0) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(5,2)) AS avg_7_days
FROM daily
)
SELECT d, orders, avg_7_days
FROM averaged
WHERE d >= '2025-12-20'
ORDER BY d;d orders avg_7_days
---------- ------ ----------
2025-12-20 0 .71
2025-12-21 2 1.00
2025-12-22 4 1.43
2025-12-23 1 1.57
2025-12-24 2 1.71
2025-12-25 1 1.71
2025-12-26 1 1.57
2025-12-27 1 1.71
2025-12-28 1 1.57
2025-12-29 1 1.14
2025-12-30 1 1.14
2025-12-31 2 1.14Takvimi GENERATE_SERIES (SQL Server 2022+) ile ürettim; 12. yazıda gördüğümüz recursive CTE de aynı işi görür. Artık 20 Aralık 0 siparişle listede ve 21 Aralık'ın ortalaması 1,43 değil 1,00. Fark küçük görünse de bir dashboard'da "son 7 gün" yazıyorsa gerçekten 7 gün olmalı.
Şimdi o CTE'nin neden gerektiğine gelelim. Filtreyi window function ile aynı sorguya yazarsan:
WITH daily AS (
SELECT CAST(o.ordered_at AS date) AS d, COUNT(*) AS orders
FROM dbo.orders AS o
WHERE o.ordered_at >= '2025-12-01' AND o.ordered_at < '2026-01-01'
GROUP BY CAST(o.ordered_at AS date)
)
SELECT d, orders,
CAST(AVG(orders * 1.0) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(5,2)) AS avg_7_rows
FROM daily
WHERE d >= '2025-12-20'
ORDER BY d;d orders avg_7_rows
---------- ------ ----------
2025-12-21 2 2.00
2025-12-22 4 3.00
2025-12-23 1 2.33
2025-12-24 2 2.25
2025-12-25 1 2.00
2025-12-26 1 1.83
2025-12-27 1 1.71
2025-12-28 1 1.57
2025-12-29 1 1.14
2025-12-30 1 1.14
2025-12-31 2 1.1421 Aralık'ın ortalaması 2,00 çıktı, çünkü WHERE window'dan önce çalışır ve 20 Aralık'tan önceki bütün row'ları window hesaplanmadan siler. Window yalnız kendisine kalan row'ları görür. Kural: window function'ın geçmişe bakması gerekiyorsa hesabı geniş veride yap, görüntülenecek aralığı dış sorguda filtrele.
Standart bu sorunu daha zarif çözer: RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW ile frame'i değer aralığıyla tanımlarsın ve eksik günler sorun olmaz. PostgreSQL bunu destekliyor (denemede RANGE BETWEEN INTERVAL '6 days' PRECEDING beklendiği gibi çalıştı). SQL Server ise RANGE ile yalnız UNBOUNDED ve CURRENT ROW kabul ediyor:
SELECT CAST(ordered_at AS date) AS d,
COUNT(*) OVER (ORDER BY CAST(ordered_at AS date) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS last_7_days
FROM dbo.orders;Msg 4194, Level 16, State 1, Server b8be943222fa, Line 1
RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.Önceki ve Sonraki Row: LAG, LEAD, FIRST_VALUE, LAST_VALUE
LAG bir önceki row'un değerini, LEAD bir sonrakini getirir; self join'e gerek kalmaz. Aylık ciro ve bir önceki aya göre değişim:
WITH monthly AS (
SELECT DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1) AS month_start,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'Cancelled' AND o.ordered_at >= '2025-07-01' AND o.ordered_at < '2026-01-01'
GROUP BY DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1)
)
SELECT month_start, revenue,
LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
CAST(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
/ LAG(revenue) OVER (ORDER BY month_start) AS DECIMAL(6,1)) AS change_pct,
LEAD(month_start, 1, '9999-12-31') OVER (ORDER BY month_start) AS next_month
FROM monthly
ORDER BY month_start;month_start revenue prev_revenue change_pct next_month
----------- -------- ------------ ---------- ----------
2025-07-01 12866.70 NULL NULL 2025-08-01
2025-08-01 12681.50 12866.70 -1.4 2025-09-01
2025-09-01 33349.23 12681.50 163.0 2025-10-01
2025-10-01 27489.90 33349.23 -17.6 2025-11-01
2025-11-01 20325.80 27489.90 -26.1 2025-12-01
2025-12-01 41866.20 20325.80 106.0 9999-12-31İlk ayın önceki ayı olmadığı için LAG NULL döndü ve değişim de NULL oldu. LEAD'in üçüncü argümanı varsayılan değerdir: son ayın sonraki ayı yerine 9999-12-31 yazdı. İkinci argüman kaç row geriye ya da ileriye bakılacağıdır; LAG(revenue, 12) geçen yılın aynı ayını getirir.
Aynı function PARTITION BY ile müşteri bazında da çalışır. Bir müşterinin ardışık siparişleri arasındaki gün sayısı, yeniden satın alma analizinin temeli:
SELECT TOP (6) order_id, ordered_at,
DATEDIFF(day, LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id), ordered_at) AS days_since_prev
FROM dbo.orders
WHERE customer_id = 5
ORDER BY ordered_at, order_id;order_id ordered_at days_since_prev
-------- ------------------- ---------------
56 2025-03-31 21:48:08 NULL
57 2025-04-02 09:27:19 2
107 2025-06-24 15:45:36 83
208 2025-11-20 15:27:45 149
359 2026-05-15 12:00:31 176FIRST_VALUE ve LAST_VALUE frame'in ilk ve son row'unu getirir ve burada açılıştaki varsayılan frame bir kez daha karşına çıkar:
SELECT title, list_price,
FIRST_VALUE(title) OVER (ORDER BY list_price DESC, book_id) AS most_expensive,
LAST_VALUE(title) OVER (ORDER BY list_price DESC, book_id) AS cheapest_wrong,
LAST_VALUE(title) OVER (ORDER BY list_price DESC, book_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS cheapest
FROM dbo.books
WHERE category_id = 4
ORDER BY list_price DESC, book_id;title list_price most_expensive cheapest_wrong cheapest
-------------------- ---------- ------------------- -------------------- ---------------
The Handmaid's Tale 580.50 The Handmaid's Tale The Handmaid's Tale Brave New World
Nineteen Eighty-Four 530.90 The Handmaid's Tale Nineteen Eighty-Four Brave New World
Fahrenheit 451 530.90 The Handmaid's Tale Fahrenheit 451 Brave New World
Brave New World 320.90 The Handmaid's Tale Brave New World Brave New WorldFIRST_VALUE beklendiği gibi her row'a en pahalı kitabı yazdı. LAST_VALUE ise her row'a kendi adını yazdı: varsayılan frame "partition'ın başından current row'a kadar" olduğu için frame'in son row'u her zaman current row'dur. Doğru sonuç için frame'i partition'ın sonuna kadar açman gerekir: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Pratikte ben LAST_VALUE yerine ters sıralı FIRST_VALUE yazmayı tercih ediyorum; frame'e takılmaz. SQL Server 2022 ile FIRST_VALUE ve LAST_VALUE standarttaki IGNORE NULLS seçeneğini de destekliyor.
Medyan: PERCENTILE_CONT ve PERCENTILE_DISC
Ortalama sipariş tutarı, birkaç büyük siparişle kolayca şişer; medyan daha dürüst bir ölçüdür. PERCENTILE_CONT sıralı değerler arasında doğrusal interpolasyon yapar, PERCENTILE_DISC ise kümede gerçekten var olan bir değeri döndürür:
WITH order_totals AS (
SELECT o.order_id, c.country_code, SUM(oi.quantity * oi.unit_price) AS total
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'Cancelled'
GROUP BY o.order_id, c.country_code
)
SELECT DISTINCT country_code,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) OVER (PARTITION BY country_code) AS median_cont,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total) OVER (PARTITION BY country_code) AS median_disc,
AVG(total) OVER (PARTITION BY country_code) AS mean_total
FROM order_totals
ORDER BY country_code;country_code median_cont median_disc mean_total
------------ ------------------ ----------- -----------
DE 940.89999999999998 940.90 1027.002298
GB 660.0 660.00 916.208604
NL 1490.7 1430.90 1233.150000
TR 847.62 847.62 1031.972648Üç şeye dikkat. NL'de (Hollanda) çift sayıda sipariş olduğu için iki yöntem farklı sonuç verdi: PERCENTILE_CONT iki ortadaki değerin ortalamasını, PERCENTILE_DISC ilkini aldı. DE'deki (Almanya) 940.89999999999998, PERCENTILE_CONT'un float döndürmesinin sonucu; parayı raporlarken decimal'a CAST et. Üçüncüsü DISTINCT: SQL Server'da bu iki function yalnız window function olarak çalışır, yani her row'a sonucu yazar ve ülke başına tek row için DISTINCT gerekir. Standartta ve PostgreSQL'de ise bunlar ordered-set aggregate'tir; GROUP BY ile, OVER olmadan kullanılır. SQL Server'da aynısını denersen:
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY list_price) AS median_price
FROM dbo.books;Msg 10753, Level 15, State 3, Server b8be943222fa, Line 1
The function 'PERCENTILE_CONT' must have an OVER clause.Gaps and Islands
"Müşterinin art arda sipariş verdiği günler" ya da "hiç sipariş gelmeyen dönemler" gibi sorular gaps and islands problemi olarak bilinir. Grace'in (customer 3) Kasım sonundaki sipariş günleri:
d
----------
2025-11-24
2025-11-25
2025-11-26
2025-11-27
2025-12-01
2025-12-0224–27 Kasım arası dört gün art arda, sonra 1–2 Aralık iki gün. Bu adaları (islands) bulmanın klasik hilesi şu: ardışık tarihlerden, onların sıra numarası kadar gün çıkarırsan aynı adadaki bütün tarihler aynı değeri verir.
WITH days AS (
SELECT DISTINCT CAST(ordered_at AS date) AS d
FROM dbo.orders
WHERE customer_id = 3 AND ordered_at >= '2025-11-20' AND ordered_at < '2025-12-05'
)
SELECT d,
ROW_NUMBER() OVER (ORDER BY d) AS rn,
DATEADD(day, -ROW_NUMBER() OVER (ORDER BY d), d) AS grp
FROM days
ORDER BY d;d rn grp
---------- -- ----------
2025-11-24 1 2025-11-23
2025-11-25 2 2025-11-23
2025-11-26 3 2025-11-23
2025-11-27 4 2025-11-23
2025-12-01 5 2025-11-26
2025-12-02 6 2025-11-2624 Kasım − 1 gün = 23 Kasım, 25 Kasım − 2 gün = 23 Kasım... Ada bozulduğunda (28–30 Kasım boşluğu) tarih sayıdan hızlı ilerler ve grp değişir. Artık grp'e göre gruplamak yeter. Bütün müşteriler için en az üç gün süren seriler:
WITH days AS (
SELECT DISTINCT customer_id, CAST(ordered_at AS date) AS d
FROM dbo.orders
),
grouped AS (
SELECT customer_id, d,
DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY d), d) AS grp
FROM days
)
SELECT customer_id, MIN(d) AS streak_start, MAX(d) AS streak_end, COUNT(*) AS days_in_a_row
FROM grouped
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3
ORDER BY days_in_a_row DESC, customer_id, streak_start;customer_id streak_start streak_end days_in_a_row
----------- ------------ ---------- -------------
3 2025-11-24 2025-11-27 4Tek ön koşul, tarihlerin tekil olması: aynı gün iki sipariş varsa sıra numarası tarihten hızlı artar ve hesap bozulur. Bu yüzden days CTE'sinde DISTINCT kullandım. Boşlukları (gaps) bulmak ise LEAD ile daha kolay: her günün bir sonraki sipariş gününe bak, arada bir günden fazla varsa boşluk vardır. Bookstore'da hiç sipariş gelmeyen en uzun dönemler:
WITH days AS (
SELECT DISTINCT CAST(ordered_at AS date) AS d FROM dbo.orders
),
next_day AS (
SELECT d, LEAD(d) OVER (ORDER BY d) AS next_d FROM days
)
SELECT TOP (5) DATEADD(day, 1, d) AS gap_start, DATEADD(day, -1, next_d) AS gap_end,
DATEDIFF(day, d, next_d) - 1 AS days_without_orders
FROM next_day
WHERE DATEDIFF(day, d, next_d) > 1
ORDER BY days_without_orders DESC, gap_start;gap_start gap_end days_without_orders
---------- ---------- -------------------
2025-02-23 2025-03-03 9
2026-05-16 2026-05-24 9
2025-01-03 2025-01-09 7
2026-05-07 2026-05-13 7
2025-07-07 2025-07-12 6Üretimde Dikkat
Aynı window tanımını birçok function'da tekrar ediyorsan SQL Server 2022 ile gelen WINDOW clause'u tanımı bir kez yazmanı sağlar. Standardın bir parçası ve PostgreSQL ile MySQL'de de var; SQL Server'da compatibility level 160 ya da üstü gerektirir:
SELECT title, list_price,
MIN(list_price) OVER w AS min_price,
MAX(list_price) OVER w AS max_price,
AVG(list_price) OVER w AS avg_price
FROM dbo.books
WHERE category_id = 12
WINDOW w AS (PARTITION BY category_id);title list_price min_price max_price avg_price
------------------------------- ---------- --------- --------- -----------
The Art of Computer Programming 1450.00 1150.00 1450.00 1300.000000
The Algorithm Design Manual 1150.00 1150.00 1450.00 1300.000000Window function'ların maliyeti partition ve sıralama için yapılan sort'tan ve frame hesaplamasından gelir. Partition ve sıralama column'larıyla başlayan bir index sort'u kaldırır; frame'i ROWS olarak yazmak worktable'ı kaldırır. İkisinin etkisini logical read'lerle ölçebilirsin, ölçmeden "window function yavaş" deme.
Hareketli ortalama ve YTD gibi raporlarda tarih boşlukları en sık görülen sessiz hata. Hesabı her zaman eksiksiz bir takvime join edilmiş veride yap; takvim tablosu (calendar table) birçok ekipte bu yüzden kalıcı bir tablo olarak durur.
Gaps and islands hilesinde tarih yerine bir sayı dizisi de kullanılabilir (fatura numaraları, sensör ölçüm sırası). Kural aynı: ardışıklık "değer − sıra numarası" sabit kaldıkça sürer.
Sık Düşülen Tuzaklar
SUM() OVER (ORDER BY ...) yazıp frame'i belirtmemek. Varsayılan RANGE, aynı değerli row'larda aynı toplamı verir ve worktable maliyeti getirir.
LAST_VALUE'yu varsayılan frame ile kullanmak. Her row kendini döndürür; frame'i UNBOUNDED FOLLOWING'e kadar aç ya da ters sıralı FIRST_VALUE yaz.
ROWS BETWEEN 6 PRECEDING ile "son 7 gün" hesaplamak. Eksik günler window'u esnetir; önce takvime join et.
PERCENTILE_CONT'un float döndürdüğünü unutmak. Para birimini decimal'a çevir.
Gaps and islands'ta tekrarlayan tarihleri DISTINCT ile tekilleştirmemek.
Window function'ın bakacağı geçmiş row'ları aynı sorgudaki WHERE ile silmek. Önce CTE içinde hesapla, sonra filtrele.
WINDOW clause'unu eski compatibility level'daki bir veritabanında kullanıp "SQL Server 2022'de çalışıyordu" demek.
Küçük Bir Deneme
★☆☆ Her müşterinin siparişleri için müşteri bazında kümülatif harcama (PARTITION BY customer_id) hesapla. Frame'i açıkça ROWS olarak yaz ve sıralamaya order_id'yi ekle.
★★☆ Aylık ciroda LAG(revenue, 12) ile geçen yılın aynı ayına göre değişimi hesapla. 2026'nın ilk altı ayı için sonuç dolu, 2025'in ayları için NULL olmalı. Neden?
★★★ Her kitap için "art arda en çok gün satılan seri"yi bul: kitabın sipariş edildiği günlerden adaları çıkar ve her kitabın en uzun adasını döndür. Sonra aynı sorguyu "hiç sipariş gelmeyen en uzun dönem" için LEAD ile yaz.
Window function'larla seri boyunca hep okuma yaptık. Bir sonraki yazıda veriyi değiştiriyoruz: çok satırlı INSERT, INSERT ... SELECT, OUTPUT ile yeni id'leri almak, UPDATE ... FROM, DELETE ile TRUNCATE farkı, MERGE'ün tuzakları ve WHERE'siz bir UPDATE'e karşı kendini nasıl koruyacağın.