Veritabanından Veri Çekme

Bir önceki ders veriyi dosyadan aldı ve ayrıştırma kararının 142 satırı sessizce düşürdüğünü gösterdi. Dosyanın bir kolaylığı vardı: bir satır bir kayıttı, en azından tırnak doğru okunduğunda. Veritabanı bu kolaylığı vermez. Bir sorgu satır döndürür ve o satırın neyin bir kaydı olduğu tabloların yapısından değil, sorgunun yazımından çıkar.

Bu dersin ölçüsü budur: çözümlemenin sorduğu birim ile sorgunun döndürdüğü satırın birimi aynı mı. İlişkisel model, anahtar ve sorgu yazımı önkoşuldur ve burada tekrarlanmaz; ölçülen tek şey satırın birimi ve o birim kaydığında satır sayısının nereye gittiğidir.

• VT6. Depo kurgudur ve node dizileriyle modellenir. Birleştirme küçük bir yardımcıyla yapılır; sorgu sözdizimi ve iyileştirme bu kursun konusu değildir. Modeldir.
• VT7. Üç tablo vardır: 1.400 satırlık abone, abone-dönem başına bir satır tutan 1.877 satırlık okuma ve abone başına sıfır ile üç arasında satır tutan 1.328 satırlık saha ziyareti.
• VT8. Çözümleme birimi açıkça yazılır: sorulan soru abone başına ortalama tüketimdir, yani raporun satırı bir abonedir.
• VT9. İki ortalamanın aynı sayılması için eşik 0,05 m³’tür, çünkü okumalar bir ondalıkla tutuluyor ve bunun altındaki bir fark yalnızca yuvarlamadan gelebilir.

Deponun Üç Tablosu

// depo.mjs — bolgesel olcum agindan kurgu bir iliskisel depo. MODELDIR: tablolar
// node dizileridir, birlestirme kucuk bir yardimciyla yapilir. Sorgu sozdizimi bu
// kursun konusu degil; olculen sey donen satirin hangi birimin kaydi oldugudur.
export const TOHUM = 4711;
export const BOLGE = ["kuzey", "doğu", "merkez", "güney", "batı"];
const uretec = (t) => { let s = t >>> 0;
  return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; };

const r = uretec(TOHUM);
// abone: cozumleme biriminin kendisi. Her abone bir kez gecer.
export const ABONE = Array.from({ length: 1400 }, (_, i) => ({
  abone_no: 10000 + i, bolge: BOLGE[i % 5], tur: i % 7 === 0 ? "ticari" : "mesken" }));
// okuma: abone-donem cifti basina bir satir. 1400 abonenin ilki, 477 abonenin ikinci donemi.
export const OKUMA = [];
for (let i = 0; i < 1400; i++)
  OKUMA.push({ abone_no: 10000 + i, donem: "2024-01", okuma_m3: 5 + Math.floor(r() * 321) / 10 });
for (let i = 0; i < 477; i++)
  OKUMA.push({ abone_no: 10000 + i, donem: "2024-02", okuma_m3: 5 + Math.floor(r() * 321) / 10 });
// ziyaret: saha ziyaretleri. Bir abonenin sifir, bir, iki ya da uc ziyareti olabilir.
export const ZIYARET = [];
for (const a of ABONE) {
  const p = r(), n = p < 0.41 ? 0 : p < 0.71 ? 1 : p < 0.91 ? 2 : 3;
  for (let j = 0; j < n; j++)
    ZIYARET.push({ abone_no: a.abone_no, sira: j + 1, tur: j === 0 ? "okuma" : "kontrol" });
}

// Ic birlestirme: sol tablonun her satiri, sag tabloda anahtari eslesen her satirla
// bir kez eslesip cikar. Eslesme yoksa sol satir hic cikmaz.
export const ic = (sol, sag, anahtar) => {
  const dizin = new Map();
  for (const s of sag) { const k = s[anahtar];
    if (!dizin.has(k)) dizin.set(k, []); dizin.get(k).push(s); }
  return sol.flatMap((x) => (dizin.get(x[anahtar]) ?? []).map((y) => ({ ...x, ...y })));
};
// Sol birlestirme: eslesme yoksa sol satir bir kez, sag alanlari bos olarak cikar.
export const solB = (sol, sag, anahtar) => {
  const dizin = new Map();
  for (const s of sag) { const k = s[anahtar];
    if (!dizin.has(k)) dizin.set(k, []); dizin.get(k).push(s); }
  return sol.flatMap((x) => { const e = dizin.get(x[anahtar]);
    return e ? e.map((y) => ({ ...x, ...y })) : [{ ...x }]; });
};

