Pivot; bilindiği üzere raporların önemli bir unsuru. Veritabanında biriktirdiğimiz verilerin, ya da mecazi tabirle, eteğimizdeki taşların, günü gelip yöneticilerin bir rapor talebine mukabil, bir nizam içerisinde dökülmesi gerekir..
Örneğin, A kullanıcının, yaptığı B işlemlerinin toplam tutarlarını, ay ay isteyebilirler. Bu durumda, gruplama fonksiyonları tek başına bir anlam ifaade etmeyecektir.Çünkü burada, ay ay yazılan B verilerinin toplamlarını, A kullanıcısına göre satır sutun bağlamında, üstelik son kullanıcının (ya da yöneticilerin) anlayacağı bir formatta sunmanız gerekmektedir.
Çözüm mantığı itibariyle gayet basit, raporlamanın ana eksenini oluşturan ve verinin temel ölçütü olan (örneğimize göre) ayları, satır verisi olmaktan çıkarıp sutunlara dönüştürmek.. Bunun da bilinen en iyi yöntemi, Excel'den hatırlayacağımız üzere Pivot tablolar oluşturmak.
Eğer MSSQL veritabanı kullanıcı iseniz, T-SQL 'de hazır bulunan pivot komutu ziyadesiyle işinizi görecektir. Siz parametre olarak sutunlara dönüştürülecek verileri kendisine atadığınıda, daha fazla mesaiye gerek kalmaksızın, sizin yerinize işi halledecektir.
Ama eğer MySQL kullanıyorsanız ve Sihirbaz(!) düzeyinde bir kullanıcı değilseniz, amiyane tabirle vay halinize!
Konumuzun temelini oluşturan ve verileri içeren tabloya şöyle bir göz atalım isterseniz:
Ad
|
Soyad
|
Tarih
|
Odeme
|
Ziyahan
|
Albeniz
|
2011-11-01
|
100.00
|
Eren
|
Albeniz
|
2011-11-02
|
50.00
|
Kadir
|
Muşta
|
2011-12-03
|
20.00
|
Emre
|
Yılmaz
|
2011-12-04
|
52.00
|
Bayram
|
Gök
|
2011-12-03
|
275.00
|
Ziyahan
|
Albeniz
|
2011-12-04
|
75.00
|
Kadir
|
Muşta
|
2011-12-05
|
40.00
|
Tablo ziyadesiyle basit oldu. Müşterilerin ya da kullanıcıların hangi tarihte ne kadar ödeme yaptığını tablomuzdan görüyoruz. 2011 yılının hangi ayında, hangi kullanıcının ne kadar ödeme yaptığını görmek MySQL'de aşağı yukarı şöyle bir kodla mümkün...
select concat(ad,' ',soyad),sum(case when month(Tarih)=1 then odeme else 0) as 'Ocak',
sum(case when month(Tarih)=2 then odeme else 0) as 'Subat',
sum(case when month(Tarih)=3 then odeme else 0) as 'Mart'
from TABLO
group by concat(ad,' ',soyad),tarih
Bu tablo böylece uzayıp gider.. Mevzu bahis ay ay listelemek olunca, ay verisini sutuna dönüştürecek satırımızı 12 kez yazmak kimine kolay gelebilir.. Ama sutuna dönüştürülecek veri dinamik bir veri ise ve biz adedini bilmiyor isek?..
Böyle bir durumda benim de imdadıma, Pivot tablo görünümü hazırlayan aşağıdaki prosedür yetişti, umarım sizler için de faydalı olur.
CREATE PROCEDURE pivotwizard(
IN P_Row_Field VARCHAR(255),
IN P_Column_Field VARCHAR(255),
IN P_Value VARCHAR(255),
IN P_From VARCHAR(4000),
IN P_Where VARCHAR(4000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE M_Count_Columns int DEFAULT 0;
DECLARE M_Column_Field varchar(60);
DECLARE M_Columns VARCHAR(8000) DEFAULT '';
DECLARE M_sqltext VARCHAR(8000);
DECLARE M_stmt VARCHAR(8000);
DECLARE cur1 CURSOR FOR SELECT CAST(Column_Field AS CHAR) FROM Temp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS Temp;
SET @M_sqltext = CONCAT('CREATE TEMPORARY TABLE Temp ',
' SELECT DISTINCT ',P_Column_Field,
' AS Column_Field', ' FROM ',P_From, ' WHERE ',P_Where, ' ORDER BY ', P_Column_Field); PREPARE M_stmt FROM @M_sqltext; EXECUTE M_stmt; SELECT COUNT(*) INTO M_Count_Columns
FROM Temp
WHERE Column_Field IS NOT NULL; IF (M_Count_Columns > 0) THEN OPEN cur1; REPEAT FETCH cur1 INTO M_Column_Field; IF (NOT done) and (M_Column_Field IS NOT NULL) THEN SET M_Columns = CONCAT(M_Columns,
' SUM( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''', ' THEN ',P_Value, ' ELSE 0 END) AS `', M_Column_Field ,'`,'); END IF; UNTIL done END REPEAT;
SET M_Columns = Left(M_Columns,Length(M_Columns)-1); SET @M_sqltext = CONCAT('SELECT ',P_Row_Field,',',M_Columns, ' FROM ', P_From, ' WHERE ', P_Where, ' GROUP BY ', P_Row_Field, ' ORDER BY ', P_Row_Field); PREPARE M_stmt FROM @M_sqltext; EXECUTE M_stmt; END IF; END
Yukarıdaki bütün satırlardan ziyade, aşağıdaki parametrelerle prosedürümüz çağrılması yeterli olacaktı... Gözünü sevdiğim dinamik SQL'i :)
CALL pivotwizard('concat(ad,'''',soyad)','Month(tarih)', 'odeme', 'TABLOADI', '(year(tarih)=2011)')
Hiç yorum yok:
Yorum Gönder