MySQL`de Pivot Tablo Uygulaması Örneği

Örnek uygulamamızda 3 tane tablomuz olsun.

• İlçeler (Id, Ilce),
• Okullar (Id, IlceId, OkulAdi),
• OgrenciSayilari (Id, KurumId, Seviye, Erkek, Kiz)

Amacımız da ilçe bazında, sınıf seviyelerine göre öğrenci toplamlarını göstermek olsun.

Önce tablolarımızı oluşturalım:

CREATE TABLE `ilceler` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `Ilce` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `Ilce` (`Ilce`)
)
COLLATE='latin5_turkish_ci'
ENGINE=InnoDB;


CREATE TABLE `okullar` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `IlceId` INT(11) NOT NULL DEFAULT '0',
    `OkulAdi` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `IlceId` (`IlceId`)
)
COLLATE='latin5_turkish_ci'
ENGINE=InnoDB;


CREATE TABLE `ogrencisayilari` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `KurumId` INT(11) NOT NULL DEFAULT '0',
    `Seviye` INT(11) NOT NULL DEFAULT '0',
    `Erkek` INT(11) NOT NULL DEFAULT '0',
    `Kiz` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`Id`),
    INDEX `KurumId` (`KurumId`),
    INDEX `Seviye` (`Seviye`)
)
COLLATE='latin5_turkish_ci'
ENGINE=InnoDB;

Örnek dataları ekleyelim:

INSERT INTO `ilceler` (`Id`, `Ilce`) VALUES
    (1, 'A İlçesi'),
    (2, 'B İlçesi');

INSERT INTO `okullar` (`Id`, `IlceId`, `OkulAdi`) VALUES
    (1, 1, 'A İlkokulu'),
    (2, 2, 'B İlkokulu'),
    (3, 1, 'C İlkokulu');

INSERT INTO `ogrencisayilari` (`Id`, `KurumId`, `Seviye`, `Erkek`, `Kiz`) VALUES
    (1, 1, 1, 9, 7),
    (2, 1, 1, 6, 10),
    (3, 1, 1, 9, 7),
    (4, 1, 2, 12, 11),
    (5, 1, 2, 13, 10),
    (6, 1, 3, 16, 7),
    (7, 1, 3, 15, 10),
    (8, 1, 3, 10, 11),
    (9, 1, 4, 9, 10),
    (10, 1, 4, 6, 9),
    (11, 1, 4, 7, 9),
    (12, 2, 1, 7, 5),
    (13, 2, 2, 10, 7),
    (14, 2, 2, 9, 7),
    (15, 2, 3, 6, 11),
    (16, 2, 3, 5, 12),
    (17, 2, 4, 4, 7),
    (18, 2, 4, 3, 7),
    (19, 3, 1, 38, 2),
    (20, 3, 2, 0, 32),
    (21, 3, 3, 36, 0),
    (22, 3, 4, 0, 30);

(ogrencisayilari tablosundaki Seviye alanını, 1. sınıf, 2. sınıf, .. karşılığı olarak kullandım.)

Dilerseniz önce büyük resmi göstermek adına, sql sorgumuzu yazalım ve sonucu görelim:

SET SESSION group_concat_max_len = 4294967295;

SET @sql = NULL;

SELECT 
  GROUP_CONCAT(DISTINCT ' SUM(IF(Seviye=', Seviye, ', Erkek+Kiz, 0 )) as \'', Seviye,' .Sınıf\'')
INTO @sql from ogrencisayilari;

SET @sql = CONCAT(
  'SELECT i.Ilce İlçe, ', @sql, ' FROM okullar k 
  join ogrencisayilari o on k.Id = o.KurumId 
  join ilceler i on i.Id = k.IlceId
  GROUP BY i.Id');

PREPARE sorgu FROM @sql;
EXECUTE sorgu;
DEALLOCATE PREPARE sorgu;

Tam olarak istediğimiz sonucu aldık.

Yukarıdaki tablolardan bu sonucu nasıl aldık diye soracaksınız. Şimdi sql'imizi satır satır inceleyelim.

SET SESSION group_concat_max_len = 4294967295;

Group_Concat komutu için varsayılan maks. uzunluk değeri 1024'tür. Biz bu değeri daha büyük bir değerle değiştiriyoruz ki sorgumuz hata vemesin.

SET @sql = NULL;

Değişkenimizi tanımlıyoruz.

SELECT 
  GROUP_CONCAT(DISTINCT ' SUM(IF(Seviye=', Seviye, ', Erkek+Kiz, 0 )) as \'', Seviye,' .Sınıf\'')
INTO @sql from ogrencisayilari;

Concat komutu string birleştirmeyi sağlar. Group_Concat ise sorgudan dönen değerleri bir ayraç (varsayılan olarak virgül) ile ayırıp tek bir satır elde etmeyi sağlıyor.

Yukarıdaki sql cümleciği ile sorgu sonucunu @sql değişkenimizin içine atıyoruz.

Sorgu sonucunun çıktısını direkt görmek için şunu yazabilirsiniz:

SELECT 
  GROUP_CONCAT(DISTINCT ' SUM(IF(Seviye=', Seviye, ', Erkek+Kiz, 0 )) as \'', Seviye,' .Sınıf\'')
from ogrencisayilari;

SUM(IF(Seviye=1, Erkek+Kiz, 0 )) as '1 .Sınıf', SUM(IF(Seviye=2, Erkek+Kiz, 0 )) as '2 .Sınıf', SUM(IF(Seviye=3, Erkek+Kiz, 0 )) as '3 .Sınıf', SUM(IF(Seviye=4, Erkek+Kiz, 0 )) as '4 .Sınıf'

Evet! Sorgunun çıktısı tam olarak bu şekilde. :)

Sorgu satırlarına devam edelim.

SET @sql = CONCAT(
  'SELECT i.Ilce İlçe, ', @sql, ' FROM okullar k 
  join ogrencisayilari o on k.Id = o.KurumId 
  join ilceler i on i.Id = k.IlceId
  GROUP BY i.Id');

group_concat ile oluşturduğumuz satırı yukarıdaki sql ile birleştiriyoruz. (select, join, group ne iş yapar diye sormazsınız herhalde)

Dikkatinizi çektiyse; şu ana kadar direkt sql sorgusu yazmadık. Sql komutları içeren stringleri yazdık hep.

Peki bu stringler nasıl çalıştırılacak?

JavaScript'te eval komutu var. Bilmem hiç duydunuz mu?

var x = 10;
var y = 20;
var a = eval("x * y");

Yukarıdaki javascript kodları a=200 değerini verecektir. eval komutu, kendisine verdiğimiz string bilgiyi sanki o satırda stringin içindeki komut(lar) varmış gibi çalıştırabilmemizi sağlıyor.

Aynı işlemi MySql'de de yapabiliriz.

Tahmin edeceğiniz üzere sorgumuzun son 3 satırında bu işlemi görüyorsunuz.

PREPARE sorgu FROM @sql;
EXECUTE sorgu;
DEALLOCATE PREPARE sorgu;

Sorguyu hazırla, çalıştır, işi bitince de hafızadan at.

Şimdi başka sorgular yazabiliriz.

Örnek 1: okul bazında seviyelere göre kız ve erkek öğrenci toplamlarını alalım.

SET  SESSION group_concat_max_len = 4294967295;

SET @sql = NULL;

SELECT GROUP_CONCAT(
    DISTINCT 
     ' SUM(IF(Seviye=', Seviye, ', Erkek, 0 )) as \'', Seviye,' .Sınıf (Erkek)\',
       SUM(IF(Seviye=', Seviye, ', Kiz, 0 )) as \'', Seviye,' .Sınıf (Kız)\'')
INTO @sql from ogrencisayilari;

SET @sql = CONCAT(
'SELECT k.OkulAdi, ', @sql, 
' FROM okullar k 
join ogrencisayilari o on k.Id = o.KurumId 
GROUP BY k.Id');

PREPARE sorgu FROM @sql;
EXECUTE sorgu;
DEALLOCATE PREPARE sorgu;

Örnek 2: ilçe bazında seviyelere göre ortalama öğrenci sayılarını hesaplayalım.

SET  SESSION group_concat_max_len = 4294967295;

SET @sql = NULL;

SELECT GROUP_CONCAT(
    DISTINCT 
     ' avg(IF(Seviye=', Seviye, ', Erkek+Kiz, 0 )) 
      as \'', Seviye,' .Sınıf\'')
INTO @sql from ogrencisayilari;

SET @sql = CONCAT(
'SELECT i.Ilce İlçe, ', @sql, 
' FROM okullar k 
join ogrencisayilari o on k.Id = o.KurumId 
join ilceler i on i.Id = k.IlceId
GROUP BY i.Id,Seviye');

set @sql1 = null;
SELECT GROUP_CONCAT(
     DISTINCT 'sum(`', Seviye, ' .Sınıf`)  as \'', Seviye,' .Sınıf Ort.\'')
INTO @sql1 from ogrencisayilari;

set @sql2 = concat('select İlçe,',@sql1, ' from (', @sql, ') x group by İlçe');

PREPARE sorgu FROM @sql2;
EXECUTE sorgu;
DEALLOCATE PREPARE sorgu;

Vay be! Herhalde bu da pivot işinin nirvanası oluyor :)

Umarım bu yazı birilerinin işini görür.