if (import.meta.url === `file://${process.argv[1]}`) {
  const dagilim = new Map();
  for (const a of ABONE) {
    const n = ZIYARET.filter((z) => z.abone_no === a.abone_no).length;
    dagilim.set(n, (dagilim.get(n) ?? 0) + 1);
  }
  console.log(`tohum ${TOHUM}; abone ${ABONE.length} satir, okuma ${OKUMA.length} satir, ` +
    `ziyaret ${ZIYARET.length} satir`);
  console.log(`okuma tablosunda farkli abone ${new Set(OKUMA.map((x) => x.abone_no)).size}, ` +
    `farkli abone-donem ${new Set(OKUMA.map((x) => x.abone_no + x.donem)).size}`);
  console.log("abone basina ziyaret sayisi dagilimi:");
  for (const n of [...dagilim.keys()].sort())
    console.log(`  ${n} ziyaret: ${dagilim.get(n)} abone`);
  console.log(`okuma toplami ${OKUMA.reduce((s, x) => s + x.okuma_m3, 0).toFixed(1)} m3`);
}

tohum 4711; abone 1400 satir, okuma 1877 satir, ziyaret 1328 satir
okuma tablosunda farkli abone 1400, farkli abone-donem 1877
abone basina ziyaret sayisi dagilimi:
  0 ziyaret: 570 abone
  1 ziyaret: 453 abone
  2 ziyaret: 256 abone
  3 ziyaret: 121 abone
okuma toplami 38690.7 m3

Üç tablo üç farklı birimde tutuluyor. Abone tablosunun satırı bir abonedir ve abone numarası orada tekildir. Okuma tablosunun satırı bir abone-dönem çiftidir; 1.400 abonenin 477’sinin ikinci dönem okuması da girildiği için tablo 1.877 satırdır ve abone numarası orada tekil değildir. Ziyaret tablosunun satırı bir saha ziyaretidir ve bir abone bu tabloda hiç geçmeyebileceği gibi üç kez de geçebilir.

Dağılım tablosu asıl riski gösteriyor. 1.400 abonenin 570’inin hiç ziyareti yok, 121’inin üç ziyareti var. Bir birleştirme bu iki uçtan birincisini siler, ikincisini üçe katlar. İki etki de sorgunun döndürdüğü satır sayısında görünür ve hiçbiri bir hata iletisi üretmez.

Satırın Birimi Kayınca

// sorgu.mjs — uc sorgu bicimi ayni depodan uc farkli satir kumesi dondurur.
// Olculen sey satirin dogru olup olmadigi degil, satirin HANGI BIRIMIN kaydi
// oldugudur: cozumleme abone-donem soruyorsa, sonuc kumesinde her abone-donem
// cifti tam bir kez gecmelidir.
import { ABONE, OKUMA, ZIYARET, ic, solB } from "./depo.mjs";

const S1 = ic(OKUMA, ABONE, "abone_no");            // okuma + abone
const S2 = ic(S1, ZIYARET, "abone_no");             // + ziyaret, ic birlestirme
const S3 = solB(S1, ZIYARET, "abone_no");           // + ziyaret, sol birlestirme

// Bir sonuc kumesinde cozumleme birimi (abone-donem) kac kez geciyor.
const birim = (kume) => {
  const say = new Map();
  for (const s of kume) { const k = s.abone_no + s.donem; say.set(k, (say.get(k) ?? 0) + 1); }
  return { enCok: Math.max(...say.values()),
    dusen: OKUMA.length - say.size, cogalan: kume.length - say.size };
};
const toplam = (kume) => kume.reduce((t, s) => t + s.okuma_m3, 0);
const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");
const y1 = y([24, 8, 8, 8, 9, 8, 12, 8]);
console.log(y1(["sorgu", "giren", "cikan", "dusen", "cogalan", "en cok", "toplam m3", "oran"]));
for (const [ad, k] of [["S1 okuma + abone", S1], ["S2 + ziyaret (ic)", S2],
  ["S3 + ziyaret (sol)", S3]]) {
  const b = birim(k);
  console.log(y1([ad, OKUMA.length, k.length, b.dusen, b.cogalan, b.enCok,
    toplam(k).toFixed(1), (toplam(k) / toplam(S1)).toFixed(2)]));
}
const ziyaretli = new Set(ZIYARET.map((z) => z.abone_no));
const sifir = ABONE.filter((a) => !ziyaretli.has(a.abone_no));
console.log(`\nziyareti olmayan ${sifir.length} abonenin ` +
  `${OKUMA.filter((o) => !ziyaretli.has(o.abone_no)).length} okuma satiri S2'de hic gorunmuyor; ` +
  `sorgu hata vermedi, satir sayisi tek isarettir`);
