PostgreSQL Window Function Rehberi: OVER, PARTITION BY, row_number, rank

Örnek tablo

Bölüm ve maaş bilgisi olan küçük bir personel tablosu kullanıyoruz. Bilerek eşit maaşlar koyduk; sıralama fonksiyonlarının ve çerçeve modlarının farkı eşitliklerde ortaya çıkar.

CREATE TABLE personel (id integer PRIMARY KEY, ad text, bolum text, maas integer);
INSERT INTO personel VALUES
    (1, 'Ayşe',   'Yazılım', 52000), (2, 'Mehmet', 'Yazılım', 60000),
    (3, 'Elif',   'Yazılım', 52000), (4, 'Can',    'Yazılım', 45000),
    (5, 'Zeynep', 'Satış',   39000), (6, 'Burak',  'Satış',   35000),
    (7, 'Deniz',  'Destek',  48000), (8, 'Selin',  'Destek',  48000);

OVER ve PARTITION BY: satırı kaybetmeden grup hesabı

Kılavuzun eğitim bölümündeki ilk örnek, her çalışanın maaşını kendi bölümünün ortalamasıyla karşılaştırmaktır. avg(maas) normalde bir toplama fonksiyonudur; OVER (PARTITION BY bolum) eklendiğinde ise her satır için o satırın bölümündeki bütün satırların ortalamasını hesaplar ve satırları birleştirmez:

SELECT bolum, ad, maas,
       round(avg(maas) OVER (PARTITION BY bolum)) AS bolum_ort,
       maas - round(avg(maas) OVER (PARTITION BY bolum)) AS fark
FROM personel
ORDER BY bolum, maas DESC;

  bolum  |   ad   | maas  | bolum_ort | fark
---------+--------+-------+-----------+-------
 Destek  | Selin  | 48000 |     48000 |     0
 Destek  | Deniz  | 48000 |     48000 |     0
 Satış   | Zeynep | 39000 |     37000 |  2000
 Satış   | Burak  | 35000 |     37000 | -2000
 Yazılım | Mehmet | 60000 |     52250 |  7750
 Yazılım | Ayşe   | 52000 |     52250 |  -250
 Yazılım | Elif   | 52000 |     52250 |  -250
 Yazılım | Can    | 45000 |     52250 | -7250
(8 rows)

Sekiz satırın hepsi sonuçta duruyor; aynı sorguyu GROUP BY bolum ile yazsaydınız üç satır alır ve kişi adlarını kaybederdiniz. PARTITION BY verilmezse bütün satırlar tek bir bölüm sayılır. Kılavuza göre pencere fonksiyonlarının gördüğü satırlar, FROM ile üretilip WHERE, GROUP BY ve HAVING ile süzülmüş sanal tablonun satırlarıdır; WHERE ile elenen bir satırı hiçbir pencere fonksiyonu görmez.

Destek bölümündeki iki eşit maaşlı satırın sırası sorgunun ORDER BY yan tümcesi tarafından belirlenmiyor; bu çalıştırmada Selin önce geldi, başka bir çalıştırmada Deniz önce gelebilir. Kesin sıra istiyorsanız dış ORDER BY listesine benzersiz bir sütun ekleyin.

row_number, rank ve dense_rank farkı

Üç sıralama fonksiyonu da pencerenin ORDER BY sırasına bağlıdır, ama eşitlikleri farklı ele alır. ORDER BY açısından ayırt edilemeyen satırlara kılavuz "eş" (peer) der. row_number eşlere de farklı ardışık numaralar verir ve eşler arasındaki sıra belirsizdir; rank eşlere aynı sırayı verip sonraki sırada boşluk bırakır; dense_rank ise boşluk bırakmadan eş gruplarını sayar. Örnekte aynı pencere tanımını üç kez yazmamak için WINDOW yan tümcesiyle adlandırdık:

SELECT ad, maas,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM personel
WHERE bolum = 'Yazılım'
WINDOW w AS (ORDER BY maas DESC)
ORDER BY maas DESC, ad;

   ad   | maas  | row_number | rank | dense_rank
--------+-------+------------+------+------------
 Mehmet | 60000 |          1 |    1 |          1
 Ayşe   | 52000 |          2 |    2 |          2
 Elif   | 52000 |          3 |    2 |          2
 Can    | 45000 |          4 |    4 |          3
(4 rows)

Grup başına ilk N satır

