SQL Server MERGE Komutu Kullanımı ve Performans Önerileri

SQL Server 2008 versiyonu ile birlikte hayatımıza giren MERGE komutu kullanılarak ayrı ayrı yapılabilen INSERT, UPDATE ve DELETE işlemleri artık tek bir komut ile gerçekleştirilebilmektedir. Özellikle iki tablo arasında veri senkronizasyonu sağlanması için gereken farklı DML işlemleri MERGE komutu ile tek seferde yapılabilmektedir. MERGE komutunun bir başka kullanımı ise veriambarı geliştirme kısmında gerçekleştirilen ETL süreçlerinin bir parçası olan Slowly Changing Dimensions (SCD) yapılarının MERGE kullanılarak tasarlanabilmesidir. Bu konuyu başka bir yazıda ele alacağız. Bu yazımızda SQL Server’da kullanılan MERGE komutunun genel kullanımını ele alacak ve performans optimizasyonu için nelere dikkat edilmesi gerektiği kısaca açıklayacağız.

Kullanılan sistemlerde bazen bir tablodaki verilerin düzenli aralıklarla başka bir tabloya verilerini taşımak ve her iki tabloyu senkron etmek gerekebilir. Bu bazen bir test ortamı ile production ortamı arasında ihtiyaç duyulabilen bir senaryo olabileceği gibi bazen de OLTP veritabanları ile veriambarı katmanı arasında bu gibi ihtiyaçlara gereksinim duyulabilmektedir. Örneğimizde EmployeeSource isminde bir kaynak tablomuzu ve aynı yapıya sahip EmployeeTarget isminde bir hedef tablomuzu kullanacağız. Bu iki tablonun scriptleri aşağıdaki gibidir:

create table EmployeeSource
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO


create table EmployeeTarget
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO

Bu iki tabloyu create ettikten sonra içine örneğimizi gerçekleştireceğimiz birkaç tane veri girişi yapalım:

insert into dbo.EmployeeTarget 
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1)
GO

insert into dbo.EmployeeSource
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 0)
GO

Ardından bu komutları da çalıştırdıktan sonra tablolarımız üzerindeki verileri kontrol edelim:

select * from dbo.EmployeeSource
select * from dbo.EmployeeTarget

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız (source tablosu ile senkron olmasını istediğimiz) hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaş’ın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.

kaynak ve hedef Amacımız bu iki tablonun verilerini senkronize etmek olduğundan şu işlemleri yapmamız gerekmektedir;

  1. Kaynak tabloda olup hedef tabloda olmayan verileri insert etmek,
  2. Kaynak tabloda olup hedef tabloda da olan verileri olması muhtemel değişiklikleri uygulamak için update etmek,
  3. Hedef tabloda olup artık kaynak tabloda bulunmayan kayıtları delete etmek.

Bu işlemleri ayrı ayrı INSERT, UPDATE ve DELETE kullanarak yapabileceğimiz gibi SQL Server 2008 ile beraber gelen MERGE komutu ile tek seferde de gerçekleştirebiliriz. Şimdi beklenen değişiklikleri uygulayabilmek için gerekli olan kodu yazalım:

MERGE INTO dbo.EmployeeTarget as t
USING dbo.EmployeeSource as s
ON t.EmployeeID = s.EmployeeID
WHEN MATCHED THEN
	UPDATE SET t.FirstName = s.FirstName, 
				t.LastName = s.LastName, 
				t.Title = s.Title, 
				t.RecruitmentDate = s.RecruitmentDate,
				t.Salary = s.Salary,
				t.IsActive = s.IsActive
WHEN NOT MATCHED BY TARGET THEN
	INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive)
	VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, s.IsActive)
WHEN NOT MATCHED BY SOURCE THEN
	DELETE
OUTPUT $action as YapilanIslem, deleted.EmployeeID, inserted.EmployeeID;

Yazdığımız kod bloğunu isterseniz kısaca açıklayalım:

İlk olarak MERGE INTO komutu ile hedef tablomuzu belirttik. Ardından verileri hangi tablodan çekeceğimizi USING ifadesi ile belirledik. (Burada USING ifadesi ile sadece var olan tabloları değil view, CTE, derived table, udf vb kullanabilirsiniz.) Kaynak ve hedef tablolarındaki verilerin daha önceden olup olmadıklarını karşılaştırabilmek için unique olan EmployeeID kolonu üzerinden gerekli kontrolü gerçekleştirdik. Bu işlemin ardından;

  1. WHEN MATCHED THEN ifadesi ile hedefte daha önce bulunan ve kaynak tablosundaki muhtemel değişiklikleri uygulamamız için gerekli olan UPDATE komutu ile güncelleme yapılması,
  2. WHEN NOT MATCHED BY TARGET ifadesi ile hedefte henüz bulunmayan ama kaynak tablosunda bulunan ve hedef tablosuna eklenmesi gereken kayıtlar için INSERT komutu ile gerekli kayıtların eklenmesi,
  3. WHEN NOT MATCHED BY SOURCE ifadesi ile hedefte bulunan ancak kaynak tablosunda artık bulunmayan kayıtların silinmesi için (test verileri vb olabilir) DELETE komutu ile bu verilerin silinmesi sağlanmıştır.

Burada dikkat edilmesi gereken her 3 işlem için de (INSERT, UPDATE, DELETE) hangi tablo üzerinde bu komutların çalıştırılacağının yazılmamasıdır. MERGE komutu içinde bu işlemler otomatik olarak sadece hedef tablosunda yapılacaktır. Tablo ismini bu 3 komutun yanında kullanmamız durumunda syntax hatası alacağımızı belirtmek isterim. Ayrıca bir diğer ipucu olarak da WHEN NOT MATCHED BY TARGET komutu yerine kısaca  WHEN NOT MATCHED yazabileceğimizi de belirtmek isterim.

Kodun son kısmında OUTPUT seçeneği ile etkilenen kayıtlara ait bilgiler çıktı olarak ekranda sonuç setinde gösterilecektir. $action ifadesi MERGE içinde OUTPUT ile birlikte kullanılabilmekte ve yapılan işlemi (INSERT, UPDATE, DELETE) göstermektedir.

İlgili MERGE komutunu çalıştırdığımızda aşağıdaki sonuç karşımıza çıkacaktır:

Buradan da görüleceği üzere EmployeeSource tablosundaki 1, 2 ve 3 nolu kayıtlar UPDATE edilmiş, 5 nolu kayıt (daha önce hedefte olmadığı için) INSERT edilmiş, kaynakta NULL yazan kayıt (aslında hedefte 4 nolu kayıt) artık olmadığı için DELETE edilmiştir.

MERGE komutunu çalıştırdıktan sonra EmployeeSource ve EmployeeTarget tablolarını tekrar sorgulayıp sonucuna bakalım:

kaynak ve hedef2

Görüldüğü gibi her iki tablodaki kayıtlar artık tamamen birbirine eşit duruma gelmiştir.

  • MERGE – Performans İçin Dikkat Edilecek Noktalar

MERGE komutu kullanılmadan iki tablo birbirine senkron edilmek istenirse ayrı ayrı INSERT, UPDATE ve DELETE işlemleri gerçekleştirilecek ve tablodaki veriler 3 işlem için tekrar tekrar ele alınacaktır. MERGE ifadesi ile tablo tek seferde ele alınacağından bu 3 işlem tek seferde yapılacaktır. Bu da çoğu durumda bize performans kazanımı sağlamaktadır. Yine de MERGE komutunun performanslı bir şekilde çalışmasını sağlamak için dikkat edilmesi gereken bazı noktalar vardır. Bunlardan başlıca bir kaç tanesini burada belirtelim;

  1. Kaynak ve hedef tabloları joinleyeceğimiz kolonlar üzerinde uygun indexlerin oluşturulması,
  2. Eğer verileri üzerinde bir filtre verilmek isteniyorsa bunun ON ifadesinde değil uygun eşleştirme kısmında (WHEN MATCHED ya da NOT MATCHED) ele alınması,
  3. Bir veri dosyasından OPENROWSET ile veri çekilirken uygun indexlerin oluşturulmasının yanı sıra OPENROWSET ifadesi ile birlikte ORDER ve UNIQUE bileşenlerinin verilmesi performansa olumlu katkı yapacaktır.

MERGE komutunun performanslı bir şekilde çalışmasını sağlayabilmek için nelere dikkat edilmesi gerektiğine aşağıdaki linkten daha detaylı olarak ulaşabilirsiniz;

https://technet.microsoft.com/en-us/library/cc879317.aspx

Bu yazımızda genel olarak SQL Server’da MERGE komutunun nasıl kullanılabileceğini ele aldık. Örnek bir uygulama ile iki tablo üzerindeki verilerin senkron edilmesini sağladık. Başka bir yazımızda MERGE komutu kullanılarak SCD type 1 ve SCD type 2 işlemlerinin nasıl yapılabileceğini ele alacağız. Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

SQL Server 2016 1 Haziran 2016 Tarihinde Genel Kullanıma Alınıyor…

