SQL Server’da Hareketli Ortalama Hesaplama (Moving Average)

SQL Server‘da SUM, MIN, MAX, COUNT ve AVG gibi aggregate fonksiyonların kullanımı bildiğiniz gibi oldukça yaygın durumdadır. Ancak bu fonksiyonlarla hesaplama yaparken bazı durumlarda daha karmaşık senaryolar karşımıza çıkabilmektedir. Şirketinizin var olan satış verilerini kullanarak son bir yıllık periyotta satış cirosunun aylık bazda ortalamasını görmek karşımıza çıkabilecek senaryolardan birine örnek gösterilebilir. Literatiürde Hareketli Ortalama veya Moving Average (Rolling Average) olarak da bilinen bu hesaplama yönteminin SQL Server‘da nasıl ele alınacağını bu yazımızda ele alacağız.

Daha çok var olan veriler üzerinde uzun dönemli trendleri hesaplarken ve time series hesaplamalar yaparken kullanabileceğiniz hareketli ortalama sorgularını SQL Server‘ın farklı versiyonlarında farklı şekillerde yazılabilmektedir. SQL Server 2008 R2 ve öncesinde bu çözümü Common Table Expression (CTE) yapısı ile recursive bir sorgu yazarak çözebilmekteydik. SQL Server 2012 ve sonraki sürümlerinde ise hareketli ortalama hesabı window function‘lara ek olarak getirilen ROW ve RANGE komutlarıyla çok daha basit ve performanslı bir şekilde yapılabilmektedir.

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

https://gallery.technet.microsoft.com/Moving-Average-Calculation-ecf97f10

  • SQL Server 2012 ve Sonraki Versiyonları İçin;

Genel olarak hareketi ortalama‘nın yapısı, kullanım senaryoları ve versiyonlara bağlı olarak nasıl yazılabileceğini ele aldıktan sonra bir örnek üzerinde uygulamamızı gerçekleştirelim. Örneğimizde şirketimizin satış cirosunun son 12 aylık periyotta aylık olarak nasıl değiştiğini hareketi ortalama hesabı yaparak bulacağız. İlk olarak SQL Server 2012 ve sonrasında kullanabileceğimiz AVG fonksiyonunun window function konsepti ile nasıl yazılabileceğini inceleyelim. Burada ilk olarak var olan satış verisinin 2013 yılı için aylık bazda cirolarını öncelikle aşağıdaki sorgu vasıtasıyla gösterelim:

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

Bu sorgu sonucunda 2013 yılı içerisinde ay bazında satış toplamlarını aşağıdaki şekilde elde etmiş oluyoruz:

ana sorgu

Şimdi bu ay bazında hesaplanan ciro bilgilerinin SQL Server 2012 ve sonrasında kullanılabilecek olan ROWS ve RANGE komutları yardımıyla hareketli ortalamalarını hesaplatalım;

select Yıl,
		Ay,
		AVG(Ciro) OVER(order by Yıl, Ay ROWS BETWEEN 12 PRECEDING AND CURRENT ROW)
from
	(
	select YEAR(OrderDate) as Yıl, 
			MONTH(OrderDate) as Ay,
			SUM(SubTotal) as Ciro
	from Sales.SalesOrderHeader
	where OrderDate >= '20130101' AND OrderDate < '20140101'
	group by YEAR(OrderDate), MONTH(OrderDate)
	) as tbl
order by Yıl, Ay

Yazılan kod bloğunda AVG() fonksiyonu ile daha önce hesaplanan ciro verilerinin ortalaması hesaplatılmaktadır. Burada farklı olan ise AVG() fonksiyonunun bu hesaplamayı OVER() yapısı ile window üzerinde yapıyor olmasıdır. OVER() yapısı içerisinde order by komutu ile ilgili veriler Yıl ve Ay bazında sıralanacak ve ardından ROWS BETWEEN 12 PRECEDING AND CURRENT ROW komutu ile o an üzerinde bulunulan satırdan 12 satır öncesine kadar olan verilerin ortalaması hesaplanacaktır. Dolayısıyla istenilen hareketli ortalama hesabı elde edilebilecektir. Sorgunun sonucu aşağıdaki gibidir:

