Birleştirme ve İlişkilendirme

Bir önceki konu satırların içini açtı ve bitirdiğinde küme temizdi: eksikler işaretlenmiş, yinelemeler kaldırılmış, tipler dönüştürülmüş, kategoriler ve metinler düzeltilmişti. Yine de küme çözümlemeye hazır değil, çünkü hâlâ soruya göre değil kaynağa göre biçimli. Abone bilgisi bir tabloda, sayaç okumaları başka bir tabloda, saha ziyaretleri üçüncü bir tablodadır; hiçbir soru bu üç tablonun birine tek başına sorulamaz.

Bu konu kümeyi soruya göre yeniden biçimlendirir ve ilk adım tabloları yan yana getirmektir. Adımın ölçüsü şudur: birleştirme satır sayısını değiştirir ve değişimin yönü tek başına bilgi vermez. Satır sayısı düşebilir, artabilir, aynı kalabilir; üçü de hem doğru hem de sessiz bir hatanın işareti olabilir. Bu yüzden bu derste giren, çıkan ve aradaki farkın nereye gittiği kalem kalem yazılır.

• VD1. Küme, önceki konudan çıkan temizlenmiş halidir ve kurgudur: abone tablosu bir satırda bir abone, okuma tablosu bir satırda bir dönem okuması, ziyaret tablosu bir satırda bir saha ziyareti taşır. Üç tablo da node ile modellenir; hiçbir sorgu arayüzü taklit edilmez.
• VD2. Birleştirme (join), iki tablonun satırlarını bir anahtar üzerinden yan yana getirir. Veritabanları kursunda tanıtılan birleşim (union) ise iki tablonun satırlarını alt alta ekler ve sütun düzenini değiştirmez: biri satır ekler, öteki sütun ekler ve satır sayısını anahtarın eşleşme sayısına göre değiştirir. Bu derste ölçülen ikincisidir.
• VD3. İç birleştirme yalnız eşleşen satırları çıkarır; iki taraftaki eşleşmeyenler düşer. Dış birleştirme sol tablonun eşleşmeyen satırını boş bir sağ tarafla korur. İkisi arasındaki fark bir tercih değil, bir sayıdır.
• VD4. Anahtarın tanesi birleştirmenin kendisinden ayrı bir karardır. Sol tablonun anahtarı sağ tabloda birden çok satıra karşılık geliyorsa sol satır o kadar kez çıkar; buna çoğalma denir.
• VD5. Bir birleştirme aynı anda hem düşürebilir hem çoğaltabilir. Bu durumda çıkan satır sayısı girenden az olsa bile bir eleme yapılmamıştır ve toplamlar hem eksik hem fazla sayılmış olur.

İç Birleştirme Kimi Düşürüyor

