Menu

PostgreSQL Pencere Fonksiyonları: OVER, PARTITION BY

Pencere fonksiyonları (window functions), ilişkili satırlardan oluşan bir gruptan her satır için bir değer hesaplar ve satırları birleştirmez: sıralamalar, kümülatif toplamlar, hareketli ortalamalar ve önceki satırla karşılaştırmalar.

Bu sayfada çalıştırılabilir editörler var. Düzenle, çalıştır ve sonucu anında gör.

Bir pencere fonksiyonu, ilişkili satırlardan oluşan bir kümeden, yani penceresinden, her satır için bir değer hesaplar ve satırları GROUP BY gibi birleştirmez. Onu function() OVER (PARTITION BY ... ORDER BY ...) şeklinde yaz. PARTITION BY satırları gruplara böler, ORDER BY ise her grubun içindeki sırayı belirler.

Her satır sonuçta kalır ve bölgesinin toplamını taşır: north için 1000, south için 500. Ardından her temsilcinin payı tek bir ifadedir (ana, north'un yüzde 50'sine sahip). Herhangi bir aggregate (sum, count, avg, min, max), OVER eklediğinde pencere fonksiyonuna dönüşür. Boş bir OVER () tüm sonucu tek bir pencere yapar.

ROW_NUMBER, RANK ve DENSE_RANK ile satırları numaralandırma

Üç sıralama fonksiyonu yalnızca satırlar ORDER BY değerinde eşit olduğunda farklılaşır:

ben ve cy ikisi de 85 aldı. row_number yine de onlara 2 ve 3 verir; ORDER BY eşitliği bozmadıkça hangisinin 2 alacağı belirsizdir. rank ikisine de 2 verir ve dee için 4'e atlar. dense_rank ikisine de 2 verir ve 3 ile devam eder.

FonksiyonEşitliklerEşitlikten sonra boşluk
row_number()farklı numaralaryok
rank()aynı numaravar
dense_rank()aynı numarayok

Grup başına ilk N satırı alma

Pencere fonksiyonları WHERE'den sonra hesaplanır, bu yüzden onlarla doğrudan filtreleyemezsin. Satırları bir alt sorguda ya da CTE'de numaralandır, sonra dışarıda filtrele:

Bu sorgu her bölgenin en iyi iki temsilcisini döndürür. İkincilik için eşit olan her satırı tutmak için row_number() yerine rank() kullan. Grup başına sadece ilk satır için DISTINCT ON daha kısadır.

Kümülatif toplam ya da hareketli ortalama hesaplama

OVER içinde ORDER BY varsa bir aggregate bir frame üzerinde çalışır. Varsayılan olarak frame bölümün ilk satırından geçerli satıra kadar gider, bu da kümülatif bir toplam verir. Bir ROWS BETWEEN cümlesi, geçerli satır ve ondan önceki iki satır gibi farklı bir frame belirler:

Kümülatif toplam 5. günde 130'a ulaşır ve 5. gündeki üç günlük ortalama (30 + 20 + 50) / 3 = 33.3'tür.

Varsayılan frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'dur. ORDER BY değerinde geçerli satırla eşit olan her satırı içerir, bu yüzden aynı güne sahip iki satır aynı kümülatif toplamı alır. Onları teker teker eklemek için ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW yaz.

Önceki satırla karşılaştırma: LAG ve LEAD

lag(col) pencere sırasında geçerli satırdan önceki satırdan, lead(col) ise sonraki satırdan bir değer okur. Üçüncü argüman olarak bir varsayılan vermedikçe ikisi de kenarın ötesinde NULL döndürür.

  1. günün önceki satırı yok, bu yüzden previous ve change NULL'dır. WINDOW cümlesi bir pencereyi bir kez, adıyla tanımlar ve birden fazla fonksiyon onu paylaşır.

first_value varsayılan frame ile çalışır, ama last_value çalışmaz: varsayılan frame geçerli satırda biter, bu yüzden geçerli satırın değerini döndürür. Frame'i tüm bölüm yapmak için ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ekle. Sadece pencerede kullanılan diğer fonksiyonlar ntile(n), percent_rank(), cume_dist() ve nth_value()'dur; PostgreSQL pencere fonksiyonları başvurusu hepsini listeler.

Bir aggregate'in hangi satırları sayacağını filtreleme

FILTER (WHERE ...) pencere aggregate'lerinde de çalışır ve her satırda koşullu toplamlar verir:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

Sıkça Sorulan Sorular

GROUP BY ile PARTITION BY arasındaki fark nedir?

GROUP BY her grup için bir satır döndürür. OVER içindeki PARTITION BY ise her satırı tutar ve her birine kendi grubu üzerinden hesaplanan bir değer verir, böylece bir satırın tutarını grubunun toplamının yanında gösterebilirsin.

Pencere fonksiyonu WHERE içinde kullanılabilir mi?

Hayır. Pencere fonksiyonları WHERE, GROUP BY ve HAVING'den sonra çalışır, bu yüzden sadece seçim listesinde ve ORDER BY içinde kullanılabilir. Değeri bir alt sorguda ya da CTE'de hesapla ve dıştaki sorguda filtrele.

LAST_VALUE neden geçerli satırın değerini döndürüyor?

OVER içinde ORDER BY varsa varsayılan frame geçerli satırda (ve onunla eşit olanlarda) biter, bu yüzden frame'in son satırı geçerli satırdır. Frame'i tüm bölüm yapmak için ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ekle.

Frame içinde ROWS ile RANGE arasındaki fark nedir?

ROWS fiziksel satırları sayar: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW geçerli satır ve ondan önceki iki satırdır. RANGE ise ORDER BY değeri üzerinde çalışır, bu yüzden değeri eşit olan satırlar (peer'lar) her zaman frame'de birlikte yer alır.

Coddy programlama dilleri çizimi

Coddy ile kodlamayı öğren

Başla