Birleştirme İşlemleri

Gruplama tek bir tablonun içinde kalır. Analizin sorularının çoğu ise birden çok tablo ister: okuma tablosunun yanına saha ziyaretleri gelmelidir. Veri Toplama ve Hazırlama kursunun birleştirme dersi bu işi elle yazılan bir eşleştirme döngüsüyle yapmış ve satır hesabını kalem kalem tutmuştu; 3.471 okuma ile 1.329 aboneden 3.302 satır çıkmış, 169 okuma eşleşmemiş, 69 abone çıktıda hiç görünmemişti. O hesap bu dersin girdisidir ve tekrarlanmaz.

Bir veri çerçevesi arayüzünde aynı iş tek bir çağrıdır ve o çağrı bir birleştirme türü seçer. Bu dersin ölçüsü şudur: aynı iki tablo, aynı anahtar, dört tür ve dört farklı satır sayısı. Yanına ikinci bir sayı gelir — anahtarın tanesi doğrulanmadığında kaç satır çoğalır ve toplam ne kadar şişer.

• VC22. Birleştirme türü çıktının satır sayısını belirleyen karardır. İç birleştirme yalnız eşleşen satırları, sol dış eşleşmeyen sol satırları da, tam dış iki taraftaki eşleşmeyenleri de tutar. Çapraz birleştirme anahtarı hiç okumaz ve iki tablonun bütün ikililerini üretir.
• VC23. Anahtarın tanesi, bir anahtar değerinin her tarafta kaç satıra karşılık geldiğidir. İlişki bir bire, bir çoka ya da çok çoka olabilir ve birleştirme bunu doğrulamadan yapar.
• VC24. Çoğalma, sol satırın sağdaki eşleşme sayısı kadar tekrarlanmasıdır. Çoğalma bir hata değildir; hata, çoğalmış bir tabloda toplam almaktır.
• VC25. Çıkan satır sayısı girenden az olsa bile bir eleme yapılmış olmayabilir: aynı işlem içinde bazı satırlar düşerken bazıları çoğalabilir ve net değişim ikisini birden gizler.

İki Tablo ve Üç Küme

# kume.py — MODELDIR: bir onceki dersin uzun bicimli okuma tablosu ayni tohumla
# yeniden uretilir. Kume kurgudur; uretecin ayrintisi konunun ilk dersindedir.
import math

M32 = 0xFFFFFFFF
TOHUM, DONEM = 20260218, ["2026-01", "2026-02", "2026-03"]
BOLGE = [("kuzey", 0.28, 21), ("guney", 0.22, 17), ("dogu", 0.18, 26),
         ("bati", 0.14, 14), ("merkez", 0.18, 23)]

def uretec(tohum: int):
    s = ((tohum ^ (tohum >> 16)) * 2246822507) & M32
    s = ((s ^ (s >> 13)) * 3266489909) & M32
    s ^= s >> 16
    def sonraki() -> float:
        nonlocal s
        s = (s * 1664525 + 1013904223) & M32
        return s / 4294967296
    return sonraki

def ayrik(u: float, agirlik: list[float]) -> int:
    t = 0.0
    for i, p in enumerate(agirlik):
        t += p
        if u < t:
            return i
    return len(agirlik) - 1

abone, elenen = [], []
for i in range(1400):
    r = uretec(TOHUM + i)
    ad, _, taban = BOLGE[ayrik(r(), [b[1] for b in BOLGE])]
    r(); r()  # hane ve memnuniyet cekilisleri bu derste kullanilmiyor
    (elenen if r() < 0.046 else abone).append(
        {"abone_no": 10001 + i, "bolge": ad, "taban": taban})