Pencere fonksiyonları yalnızca seçim listesinde ve sorgunun ORDER BY yan tümcesinde kullanılabilir; WHERE, GROUP BY ve HAVING içinde yasaktır. Kılavuzun açıklamasına göre bunun nedeni, pencere fonksiyonlarının mantıksal olarak bu yan tümcelerden sonra çalışmasıdır. "Her bölümün en yüksek maaşlı ilk üç kişisi" sorgusunu doğrudan WHERE ile yazmaya çalışırsanız:

SELECT ad, maas FROM personel
WHERE row_number() OVER (ORDER BY maas DESC) <= 3;

ERROR:  window functions are not allowed in WHERE
LINE 2: WHERE row_number() OVER (ORDER BY maas DESC) <= 3;
              ^

Kılavuzun önerdiği çözüm, pencere hesabını bir alt sorguda yapıp dış sorguda süzmektir. Aşağıda her bölümden en yüksek maaşlı iki kişiyi alıyoruz; eşitlikte sonucun belirli olması için pencere sırasına id ekledik:

SELECT bolum, ad, maas
FROM (
    SELECT bolum, ad, maas,
           row_number() OVER (PARTITION BY bolum ORDER BY maas DESC, id) AS sira
    FROM personel
) AS s
WHERE sira <= 2
ORDER BY bolum, sira;

  bolum  |   ad   | maas
---------+--------+-------
 Destek  | Deniz  | 48000
 Destek  | Selin  | 48000
 Satış   | Zeynep | 39000
 Satış   | Burak  | 35000
 Yazılım | Mehmet | 60000
 Yazılım | Ayşe   | 52000
(6 rows)

Yazılım bölümünde Ayşe ile Elif aynı maaşı alıyor; id sıralaması sayesinde ikinci sıraya her seferinde Ayşe yerleşiyor. Eşitleri de dahil etmek isteseydiniz row_number yerine rank kullanırdınız ve Yazılım'dan üç kişi gelirdi. Grup başına tek satır için DISTINCT ON, grup başına birden fazla satırı her satır için ayrı hesaplamak için LATERAL birleştirme de kullanılabilir; aynı alt sorgu bir CTE içinde de yazılabilir.

Pencere çerçevesi: yürüyen toplam ve RANGE ile ROWS farkı

Bazı pencere fonksiyonları bütün bölüm üzerinde değil, yalnızca o satırın pencere çerçevesi üzerinde çalışır. Toplama fonksiyonları ile first_value, last_value ve nth_value bu gruptadır. Kılavuzun tanımladığı varsayılan çerçeve RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW biçimindedir. Pencerede ORDER BY varsa bu, bölümün başından geçerli satırın son eşine kadar olan satırlar demektir; ORDER BY yoksa bütün satırlar birbirinin eşi olduğu için çerçeve bölümün tamamıdır.

Sonuç olarak sum(maas) OVER (ORDER BY maas) bir yürüyen toplam üretir, ama eşit değerli satırlar aynı toplamı alır, çünkü eşler çerçeveye birlikte girer. ROWS modunda ise CURRENT ROW gerçekten yalnızca geçerli satırı ifade eder:

