SQL'de Kümülatif Toplam ve Hareketli Ortalama Nasıl Hesaplanır

Kümülatif toplam ve hareketli ortalama neyi gösterir?

Kümülatif toplam (running total), bir sıralamanın başından geçerli satıra kadar biriken toplamdır. Yılbaşından bugüne satış, ay ay biriken tahsilat, her işlemden sonra güncellenen bakiye bunun örnekleridir. Hareketli ortalama ise her satır için yalnızca son birkaç satırın ortalamasını alır; dalgalı bir seriyi yumuşatıp eğilimi görünür kılar.

Bu hesaplar aynı mantıkla çalışır: satırlar kaybolmaz, her satır kendi değerinin yanında bir hesaplanmış değer taşır. Bu, GROUP BY ile yapılan özetlemeden farklıdır: GROUP BY satırları birleştirir, pencere fonksiyonu satırları korur. Bu nedenle kümülatif hesap pencere fonksiyonlarının klasik kullanım alanıdır.

Örneğin günlük satış tablosunda her gün için o güne kadarki toplam satış istenir. Yönetici tek bakışta hedefin neresinde olunduğunu görür; veri analisti aynı sonucu grafik aracına doğrudan verir.

Temel kalıp: SUM ile OVER ve ORDER BY

Kümülatif toplamın temel kalıbı, toplulaştırma fonksiyonunu OVER cümlesiyle çağırmaktır:

SELECT tarih, tutar, SUM(tutar) OVER (ORDER BY tarih) AS kumulatif_tutar FROM gunluk_satis;

OVER içindeki ORDER BY, toplamın hangi sırayla birikeceğini belirler. PostgreSQL belgesi bu kalıbı örnekleyerek ORDER BY verildiğinde pencerenin bölümün başından başlayıp geçerli satırı ve onunla eşit olan satırları kapsadığını anlatır. Bu varsayılan çerçeve, bir tuzağın kaynağıdır: aynı tarihte birden çok satır varsa hepsi aynı kümülatif değeri alır, çünkü eşit satırlar birlikte pencereye girer.

Satır satır birikmesini istiyorsanız çerçeveyi açıkça yazın:

