İçeriğe geç
academia.sh
en
Giriş yap

Ders 07 / 20

Döndürme İşlemleri

Uzun biçimden geniş biçime koşullu toplamayla döndürme, FILTER yan tümcesi, ters döndürmenin iki yazımı ve sütun listesinin sorgu yazılırken bilinme zorunluluğu.

İçindekiler

Buraya kadarki her sorgunun sonucu, sütunları sorgu yazılırken belli olan bir tabloydu. Raporlarda ise sık sık tersi istenir: satırlardaki değerler sütun başlığına çıksın, her kitap türü ayrı bir sütun olsun, şubeler satırlarda kalsın.

Bu dönüşüme döndürme (pivot) denir. Kaynak biçim uzun biçimdir: her ölçüm bir satır, ayrım sütunları değer taşır. Hedef biçim geniş biçimdir: ayrım sütunundaki her değer bir sütuna dönüşür. Dönüşüm, SQL’in temel bir kuralıyla gerilim içindedir — sonucun sütun listesi, sorgu derlenirken bilinmek zorundadır.

Uzun Biçim

Başlangıç noktası, gruplamayla üretilen olağan özettir: şube ve tür ikilisi başına ödünç sayısı.

sqlite3 -box -header <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube TEXT);
CREATE TABLE kitap(id INTEGER PRIMARY KEY, baslik TEXT, tur TEXT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO uye VALUES (1,'Ayse','Kadikoy'),(2,'Burak','Kadikoy'),(3,'Ceren','Uskudar'),
  (4,'Deniz','Uskudar'),(5,'Emre','Besiktas'),(6,'Fatma','Besiktas'),
  (7,'Gokhan','Kadikoy'),(8,'Hale','Uskudar');
INSERT INTO kitap VALUES (1,'Kayip Zaman','roman'),(2,'Sessiz Ev','roman'),
  (3,'Sayilar Kurami','bilim'),(4,'Evrenin Yapisi','bilim'),(5,'Kisa Tarih','tarih'),
  (6,'Anadolu Notlari','tarih'),(7,'Siirler','siir'),(8,'Denemeler','deneme'),
  (9,'Yol Haritasi','roman'),(10,'Bilgi Kurami','felsefe');
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT u.sube, k.tur, COUNT(*) AS adet
FROM odunc o JOIN uye u ON u.id = o.uye_id JOIN kitap k ON k.id = o.kitap_id
GROUP BY u.sube, k.tur
ORDER BY u.sube, k.tur;
SQL

┌──────────┬─────────┬──────┐
│   sube   │   tur   │ adet │
├──────────┼─────────┼──────┤
│ Besiktas │ bilim   │ 1    │
│ Besiktas │ deneme  │ 1    │
│ Besiktas │ felsefe │ 1    │
│ Besiktas │ roman   │ 2    │
│ Besiktas │ tarih   │ 1    │
│ Kadikoy  │ bilim   │ 2    │
│ Kadikoy  │ deneme  │ 1    │
│ Kadikoy  │ roman   │ 4    │
│ Kadikoy  │ siir    │ 1    │
│ Kadikoy  │ tarih   │ 3    │
│ Uskudar  │ bilim   │ 3    │
│ Uskudar  │ felsefe │ 1    │
│ Uskudar  │ roman   │ 2    │
│ Uskudar  │ siir    │ 1    │
│ Uskudar  │ tarih   │ 1    │
└──────────┴─────────┴──────┘

Bu biçim veri işleme için elverişlidir: yeni bir tür eklendiğinde sorgu değişmez, yalnız satır sayısı artar. Okumak içinse elverişsizdir. Kadıköy ile Üsküdar’ın roman sayısını karşılaştırmak için gözün on beş satır arasında yukarı aşağı gezinmesi gerekir; ayrıca sıfır olan hücreler — Beşiktaş’ta şiir yok — hiç görünmez.

Koşullu Toplamayla Döndürme

Döndürmenin taşınabilir yazımı, her hedef sütun için bir koşullu toplama ifadesi yazmaktır. CASE ifadesi, satırın o sütuna ait olup olmadığına göre 1 ya da 0 üretir; SUM bunları toplar.

sqlite3 -box -header <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube TEXT);
CREATE TABLE kitap(id INTEGER PRIMARY KEY, baslik TEXT, tur TEXT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO uye VALUES (1,'Ayse','Kadikoy'),(2,'Burak','Kadikoy'),(3,'Ceren','Uskudar'),
  (4,'Deniz','Uskudar'),(5,'Emre','Besiktas'),(6,'Fatma','Besiktas'),
  (7,'Gokhan','Kadikoy'),(8,'Hale','Uskudar');
INSERT INTO kitap VALUES (1,'Kayip Zaman','roman'),(2,'Sessiz Ev','roman'),
  (3,'Sayilar Kurami','bilim'),(4,'Evrenin Yapisi','bilim'),(5,'Kisa Tarih','tarih'),
  (6,'Anadolu Notlari','tarih'),(7,'Siirler','siir'),(8,'Denemeler','deneme'),
  (9,'Yol Haritasi','roman'),(10,'Bilgi Kurami','felsefe');
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT u.sube,
       SUM(CASE WHEN k.tur = 'roman'   THEN 1 ELSE 0 END) AS roman,
       SUM(CASE WHEN k.tur = 'bilim'   THEN 1 ELSE 0 END) AS bilim,
       SUM(CASE WHEN k.tur = 'tarih'   THEN 1 ELSE 0 END) AS tarih,
       SUM(CASE WHEN k.tur = 'siir'    THEN 1 ELSE 0 END) AS siir,
       SUM(CASE WHEN k.tur = 'deneme'  THEN 1 ELSE 0 END) AS deneme,
       SUM(CASE WHEN k.tur = 'felsefe' THEN 1 ELSE 0 END) AS felsefe,
       COUNT(*) AS toplam
FROM odunc o JOIN uye u ON u.id = o.uye_id JOIN kitap k ON k.id = o.kitap_id
GROUP BY u.sube ORDER BY u.sube;
SQL

┌──────────┬───────┬───────┬───────┬──────┬────────┬─────────┬────────┐
│   sube   │ roman │ bilim │ tarih │ siir │ deneme │ felsefe │ toplam │
├──────────┼───────┼───────┼───────┼──────┼────────┼─────────┼────────┤
│ Besiktas │ 2     │ 1     │ 1     │ 0    │ 1      │ 1       │ 6      │
│ Kadikoy  │ 4     │ 2     │ 3     │ 1    │ 1      │ 0       │ 11     │
│ Uskudar  │ 2     │ 3     │ 1     │ 1    │ 0      │ 1       │ 8      │
└──────────┴───────┴───────┴───────┴──────┴────────┴─────────┴────────┘

On beş satır üçe indi ve boş hücreler sıfır olarak göründü. Bu, uzun biçimin veremediği bilgidir: Beşiktaş’ta şiir ödüncü yok ile şiir ödüncü bilinmiyor arasındaki fark artık okunur.

ELSE 0 bölümü ihmal edilirse eşleşmeyen satırlar boş değer üretir. SUM boş değerleri atladığı için sonuç çoğu durumda değişmez; ancak bir grubun tüm satırları eşleşmezse sütun sıfır yerine boş kalır. Sıfırın anlamlı olduğu raporlarda ELSE 0 yazılması ya da sonucun COALESCE ile sarılması gerekir.

Bu yazımın maliyeti tek bir taramadır: her satır bir kez okunur, altı ifade aynı satır üzerinde değerlendirilir. Aynı sonucu altı ayrı sorguyu birleştirerek üretmek, tabloyu altı kez taramak demektir.

FILTER Yan Tümcesi

Standart SQL, koşullu toplama için ayrı bir yazım tanımlar: toplama işlevinin ardına gelen FILTER (WHERE …) yan tümcesi, o işlevin yalnız koşulu sağlayan satırları görmesini sağlar. Amaç CASE ile aynıdır, okunurluk daha iyidir — koşul, sayılan şeyin yanında durur:

sqlite3 -box -header <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube TEXT);
CREATE TABLE kitap(id INTEGER PRIMARY KEY, baslik TEXT, tur TEXT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO uye VALUES (1,'Ayse','Kadikoy'),(2,'Burak','Kadikoy'),(3,'Ceren','Uskudar'),
  (4,'Deniz','Uskudar'),(5,'Emre','Besiktas'),(6,'Fatma','Besiktas'),
  (7,'Gokhan','Kadikoy'),(8,'Hale','Uskudar');
INSERT INTO kitap VALUES (1,'Kayip Zaman','roman'),(2,'Sessiz Ev','roman'),
  (3,'Sayilar Kurami','bilim'),(4,'Evrenin Yapisi','bilim'),(5,'Kisa Tarih','tarih'),
  (6,'Anadolu Notlari','tarih'),(7,'Siirler','siir'),(8,'Denemeler','deneme'),
  (9,'Yol Haritasi','roman'),(10,'Bilgi Kurami','felsefe');
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT u.sube,
       COUNT(*) FILTER (WHERE k.tur = 'roman') AS roman,
       COUNT(*) FILTER (WHERE k.tur = 'bilim') AS bilim,
       COUNT(*) FILTER (WHERE k.tur = 'tarih') AS tarih,
       COUNT(*) FILTER (WHERE o.iade IS NULL)  AS iade_edilmemis,
       COUNT(*) AS toplam
FROM odunc o JOIN uye u ON u.id = o.uye_id JOIN kitap k ON k.id = o.kitap_id
GROUP BY u.sube ORDER BY u.sube;
SQL

┌──────────┬───────┬───────┬───────┬────────────────┬────────┐
│   sube   │ roman │ bilim │ tarih │ iade_edilmemis │ toplam │
├──────────┼───────┼───────┼───────┼────────────────┼────────┤
│ Besiktas │ 2     │ 1     │ 1     │ 1              │ 6      │
│ Kadikoy  │ 4     │ 2     │ 3     │ 2              │ 11     │
│ Uskudar  │ 2     │ 3     │ 1     │ 2              │ 8      │
└──────────┴───────┴───────┴───────┴────────────────┴────────┘

Roman, bilim ve tarih sütunları önceki sonucun aynısıdır. Dördüncü sütun, döndürmenin yalnız bir ayrım sütunuyla sınırlı olmadığını gösterir: iade_edilmemis, tür ile ilgisi olmayan bağımsız bir koşuldur ve aynı taramada hesaplanmıştır. Farklı ölçütlerin aynı satırda yan yana getirilmesi, koşullu toplamanın döndürmeden bağımsız asıl yararıdır.

FILTER yan tümcesi standarttadır ancak her motorda bulunmaz; desteklenmediği yerde CASE yazımı her zaman çalışır.

Ters Yön: Sütundan Satıra

Ters işlem, geniş biçimdeki bir tabloyu uzun biçime çevirir. İhtiyaç, dış bir kaynaktan gelen geniş özetin normalleştirilmiş bir tabloya aktarılmasında ya da tür sayısı değiştikçe bozulmayan bir sorgu yazılmasında doğar.

Taşınabilir yazım, sütun adlarını bir değer listesine dönüştürüp kaynak tabloyla çapraz birleştirmektir:

sqlite3 -box -header <<'SQL'
CREATE TABLE ozet(sube TEXT PRIMARY KEY, roman INT, bilim INT, tarih INT);
INSERT INTO ozet VALUES ('Besiktas',2,1,1),('Kadikoy',4,2,3),('Uskudar',2,3,1);

WITH turler(tur) AS (VALUES ('roman'),('bilim'),('tarih'))
SELECT o.sube, t.tur,
       CASE t.tur WHEN 'roman' THEN o.roman
                  WHEN 'bilim' THEN o.bilim
                  WHEN 'tarih' THEN o.tarih END AS adet
FROM ozet o CROSS JOIN turler t
ORDER BY o.sube, t.tur;
SQL

┌──────────┬───────┬──────┐
│   sube   │  tur  │ adet │
├──────────┼───────┼──────┤
│ Besiktas │ bilim │ 1    │
│ Besiktas │ roman │ 2    │
│ Besiktas │ tarih │ 1    │
│ Kadikoy  │ bilim │ 2    │
│ Kadikoy  │ roman │ 4    │
│ Kadikoy  │ tarih │ 3    │
│ Uskudar  │ bilim │ 3    │
│ Uskudar  │ roman │ 2    │
│ Uskudar  │ tarih │ 1    │
└──────────┴───────┴──────┘

Çapraz birleştirme üç şubeyi üç türle eşleştirerek dokuz satır üretti; CASE her satırda doğru sütunu seçti. Kaynak tablo bir kez okunur.

Aynı sonuç, her sütun için bir sorgu yazıp UNION ALL ile birleştirerek de üretilir:

sqlite3 -box -header <<'SQL'
CREATE TABLE ozet(sube TEXT PRIMARY KEY, roman INT, bilim INT, tarih INT);
INSERT INTO ozet VALUES ('Besiktas',2,1,1),('Kadikoy',4,2,3),('Uskudar',2,3,1);

SELECT sube, 'roman' AS tur, roman AS adet FROM ozet
UNION ALL SELECT sube, 'bilim', bilim FROM ozet
UNION ALL SELECT sube, 'tarih', tarih FROM ozet
ORDER BY sube, tur;
SQL

┌──────────┬───────┬──────┐
│   sube   │  tur  │ adet │
├──────────┼───────┼──────┤
│ Besiktas │ bilim │ 1    │
│ Besiktas │ roman │ 2    │
│ Besiktas │ tarih │ 1    │
│ Kadikoy  │ bilim │ 2    │
│ Kadikoy  │ roman │ 4    │
│ Kadikoy  │ tarih │ 3    │
│ Uskudar  │ bilim │ 3    │
│ Uskudar  │ roman │ 2    │
│ Uskudar  │ tarih │ 1    │
└──────────┴───────┴──────┘

Sonuçlar aynı, okunma ve maliyet farklı. Birleşimli yazım her sütun için tabloyu bir kez tarar: üç sütun, üç tarama. Küçük özet tablolarında bu önemsizdir; kaynak büyükse çapraz birleştirmeli yazım tercih edilir.

Sütun Listesi Neden Sabittir

Her iki yönde de tür adları sorgunun metnine elle yazıldı. Bunun nedeni bir eksiklik değil, ilişkisel modelin bir sonucudur: bir sorgunun sonucu bir bağıntıdır ve bağıntının başlığı — sütun adları ve tipleri — sorgu çalıştırılmadan bellidir. Veriye bakarak sütun üretmek bu tanımı bozar.

Bunun pratik karşılığı şudur: yeni bir kitap türü eklendiğinde geniş biçimli sorgu kendiliğinden bir sütun kazanmaz; sorgunun güncellenmesi gerekir. Sütun listesini veriden üretmek isteyen, sorgu metnini uygulama tarafında birleştirip motora göndermek zorundadır. Bu yol açıktır ama iki bedeli vardır: metin birleştirmeyle üretilen sorgular enjeksiyon yüzeyi açar ve her farklı sütun listesi ayrı bir sorgu metni ürettiği için plan önbelleğinden yararlanmaz. Bu iki konu, kursun Sorgu Başarımı konusundaki Dinamik SQL Riskleri dersinde ele alınacaktır.

Bazı motorlar PIVOT ya da benzeri bir anahtar sözcük sunar. Bu yazımlar standart SQL’in parçası değildir ve sütun listesini yine sorgu yazılırken ister; yaptıkları, koşullu toplamayı kısaltmaktır.

Özet

  • Uzun biçim veri işlemeye, geniş biçim okumaya elverişlidir; döndürme bu iki biçim arasındaki dönüşümdür.
  • Taşınabilir döndürme yazımı, hedef sütun başına bir koşullu toplama ifadesidir ve tek taramada tamamlanır; ELSE 0 yazılmazsa hiç eşleşmeyen gruplarda sütun boş kalır.
  • FILTER (WHERE …) yan tümcesi aynı işi daha okunur yazar ve birbirinden bağımsız ölçütlerin aynı satırda toplanmasına izin verir.
  • Ters döndürme, sütun adlarını değer listesine çevirip çapraz birleştirerek tek taramada ya da UNION ALL ile sütun başına bir tarama yaparak yazılır.
  • Sonuç sütunlarının sorgu yazılırken bilinmesi zorunludur; sütun listesini veriden üretmek sorgu metninin uygulama tarafında birleştirilmesini gerektirir.

Sonraki Adım

Bu konu boyunca yazılan her sorgu tek bir deyimdi ve tek bir soruya yanıt verdi. Veri değiştiren işler ise çoğu zaman tek deyime sığmaz: bir kitabın ödünç verilmesi, ödünç kaydının açılmasını ve kitabın durumunun güncellenmesini birlikte gerektirir. İkisinden biri yapılıp diğeri yapılmazsa veri tutarsız kalır. Sonraki konu, birden çok deyimi bölünmez tek bir birim hâline getiren işlemleri ele alacak; ilk ders işlemin başlatılması, kesinleştirilmesi ve geri alınmasıyla başlayacak.

İlerlemeni kaydetmek ve not almak için Giriş yap

Notlarım

Not almak için giriş yapmalısın.

Aramak için yazmaya başlayın.

↑↓ ↵ Esc gezin · aç · kapat