gecerli = {k["abone_no"] for k in abone}
cift: dict[tuple[int, str], float] = {}
for k in abone + elenen:
    r = uretec(TOHUM + 7000 + k["abone_no"])
    for d in range(ayrik(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(k["taban"] * math.exp(
            (r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100 + 0.5) / 100
        if k["abone_no"] in gecerli:
            cift.setdefault((k["abone_no"], DONEM[d]), t)
        r()
UZUN = [{"abone_no": a, "donem": d, "tuketim_m3": t} for (a, d), t in cift.items()]

# Saha ziyaretleri tablosu: bir satir bir ziyarettir ve doneme bagli degildir.
ziyaret = []
for k in abone:
    r = uretec(TOHUM + 31000 + k["abone_no"])
    for z in range(ayrik(r(), [0.58, 0.27, 0.11, 0.04])):
        ziyaret.append({"abone_no": k["abone_no"],
                        "sonuc": ["olumlu", "tekrar", "erisilemedi"][
                            ayrik(r(), [0.60, 0.28, 0.12])]})

okuyan = {o["abone_no"] for o in UZUN}
gezilen = {z["abone_no"] for z in ziyaret}
print(f"okuma tablosu {len(UZUN)} satir, {len(okuyan)} abone")
print(f"ziyaret tablosu {len(ziyaret)} satir, {len(gezilen)} abone")
print(f"iki tarafta da bulunan {len(okuyan & gezilen)} abone; yalniz okumada "
      f"{len(okuyan - gezilen)}, yalniz ziyarette {len(gezilen - okuyan)}")

okuma tablosu 3199 satir, 1260 abone
ziyaret tablosu 818 satir, 559 abone
iki tarafta da bulunan 534 abone; yalniz okumada 726, yalniz ziyarette 25

Birleştirmeden önce üç sayı biliniyor ve bu üç sayı çıkacak bütün satır sayılarını önceden açıklıyor. 534 abone iki tabloda da vardır, 726 abone yalnız okuma tablosunda, 25 abone yalnız ziyaret tablosundadır. Bu üç kümenin toplamı 1.285’tir ve birleştirme türü, aslında bu üç kümeden hangilerinin çıktıda temsil edileceğini seçmektir.

Bir ayrıntı da satır sayısını doğrudan etkiler: iki tablo aynı taneyi taşımıyor. Okuma tablosunda bir satır bir abonenin bir dönemidir, ziyaret tablosunda bir satır bir ziyarettir ve döneme bağlı değildir. Anahtar olarak yalnız abone numarası kullanıldığında bir abonenin üç okuması ile iki ziyareti altı satır üretir. Bu, tabloların kendisinden okunabilen bir özelliktir ve birleştirme çağrısından önce bilinir.

Aynı İki Tablo, Dört Tür

# birlestir.py — ayni iki tablo dort birlestirme turuyle. Tur, ciktinin satir
# sayisini belirleyen karardir.
GECIS = {"sayi": 0}

def birlestir(sol, sag, alan: str, tur: str = "ic"):
    """Anahtar uzerinden yan yana getirir. tur: ic, sol_dis, tam_dis, capraz."""
    if tur == "capraz":
        GECIS["sayi"] += len(sol) * len(sag)
        return len(sol) * len(sag), 0, 0
    dizin: dict = {}
    for s in sag:
        dizin.setdefault(s[alan], []).append(s)
    GECIS["sayi"] += len(sag) + len(sol)
    cikan, bos_sol, eslesen = 0, 0, set()
    for l in sol:
        e = dizin.get(l[alan])
        if not e:
            bos_sol += 1
            cikan += 1 if tur in ("sol_dis", "tam_dis") else 0
            continue
        cikan += len(e)
        eslesen.add(l[alan])
    bos_sag = len({s[alan] for s in sag} - eslesen)
    if tur == "tam_dis":
        cikan += sum(1 for s in sag if s[alan] not in eslesen)
    return cikan, bos_sol, bos_sag

EN = [22, 12, 12, 12, 12]
y = lambda h: "".join(str(v).rjust(EN[k]) if k else str(v).ljust(EN[0])
                      for k, v in enumerate(h))
print(y(["birlestirme turu", "giren sol", "giren sag", "cikan", "eslesmeyen"]))
sonuc = {}
for tur, ad in [("ic", "ic"), ("sol_dis", "sol dis"), ("tam_dis", "tam dis"),
                ("capraz", "capraz")]:
    GECIS["sayi"] = 0
    cikan, bs, bg = birlestir(UZUN, ziyaret, "abone_no", tur)
    sonuc[tur] = (cikan, bs, bg, GECIS["sayi"])
    print(y([ad, len(UZUN), len(ziyaret), cikan, bs if tur != "capraz" else 0]))
print(f"\nic birlestirme {len(UZUN)} okumadan {sonuc['ic'][1]} tanesini dusurur ve "
      f"{sonuc['ic'][0]} satir uretir")
print(f"capraz birlestirme anahtar hic okumaz: {len(UZUN)} x {len(ziyaret)} = "
      f"{sonuc['capraz'][0]} satir")

birlestirme turu         giren sol   giren sag       cikan  eslesmeyen
ic                            3199         818        1965        1855
sol dis                       3199         818        3820        1855
tam dis                       3199         818        3856        1855
capraz                        3199         818     2616782           0

ic birlestirme 3199 okumadan 1855 tanesini dusurur ve 1965 satir uretir
capraz birlestirme anahtar hic okumaz: 3199 x 818 = 2616782 satir

Dört tür, aynı iki tablo, dört farklı satır sayısı: 1.965, 3.820, 3.856 ve 2.616.782. Aralarındaki oran binde birden sekiz yüz katına kadar uzanıyor ve seçimi yapan tek şey, çağrıda yazılan ya da yazılmayan bir tür adı.

İç birleştirmenin sayısı iki yönde birden ilginçtir. Çıkan 1.965 satır girenden azdır ve ilk bakışta bir eleme gibi görünür. Değildir: 1.855 okuma düşmüş, kalan 1.344 okuma ise 1.965 satıra açılmıştır. Net değişim negatif olduğu için çoğalma sayının içinde görünmez. Bu, Veri Toplama ve Hazırlama kursunda ölçülen davranışın aynısıdır; burada yeni olan, bütün işlemin tek bir çağrıya sığması ve 1.855 ile 621 sayılarının hiçbirinin çağrıdan geri dönmemesidir.

Sol dış birleştirme 3.820 satır verir ve okuma tablosunun bütün satırlarını korur: 1.965 eşleşmiş satır ve 1.855 boş ziyaret tarafı. Tam dış 3.856 satırla yalnız ziyaret tablosunda bulunan 25 abonenin 36 ziyaret satırını da ekler. Çapraz birleştirme ise anahtarı hiç okumaz; ürettiği 2.616.782 satır bir sorunun cevabı değil, iki tablonun bütün ikilileridir.

Çoğalma ve Anahtarın Tanesi

# cogalma.py — anahtarin tekilligi dogrulanmazsa birlestirme ayni satiri birden
# cok kez sayar. Olcu, cogalan satir ve sisen toplamdir.
sayi: dict[int, int] = {}
for z in ziyaret:
    sayi[z["abone_no"]] = sayi.get(z["abone_no"], 0) + 1
eslesen = [o for o in UZUN if o["abone_no"] in sayi]
cikan = sum(sayi[o["abone_no"]] for o in eslesen)
T = lambda a: round(sum(x["tuketim_m3"] for x in a), 2)
sisen = round(sum(o["tuketim_m3"] * sayi[o["abone_no"]] for o in eslesen), 2)
print(f"kume {len(UZUN)} satir, {T(UZUN)} m3")
print(f"eslesen {len(eslesen)} satir, {T(eslesen)} m3")
print(f"ic birlestirme ciktisi {cikan} satir, {sisen} m3; "
      f"{cikan - len(eslesen)} satir cogalma, {round(sisen - T(eslesen), 2)} m3 fazla sayim")

def iliski(sol, sag, alan: str) -> str:
    """Anahtarin tanesi: her iki tarafta da tekil mi, bir tarafta cok mu."""
    sol_tekil = len({s[alan] for s in sol}) == len(sol)
    sag_tekil = len({s[alan] for s in sag}) == len(sag)
    return ("bir-bir" if sol_tekil and sag_tekil else
            "bir-cok" if sag_tekil or sol_tekil else "cok-cok")

cok = sum(1 for o in eslesen if sayi[o["abone_no"]] > 1)
print(f"\nanahtarin tanesi: {iliski(UZUN, ziyaret, 'abone_no')}; sol tarafta "
      f"{cok} satirin anahtari sagda birden cok satira karsilik geliyor")
print(f"dogrulama yapilmadiginda bu {cok} satir sessizce {cikan - len(eslesen) + cok} "
      f"satira acilir")
print(f"\ngecis : elle ic ice dongu {len(UZUN) * len(ziyaret)}, dizinli birlestirme "
      f"{len(UZUN) + len(ziyaret)}")
print(f"satir : ayni iki tablo dort turde {sonuc['ic'][0]}, {sonuc['sol_dis'][0]}, "
      f"{sonuc['tam_dis'][0]} ve {sonuc['capraz'][0]} satir uretir")

kume 3199 satir, 63060.63 m3
eslesen 1344 satir, 26450.81 m3
ic birlestirme ciktisi 1965 satir, 38701.46 m3; 621 satir cogalma, 12250.65 m3 fazla sayim

anahtarin tanesi: cok-cok; sol tarafta 468 satirin anahtari sagda birden cok satira karsilik geliyor
dogrulama yapilmadiginda bu 468 satir sessizce 1089 satira acilir

gecis : elle ic ice dongu 2616782, dizinli birlestirme 4017
satir : ayni iki tablo dort turde 1965, 3820, 3856 ve 2616782 satir uretir

Çoğalmanın bedeli ikinci satırda okunuyor. Eşleşen 1.344 okumanın toplamı 26.450,81 m³’tür; birleştirme çıktısındaki aynı okumaların toplamı 38.701,46 m³’tür. Aradaki 12.250,65 m³ yeni bir tüketim değildir, aynı okumaların ikinci ve üçüncü kez sayılmasıdır. Bir abonenin ocak okuması, o abonenin iki ziyareti olduğu için toplama iki kez girmiştir.

Bu sayının tehlikesi büyüklüğünde değil, makullüğündedir. 38.701,46 m³ kümenin gerçek toplamından, yani 63.060,63 m³’ten küçüktür; bir denetim “toplam küme toplamını aşmıyor” diye baksa bu tabloyu geçirir. Oysa tablo hem eksik hem fazla sayılmış bir toplam taşır: 1.855 okuma hiç sayılmamış, 468 okuma birden çok kez sayılmıştır.

Son iki satır kazancı ve gizlenen kararı yan yana koyuyor. Kazanç açıktır: elle iç içe döngü 2.616.782 karşılaştırma tutarken, sağ tarafa bir anahtar dizini kurup sol tarafı bir kez gezen birleştirme 4.017 geçişle biter — 651 kat daha az. Gizlenen karar ise ilişkinin çok çoka olması ve bunun hiçbir yerde doğrulanmamasıdır. Birleştirme çağrısı ilişkinin ne olduğunu sormaz, sorulmuş olsaydı 468 satırın 1.089 satıra açılacağı çağrıdan önce bilinirdi.

Taneyi Düzeltmek

# tane_duzeltme.py — cogalmayi onleyen sey birlestirme turu degil, sag tarafin
# taneye getirilmesidir: once toplulastir, sonra birlestir.
ozet_ziyaret = [{"abone_no": no, "ziyaret_sayisi": n} for no, n in sayi.items()]
tekil = len({z["abone_no"] for z in ozet_ziyaret}) == len(ozet_ziyaret)
print(f"ziyaret tablosu {len(ziyaret)} satirdan {len(ozet_ziyaret)} satira indi; "
      f"anahtar tekil mi: {'evet' if tekil else 'hayir'}")
print(f"anahtarin tanesi artik {iliski(UZUN, ozet_ziyaret, 'abone_no')}")
ic2, bs2, bg2 = birlestir(UZUN, ozet_ziyaret, "abone_no", "ic")
sol2, _, _ = birlestir(UZUN, ozet_ziyaret, "abone_no", "sol_dis")
print(f"\nic birlestirme {ic2} satir ({sonuc['ic'][0]} degil), toplam {T(eslesen)} m3")
print(f"sol dis birlestirme {sol2} satir, toplam {T(UZUN)} m3: giren satir ve giren "
      f"toplam degismedi")
print(f"cogalma {sonuc['ic'][0] - len(eslesen)} satirdan {ic2 - len(eslesen)} satira, "
      f"fazla sayim {round(sisen - T(eslesen), 2)} m3'ten 0.0 m3'e indi")

ziyaret tablosu 818 satirdan 559 satira indi; anahtar tekil mi: evet
anahtarin tanesi artik bir-cok

ic birlestirme 1344 satir (1965 degil), toplam 26450.81 m3
sol dis birlestirme 3199 satir, toplam 63060.63 m3: giren satir ve giren toplam degismedi
cogalma 621 satirdan 0 satira, fazla sayim 12250.65 m3'ten 0.0 m3'e indi

Çoğalmayı ortadan kaldıran şey birleştirme türünü değiştirmek değil, sağ tarafı sol tarafın tanesine getirmektir. Ziyaret tablosu abone başına toplulaştırılıp 818 satırdan 559 satıra indiğinde anahtar tekil olur, ilişki çok çoka olmaktan çıkıp bir çoka döner ve aynı birleştirme çağrısı artık hiçbir satırı çoğaltmaz. Fazla sayım 12.250,65 m³’ten sıfıra iner ve bu düşüş bir düzeltme değildir; hiç oluşmamıştır.

Son satır bu dersin aradığı denetimi veriyor. Doğru taneyle yapılan sol dış birleştirme 3.199 satır ve 63.060,63 m³ ile kapanır: giren satır sayısı ve giren toplam değişmemiştir. Bir birleştirmenin doğru yapıldığını gösteren tek ucuz işaret budur — sol tablonun satır sayısı ve toplamı çıktıda korunuyorsa sol taraf ne düşmüş ne çoğalmıştır. Korunmuyorsa fark iki kalem halinde yazılır ve bu iki kalem, çağrının kendisinin hiç raporlamadığı sayılardır.

Önce toplulaştırıp sonra birleştirmenin bir bedeli de vardır ve yazılmalıdır: 818 ziyaret satırının taşıdığı ayrıntı, yani her ziyaretin kendi sonucu, özetin içinde kaybolur. Tane kararı bu yüzden sorulacak soruya bağlıdır. “Abone başına kaç ziyaret” sorusu özet tabloyla cevaplanır, “kaç ziyaret erişilemedi ile kapandı” sorusu ise ziyaret tablosunun kendisine sorulur ve okuma tablosuyla hiç birleştirilmez.

Özet

• Birleştirme türü çıktının satır sayısını belirler: aynı iki tablo iç birleştirmede 1.965, sol dışta 3.820, tam dışta 3.856, çaprazda 2.616.782 satır verir.
• İç birleştirmenin girenden az satır vermesi bir eleme anlamına gelmez: 1.855 okuma düşmüş, kalan 1.344 okuma 1.965 satıra açılmıştır ve net değişim ikisini birden gizler.
• Çoğalma toplamı şişirir: eşleşen okumaların gerçek toplamı 26.450,81 m³ iken çıktıdaki toplam 38.701,46 m³’tür ve aradaki 12.250,65 m³ ikinci kez sayılmış okumadır.
• Şişmiş toplam küme toplamının altında kaldığı için üst sınır denetiminden geçer; tabloda hem eksik hem fazla sayım aynı anda vardır.
• Anahtarın ilişkisi çok çokadır ve doğrulanmaz: 468 satır sessizce 1.089 satıra açılır. Dizinli birleştirme 4.017 geçişle biter, iç içe döngü 2.616.782 karşılaştırma tutar.

Sonraki Adım

Birleştirme tabloları yan yana getirdi ve satır hesabı yazıldı. Kümede henüz bir eksen ele alınmadı: zaman. Okuma tablosunda dönem bir metin sütunudur ve sıralanabilir, ama dönemler arasında “aradan kaç hafta geçti” ya da “hangi haftada hiç okuma yok” diye bir soru bu haliyle sorulamaz. Zaman ekseni etiket dizinine taşındığında bu sorular sorulabilir hale gelir ve yeni bir karar doğar: yeniden örneklemede hiç kayıt taşımayan bir dönem sıfır mı sayılır, yoksa hiç var olmamış mı sayılır. Sonraki ders bu iki cevabı ayrı ayrı hesaplar ve toplamı ile ortalamayı nasıl değiştirdiklerini ölçer.