hareketli ortalama 1

 

  • SQL Server 2008 R2 ve Önceki Versiyonları İçin;

Şimdi de SQL Server 2012 öncesinde kullanılan hareketli ortalama hesabına bir örnek verelim. Yukarıdaki isteğimizin aynısını CTE kullanarak elde edelim;

	;WITH  tbl
          AS ( 
			select  RANK() OVER(order by YEAR(OrderDate), MONTH(OrderDate)) as SiraNo,
					YEAR(OrderDate) as Yıl, 
					MONTH(OrderDate) as Ay,
					SUM(SubTotal) as Ciro
			from Sales.SalesOrderHeader
			where OrderDate >= '20130101' AND OrderDate < '20140101'
			group by YEAR(OrderDate), MONTH(OrderDate)
             )
    SELECT  tbl1.Yıl ,
            tbl1.Ay ,
            AVG(tbl2.Ciro) AS [Hareketli Ortalama]
    FROM    tbl AS tbl1
             INNER JOIN tbl AS tbl2 ON tbl2.SiraNo between tbl1.SiraNo - 12 and tbl1.SiraNo
    GROUP BY tbl1.Yıl ,
            tbl1.Ay
    ORDER BY tbl1.Yıl ,
            tbl1.Ay ;

Bu sorguyu çalıştırdığımızda yine aynı sonucu elde etmiş olacağız. SQL Server 2008 R2 ve öncesinde kullanılan bu yöntemi isterseniz kısaca açıklayalım. tbl ismini verdiğimiz CTE kullanılarak 2013 yılına ait Yıl ve Ay bazlı cirolar hesaplatılmış ve RANK() fonksiyonu ile Yıl ve Ay sırasına göre SıraNo isminde bir sıra numarası hesaplatılmıştır. Kullanılan CTE ile tbl üzerinde self join işlemi yapılmış ve hareketli ortalamanın hesaplanması için tbl2.SiraNo between tbl1.SiraNo – 12 and tbl1.SiraNo şeklinde bir koşul ile recursive bir yapı sağlanmıştır. Bu şekilde elde edilen sonuç bir önceki hareketli ortalama hesabı ile aynı sonucu vermektedir.

  • Performans Karşılaştırması:

Yukarıda hem SQL Server 2008 R2 öncesinde hem de SQL Server 2012 ve sonrasında kullanılabilecek hareketli ortalama hesaplama yöntemlerini ele aldık. Şimdi bu iki yöntemi performans açısından incelemek için her iki sorgunun birlikte alınan Execution Plan‘larına birlikte bakalım:

plan karsilastirma

Her iki sorgunun Execution Plan‘ları karşılaştırıldığında ilk sorgumuz olan ve SQL Server 2012 ve sonrasında kullanılabilen AVG() OVER() yapısı %33’lük bir maliyete sahipken, ikinci sorgumuz olan ve SQL Server 2008 R2 ve öncesinde kullanılan recursive CTE yöntemi ile hesaplamanın maliyeti %67’dir. Buradan da görüldüğü gibi eğer SQL Server 2012 veya daha üst bir versiyon kullanılıyor ise ilk yöntemin tercih edilmesi gerektiği açıkça ortadadır.

Bu yazımızda SQL Server’ın farklı versiyonlarında Hareketli Ortalama (Moving Average) hesaplamalarının nasıl yapılabildiği ve bunlar arasında performans karşılaştırmalarının nasıl olduğunu ele aldık. Umarım faydalı olur. Bir sonraki yazımızda görüşmek dileğiyle…

Yazar: Abdullah ALTINTAŞ

Yorum Yaz