MariaDB Window Fonksiyonları: OVER, PARTITION BY, ROW_NUMBER, RANK ve LAG

Temel sözdizimi

window_function(expr) OVER (
  [PARTITION BY expr [, ...]]
  [ORDER BY expr [ASC | DESC] [, ...]]
  [{ROWS | RANGE} frame_clause]
)

frame_clause:
  {frame_border | BETWEEN frame_border AND frame_border}

frame_border:
  UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | CURRENT ROW
  | expr PRECEDING
  | expr FOLLOWING

Aynı pencere tanımını birden fazla fonksiyonda kullanacaksanız onu WINDOW yan tümcesinde adlandırabilirsiniz. Belgedeki örnek yürüyen toplamı ve yürüyen ortalamayı aynı pencereyle hesaplar:

SELECT
  SUM(score) OVER w AS running_total,
  AVG(score) OVER w AS running_avg
FROM student
WINDOW w AS (
  ORDER BY score
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

Window fonksiyonu ile GROUP BY farkı

Belgedeki örnek tabloyu kullanalım. Dört öğrencinin iki sınavdan aldığı puanlar var:

CREATE TABLE student (
  name CHAR(10),
  test CHAR(10),
  score TINYINT
);

INSERT INTO student VALUES
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

SELECT test, AVG(score) FROM student GROUP BY test; her sınav için tek bir satır döndürür; öğrenci adları kaybolur. Window fonksiyonu ise tüm satırları korur ve yanlarına grup ortalamasını ekler:

SELECT
  name,
  test,
  score,
  AVG(score) OVER (PARTITION BY test) AS avg_by_test
FROM student
ORDER BY test, name;

+---------+--------+-------+-------------+
| name    | test   | score | avg_by_test |
+---------+--------+-------+-------------+
| Chun    | SQL    |    75 |     65.2500 |
| Esben   | SQL    |    43 |     65.2500 |
| Kaolin  | SQL    |    56 |     65.2500 |
| Tatiana | SQL    |    87 |     65.2500 |
| Chun    | Tuning |    73 |     68.7500 |
| Esben   | Tuning |    31 |     68.7500 |
| Kaolin  | Tuning |    88 |     68.7500 |
| Tatiana | Tuning |    83 |     68.7500 |
+---------+--------+-------+-------------+

Kural basittir: grup başına tek satır istiyorsanız GROUP BY, satırları koruyup yanlarına grup ya da sıra bazlı hesap eklemek istiyorsanız window fonksiyonu kullanın. Bu örnekte her öğrencinin puanını sınav ortalamasıyla aynı satırda karşılaştırabilirsiniz.

Sıralama fonksiyonları: ROW_NUMBER, RANK, DENSE_RANK

Üçü de bölüm içindeki ORDER BY sırasına göre 1'den başlayan bir numara verir; fark eşit değerlerde ortaya çıkar. ROW_NUMBER() eşit değerlere bile farklı numara verir. RANK() eşit değerlere aynı sırayı verir ve sonrasında atlama yapar. DENSE_RANK() de eşitlere aynı sırayı verir ama atlama yapmaz. Belgedeki örnek bunu tek sorguda gösterir:

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

INSERT INTO student VALUES 
  ('Maths', 60, 'Thulile'),
  ('Maths', 60, 'Pritha'),
  ('Maths', 70, 'Voitto'),
  ('Maths', 55, 'Chun'),
  ('Biology', 60, 'Bilal'),
   ('Biology', 70, 'Roger');

SELECT 
  RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank, 
  DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank, 
  ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num, 
  course, mark, name 
FROM student ORDER BY course, mark DESC;

+------+------------+---------+---------+------+---------+
| rank | dense_rank | row_num | course  | mark | name    |
+------+------------+---------+---------+------+---------+
|    1 |          1 |       1 | Biology |   70 | Roger   |
|    2 |          2 |       2 | Biology |   60 | Bilal   |
|    1 |          1 |       1 | Maths   |   70 | Voitto  |
|    2 |          2 |       2 | Maths   |   60 | Thulile |
|    2 |          2 |       3 | Maths   |   60 | Pritha  |
|    4 |          3 |       4 | Maths   |   55 | Chun    |
+------+------------+---------+---------+------+---------+

Matematikte iki öğrenci 60 aldığı için ikisi de RANK ve DENSE_RANK açısından ikinci sıradadır. Sonraki öğrenci RANK'te 4., DENSE_RANK'te 3. olur. ROW_NUMBER ise eşitlerden birine 2, diğerine 3 verir; belge eşit değerlerde hangi satırın önce numaralanacağını tanımlamaz, bu yüzden ORDER BY eşitliği bozacak bir sütun içermiyorsa bu sıraya güvenmeyin. Sayfalama veya "tam olarak bir satır" seçimi için ORDER BY'a benzersiz bir sütun ekleyin.

Her gruptan ilk N kayıt

Window fonksiyonları WHERE içinde kullanılamaz, çünkü belgeye göre WHERE, GROUP BY ve HAVING işlendikten sonra değerlendirilirler. Çözüm sonucu bir CTE ya da alt sorguda hesaplayıp dış sorguda süzmektir. Belgedeki örnek her sınavın en yüksek iki puanını bulur:

WITH ranked AS (
  SELECT
    name,
    test,
    score,
    RANK() OVER (PARTITION BY test ORDER BY score DESC) AS rnk
  FROM student
)
SELECT
  name,
  test,
  score,
  rnk
FROM ranked
WHERE rnk <= 2
ORDER BY test, rnk, name;

+---------+--------+-------+-----+
| name    | test   | score | rnk |
+---------+--------+-------+-----+
| Tatiana | SQL    |    87 |   1 |
| Chun    | SQL    |    75 |   2 |
| Kaolin  | Tuning |    88 |   1 |
| Tatiana | Tuning |    83 |   2 |
+---------+--------+-------+-----+

RANK kullanıldığı için ikinci sırada eşitlik olsaydı ikiden fazla satır dönerdi; tam olarak iki satır istiyorsanız ROW_NUMBER seçin. CTE yazımı için MariaDB CTE ve WITH RECURSIVE yazısına bakın.

Çerçeveler: yürüyen toplam ve hareketli pencere

Çerçeve, geçerli satırın sonucuna hangi satırların katkıda bulunacağını belirler. Toplama fonksiyonları çerçeve kullanır; ROW_NUMBER() ve RANK() gibi sıralama fonksiyonları kullanmaz. Belgenin altını çizdiği iki varsayılan davranış vardır: OVER () bütün sonuç kümesini kullanır; toplama fonksiyonlarında OVER (ORDER BY ...) ise yürüyen bir çerçeve kullanır ve MariaDB'de bu varsayılan RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'dur. Açık çerçeveyle yazılmış yürüyen toplam:

SELECT
  name,
  score,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM student
ORDER BY score;

+---------+-------+---------------+
| name    | score | running_total |
+---------+-------+---------------+
| Esben   |    31 |            31 |
| Esben   |    43 |            74 |
| Kaolin  |    56 |           130 |
| Chun    |    73 |           203 |
| Chun    |    75 |           278 |
| Tatiana |    83 |           361 |
| Tatiana |    87 |           448 |
| Kaolin  |    88 |           536 |
+---------+-------+---------------+

Çıktıyı okurken her satırın değerinin, o satıra kadar olan tüm puanların toplamı olduğunu görürsünüz: 31, ardından 31 + 43 = 74, ardından 74 + 56 = 130 ve son satırda sekiz puanın toplamı olan 536. Aynı sonucu window fonksiyonu olmadan elde etmek için tabloyu kendisiyle birleştirip her satır için kendinden küçük puanları toplamanız gerekirdi; yazması da okuması da zor bir sorgu olurdu. Belge window fonksiyonlarının tipik kullanım alanlarını dört başlıkta toplar: grup içinde sıralama, yürüyen toplam veya hareketli ortalama, bir satırı önceki ya da sonraki satırlarla karşılaştırma ve her gruptan ilk N satırı döndürme. Bu yazıdaki örnekler bu dört kalıbın her birini kapsıyor.

ROWS ile RANGE farkı

ROWS fiziksel satırları sayar; RANGE ise ORDER BY değeri aynı olan "eş" satırları tek bir grup olarak ele alır. Fark yalnızca eşit sıralama değerlerinde görünür:

CREATE TABLE t (score INT);
INSERT INTO t VALUES (10), (10), (20);

SELECT
  score,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_sum,
  SUM(score) OVER (
    ORDER BY score
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS range_sum
FROM t;

+-------+----------+-----------+
| score | rows_sum | range_sum |
+-------+----------+-----------+
|    10 |       10 |        20 |
|    10 |       20 |        20 |
|    20 |       40 |        40 |
+-------+----------+-----------+

İlk satırda ROWS yalnızca o satırı (10) toplar, RANGE ise aynı değere sahip iki satırı birden (20). Varsayılan çerçeve RANGE olduğu için, çerçeve yazmadan SUM(...) OVER (ORDER BY ...) kullandığınızda eşit değerli satırlar aynı yürüyen toplamı gösterir. Satır satır ilerleyen bir toplam istiyorsanız ROWS'u açıkça yazın.

Sabit genişlikte pencereler için n PRECEDING ve n FOLLOWING kullanılır. "Window Frames" sayfası, yedi satırlık bir tabloda bir önceki, geçerli ve bir sonraki satırın toplamını şöyle hesaplar:

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS total_score 
  FROM student_test ORDER BY score;

+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          74 |
| Esben   | SQL    |    43 |         130 |
| Kaolin  | SQL    |    56 |         172 |
| Chun    | Tuning |    73 |         204 |
| Chun    | SQL    |    75 |         235 |
| Tatiana | SQL    |    87 |         250 |
| Kaolin  | Tuning |    88 |         175 |
+---------+--------+-------+-------------+

Bu sayfadaki student_test tablosu, genel bakıştaki tablodan farklı olarak Tatiana'nın Tuning puanını içermez; bu yüzden yedi satır vardır. İlk satırda önceki satır olmadığı için yalnızca iki değer (31 + 43), son satırda sonraki satır olmadığı için yine iki değer (87 + 88) toplanır. Aynı kalıbı AVG ile kullanırsanız hareketli ortalama elde edersiniz.

Önceki ve sonraki satır: LAG ve LEAD

LAG(expr[, offset]), kendi kendine birleştirme (self join) gerekmeden ORDER BY sırasına göre önceki bir satırdaki değere erişir. offset varsayılan olarak 1'dir ve geçerli satırın kaç satır gerisine bakılacağını belirtir; 0 geçerli satırın kendisidir. LEAD aynı işi sonraki satırlar için yapar. Belgedeki örnek, pk değerleri 1'den 11'e kadar giden on bir satırlık bir t1 tablosu kullanır ve negatif offset'in de çalıştığı görülür: LAG(pk,-1) bir sonraki satırı döndürür. Öncesinde ya da sonrasında satır yoksa sonuç NULL'dır.

SELECT pk, LAG(pk) OVER (ORDER BY pk) AS l,
  LAG(pk,1) OVER (ORDER BY pk) AS l1,
  LAG(pk,2) OVER (ORDER BY pk) AS l2,
  LAG(pk,0) OVER (ORDER BY pk) AS l0,
  LAG(pk,-1) OVER (ORDER BY pk) AS lm1,
  LAG(pk,-2) OVER (ORDER BY pk) AS lm2 
FROM t1;

+----+------+------+------+------+------+------+
| pk | l    | l1   | l2   | l0   | lm1  | lm2  |
+----+------+------+------+------+------+------+
|  1 | NULL | NULL | NULL |    1 |    2 |    3 |
|  2 |    1 |    1 | NULL |    2 |    3 |    4 |
|  3 |    2 |    2 |    1 |    3 |    4 |    5 |
|  4 |    3 |    3 |    2 |    4 |    5 |    6 |
|  5 |    4 |    4 |    3 |    5 |    6 |    7 |
|  6 |    5 |    5 |    4 |    6 |    7 |    8 |
|  7 |    6 |    6 |    5 |    7 |    8 |    9 |
|  8 |    7 |    7 |    6 |    8 |    9 |   10 |
|  9 |    8 |    8 |    7 |    9 |   10 |   11 |
| 10 |    9 |    9 |    8 |   10 |   11 | NULL |
| 11 |   10 |   10 |    9 |   11 | NULL | NULL |
+----+------+------+------+------+------+------+

Gerçek bir kullanım olarak aylık satış tablosunda her ayı bir önceki ayla karşılaştırabilirsiniz. Aşağıdaki sorgu ürün bazında bölümleyip aylara göre sıralar ve farkı hesaplar; ilk ay için önceki değer olmadığından fark NULL olur:

SELECT urun, ay, tutar,
  tutar - LAG(tutar) OVER (PARTITION BY urun ORDER BY ay) AS onceki_aya_gore
FROM aylik_satis;

FIRST_VALUE, LAST_VALUE ve varsayılan çerçeve tuzağı

FIRST_VALUE sıralı kümedeki ilk değeri, LAST_VALUE son değeri döndürür. Ancak bu fonksiyonlar çerçeveye bağlıdır. Belgedeki örnekte LAST_VALUE(pk) OVER (ORDER BY pk) her satırda tablonun son değerini değil, satırın kendi pk değerini döndürür: 11. satırda 11, 10. satırda 10 ve böyle devam eder. Nedeni varsayılan çerçevedir; çerçeve geçerli satırda bittiği için "son" satır her zaman geçerli satırdır. Bölümün gerçek son değerini istiyorsanız çerçeveyi açıkça genişletin:

SELECT pk,
  LAST_VALUE(pk) OVER (
    ORDER BY pk
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS gercek_son
FROM t1;

Aynı sayfadaki ikinci örnek ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING gibi geçerli satırı içermeyen çerçevelerin de kullanılabildiğini ve çerçeve boş kaldığında sonucun NULL olduğunu gösterir. NTH_VALUE(expr, n) ise çerçevenin n. satırındaki değeri, öyle bir satır yoksa NULL döndürür.

NTILE, MEDIAN ve diğer fonksiyonlar

NTILE(n) sıralı satırları olabildiğince eşit büyüklükte n gruba böler ve her satırın kaçıncı grupta olduğunu döndürür. Belgedeki on satırlık örnekte NTILE(4) satırları 3, 3, 2 ve 2 satırlık dört gruba ayırır; çıktıda görüldüğü gibi fazla satırlar ilk gruplara düşer. Müşterileri harcamaya göre çeyrek dilimlere ayırmak gibi işlerde kullanışlıdır. MEDIAN() bir değer aralığının ortanca değerini döndürür ve PERCENTILE_CONT(0.5) özel durumuna eşdeğerdir; CUME_DIST, PERCENT_RANK ve PERCENTILE_DISC de desteklenen özel window fonksiyonları arasındadır.

Toplama fonksiyonlarından AVG, COUNT, MAX, MIN, SUM, STD ve varyans fonksiyonları ile BIT_AND, BIT_OR, BIT_XOR da OVER ile pencere fonksiyonu olarak kullanılabilir. Bir toplama fonksiyonunun pencere olarak çalışması için OVER yan tümcesi şarttır. Belgeye göre toplama window fonksiyonları DISTINCT desteklemez.

MariaDB'nin desteklemedikleri

Başka bir veritabanından sorgu taşıyorsanız şu sınırları bilmeniz gerekir:

• GROUPS çerçeveleri desteklenmez; yalnızca ROWS ve RANGE vardır.
• Çerçeve dışlama (frame exclusion) desteklenmez.
• Açık NULLS FIRST veya NULLS LAST desteklenmez.
• RANGE çerçevelerinde DATE veya DATETIME aritmetiği desteklenmez.
• Toplama window fonksiyonlarında DISTINCT kullanılamaz.

Buna karşılık RANGE n PRECEDING ve RANGE n FOLLOWING dahil tüm çerçeve sınırları desteklenir. Tarih ve saat fonksiyonları için MariaDB tarih ve saat fonksiyonları yazısına bakın.

Performans: sıralama geçişleri

Belgeye göre window fonksiyonlarının değerlendirilmesi, uyumlu pencerelerin her grubu için bir sıralama geçişi gerektirir ve her geçiş yürütme planında bir filesort düğümü olarak görünür. Sorgu hem GROUP BY hem window fonksiyonu içeriyorsa önce gruplama yapılır, pencere adımı gruplanmış sonuç üzerinde çalışır. GROUP BY month, dept ile gruplayıp pencereyi PARTITION BY dept ORDER BY month ile tanımlamak gibi sıra uyuşmazlıkları ek bir sıralama gerektirir. Birden fazla window fonksiyonu, PARTITION BY ve ORDER BY tanımları aynıysa ya da biri diğerinin öneki ise tek bir sıralama geçişini paylaşabilir. Pratik öneri: gruplama ve pencere anahtarlarını hizalayın, aynı tanımı tekrar kullanın ve geçiş sayısını EXPLAIN FORMAT=JSON ya da gerçek süreler için ANALYZE FORMAT=JSON ile sayın. Ayrıntılı plan okuma için MariaDB EXPLAIN ve ANALYZE yazısına bakın.

Sık yapılan hatalar

• Window fonksiyonunu WHERE içinde kullanmak: izin verilmez; sonucu CTE veya alt sorguda hesaplayıp dışarıda süzün.
• Varsayılan çerçeveyi unutmak: ORDER BY içeren pencerede varsayılan RANGE ... CURRENT ROW olduğu için LAST_VALUE beklenmedik sonuç verir ve eşit değerler aynı yürüyen toplamı alır.
• ROW_NUMBER sırasını garanti sanmak: eşit sıralama değerlerinde numaralar belirsizdir; benzersiz bir sütunla eşitliği bozun.
• RANK ile tam N satır beklemek: eşitlik varsa daha fazla satır döner; tam N için ROW_NUMBER kullanın.
• DISTINCT ile pencere toplaması yazmak: MariaDB desteklemez.
• Her fonksiyona farklı bir pencere vermek: gereksiz sıralama geçişleri doğar; mümkünse ortak bir WINDOW tanımı kullanın.

Sık Sorulan Sorular

PARTITION BY yazmazsam ne olur?

Tüm sonuç kümesi tek bir bölüm sayılır. OVER () tamamen boş bırakılırsa toplama fonksiyonu her satırda bütün kümenin değerini döndürür; "Window Frames" sayfasındaki örnekte her satırda toplam puan 453 olarak görünür.

Window fonksiyonları GROUP BY ile birlikte kullanılabilir mi?

Evet. Gruplama önce yapılır, window fonksiyonları gruplanmış satırlar üzerinde çalışır. Bu yüzden pencere içinde SUM(sales) gibi toplama sonuçlarını sıralayabilirsiniz.

Hareketli ortalamayı nasıl hesaplarım?

AVG(deger) OVER (ORDER BY tarih ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) biçiminde, geçerli satırla birlikte önceki altı satırın ortalamasını alan bir ROWS çerçevesi kullanın. Satır sayısını verinizin sıklığına göre seçin.

ColumnStore'daki window fonksiyonları aynı mı?

Belgelerde ColumnStore için ayrı bir sayfa bulunur. Bu yazı InnoDB gibi standart motorlarla kullanılan MariaDB Server window fonksiyonlarını anlatır; ColumnStore kullanıyorsanız o sayfaya bakın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · GROUP BY ve toplama fonksiyonları · CTE ve WITH RECURSIVE · SELECT, WHERE, ORDER BY · EXPLAIN ve ANALYZE