16 Aralık 2011 Cuma

MySQL'de Pivot Tablo Nasıl Oluşturulur?

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..



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: