Merhaba, sp_execute_external_script ‘in ilk makalesinde Python ile işlemler yapıp çıktı vermeyi göstermiştik. Bu makalede ise tam tersi sql’deki verilerimizi Python’a verip Python’da işleyeceğim:
Sql server’ın kendi içerisinde aslında Pivot ve Unpivot kullanımı mevcut fakat bununlla ilgili oldukça fazla makale var. Alternatif olarak Python pandas modülü bu işi çok kolay yapıyor ve veri işlemeye uygun olduğundan bunu örneklendirmeye karar verdim.
Örnek veri olarak Ömer Çolakoğlu’nun Türk market satış datasetini kullandım. Kaggle Linki
Senaryomuzda bölge ve şube bazında bizden aylık satışların karşılaştırılması isteniyor. Bunun için ilk önce aşağıdaki gibi bir sql cümlesi yazdım.
select sube,bolge,month(tarih) as ay,datepart(week,tarih) as hafta,sum(miktar) as satisAdeti,sum(netTutar) as satisTutari from satisTablosu st with (nolock) group by sube,bolge,month(tarih),datepart(week,tarih)
Bu sorgu sonucu bize aşağıdaki gibi bir veri döndürdü. Fakat böyle bir raporun sunulması karşılaştırma yapmak için oldukça kötü.
Bu veriyi Excel, Power BI, QPortal Rapor 🙂 gibi bir rapor üreticiye verip kolay bir şekilde sunum yapabilirsiniz fakat sql’den bunun hazırlanıp herhangi bir rapor üreticiye gerek duymadan ilgili kişiye sunulması mümkün.
Sql’den python kullanarak aşağıdaki gibi bir kod yazıyoruz.
EXEC sp_execute_external_script @language=N'Python',@script=N'
import numpy
pivotTablo = pandas.pivot_table(sorguVerisi, index=["bolge"],columns=["ay"],values=["satisTutari"],aggfunc={"satisTutari":[numpy.sum]}).reset_index()
'
,@input_data_1 = N'
select sube,bolge,month(tarih) as ay,datepart(week,tarih) as hafta,sum(miktar) as satisAdeti,sum(netTutar) as satisTutari from satisTablosu st with (nolock) group by sube,bolge,month(tarih),datepart(week,tarih)
'
,@input_data_1_name = N'sorguVerisi'
,@output_data_1_name = N'pivotTablo'
@input_data_1_name kısmına sorgumuzu olduğu gibi yazıyoruz herhangi bir şey değiştirmeden ve bunu sorguVerisi olarak Python’da kullanabiliriz artık.
pandas modülünde pivot_table’ye verimizi verdikten sonra satır (index), sütun (columns), değerler (values) ‘ini ayarlıyoruz. Bunun dışunda aggfunc’da numpy kullanarak verinin hesaplama türünü belirtiyoruz. reset_index() alanı sütun başlıklarını temizlemek için kullanıyoruz. Eğer siz datayı grid olarak listelemeyecek iseniz @output_data_1_name kaldırıp print(pivotTablo) olarak sütun başlıklarını tüm level’leri ile birlikte listeleyebilirsiniz.
Yukarıdaki kod bize aşağıdaki gibi bir sonuç verecektir.
Buraya kadar aslında basit bir pivot yaptık fakat pivot tablomuzu biraz daha detaylandırmak istersek çok küçük dokunuşlar ile bunu yapmak mümkün.
Çift kırılımlı bir pivot istiyorsak tek değiştirmemiz gereken yer burası.
index=["bolge","sube"]
Sonuç:
Aynı sonucu sütun ve değerler kısmında benzer şekilde yapabilirsiniz.
columns=["ay","hafta"] veya values=["satisAdeti","satisTutari"] gibi...
aggfunc alanında hesaplanan değerlerde’de birden fazla sütun birden fazla hesaplamaya ulaşabilirsiniz.
Örnek aşağıdaki gibi sadece küçük bir ekleme yaparak ay içerisindeki toplam satış tutarı yanına şubenin haftalık rekorunuda alabiliriz.
pivotTablo = pandas.pivot_table(sorguVerisi, index=["bolge","sube"],columns=["ay"],values=["satisAdeti","satisTutari"],aggfunc={"satisTutari":[numpy.max,numpy.sum]}).reset_index()
EXEC sp_execute_external_script @language=N'Python'
,@script=N'
import numpy
pivotTablo = pandas.pivot_table(sorguVerisi, index=["bolge","sube"],columns=["ay"],values=["satisTutari"], aggfunc={"satisTutari":[numpy.sum]} ).reset_index()
'
,@input_data_1 = N'
select sube,bolge,month(tarih) as ay,datepart(week,tarih) as hafta,sum(miktar) as satisAdeti,sum(netTutar) as satisTutari from satisTablosu st with (nolock) group by sube,bolge,month(tarih),datepart(week,tarih)
'
,@input_data_1_name = N'sorguVerisi'
,@output_data_1_name = N'pivotTablo'
WITH RESULT SETS
(
(
[Bölge] NVARCHAR(MAX)
,[Şube] NVARCHAR(MAX)
,[Ocak Satış Tut.] float
,[Şubat Satış Tut.] float
,[Mart Satış Tut.] float
)
)
Sonuçlara bir başlık vermek istiyorsanız yukarıdaki kod size aşağıdaki gibi bir çıktı verecektir. Bunun yerine bir temp tablo kullanmakda size istediğinizi verecektir.
Yeni bir yazıda görüşmek üzere.
#sqlServer #sp_execute_external_script #inputData #python #pandas #pivot