const b2 = birim(S2), etkilenen = b2.dusen + b2.cogalan;
console.log(`S2'de ${b2.dusen} satir dustu ve ${b2.cogalan} satir cogaldi: ${etkilenen} satir, ` +
  `yani girisin yuzde ${(100 * etkilenen / OKUMA.length).toFixed(0)} kadari etkilendi. Ama iki ` +
  `hata ` +
  `ters yonde calistigi icin satir sayisi 1877'den 1780'e, yalnizca ${OKUMA.length - S2.length} ` +
  `satir dustu ve toplam yuzde ${(100 * (1 - toplam(S2) / toplam(S1))).toFixed(1)} sapti`);

// Ayni soru, uc karar: "abone basina ortalama donemlik tuketim".
const abonToplam = new Map();
for (const s of S1) abonToplam.set(s.abone_no, (abonToplam.get(s.abone_no) ?? 0) + s.okuma_m3);
const kA = toplam(S1) / S1.length;
const kB = [...abonToplam.values()].reduce((a, b) => a + b, 0) / abonToplam.size;
const kC = toplam(S2) / S2.length;
const y2 = y([34, 14, 10]);
console.log(`\n"abone basina ortalama tuketim" sorusuna uc karar`);
console.log(y2(["karar", "satirin birimi", "m3"]));
console.log(y2(["A okuma satiri basina ortalama", "abone-donem", kA.toFixed(2)]));
console.log(y2(["B once abone basina toplanir", "abone", kB.toFixed(2)]));
console.log(y2(["C S2 satiri basina ortalama", "abone-ziyaret", kC.toFixed(2)]));
// Iki ortalamanin ayni sayilmasi icin esik 0.05 m3'tur: okumalar bir ondalikla
// tutuluyor, altindaki fark yalnizca yuvarlamadan gelebilir.
const ESIK = 0.05;
console.log(`A ile B arasindaki fark ${(kB - kA).toFixed(2)} m3, esik ${ESIK}'in ` +
  `${Math.round((kB - kA) / ESIK)} kati; ikisi de dogru hesaptir ama ayni soruya cevap degildir`);
console.log(`C ise cok ziyaret alan abonelerin okumasini agirliklandiran bir ortalamadir ve ` +
  `A'dan yalnizca ${Math.abs(kC - kA).toFixed(2)} m3 ayrildigi icin fark edilmez`);

sorgu                      giren   cikan   dusen  cogalan  en cok   toplam m3    oran
S1 okuma + abone            1877    1877       0        0       1     38690.7    1.00
S2 + ziyaret (ic)           1877    1780     766      669       3     35967.4    0.93
S3 + ziyaret (sol)          1877    2546       0      669       3     51971.8    1.34

ziyareti olmayan 570 abonenin 766 okuma satiri S2'de hic gorunmuyor; sorgu hata vermedi, satir sayisi tek isarettir
S2'de 766 satir dustu ve 669 satir cogaldi: 1435 satir, yani girisin yuzde 76 kadari etkilendi. Ama iki hata ters yonde calistigi icin satir sayisi 1877'den 1780'e, yalnizca 97 satir dustu ve toplam yuzde 7.0 sapti

"abone basina ortalama tuketim" sorusuna uc karar
karar                             satirin birimi        m3
A okuma satiri basina ortalama       abone-donem     20.61
B once abone basina toplanir               abone     27.64
C S2 satiri basina ortalama        abone-ziyaret     20.21
A ile B arasindaki fark 7.02 m3, esik 0.05'in 140 kati; ikisi de dogru hesaptir ama ayni soruya cevap degildir
C ise cok ziyaret alan abonelerin okumasini agirliklandiran bir ortalamadir ve A'dan yalnizca 0.41 m3 ayrildigi icin fark edilmez

S1 doğru kümedir. Okuma tablosu abone tablosuyla birleştiğinde abone numarası sağ tarafta tekil olduğu için her okuma satırı tam bir kez çıkar: giren 1.877, çıkan 1.877, düşen sıfır, çoğalan sıfır. Bir birleştirmenin satır sayısını değiştirmemesinin tek koşulu budur — anahtarın karşı tarafta tekil olması.

S2 aynı kümeye ziyaret tablosunu ekliyor ve iki şey aynı anda oluyor. Ziyareti olmayan 570 abonenin 766 okuma satırı sonuçta hiç görünmüyor, çünkü iç birleştirme eşleşmeyen sol satırı düşürür. Aynı anda birden çok ziyareti olan abonelerin okumaları çoğalıyor: 669 fazladan satır ve bir abone-dönem çifti sonuç kümesinde en çok üç kez geçiyor. Birim artık abone-dönem değil, abone-ziyaret çiftidir.

Bu dersin asıl bulgusu iki sayının birbirini gizlemesidir. 1.877 satırın 1.435’i, yani girişin yüzde 76 kadarı ya düştü ya çoğaldı. Ama iki hata ters yönde çalıştığı için sonuç kümesi 1.780 satır oldu: net kayıp 97 satır, toplamdaki sapma yüzde 7,0. Satır sayısına ya da toplama bakan biri kümenin dörtte üçünün bozulduğunu göremez. Yalnızca düşen ve çoğalan ayrı ayrı sayıldığında hesap açılır.