Microsoft’un modern veritabanı yönetim sistemi olan SQL Server’ın son sürümü SQL Server 2016, 1 Haziran 2016 tarihinde General Available oluyor. Uzun zamandır yeniliklerini duyurduğumuz ürünün Relese Date‘i sonunda Microsoft tarafından duyuruldu. Haziran 2016’da genel kullanıma alınacak olan SQL Server 2016 ile birlikte In Memory yeniliklerinden Operational Analytics‘e, Hybrid çözümlerden BI alanındaki yeniliklere kadar bir çok dikkat çekici yenilik bulunuyor.

microsoft-sql-server-2016

SQL Server 2016 Yenilikleri hakkında daha detaylı bilgi almak için aşağıdaki linkten faydalanabilirsiniz:

https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

Ayrıca Türkçe kaynak olarak da İsmail Adar’ın yazmış olduğu SQL Server 2016 Yenilikleri kitabını pdf olarak ücretsiz bir şekilde aşağıdaki linkten indirebilirsiniz:

http://silikonakademi.com/BookList/1/SQL-Server-2016-Yenilikleri

Yenilikler ile ilgili detaylı özellikleri ilerleyen dönemlerde makaleler ve videolar vasıtasıymla sizlere ulaştıracağız. Takipte kalın 🙂

 

Microsoft – Azure Solutions Etkinliği

Geçtiğimiz hafta 12 Mayıs tarihinde Microsoft Türkiye ofisinde Microsoft Azure Solutions başlığı altında bir etkinlik gerçekleştirdik. Etkinliğin amacı üniversiteden yeni mezun olmuş öğrencileri Microsoft Azure teknolojileri ve çözümleri hakkında bilgilendirmekti.

Bu bağlamda organizatörlüğünü üstlendiğim bu etkinlikte  Microsoft profesyonelleri, Data Platform MVP’leri ve sektör profesyonelleri sunumlarını gerçekleştirdiler.

Katılım sağlayan tüm öğrencilerimize, Data Platform MVP’leri Koray Kocabaş ve İsmail Adar’a, Microsoft RD ünvanına sahip üstadımız Engin Polat’a ve Mustafa Acungil’e katılıp sunum yaptıkları için ayrıca teşekkür ederim.

Yeni etkinliklerde görüşmek dileğiyle…

Yazar: Abdullah ALTINTAŞ

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Ş

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Ş

Real-time Click Stream Analizi İçin Microsoft Azure Çözümleri Serisi

Real-time click stream verisinin analizi için kullanabileceğimiz yöntemleri ve Microsoft Azure Çözümleri ile bu isteklerin nasıl çözümlenebileceğini ele aldığım yazı serisi (en azından şimdilik) tamamlandı. Bu yazı serisi içinde Microsoft Azure Event Hubs Servisi, Microsoft Azure Stream Analytics Servisi ve Power BI ürününü entegre ederek uçtan uca bir çözüm örneği gerçekleştirilmiş oldu. Serinin her bir parçası için bir makale yazdım, ancak toplu bir şekilde seriye ulaşmak isteyenler için de hepsini içeren bir yazı yazma gereği duydum. Umarım okurken keyif alır ve ihtiyacınıza göre faydalanırsınız.

Aşağıda sırayla yazılmış olan serinin her bir yazısını ve linkini paylaşıyorum:

  • Advanced Data Analytics Kavramına Giriş ve IoT (Internet of Things) Nedir

http://www.abdullahaltintas.com/index.php/advanced-data-analytics-kavramina-giris-ve-iot-internet-of-things-nedir/

 

  • Veri Edinme Seçenekleri (Azure Event Hubs, AWS Kinesis, Apache Kafka) 

http://www.abdullahaltintas.com/index.php/veri-edinme-secenekleri-azure-event-hubs-aws-kinesis-apache-kafka/

 

  • Microsoft Azure Event Hubs Servisi ile Veri Edinme

http://www.abdullahaltintas.com/index.php/microsoft-azure-event-hubs-servisi-ile-veri-edinme/

 

  • Microsoft Azure Stream Analytics Servisi

http://www.abdullahaltintas.com/index.php/microsoft-azure-stream-analytics-servisi/

 

  • Azure Event Hubs ve Stream Analytics ile Uçtan Uca Mimari Çözümü

http://www.abdullahaltintas.com/index.php/azure-event-hubs-ve-stream-analytics-ile-uctan-uca-mimari-cozumu/

 

  • Microsoft Azure Event Hubs Servisinin Ayağa Kaldırılması (Demo)

http://www.abdullahaltintas.com/index.php/microsoft-azure-event-hubs-servisinin-ayaga-kaldirilmasi-demo/

 

  • Microsoft Azure Stream Analytics Job’ının Oluşturulması (Demo)

