GROUP BY ile pencere fonksiyonunun farkı

Bir raporda bazen kategori başına tek toplam isterim; bazen her satırın yanında kategori toplamı da görünmeli. İkisinde de SUM yazılır ama sonuçların satır sayısı farklıdır. SQL notlarımdaki GROUP BY ve window fonksiyonlarını bu ayrım üzerinden okumak, özellikle rapor toplamlarının yanlışlıkla iki kez hesaplanmasını önler.

Önce çıktının tane düzeyini seçmek

A kategorisinde 10 ve 20, B kategorisinde 5 tutarı olsun. GROUP BY iki kategori için iki satır üretir. SUM OVER ise üç ayrıntı satırını korur; A'nın iki satırında da 30, B'de 5 görünür. Aşağıdaki küçük VALUES sorgusu SQL Server veya PostgreSQL oturumunda uygulama verisine dokunmadan denenebilir.

WITH Items AS (
 SELECT * FROM (VALUES ('A',10),('A',20),('B',5)) d(Category,Amount)
)
SELECT Category,Amount,
 SUM(Amount) OVER(PARTITION BY Category) AS CategoryTotal
FROM Items ORDER BY Category,Amount;

SELECT Category,SUM(Amount) AS CategoryTotal
FROM (VALUES ('A',10),('A',20),('B',5)) d(Category,Amount)
GROUP BY Category ORDER BY Category;

İlk sonuç A/10/30, A/20/30 ve B/5/5 biçimindedir. İkinci sonuç A/30 ve B/5 olur. Birinci sorguda CategoryTotal satırın kendi tutarı değildir; o satırın ait olduğu grubun bilgisidir. İstemci bu sütunu yeniden toplarsa A için 60 bulur. Aynı değerin birden fazla ayrıntı satırında görünmesi, birden fazla kez hesaba katılması gerektiği anlamına gelmez.

Window toplamı bir annotation gibi düşünmek

PARTITION BY Category, fonksiyonun hesap yapacağı satır grubunu belirliyor. Mevcut örnekte pencere içinde ORDER BY olmadığı için her bölümün tamamı toplanıyor. Bu, detay satırının kimliğini koruyarak oran, kategori payı veya grup karşılaştırması hesaplamak için kullanışlıdır. Gruplanmış özet üretmek istediğimde ise satırları koruma çabası gereksizdir; GROUP BY ihtiyacı doğrudan anlatır.

ORDER BY eklemek hesabı değiştirebilir

Pencereye ORDER BY eklediğimde yalnız görsel sırayı değiştirmiş olmam. Aggregate window için varsayılan çerçeve devreye girer; sonuç tüm bölüm toplamı yerine sıralamayla büyüyen bir toplam olabilir. Eşit değerlere ait peer grupları da varsayılan RANGE davranışını etkiler. “Sonucu tarih sırasına soktum, toplamlar değişti” hatasının kaynağı bu olabilir.

SUM(Amount) OVER (
    PARTITION BY Category
    ORDER BY Id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal

Bu parça Id kolonu olan bir veri kümesinde kümülatif toplamı açıkça tanımlar; ilk örnekteki tabloya doğrudan yapıştırılacak bağımsız sorgu değildir. Bütün kategori toplamını her satırda istiyorsam pencereye sıralama eklemem veya çerçeveyi bölümün tamamını kapsayacak şekilde bilinçli kurarım.

Filtreli detay ile tüm dönem toplamı

WHERE, pencere hesabından önce satırları azaltır. Kullanıcı yalnız 20 ve üzerini görürken toplamın tüm dönemi kapsamasını istiyorsa tek seviyede filtre uygulamak yeterli olmaz. Önce toplamı hangi veri kümesinde hesaplayacağımı, sonra hangi ayrıntıyı göstereceğimi ayırırım. Aksi halde sorgu doğru çalışsa bile ekran yanlış soruya cevap verir.

Join'lerin çoğalttığı satırlar pencere hesabına da girer. Bir kayıt üç alt belgeyle birleşmişse tutarın üç kez hesaba girmesi GROUP BY'a özgü bir sorun değildir. Önce doğru tane düzeyini kurar, ardından toplam fonksiyonunu uygularım. Sonradan DISTINCT eklemek her durumda aynı iş anlamını geri getirmez.

Rapor tüketicisinin de sözleşmeyi bilmesi gerekir

Satır tutarı Amount, grup toplamı CategoryTotal, kümülatif toplam RunningTotal olarak ayrı adlandırılırsa arayüz ve dışa aktarım kodu daha az tahmin yapar. Boş kategori, null tutar ve aynı sıralama değerlerine sahip satırlarla kontrol ederim. Beklenen satır adedi ve toplamı birlikte doğrulamak, yalnız son toplamın tesadüfen doğru olmasına güvenmekten daha güçlüdür.

PostgreSQL window fonksiyonları; T-SQL OVER ve pencere çerçevesi.