// birlestir.mjs — MODELDIR: temizlemeden cikan uc tablo ve anahtar uzerinden
// birlestirme. Veri kumesi kurgudur; hicbir sorgu arayuzu taklit edilmez.
export const TOHUM = 20260218, HAM = 1400, DONEM = ["2026-01", "2026-02", "2026-03"];
const karistir = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const uretec = (t) => { let s = karistir(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const ayrik = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
export const BOLGE = [["kuzey", 0.28, 21], ["guney", 0.22, 17], ["dogu", 0.18, 26],
  ["bati", 0.14, 14], ["merkez", 0.18, 23]].map(([ad, pay, taban]) => ({ ad, pay, taban }));

// Abone tablosu: temizlemede elenenler disarida kaldi, kalan her satir bir abone.
export const ABONE = [], ELENEN = [];
for (let i = 0; i < HAM; i++) {
  const r = uretec(TOHUM + i), b = BOLGE[ayrik(r(), BOLGE.map((x) => x.pay))];
  const k = { abone_no: 10001 + i, bolge: b.ad, taban: b.taban,
    hane_kisi: ayrik(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05]),
    memnuniyet: ayrik(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1 };
  (r() < 0.046 ? ELENEN : ABONE).push(k);
}
// Okuma tablosu: bazi okumalar elenen abonelere aittir, bazi aboneler hic okuma
// tasimaz, bazi abone+donem cifti iki farkli degerle iki kez gecer.
export const OKUMA = [];
let s = 0;
for (const k of [...ABONE, ...ELENEN]) {
  const r = uretec(TOHUM + 7000 + k.abone_no), adet = ayrik(r(), [0.05, 0.12, 0.21, 0.62]);
  for (let d = 0; d < adet; d++) {
    const t = r() < 0.038 ? 0 : Math.round(k.taban * Math.exp((r() + r() + r() - 1.5) * 0.62)
      * (1 - d * 0.05) * 100) / 100;
    OKUMA.push({ satir_no: ++s, abone_no: k.abone_no, donem: DONEM[d], tuketim_m3: t });
    if (r() < 0.028) OKUMA.push({ satir_no: ++s, abone_no: k.abone_no, donem: DONEM[d],
      tuketim_m3: Math.round(t * 1.02 * 100) / 100 });
  }
}
// Ziyaret tablosu: bir satir bir saha ziyareti; doneme bagli degildir.
export const ZIYARET = [];
for (const k of ABONE) {
  const r = uretec(TOHUM + 31000 + k.abone_no), adet = ayrik(r(), [0.58, 0.27, 0.11, 0.04]);
  for (let z = 0; z < adet; z++) ZIYARET.push({ abone_no: k.abone_no,
    sonuc: ["olumlu", "tekrar", "erisilemedi"][ayrik(r(), [0.60, 0.28, 0.12])] });
}

// Sol tablonun her satiri, anahtari esit olan sag satirlarla yan yana gelir.
// "dis" secenegi eslesmeyen sol satiri bos bir sag tarafla korur.
export const birlestir = (sol, sag, ka, kb, dis = false) => {
  const dizin = new Map();
  for (const r of sag) { const k = kb(r);
    if (!dizin.has(k)) dizin.set(k, []); dizin.get(k).push(r); }
  const cikti = [], eslesen = new Set();
  let bosSol = 0;
  for (const l of sol) {
    const e = dizin.get(ka(l));
    if (!e) { bosSol++; if (dis) cikti.push({ sol: l, sag: null }); continue; }
    for (const r of e) { cikti.push({ sol: l, sag: r }); eslesen.add(r); }
  }
  return { cikti, bosSol, bosSag: sag.length - eslesen.size };
};
export const gen = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");

if (import.meta.url === `file://${process.argv[1]}`) {
  const no = (x) => x.abone_no;
  console.log(`model (tohum ${TOHUM}): abone ${ABONE.length} satir, okuma ` +
    `${OKUMA.length} satir, ziyaret ${ZIYARET.length} satir`);
  const y = gen([28, 11, 11, 9, 12, 12]);
  console.log("\n" + y(["birlestirme", "giren sol", "giren sag", "cikan", "bos sol",
    "bos sag"]));
  const B = [
    ["B1 okuma <- abone (ic)", OKUMA, ABONE, false],
    ["B2 abone <- okuma (ic)", ABONE, OKUMA, false],
    ["B3 abone <- okuma (dis)", ABONE, OKUMA, true],
  ];
  const sonuc = {};
  for (const [ad, sol, sag, dis] of B) {
    const r = birlestir(sol, sag, no, no, dis);
    sonuc[ad.slice(0, 2)] = r;
    console.log(y([ad, sol.length, sag.length, r.cikti.length, r.bosSol, r.bosSag]));
  }
  console.log(`\nic birlestirme ${OKUMA.length} okumadan ${sonuc.B1.bosSol} tanesini ` +
    `dusurur; ayni birlestirme ${ABONE.length} abonenin ${sonuc.B1.bosSag} tanesini ` +
    `cikti tablosunda hic gostermez`);
  console.log(`dis birlestirme o ${sonuc.B3.bosSol} aboneyi bos okumayla korur: ` +
    `${sonuc.B2.cikti.length} yerine ${sonuc.B3.cikti.length} satir`);
  console.log(`iki birlestirmede de hicbir uyari uretilmedi`);
}

model (tohum 20260218): abone 1329 satir, okuma 3471 satir, ziyaret 818 satir

birlestirme                   giren sol  giren sag    cikan     bos sol     bos sag
B1 okuma <- abone (ic)             3471       1329     3302         169          69
B2 abone <- okuma (ic)             1329       3471     3302          69         169
B3 abone <- okuma (dis)            1329       3471     3371          69         169

ic birlestirme 3471 okumadan 169 tanesini dusurur; ayni birlestirme 1329 abonenin 69 tanesini cikti tablosunda hic gostermez
dis birlestirme o 69 aboneyi bos okumayla korur: 3302 yerine 3371 satir
iki birlestirmede de hicbir uyari uretilmedi

Üç satır aynı iki tabloyu okuyor ve üç farklı sayı veriyor. B1 ile B2 yalnız hangi tablonun sol olduğunda ayrılır ve ikisi de 3.302 satır çıkarır: iç birleştirmede taraf sırası çıkan satırı değiştirmez, yalnız eşleşmeyenin hangi sütunda raporlandığını değiştirir. B3 aynı anahtarla 69 satır fazla verir, çünkü hiç okuma taşımayan 69 aboneyi boş bir okuma ile korur.

Kaybın hesabı iki kalemdir. 169 okuma bir abone numarası taşıyor ama o numara abone tablosunda yok; bunlar temizlemede elenen 71 kaydın okumalarıdır. 69 abone ise gerçek bir abonedir, sayacı o dönemde hiç okunmamıştır ve iç birleştirmenin çıktısında hiç görünmez. İkincisi bu dersin sessiz kaybıdır: bir hata mesajı yoktur, bir boş hücre bile yoktur, satır yalnızca hiç üretilmemiştir. “Ortalama tüketim” sorusu iç birleştirmenin çıktısına sorulursa, hiç okunmamış 69 abone cevaba katılmaz ve bu 69 abonenin okunmamış olması rastgele değildir.

Ölçü buradan çıkıyor: iç birleştirme bir eleme kararıdır ve bir soruya cevap veren tabloyu hazırlarken alınmış olur. Dış birleştirme kararı geri okunabilir kılar, çünkü 69 boş satır çıktıda görünür ve sayılabilir.

İki taraf ayrı ayrı raporlanmadığında bu ayrım görünmez. Tek bir “3.302 satır birleştirildi” cümlesi hem 169 okumanın hem 69 abonenin düştüğünü aynı anda gizler; oysa iki kalem birbirinden bağımsızdır ve farklı sebeplerle oluşmuştur. Birincisi önceki adımın, yani temizlemenin bıraktığı bir izdir: elenen 71 kaydın okumaları öksüz kalmıştır. İkincisi ise verinin kendi eksikliğidir ve temizleme onu göremezdi, çünkü okuma tablosunda olmayan bir satırın eksik olduğu ancak abone tablosuyla karşılaştırınca anlaşılır. Bir birleştirme raporu bu yüzden tek sayı değil, en az dört sayı taşır: iki tarafın giren satırı, çıkan satır ve iki taraftaki eşleşmeyen.

Anahtarın Tanesi Yanlışsa

// tane.mjs — ayni anahtar, yanlis tane: okuma satiri donem tasir, ziyaret satiri
// tasimaz. Ikisi yalniz abone_no uzerinden birlestirilirse ne dusuyor, ne cogaliyor.
import { ABONE, OKUMA, ZIYARET, birlestir, gen } from "./birlestir.mjs";

const no = (x) => x.abone_no;
const ic = birlestir(OKUMA, ABONE, no, no).cikti.map((x) => x.sol);
const zSayi = new Map();
for (const z of ZIYARET) zSayi.set(z.abone_no, (zSayi.get(z.abone_no) ?? 0) + 1);
const oz = birlestir(ic, ZIYARET, no, no);
const dusen = ic.filter((o) => !zSayi.has(o.abone_no)).length;
const birebir = ic.filter((o) => zSayi.get(o.abone_no) === 1).length;
const cogalan = ic.filter((o) => (zSayi.get(o.abone_no) ?? 0) > 1).length;

const y = gen([34, 10, 10]);
console.log(y(["okuma x ziyaret (abone_no uzerinden)", "okuma", "satir"]));
console.log(y(["giren", ic.length, ic.length]));
console.log(y(["ziyareti yok: dustu", dusen, 0]));
console.log(y(["tek ziyaret: birebir kaldi", birebir, birebir]));
console.log(y(["cok ziyaret: cogaldi", cogalan, oz.cikti.length - birebir]));
console.log(y(["cikan", "", oz.cikti.length]));

const T = (a, f) => a.reduce((x, o) => x + f(o), 0);
const oncekiT = T(ic, (o) => o.tuketim_m3);
const sonrakiT = T(oz.cikti, (x) => x.sol.tuketim_m3);
console.log(`\ntoplam tuketim ${oncekiT.toFixed(1)} m3 iken ${sonrakiT.toFixed(1)} m3 oldu; ` +
  `${ic.length} satirdan ${oz.cikti.length} satira inildi ama bu bir eleme degil`);
console.log(`${dusen} okuma hic sayilmadi, ${cogalan} okuma ` +
  `${oz.cikti.length - birebir} kez sayildi`);

// Dogru tane: bir abonenin bir donemi bir satirdir. Ayni cift iki kez geciyorsa
// temizleme onu kesin eslesme saymamistir, cunku iki okuma degeri farklidir.
const tekil = new Map();
for (const o of ic) { const k = `${o.abone_no}|${o.donem}`; if (!tekil.has(k)) tekil.set(k, o); }
const tekilT = T([...tekil.values()], (o) => o.tuketim_m3);
console.log(`\nabone+donem cifti: ${ic.length} satirda ${tekil.size} tekil cift var; ` +
  `${ic.length - tekil.size} satir ayni donemi ikinci kez sayiyor`);
console.log(`toplam ${oncekiT.toFixed(1)} m3 yerine ${tekilT.toFixed(1)} m3; ` +
  `fark ${(oncekiT - tekilT).toFixed(1)} m3`);
console.log(`giren 3471 okuma -> cikan ${tekil.size} satir: 169 eslesmeyen, ` +
  `${ic.length - tekil.size} cift gecen, 69 abone hic gorunmeyen`);

okuma x ziyaret (abone_no uzerinden)     okuma     satir
giren                                   3302      3302
ziyareti yok: dustu                     1901         0
tek ziyaret: birebir kaldi               913       913
cok ziyaret: cogaldi                     488      1134
cikan                                             2047

toplam tuketim 65185.6 m3 iken 40374.1 m3 oldu; 3302 satirdan 2047 satira inildi ama bu bir eleme degil
1901 okuma hic sayilmadi, 488 okuma 1134 kez sayildi

abone+donem cifti: 3302 satirda 3199 tekil cift var; 103 satir ayni donemi ikinci kez sayiyor
toplam 65185.6 m3 yerine 63060.6 m3; fark 2124.9 m3
giren 3471 okuma -> cikan 3199 satir: 169 eslesmeyen, 103 cift gecen, 69 abone hic gorunmeyen

Bu tablo dersin asıl bulgusudur. Okuma tablosu bir abone ve dönem taşır, ziyaret tablosu yalnız bir abone taşır. İkisi yalnız abone_no üzerinden birleştirilirse anahtarın tanesi uyuşmaz: bir abonenin üç okuması, o abonenin iki ziyaretiyle altı satır üretir.

Sonuçta çıkan satır sayısı girenden azdır — 3.302’den 2.047’ye — ve bu tek başına bakıldığında bir eleme gibi görünür. Değildir. Aynı işlem içinde iki karşıt şey oldu: ziyareti hiç olmayan abonelerin 1.901 okuması düştü, birden çok ziyareti olan abonelerin 488 okuması 1.134 satıra çoğaldı. Net değişim negatif olduğu için çoğalma sayı üzerinde görünmez. Toplam tüketim 65.185,6 m³’ten 40.374,1 m³’e iner; bu, “tüketim düştü” diye okunabilecek bir sayıdır ve tek bir okuma değişmemiştir.

İkinci ölçüm doğru anahtarla bile tanenin sorulması gerektiğini gösteriyor. İç birleştirmenin verdiği 3.302 satırda yalnız 3.199 tekil abone ve dönem çifti vardır; 103 satır aynı dönemi ikinci kez sayar. Bunlar temizlemenin kesin eşleşme kuralına takılmamıştır, çünkü iki kaydın tüketim değeri birbirinden farklıdır. Toplama etkisi 2.124,9 m³’tür, yani toplamın yüzde üçünden fazlası.

Dersin kapanış hesabı son satırdadır: 3.471 okuma girdi, 3.199 satır çıktı; 169 kayıt eşleşmedi, 103 kayıt aynı dönemi ikinci kez saydığı için düşürüldü, 69 abone çıktıda hiç görünmedi. Üç kalem de yazıldığında birleştirme bir hazırlama adımıdır; yazılmadığında yalnız bir değişikliktir.

Özet

• Birleştirme satırları anahtar üzerinden yan yana getirir ve sütun ekler; birleşim satırları alt alta ekler. Birleştirmede satır sayısı anahtarın eşleşme sayısına göre değişir.
• İç birleştirme 3.471 okuma ile 1.329 aboneden 3.302 satır çıkarır: 169 okuma eşleşmedi, 69 abone çıktıda hiç görünmedi. Dış birleştirme o 69 aboneyi koruyarak 3.371 satır verir.
• Hiç okunmamış 69 abonenin sessizce düşmesi bir hata üretmez ve “ortalama tüketim” sorusunu bu 69 abone olmadan cevaplar.
• Anahtarın tanesi uyuşmadığında bir birleştirme aynı anda düşürür ve çoğaltır: 1.901 okuma hiç sayılmaz, 488 okuma 1.134 kez sayılır, çıkan satır girenden az olduğu için çoğalma görünmez.
• Doğru anahtarla bile tane sorulur: 3.302 satırda 3.199 tekil abone ve dönem çifti vardır ve 103 tekrar toplamı 2.124,9 m³ şişirir.

Sonraki Adım

Tablolar yan yana geldi ve elde bir satırda bir abonenin bir dönem okumasını taşıyan tek bir küme var. Bu biçim her soruya uygun değil. “Bir abonenin üç dönemi arasındaki değişim” sorusu üç satırı karşılaştırmayı gerektirir; oysa aynı bilgi, her dönemi bir sütun yapan bir biçimde tek satırda durabilir. İki biçim arasında gidip gelmek görünüşte biçimsel bir işlemdir, ama bir yönde bilgi kaybolabilir ve kayıp geri dönüşte fark edilir. Sonraki ders bu iki biçimi tanımlar ve dönüşümün kayıpsız olup olmadığını, geri dönüldüğünde özgün kümeye ulaşılıp ulaşılmadığını sayarak ölçer.