SELECT ad, maas,
       sum(maas) OVER (ORDER BY maas) AS range_toplam,
       sum(maas) OVER (ORDER BY maas ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_toplam
FROM personel
WHERE bolum = 'Yazılım'
ORDER BY maas, id;

   ad   | maas  | range_toplam | rows_toplam
--------+-------+--------------+-------------
 Can    | 45000 |        45000 |       45000
 Ayşe   | 52000 |       149000 |       97000
 Elif   | 52000 |       149000 |      149000
 Mehmet | 60000 |       209000 |      209000
(4 rows)

RANGE sütununda Ayşe ve Elif ikisi de 149000 alıyor. ROWS sütununda ise Ayşe 97000, Elif 149000 aldı; ancak kılavuz, ORDER BY satırları benzersiz sıralamıyorsa ROWS modunun öngörülemez sonuç verebileceği konusunda uyarır. Ayşe ile Elif'in hangisinin önce sayılacağı garanti değildir. Kararlı bir yürüyen toplam için pencerenin ORDER BY listesine benzersiz bir sütun ekleyin: OVER (ORDER BY maas, id). RANGE ve GROUPS modları ise eşleri her zaman birlikte ele almak için tasarlanmıştır.

Üçüncü mod olan GROUPS, kaydırma değerini satır yerine eş grubu olarak sayar. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW, geçerli satırın eş grubunu ve ondan önceki bir eş grubunu kapsar; bu mod pencerede ORDER BY gerektirir:

SELECT ad, maas, sum(maas) OVER (ORDER BY maas GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS g
FROM personel WHERE bolum = 'Yazılım' ORDER BY maas, id;

   ad   | maas  |   g
--------+-------+--------
 Can    | 45000 |  45000
 Ayşe   | 52000 | 149000
 Elif   | 52000 | 149000
 Mehmet | 60000 | 164000
(4 rows)

Mehmet'in satırı kendi grubunu (60000) ve önceki eş grubunu (iki kez 52000) topluyor; Can'ın grubu artık çerçeve dışında. RANGE modunda offset PRECEDING kullanmak ise pencerede tam olarak bir sıralama sütunu ister ve kaydırma, sütun değerleri arasındaki en büyük farkı belirtir; tarih sütunlarında bu fark interval olur, örneğin RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING. Çerçevenin başı UNBOUNDED FOLLOWING, sonu UNBOUNDED PRECEDING olamaz ve son, listede baştan daha önce gelen bir seçenek olamaz. Ayrıca EXCLUDE CURRENT ROW, EXCLUDE GROUP ve EXCLUDE TIES seçenekleri, çerçeveden geçerli satırı veya eşlerini çıkarmanızı sağlar.

lag ve lead ile önceki ve sonraki satır, hareketli ortalama

lag(deger, offset, varsayilan), bölüm içinde geçerli satırdan offset kadar önceki satırdaki değeri, lead ise sonraki satırdaki değeri döndürür. Offset verilmezse 1, varsayılan verilmezse NULL kullanılır. Aylık ciro tablosunda bir önceki aya göre değişimi, bir sonraki ayı (yoksa 0) ve son üç ayın hareketli ortalamasını aynı sorguda hesaplayalım:

CREATE TABLE aylik (ay integer PRIMARY KEY, ciro integer);
INSERT INTO aylik VALUES (1, 120), (2, 150), (3, 90), (4, 160), (5, 200), (6, 180);

SELECT ay, ciro,
       lag(ciro) OVER (ORDER BY ay) AS onceki,
       ciro - lag(ciro) OVER (ORDER BY ay) AS degisim,
       lead(ciro, 1, 0) OVER (ORDER BY ay) AS sonraki,
       round(avg(ciro) OVER (ORDER BY ay ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS ort3
FROM aylik
ORDER BY ay;

 ay | ciro | onceki | degisim | sonraki | ort3
----+------+--------+---------+---------+-------
  1 |  120 |        |         |     150 | 120.0
  2 |  150 |    120 |      30 |      90 | 135.0
  3 |   90 |    150 |     -60 |     160 | 120.0
  4 |  160 |     90 |      70 |     200 | 133.3
  5 |  200 |    160 |      40 |     180 | 150.0
  6 |  180 |    200 |     -20 |       0 | 180.0
(6 rows)

İlk ayın önceki değeri yok, bu yüzden onceki ve degisim boş. Son ayda lead için verdiğimiz varsayılan 0 devreye girdi. Hareketli ortalamada ilk iki satırın çerçevesi doğal olarak daha kısa: kılavuzun belirttiği gibi çerçevenin bölüm sonuna olan uzaklığı bölümün sınırlarıyla sınırlıdır, bu yüzden 1. ayın ortalaması tek değer, 2. ayınki iki değer üzerinden hesaplandı. Kılavuz ayrıca SQL standardındaki IGNORE NULLS seçeneğinin PostgreSQL'de uygulanmadığını, davranışın her zaman RESPECT NULLS olduğunu not eder.

first_value ve last_value tuzağı

Kılavuzun özellikle uyardığı bir durum: first_value, last_value ve nth_value yalnızca pencere çerçevesindeki satırlara bakar ve varsayılan çerçeve geçerli satırın son eşinde biter. Bu yüzden last_value çoğu zaman "bölümün son değeri" yerine "geçerli satırın kendisi" sonucunu verir:

SELECT ay, ciro,
       first_value(ciro) OVER (ORDER BY ay) AS ilk,
       last_value(ciro) OVER (ORDER BY ay) AS son_varsayilan,
       last_value(ciro) OVER (ORDER BY ay ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS son_dogru
FROM aylik
ORDER BY ay;

 ay | ciro | ilk | son_varsayilan | son_dogru
----+------+-----+----------------+-----------
  1 |  120 | 120 |            120 |       180
  2 |  150 | 120 |            150 |       180
  3 |   90 | 120 |             90 |       180
  4 |  160 | 120 |            160 |       180
  5 |  200 | 120 |            200 |       180
  6 |  180 | 120 |            180 |       180
(6 rows)

first_value doğru çalışıyor, çünkü çerçeve her zaman bölümün başından başlıyor. last_value için çerçeveyi açıkça bölümün sonuna kadar genişletmek gerekiyor. Aynı kural toplama fonksiyonları için de geçerlidir: kılavuz, ORDER BY ile varsayılan çerçevenin "yürüyen toplam" davranışı ürettiğini, bütün bölüm üzerinde toplam istiyorsanız ORDER BY yazmamanızı ya da ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING kullanmanızı önerir. nth_value için de standarttaki FROM LAST desteklenmez; aynı sonucu ORDER BY yönünü tersine çevirerek alabilirsiniz.

ntile, percent_rank ve cume_dist

ntile(n) bölümü olabildiğince eşit n dilime böler; percent_rank göreli sırayı 0 ile 1 arasında, cume_dist ise geçerli satırdan önceki ve eş satırların toplama oranını verir. Bu iki fonksiyon double precision döndürdüğü için yuvarlamadan önce numeric tipine çevirdik:

SELECT ad, maas,
       ntile(3) OVER (ORDER BY maas DESC) AS dilim,
       round(percent_rank() OVER (ORDER BY maas DESC)::numeric, 2) AS yuzde_sira,
       round(cume_dist() OVER (ORDER BY maas DESC)::numeric, 2) AS kumulatif
FROM personel
ORDER BY maas DESC, id;

   ad   | maas  | dilim | yuzde_sira | kumulatif
--------+-------+-------+------------+-----------
 Mehmet | 60000 |     1 |       0.00 |      0.13
 Ayşe   | 52000 |     1 |       0.14 |      0.38
 Elif   | 52000 |     1 |       0.14 |      0.38
 Deniz  | 48000 |     2 |       0.43 |      0.63
 Selin  | 48000 |     2 |       0.43 |      0.63
 Can    | 45000 |     2 |       0.71 |      0.75
 Zeynep | 39000 |     3 |       0.86 |      0.88
 Burak  | 35000 |     3 |       1.00 |      1.00
(8 rows)

Kılavuza göre dört sıralama fonksiyonu (cume_dist dahil) bir eş grubundaki bütün satırlar için aynı sonucu verir; Ayşe ile Elif'in percent_rank ve cume_dist değerleri bu yüzden aynı. ntile ise eşleri farklı dilimlere de koyabilir; sekiz satır üç dilime bölündüğü için ilk iki dilim üçer, son dilim iki satır aldı.

Boş OVER, FILTER ve toplamayla birlikte kullanım

OVER () boş bırakıldığında bölüm bütün sonuç kümesidir; bu, her satırın genel toplamdaki payını hesaplamanın en kısa yoludur. Toplama fonksiyonları pencere olarak kullanıldığında FILTER da alabilir; kılavuza göre FILTER yalnızca toplama türündeki pencere fonksiyonlarında kabul edilir:

SELECT bolum, ad, maas,
       sum(maas) OVER () AS genel_toplam,
       round(100.0 * maas / sum(maas) OVER (), 1) AS pay_yuzde,
       count(*) FILTER (WHERE maas >= 48000) OVER (PARTITION BY bolum) AS bolumde_yuksek
FROM personel
ORDER BY bolum, maas DESC, id;

  bolum  |   ad   | maas  | genel_toplam | pay_yuzde | bolumde_yuksek
---------+--------+-------+--------------+-----------+----------------
 Destek  | Deniz  | 48000 |       379000 |      12.7 |              2
 Destek  | Selin  | 48000 |       379000 |      12.7 |              2
 Satış   | Zeynep | 39000 |       379000 |      10.3 |              0
 Satış   | Burak  | 35000 |       379000 |       9.2 |              0
 Yazılım | Mehmet | 60000 |       379000 |      15.8 |              3
 Yazılım | Ayşe   | 52000 |       379000 |      13.7 |              3
 Yazılım | Elif   | 52000 |       379000 |      13.7 |              3
 Yazılım | Can    | 45000 |       379000 |      11.9 |              3
(8 rows)

Pencere fonksiyonları normal toplamalardan sonra çalıştığı için bir toplama fonksiyonunu pencere fonksiyonunun argümanı yapabilirsiniz, tersini yapamazsınız. Bu, gruplanmış sonuçları sıralamak için kullanışlıdır: önce bölüm toplamlarını GROUP BY ile hesaplayıp ardından bu toplamlara göre rank verebilirsiniz.

SELECT bolum, sum(maas) AS toplam,
       rank() OVER (ORDER BY sum(maas) DESC) AS sira
FROM personel
GROUP BY bolum
ORDER BY sira;

  bolum  | toplam | sira
---------+--------+------
 Yazılım | 209000 |    1
 Destek  |  96000 |    2
 Satış   |  74000 |    3
(3 rows)

WINDOW yan tümcesi ve sıralama garantileri

Aynı pencere tanımını birden fazla fonksiyonda kullanacaksanız WINDOW w AS (...) ile bir kez tanımlayıp OVER w ile başvurabilirsiniz. Yeni bir pencere, daha önce tanımlanmış birinden türetilebilir: WINDOW w2 AS (w ORDER BY maas). Bu durumda yeni pencere bölümlemeyi kopyalar, kendi PARTITION BY yan tümcesini yazamaz, kopyalanan pencerenin sıralaması yoksa kendi ORDER BY yan tümcesini ekleyebilir ve kopyalanan pencerede çerçeve tanımı bulunmamalıdır. Kılavuz OVER w ile OVER (w ...) yazımlarının tam olarak aynı olmadığını da not eder: ikincisi tanımı kopyalayıp değiştirmek anlamına gelir ve başvurulan pencerede çerçeve varsa reddedilir.

Kılavuzun sıralama konusunda verdiği garantiler de bilinmeye değer. Aynı PARTITION BY ve ORDER BY tanımına sahip bütün pencere fonksiyonları, ORDER BY satırları benzersiz sıralamasa bile girdi satırlarını aynı sırayla görür. Farklı tanımlı pencereler için böyle bir garanti yoktur. Mevcut uygulamada sorgu çıktısı genellikle pencerelerden birinin sırasında gelse de buna güvenilmemesi, belirli bir sonuç sırası için her zaman dış ORDER BY yazılması önerilir. Pencere fonksiyonlarının EXPLAIN çıktısında WindowAgg düğümü olarak nasıl göründüğü EXPLAIN yazısında anlatılıyor.

Sık yapılan hatalar

• WHERE içinde pencere fonksiyonu: window functions are not allowed in WHERE hatası. Hesabı alt sorguya veya CTE'ye taşıyın.
• last_value ile bölümün son değerini beklemek: Varsayılan çerçeve geçerli satırda biter; çerçeveyi UNBOUNDED FOLLOWING ile genişletin.
• ORDER BY ile toplamın yürüyen toplam olacağını unutmak: sum(x) OVER (PARTITION BY g ORDER BY t) bölüm toplamı değil, birikimli toplamdır.
• Eşitliklerde ROWS kullanmak: Benzersiz olmayan sıralamada sonuç çalıştırmadan çalıştırmaya değişebilir; sıralamaya benzersiz bir sütun ekleyin.
• Dış ORDER BY yazmamak: Pencerenin sırası sonucun sırasını garanti etmez.

Sık Sorulan Sorular

Window function ile GROUP BY arasındaki fark nedir?

GROUP BY her grup için tek bir satır döndürür ve ayrıntı satırlarını kaybedersiniz. Pencere fonksiyonu ise her satırı korur ve grup hesabını o satırın yanına bir sütun olarak ekler. İkisi aynı sorguda birlikte de kullanılabilir; bu durumda pencere fonksiyonları grup satırları üzerinde çalışır.

Her gruptan en yüksek değerli satırı nasıl alırım?

Alt sorguda row_number() OVER (PARTITION BY grup ORDER BY deger DESC, id) hesaplayıp dış sorguda WHERE sira = 1 ile süzün. Eşitleri de istiyorsanız rank() kullanın.

Yürüyen toplamda eşit değerler neden aynı sonucu alıyor?

Varsayılan çerçeve RANGE modundadır ve geçerli satırın bütün eşlerini kapsar. Her satırın ayrı birikmesini istiyorsanız ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW kullanın ve sıralamayı benzersiz yapın.

Sıralı küme fonksiyonları pencere fonksiyonu olarak kullanılabilir mi?

Hayır. Kılavuza göre her yerleşik veya kullanıcı tanımlı genel amaçlı ve istatistiksel toplama fonksiyonu OVER ile kullanılabilir, ama percentile_cont gibi sıralı küme ve hipotetik küme toplamaları şu an pencere fonksiyonu olarak kullanılamaz.

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: PostgreSQL öğrenme rehberi · GROUP BY ve aggregate fonksiyonlar · SELECT, WHERE, ORDER BY, LIMIT · WITH ve recursive CTE · JOIN türleri ve LATERAL · EXPLAIN ANALYZE ile plan okuma