S3 iç birleştirme yerine sol birleştirme kullanıyor ve düşen satır sorununu çözüyor: ziyareti olmayan aboneler kümede kalıyor, düşen sıfır. Çoğalma ise duruyor — küme 2.546 satıra çıkıyor ve toplam 1,34 katına yükseliyor. Sol birleştirme kaybı önler, çoğalmayı önlemez; ikisi ayrı kararlardır ve ayrı sayılır.

Koddaki birim işlevi bu üç satırı üreten testtir ve her sorgudan sonra koşturulabilir. Test tek bir soru sorar: çözümleme biriminin anahtarı sonuç kümesinde kaç kez geçiyor. Anahtar başına sayı her satırda bir ise birim korunmuştur. Sayı birden büyükse çoğalma vardır ve o anahtarın değerleri toplamda birden çok kez ağırlık taşır. Girişte olup sonuçta hiç geçmeyen anahtar varsa sessiz kayıp vardır. Bu üçü tek bir taramada çıkar ve satır sayısını okumaktan farklıdır: 1.780 sayısı tek başına S2 hakkında hiçbir şey söylemez, dusen ile cogalan yan yana yazıldığında ise kümenin ne olduğu tam olarak bilinir. Kursun kuralı burada da geçerlidir — giren ile çıkan arasındaki fark kalem kalem yazılamıyorsa veri hazırlanmamış, yalnız değiştirilmiştir.

Aynı Soru, Üç Birim

Alt tablo çözümleme birimi sorusunu tek başına ölçüyor. Soru sabittir: abone başına ortalama tüketim. VT8’e göre raporun satırı bir abonedir, yani doğru cevap B’dir — 27,64 m³. Karar A okuma satırları üzerinde ortalama alır ve 20,61 m³ verir; bu sayı yanlış hesaplanmış değildir, yalnızca başka bir sorunun cevabıdır (dönem başına ortalama okuma). İki okuması olan aboneler A’da iki kez, B’de bir kez sayılır ve fark 7,02 m³, yani eşiğin 140 katıdır.

Karar C ise en tehlikelisidir. S2’nin satırları üzerinde alınan ortalama 20,21 m³ verir ve A’dan yalnızca 0,41 m³ ayrılır. Sayı makul görünür, çünkü değerlerin kendisi bozulmamıştır; bozulan tek şey her okumanın kaç kez sayıldığıdır. Çok ziyaret alan abonelerin okuması üç kez, ziyareti olmayan abonelerinki sıfır kez ağırlık taşır. Bu ortalama hiçbir sorunun cevabı değildir ve A’ya yakınlığı onu bulunmaz kılar.

Bu üç sayının ortak dersi şudur: birleştirmeden sonra hangi birimin kaydına baktığı yazılmayan bir sorgu, kaç satır döndürdüğünü söylese bile ne döndürdüğünü söylememiştir.

Özet

• Bir sorgunun döndürdüğü satırın birimi tabloların yapısından değil sorgunun yazımından çıkar; okuma ile abone birleştirmesi 1.877 satır verir ve birimi abone-dönem olarak korur.
• Ziyaret tablosunun iç birleştirmeyle eklenmesi 766 satırı sessizce düşürür ve 669 satırı çoğaltır; birim abone-ziyaret çiftine kayar.
• İki hata ters yönde çalıştığı için net satır kaybı yalnızca 97, toplam sapması yüzde 7,0 olur; girişin yüzde 76 kadarı bozulmuş olmasına rağmen sayıya bakan biri hasarı göremez.
• Sol birleştirme düşen satırı sıfırlar ama çoğalmayı durdurmaz: küme 2.546 satıra çıkar, toplam 1,34 katına yükselir.
• Aynı soru üç birimde üç sayı verir — abone başına 27,64 m³, okuma satırı başına 20,61 m³, ziyarete yayılmış kümede 20,21 m³; sonuncusu doğruya yakın olduğu için fark edilmez.

Sonraki Adım

Dosya da veritabanı da verinin tamamını bir kerede verdi: dosya okunduğunda kaç satır olduğu belliydi, sorgu koştuğunda küme elde duruyordu. Bir arayüzden veri toplarken bu güvence yoktur. Kayıtlar parça parça gelir, her parçanın sonrakine nasıl bağlandığını çağıran taraf takip eder ve kaynak çağrı sıklığına bir sınır koyar. Sonraki ders sayfalamayı yanlış tüketmenin kaç kaydı atladığını sayar ve hız sınırına uyan bir sıranın toplama süresini nasıl belirlediğini ölçer.