SUM(tutar) OVER ( ORDER BY tarih, satis_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS kumulatif_tutar

ROWS ifadesi fiziksel satırları sayar, eşit değerlere özel muamele yapmaz. ORDER BY'a benzersiz bir ikinci sütun (satis_id) eklemek sonucun her çalıştırmada aynı çıkmasını sağlar. Hangi çerçeve davranışının varsayılan olduğu ve söz diziminin ayrıntıları ürüne göre değişebileceği için kendi veritabanınızın belgesini kontrol edin.

Gruplara göre sıfırlanan kümülatif toplam

Sıklıkla toplam her grupta baştan başlar: her müşteri, her ürün ya da her yıl için ayrı kümülatif değer. Bunun için PARTITION BY eklenir:

SELECT customer_id, tarih, tutar, SUM(tutar) OVER ( PARTITION BY customer_id ORDER BY tarih ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS customer_cumulative FROM siparisler;

PARTITION BY customer_id, her müşterinin satırlarını ayrı bir pencere yapar; toplam bir müşteriden diğerine geçerken sıfırlanır. Yılbaşından itibaren kümülatif gösterim için bölümleme yıl bilgisiyle yapılır; böylece her takvim yılında toplam yeniden başlar.

Önce özetleyip sonra kümülatif hesap yapmak çok yaygın bir akıştır. Günlük satışları aylık özetleyip aylık kümülatif istiyorsanız, iç içe fonksiyon kullanılır:

SELECT ay, SUM(tutar) AS aylik_tutar, SUM(SUM(tutar)) OVER (ORDER BY ay) AS kumulatif_tutar FROM satislar GROUP BY ay;

İçteki SUM, GROUP BY ile ayı özetler; dıştaki SUM ise bu özetin üzerinde pencere olarak çalışır. Sorgu işleme sırası gereği pencere fonksiyonu, GROUP BY'dan sonra hesaplanır.

Hareketli ortalama: çerçeveyi daraltmak

Hareketli ortalamada pencere, başlangıçtan geçerli satıra kadar değil, geçerli satır ve ondan önceki sabit sayıda satırdan oluşur. Çerçeve ifadesi bu daralmayı tarif eder:

SELECT tarih, tutar, AVG(tutar) OVER ( ORDER BY tarih ROWS BETWEEN n PRECEDING AND CURRENT ROW ) AS hareketli_ortalama FROM gunluk_satis;

Burada n, pencerede geçerli satırdan önce kaç satırın bulunacağını belirleyen bir tam sayıdır; pencere geçerli satır dahil n önceki satır ile geçerli satırdan oluşur. Hafta ortalaması için günlük seride pencere genişliği hafta uzunluğuna denk gelecek şekilde ayarlanır. Merkezi ortalama istenirse çerçeve hem öncesine hem sonrasına uzanır: ROWS BETWEEN n PRECEDING AND n FOLLOWING.

Birkaç ayrıntıya dikkat edin. Serinin başında henüz yeterli satır yoktur; ilk satırların ortalaması daha az veriden hesaplanır. Raporda bu satırları ayrı işaretlemek ya da dışarıda bırakmak gerekebilir. ROWS ise satır sayar, takvim günü saymaz. Günlerin bazıları tabloda yoksa (hafta sonu ya da veri boşluğu), "son birkaç satır" ile "son birkaç gün" aynı şey değildir. Takvim bazlı pencere gerekiyorsa önce eksik günleri tamamlayan bir takvim tablosu ile birleştirin ya da veritabanınızın aralık (RANGE) çerçevesi desteğini inceleyin.

Kümülatif oran ve yüzde: biriken payı hesaplamak

Kümülatif toplamın doğal devamı, biriken payı göstermektir: ürünler satışa göre sıralandığında ilk ürünlerin toplam satışın ne kadarını oluşturduğu. Pareto türü analizler bu hesaba dayanır:

SELECT product, tutar, SUM(tutar) OVER ( ORDER BY tutar DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) * 1.0 / SUM(tutar) OVER () AS kumulatif_pay FROM product_sales;

Paydadaki SUM(tutar) OVER (), bölümlemesiz ve sıralamasız pencere olduğu için tüm tablonun toplamını verir. Çarpanın ondalıklı yazılması tam sayı bölmesini önler; PostgreSQL ve SQL Server'da tam sayıların bölümü tam sayı verir ve oran sıfıra yuvarlanır. Yüzde gösterimi için sonuç raporlama katmanında biçimlendirilebilir. Toplam içindeki pay hesabının ayrıntısı için SQL ile yüzde ve oran hesaplama sayfasına bakın.

Sık yapılan hatalar ve eski yöntem

Sorgularda karşılaşılan hatalar:

• Varsayılan çerçeveye güvenmek ve eşit tarihli satırlarda aynı kümülatif değeri görüp şaşırmak
• ORDER BY'ı unutmak: sıralama olmadan SUM OVER bütün bölümün toplamını verir ve kümülatif olmaz
• Birden çok sütunla sıralamada benzersiz bir anahtar eklememek; sonuç çalıştırmadan çalıştırmaya değişebilir
• Pencere fonksiyonunu WHERE içinde kullanmak; filtre için sorguyu CTE'ye sarmak gerekir
• Hareketli ortalamada satır ile gün farkını ihmal etmek
• Çok büyük tablolarda tüm geçmişi tek sorguda hesaplamak; yıl ya da dönem filtresi ve uygun indeks gerekir

Pencere fonksiyonları desteklenmeyen eski sürümlerde kümülatif toplam, tabloyu kendisiyle birleştiren ya da ilişkili alt sorguyla yapılırdı: her satır için o satırdan önceki tüm satırları toplayan bir alt sorgu. Bu yöntem çalışır ama her satır için tablonun bir bölümünü yeniden tarar ve büyük veride çok yavaştır. Pencere fonksiyonunu destekleyen bir sürümde bu yöntem tercih edilmemelidir.

Doğrulamak için son satırın kümülatif değerini, tablonun doğrudan SUM sonucuyla karşılaştırın; ikisi eşit çıkmalıdır. Pencere fonksiyonlarının genel yapısı için SQL pencere fonksiyonları sayfası, önceki satırla karşılaştırma için LAG ve LEAD sayfası tamamlayıcıdır.

Mindset programlarında bu beceri nerede çalışılır?

Kümülatif ve hareketli hesaplar; finans, satış ve operasyon raporlarında eğilim izleyen analistlerin günlük işidir. Mindset'in SQL ile Veri Sorgulama (İleri) programında Pencere Fonksiyonları ile İleri Analiz ve İleri Vaka Çalışmaları ve Analitik Modeller başlıkları yer alır. SQL ile İleri Veri Dönüşümü ve Modelleme programı ise Pencere Fonksiyonları ve Olgu Tabloları ve Toplulaştırma modülleriyle aynı konunun veri ambarı tarafını işler.

Kurumsal ekiplerde aylık raporları Excel'de elle hazırlayan finans ve satış analisti için bu programlar, hesapları veritabanına taşımanın yolunu gösterir. Kapsam ayrıntısı program sayfalarında yer alır.