SQL Server’da Kümülatif Toplam Hesaplama (Running Total)

Günlük hayatımızda bazen sayısal değerlerin üst üste eklenerek artan hareketli toplamının bulunması ihtiyacı karşımıza çıkmaktadır. Veri tabanında bulunan şirketimize ait sayısal veriler de sorgulanırken bu gibi ihtiyaçlar karşımıza çıkabilmektedir. Özellikle bir kolonda bulunan verilerin kümülatif olarak toplamının bulunması hem veriyi analiz edenler için hem de raporların oluşturulmasını ve yayınlanmasını sağlayanlar için bir gereklilik olarak karşısına çıkabiliyor. Veriler üzerinde işlem yaparken o an üzerinde bulunulan satıra kadar geçmiş bütün değerlerin toplanması işlemine literatürde kümülatif toplam (Cumulative Sum, Running Total) adı verilmektedir.

Microsoft SQL Server ile çalışılırken buna benzer bir ihtiyaç duyulduğunda kullanılmakta olan SQL Server versiyonu önem arz etmektedir. Eğer SQL Server‘ın 2008 R2 veya daha eski bir versiyonunda işlem yapıyorsanız bu durumda kümülatif toplam hesabı yaptırmak için kullanacağınız tabloyu self join işlemi ile veya subquery kullanarak oluşturmanız gerekmektedir. Eğer kullanmakta olduğunuz SQL Server versiyonu SQL Server 2012 veya daha üst bir versiyon ise bu durumda SQL Server 2012 ile gelen SUM() OVER() yapısını kullanarak window function yapısı ile bu hesaplamayı yapabilmek mümkün olmaktadır. (Not: SUM() OVER() yapısı SQL Server 2008 ile gelmiş olup, ROWS BETWEEN AND eklentisi SQL Server 2012 ile getirilmiştir.)

Not: Örnek scriptleri aşağıdaki Technet Gallery’de bulunan paylaşımımdan da ücretsiz olarak download edebilirsiniz:

https://gallery.technet.microsoft.com/SQL-Server-Cumulative-Sum-b1c40b0d

İlk olarak piyasada güncel olan ve daha yaygın olarak kullanılan SQL Server 2012 ve sonrasındaki sürümler için geçerli olan yapıya bir göz atalım ve örneğimizi yapalım.

USE AdventureWorks2014
GO

select YEAR(OrderDate) as Yıl, 
	   MONTH(OrderDate) as Ay,
	   SUM(SubTotal) as Ciro
from Sales.SalesOrderHeader
where OrderDate >= '20130101'
group by YEAR(OrderDate), MONTH(OrderDate)
order by Yıl, Ay

Yukarıdaki sorgu bizlere sipariş bilgileri alınarak satış yapılan yıl ve ay bazlı siparişlerin toplam cirosunu getirmektedir. Sorgu sonucunda 2013 yılından itibaren ilgili yılın ilgili ayında yapılan ciro toplamı satır bazlı olarak getirilmektedir. Sorgunun sonucu aşağıdaki gibidir:

sorgu1

Burada 2013 yılından itibaren ay bazlı ciroların üzerinde bulunulan aya kadar olan kümülatif toplamını bulmak işlemi için aşağıdaki kod bloğunu kullanarak ilgili istek gerçekleştirilebilir;

USE AdventureWorks2014
GO