http://www.abdullahaltintas.com/index.php/microsoft-azure-stream-analytics-jobinin-olusturulmasi-demo/

 

  • Microsoft Power BI ile Real-Time Click Stream Verisinin Görüntülenmesi (Demo)

http://www.abdullahaltintas.com/index.php/microsoft-power-bi-ile-real-time-click-stream-verisinin-goruntulenmesi-demo/

 

  • Microsoft Azure Stream Analytics Servisinin Konfigürasyonu ve Ölçeklendirilmesi

http://www.abdullahaltintas.com/index.php/microsoft-azure-stream-analytics-servisinin-konfigurasyonu-ve-olceklendirilmesi/

  • Stream Analytics ile Analiz Edilen Verinin Azure SQL Database’de Depolanması (Demo)

http://www.abdullahaltintas.com/index.php/stream-analytics-ile-analiz-edilen-verinin-azure-sql-databasede-depolanmasi-demo/

  • Stream Verinin Azure SQL Database’de Table Partitioning ile Tutulması (Demo)

http://www.abdullahaltintas.com/index.php/stream-verinin-azure-sql-databasede-table-partitioning-ile-tutulmasi-demo/

Keyifli okumalar dilerim…

Yazar: Abdullah ALTINTAŞ

Microsoft Azure Stream Analytics Servisinin Konfigürasyonu ve Ölçeklendirilmesi

Microsoft Azure Stream Analytics servisi kullanılarak gerçek zamanlı click stream verisinin analizi ve raporlanması ile ilgili çözümler en etkin bir şekilde geliştirilebilmektedir. Bunun yanında uygulamadan gelen verilerin boyutu gibi bilgileri gözlemlemek için Stream Analytics servisinde bulunan Dashborad ekranı kullanılabilmektedir. Bu ekranda uygulamadan gelen veriler Dashborad’a yansımaktadır.

stream analytics dashboard

Aynı zamanda Stream Analytics job’ı ile ilgili bilgi edinmek ve monitörlemek için de yine Stream Analytics bizlere Monitor ekranı sunmaktadır. Bu ekrandan kullanılan kaynaklar, hatalar, input ve output event bilgileri izlenebilmektedir.

stream analytics monitor

Stream Analytics servisi maliyet açısından da oldukça uygun bir servistir. Bu servisi kullanırken ödeyeceğiniz ücret miktarını iki ölçüt belirlemektedir. Bunlardan birisi input olarak alınan veri büyüklüğü, diğeri de gelen stream veriyi analiz etmek için kullanılan Streaming Unit birim değeridir. Streaming Unit değeri bu işlemleri gerçekleştirebilmek için kullanılan CPU, RAM, IO gibi işlemleri temsil eden bir değerdir ve bu değer ne kadar arttırılırsa analiz işlemi de o kadar hızlı yapılabilecektir. Tabi atanan Streaming Unit değeri arttıkça ödenen ücret de artacaktır. Streaming Unit değeri Azure portal üzerinde yer alan Scale tabından rahat bir şekilde değiştirilebilmektedir. Buradan sisteme yoğun bir veri akışı olan zamanlarda ihtiyacı karşılamak için Streaming Unit değerini arttırmak, ihtiyaç azaldığında maliyet açısından avantaj sağlamak için Streamin Unit değerinin azaltılmasını sağlamak mümkündür.

stream analytics olceklendirme

Bu şekilde ölçeklenebilir bir analiz sistemini kullanmak mümkün olabilmektedir. Azure portal üzerinde bulunan Stream Analytics servisi için kullanılabilen diğer bir seçenek de Configure tabıdır. Bu alanda stream olarak gelen olayların sıralamasıyla ilgili ayarlar, dil seçeneği ayarları ve log bilgileri için kullanılacak olan storage ayarı gibi bilgiler ayarlanabilmektedir.

StreamAnalytics20

Microsoft Azure servislerinden ele aldığımız Event Hubs ve Stream Analytics çözümlerini aktardığımız yazı dizisi burada tamamlanıyor. Tabi ki yenilikler geldikçe yeni yazılar yazmaya ve siz değerli okuyucularımızı bilgilendirmeye devam edip Türkçe kaynak sayısını arttırmaya devam edeceğiz.

Bir sonraki yazımızda şimdiye kadar seri boyunca ele aldığımız yazıları toplu bir şekilde size linkleriyle birlikte sunacağız. Böylece uçtan uca stream verisinin gerçek zamanlı olarak analiz edilmesine tek bir kaynaktan erişebileceksiniz. Umarım faydalı olur.

Bir sonraki yazımızda görüşmek üzere…

Yazar: Abdullah ALTINTAŞ