select Yıl, 
		Ay,
		Ciro,
		SUM(Ciro) OVER(order by Yıl, Ay ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as [Kümülatif Toplam]
from
	(
	select YEAR(OrderDate) as Yıl, 
		   MONTH(OrderDate) as Ay,
		   SUM(SubTotal) as Ciro
	from Sales.SalesOrderHeader
	where OrderDate >= '20130101'
	group by YEAR(OrderDate), MONTH(OrderDate)
	--order by Yıl, Ay
	) as tbl

Bu sorgu ile daha önce yazılan ana sorgu parantezler içine alınarak bir drived-table oluşturulmuş ve ardından Yıl ve Ay kolonlarına göre sıralama yapılarak Ciro kolonunun kümülatif toplamı hesaplanmıştır. Burada kullanılan “ROWS BETWEEN UNBUNDED PRECEDING AND CURRENT ROW” ifadesi farklı versiyonlar ile de kullanılabilmektedir. Buradaki amacımız ilk satırdan itibaren toplama işlemine başlayacağı için UNBOUNDED PRECEDING ifadesi kullanılmış ve o an üzerinde çalışılan satıra kadar olanların toplamını almak için de CURRENT ROW ifadesi kullanılmıştır. Farklı kullanımları için aşağıdaki msdn sayfasını ziyaret edebilirsiniz:

https://msdn.microsoft.com/en-us/library/ms189461.aspx?f=255&MSPPError=-2147217396

Bu sorgu çalıştırıldığında aşağıdaki sonuç seti karşımıza çıkacaktır;

sorgu2

Şimdi de benzer bir ihtiyacı SQL Server 2012 öncesinde nasıl uyguladığımıza bir bakalım;

USE AdventureWorks2014
GO

select t1.Yıl, 
		t1.Ay,
		t1.Ciro,
		SUM(t2.Ciro)
from
	(
	select YEAR(OrderDate) as Yıl, 
		   MONTH(OrderDate) as Ay,
		   SUM(SubTotal) as Ciro,
		   ROW_NUMBER() OVER(order by YEAR(OrderDate),MONTH(OrderDate)) as SiraNo
	from Sales.SalesOrderHeader
	where OrderDate >= '20130101'
	group by YEAR(OrderDate), MONTH(OrderDate)
	--order by Yıl, Ay
	) as t1
	inner join
	(
	select YEAR(OrderDate) as Yıl, 
		   MONTH(OrderDate) as Ay,
		   SUM(SubTotal) as Ciro,
		   ROW_NUMBER() OVER(order by YEAR(OrderDate),MONTH(OrderDate)) as SiraNo
	from Sales.SalesOrderHeader
	where OrderDate >= '20130101'
	group by YEAR(OrderDate), MONTH(OrderDate)
	--order by Yıl, Ay
	) as t2
on  t2.SiraNo <= t1.SiraNo
group by t1.Yıl, t1.Ay, t1.Ciro
order by t1.Yıl, t1.Ay

Yukarıdaki sorguda self join mantığı kullanılarak SQL Server 2012 öncesinde kümülatif toplam işleminin nasıl yapılabileceği gösterilmiştir. Her iki sorgu da aynı sonucu vermesine rağmen performans açısından ilk yazılan sorgu daha az maliyetli olacağından SQL Server 2012 ve üstü bir versiyon kullanılıyorsa ilk sorgu tercih sebebi olacaktır.

Son olarak her 2 sorgunun execution planını inceleyerek performans açısından birbirine maliyetini ele alalım:

performance compare

Yukarıdaki execution plan incelendiğinde ilk sırada bulunan self join ile yazılan sorgunun maliyeti %67 iken ikinci sırada bulunan SQL Server 2012 ve üzeri sürümlerde kullanılabilen SUM() OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) yapısı %33 maliyete sahiptir. Dolayısıyla tercih edilmesi gereken sorgu türü window function yapısı olmalıdır.

Bu yazımızda SQL Server ile kümülatif toplam hesabının nasıl yapılabileceğini, farklı versiyonlarda çalışan farklı sorgu türlerini ve bu sorguların hangisinin daha performanslı olacağını ele almış olduk. Umarım faydalı olur.

Bir sonraki yazımızda görüşmek dileğiyle…

Yazar: Abdullah ALTINTAŞ

SQL Server’da Kümülatif Toplam Hesaplama (Running Total)” üzerine bir düşünce

Yorum Yaz