# فصل ۰۳ — ⚠️ پیوندی که بی‌صدا سطرها را تکثیر می‌کند

<div dir="rtl">

# فصل ۰۳ — ⚠️ پیوندی که بی‌صدا سطرها را تکثیر می‌کند

## در این فصل چه یاد می‌گیری

این فصل دلیلِ وجودِ کلِ این ترم است.

جمعِ صفحاتِ این کتابخانه **۵۸۸٬۰۴۹** است. یک `JOIN` به جدولِ امانت‌ها اضافه می‌کنیم — فقط برای اینکه یک ستونِ دیگر هم در گزارش باشد — و همان `SUM` می‌شود **۱٬۹۴۶٬۱۰۳**. هیچ خطایی، هیچ هشداری. عدد ۳٫۳ برابر شد.

و بدتر از عددِ غلط، این است: **رتبه‌بندیِ شعبه‌ها وارونه می‌شود.** شعبه‌ای که واقعاً چهارم است اول می‌شود و شعبه‌ای که سوم است آخر. گزارش فقط عددِ اشتباه نمی‌دهد؛ **برندهٔ اشتباه** می‌دهد.

![یک دستهٔ سه‌شاخه که یک جریانِ ورودی را به سه جریانِ خروجیِ هم‌اندازه تبدیل می‌کند و پیمانهٔ پایین را لبریز می‌کند](/lesson-assets/t3-s03-fan-out/prompt/01-image-fan-out.png)

آخر این فصل می‌توانی:

- بگویی یک‌به‌یک و یک‌به‌چند و چندبه‌چند یعنی چه، و کدامشان سطر می‌سازد
- تورمِ یک `SUM` را با دو عدد نشان بدهی
- نشانه‌ای را که بدونِ دانستنِ اسکیما هم کار می‌کند تشخیص بدهی
- با تجمیع قبل از پیوند، عددِ درست را برگردانی و ثابت کنی که درست است

## قبل از شروع

**از فصلِ ۱ و ۲:** `INNER JOIN` و `LEFT JOIN` و بندِ `ON`. **از ترمِ ۲:** `GROUP BY` و `SUM` و `COUNT(DISTINCT ...)`.

**دانه‌بندی:** یک سطر از `books` یک کتاب است؛ یک سطر از `loans` یک بار امانت رفتن. **یک کتاب می‌تواند ده‌ها سطر در `loans` داشته باشد — و کلِ این فصل دربارهٔ نتیجهٔ همین جمله است.**

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-3-joins/session-03-fan-out.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `books` | ۱٬۲۰۰ | کمتر از یک ثانیه |
| `branches` | ۷ | آنی |
| `loans` | ۳٬۹۹۸ | کمتر از یک ثانیه |

<div dir="rtl">

## ۱. دو کتاب، سه امانت

قبل از هر عددِ بزرگی، چیزی که با انگشت شمردنی است:

</div>

```python
con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, pages INTEGER)")
con.execute("CREATE TABLE tiny_loans (id INTEGER PRIMARY KEY, book_id INTEGER)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)", [(1, "الف", 100), (2, "ب", 200)])
con.executemany("INSERT INTO tiny_loans VALUES (?, ?)", [(1, 1), (2, 2), (3, 2)])
con.commit()

print("جمع صفحه‌ها، روی یک جدول:",
      int(q("SELECT SUM(pages) AS s FROM tiny_books").iloc[0, 0]))
print()
print(q("""
    SELECT t.title, t.pages
    FROM tiny_books AS t
    JOIN tiny_loans AS n ON n.book_id = t.id
    ORDER BY t.id, n.id
"""))
print()
print("جمع صفحه‌ها، بعد از JOIN :",
      int(q("""
          SELECT SUM(t.pages) AS s
          FROM tiny_books AS t
          JOIN tiny_loans AS n ON n.book_id = t.id
      """).iloc[0, 0]))
```

```text
جمع صفحه‌ها، روی یک جدول: 300

  title  pages
0   الف    100
1     ب    200
2     ب    200

جمع صفحه‌ها، بعد از JOIN : 500
```

<div dir="rtl">

**۱۰۰ + ۲۰۰ + ۲۰۰ = ۵۰۰. با انگشت شمردنی، و کاملاً غلط.**

کتابِ «ب» دو بار امانت رفته، پس در `loans` دو سطر دارد، پس `JOIN` دو نسخه از آن ساخت — و `SUM` هر دو نسخه را جمع زد. صفحه‌های «ب» دو بار شمرده شدند، **نه به این خاطر که دو کتاب است، بلکه به این خاطر که دو امانت است.**

اسمِ رابطهٔ بینِ این دو جدول **یک‌به‌چند** است: یک کتاب، چند امانت. سه حالت ممکن است و فقط یکی‌شان بی‌خطر است:

- **یک‌به‌یک** — هر سطر دقیقاً یک جفت دارد. تعدادِ سطرها عوض نمی‌شود. (`books` به `branches` در فصلِ ۱، از سمتِ کتاب: هر کتاب یک شعبه دارد، هرچند یک شعبه چند کتاب دارد و این پیوند از سمتِ شعبه یک‌به‌چند است)
- **یک‌به‌چند** — یک سطر چند جفت دارد. تعدادِ سطرها **زیاد** می‌شود. (`books` به `loans`)
- **چندبه‌چند** — هر دو طرف چند جفت دارند. تعدادِ سطرها ضرب می‌شود.

> 🌱 **ریشه‌اش کجاست:** این همان اصلِ ضربِ شمردن است: اگر یک انتخاب سه حالت دارد و انتخابِ بعدی چهار حالت، تعدادِ کلِ حالت‌ها ۱۲ است، نه ۷. `JOIN` هم دقیقاً همین کار را می‌کند — هر سطرِ چپ ضربدر تعدادِ جفت‌هایش. اگر می‌خواهی این ضرب را از پایه ببینی، [ریشه ترمِ ۶ فصل ۱](/courses/risheh/lessons/term-6-probability-statistics/session-01-counting) دقیقاً همین است.

## ۲. همان اتفاق، روی دادهٔ واقعی

</div>

```python
before_rows = int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0])
before_sum = int(q("SELECT SUM(pages) AS s FROM books").iloc[0, 0])
after = q("""
    SELECT COUNT(*) AS rows_now, SUM(b.pages) AS pages_now
    FROM books AS b
    JOIN loans AS l ON l.book_id = b.id
""")

print("قبل از JOIN — سطر:", before_rows, "| جمع صفحه‌ها:", before_sum)
print("بعد از JOIN — سطر:", int(after["rows_now"][0]),
      "| جمع صفحه‌ها:", int(after["pages_now"][0]))
print("نسبت جمع‌ها      :", round(int(after["pages_now"][0]) / before_sum, 2))
```

```text
قبل از JOIN — سطر: 1200 | جمع صفحه‌ها: 588049
بعد از JOIN — سطر: 3998 | جمع صفحه‌ها: 1946103
نسبت جمع‌ها      : 3.31
```

<div dir="rtl">

**عدد ۳٫۳۱ برابر شد، و هیچ‌کس چیزی نگفت.**

## ۳. نشانه‌ای که بدونِ اسکیما هم کار می‌کند

اگر فقط یک جمله از این فصل قرار است در ذهنت بماند، همین است:

> **تعدادِ سطرها عوض شد، در حالی که تو فقط یک ستون اضافه کرده بودی.**

این نشانه هیچ چیزی از اسکیما نمی‌خواهد. لازم نیست بدانی `loans` چیست یا رابطه‌اش چند به چند است. **قبل از `JOIN` یک `COUNT(*)` بگیر، بعدش یک `COUNT(*)` دیگر. اگر عوض شد، هر تجمیعی که در آن پرسش داری مشکوک است.**

و برعکسش هم درست است: در فصلِ ۱ همین آزمون را زدیم و عدد ۱۲۰۰ ماند ۱۲۰۰. **آن `JOIN` بی‌خطر بود و ثابتش کردیم، نه اینکه فرضش کنیم.**

## ۴. بدتر از عددِ غلط: برندهٔ غلط

حالا همان دو پرسش، این بار به‌تفکیکِ شعبه — دقیقاً همان کاری که یک گزارشِ واقعی می‌کند:

</div>

```python
print("درست — فقط از books:")
print(q("""
    SELECT br.name, SUM(b.pages) AS pages
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
print()
print("غلط — همان، به‌علاوهٔ ستون امانت‌ها:")
print(q("""
    SELECT br.name, SUM(b.pages) AS pages, COUNT(*) AS loans
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    JOIN loans AS l ON l.book_id = b.id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
```

```text
درست — فقط از books:
    name   pages
0   نسیم  108608
1   کوشا  106918
2   پویا   97273
3  مرکزی   94868
4   دانش   93673
5   بهار   86709

غلط — همان، به‌علاوهٔ ستون امانت‌ها:
    name   pages  loans
0  مرکزی  526045   1113
1   نسیم  437875    837
2   دانش  331396    676
3   کوشا  240599    506
4   بهار  231523    488
5   پویا  178665    378
```

<div dir="rtl">

**مرکزی از رتبهٔ چهارم به اول پرید و پویا از سوم به آخر افتاد.**

دلیلش این است که تورم برای همهٔ شعبه‌ها یکسان نیست: کتاب‌های مرکزی بیشتر امانت می‌روند، پس بیشتر تکثیر می‌شوند. **ستونِ `pages` در جدولِ دوم عملاً دارد محبوبیت را اندازه می‌گیرد، نه صفحه را** — و عنوانِ ستون همچنان `pages` است.

**اگر این گزارش مبنای تصمیمِ «کدام شعبه به قفسهٔ بیشتری نیاز دارد» باشد، جواب کاملاً عوض می‌شود.**

## ۵. `DISTINCT` مسکّن است، نه درمان

اولین چیزی که به ذهنِ همه می‌رسد `DISTINCT` است. برای شمردن جواب می‌دهد؛ برای جمع زدن نه.

</div>

```python
joined = q("""
    SELECT COUNT(*)                AS naive,
           COUNT(DISTINCT b.id)    AS distinct_books,
           SUM(DISTINCT b.pages)   AS distinct_pages,
           COUNT(DISTINCT b.pages) AS page_values
    FROM books AS b
    JOIN loans AS l ON l.book_id = b.id
""")
never = q("""
    SELECT COUNT(*) AS books, SUM(b.pages) AS pages
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE l.id IS NULL
""")
all_pages = int(q("SELECT SUM(pages) AS s FROM books").iloc[0, 0])

print("COUNT(*) بعد از JOIN      :", int(joined["naive"][0]))
print("COUNT(DISTINCT b.id)      :", int(joined["distinct_books"][0]))
print("کتاب‌های بی‌امانت (فصل ۲)  :", int(never["books"][0]))
print()
print("SUM(DISTINCT b.pages)     :", int(joined["distinct_pages"][0]))
print("جمع واقعی همان کتاب‌ها    :", all_pages - int(never["pages"][0]))
print("چند کتاب، چند مقدار متمایز:", int(joined["distinct_books"][0]), "کتاب،",
      int(joined["page_values"][0]), "مقدار")
```

```text
COUNT(*) بعد از JOIN      : 3998
COUNT(DISTINCT b.id)      : 1000
کتاب‌های بی‌امانت (فصل ۲)  : 200

SUM(DISTINCT b.pages)     : 290121
جمع واقعی همان کتاب‌ها    : 485911
چند کتاب، چند مقدار متمایز: 1000 کتاب، 598 مقدار
```

<div dir="rtl">

**`COUNT(DISTINCT b.id)` عددِ درست را برگرداند: ۱٬۰۰۰ کتابِ متمایز.** و شمارشِ دومش هم آماده است — ۱٬۰۰۰ به‌علاوهٔ ۲۰۰ کتابِ بی‌امانتِ فصلِ ۲ می‌شود ۱٬۲۰۰. درست است چون `id` یکتاست، پس «متمایز» دقیقاً همان «کتاب» است.

**ولی `SUM(DISTINCT b.pages)` فاجعه است: ۲۹۰٬۱۲۱ در برابرِ ۴۸۵٬۹۱۱.** حالا کمتر از واقعیت است، نه بیشتر. دلیلِ این فاصله یکی است: ۱٬۰۰۰ کتاب فقط ۵۹۸ مقدارِ متمایزِ صفحه دارند، یعنی ۴۰۲ کتاب کاملاً حذف شدند چون کتابِ دیگری هم‌اندازه‌شان بود. فاصله تا جمعِ کلِ کتابخانه از این هم بیشتر است: ۲۰۰ کتابِ بی‌امانت از اول با `INNER JOIN` رفته بودند و جمعِ ۴۸۵٬۹۱۱ هم آن‌ها را ندارد؛ با آن‌ها می‌شود ۵۸۸٬۰۴۹.

**`DISTINCT` مقدارهای تکراری را حذف می‌کند، و مسئلهٔ ما سطرهای تکراری بود، نه مقدارهای تکراری.** برای `COUNT` روی یک کلیدِ یکتا این دو یکی می‌شوند؛ برای `SUM` روی هر ستونِ دیگری نه.

## ۶. درمان: تجمیع قبل از پیوند

راهِ درست این است که **جدولِ یک‌به‌چند را قبل از پیوند به یک سطر برای هر کتاب تبدیل کنیم.** آن‌وقت رابطه یک‌به‌یک می‌شود و دیگر چیزی تکثیر نمی‌شود.

**یک چیزِ تازه در پرسشِ زیر هست:** به‌جای نامِ جدول، یک `SELECT` داخلِ پرانتز آمده. آن `SELECT` مثلِ یک جدول رفتار می‌کند که همان‌جا ساخته می‌شود. **فعلاً همین‌قدر بدان که هر پرسشی می‌تواند جای یک جدول بنشیند؛ فصلِ ۶ کاملاً بازش می‌کند و نامش را می‌گوید.**

</div>

```python
print(q("""
    SELECT br.name,
           SUM(b.pages)             AS pages,
           SUM(COALESCE(x.n, 0))    AS loans
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
           ON x.book_id = b.id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
print()
print("جمع ستون pages:",
      int(q("""
          SELECT SUM(b.pages) AS s
          FROM books AS b
          LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
                 ON x.book_id = b.id
      """).iloc[0, 0]), "| باید برابر 588049 باشد")
print("جمع ستون loans:",
      int(q("""
          SELECT SUM(COALESCE(x.n, 0)) AS s
          FROM books AS b
          LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
                 ON x.book_id = b.id
      """).iloc[0, 0]), "| باید برابر 3998 باشد")
```

```text
    name   pages  loans
0   نسیم  108608    837
1   کوشا  106918    506
2   پویا   97273    378
3  مرکزی   94868   1113
4   دانش   93673    676
5   بهار   86709    488

جمع ستون pages: 588049 | باید برابر 588049 باشد
جمع ستون loans: 3998 | باید برابر 3998 باشد
```

<div dir="rtl">

**هر دو ستون درست‌اند و رتبه‌بندی به همان چیزی برگشت که واقعاً هست.**

و شمارشِ دوم را همان‌جا چاپ کردیم: جمعِ ستونِ `pages` دقیقاً ۵۸۸٬۰۴۹ شد — یعنی هیچ کتابی دو بار شمرده نشده — و جمعِ ستونِ `loans` دقیقاً ۳٬۹۹۸ — یعنی هیچ امانتی گم نشده. **این دو خط تفاوتِ یک گزارش با یک ادعا هستند.**

`LEFT JOIN` و `COALESCE` هم تصادفی نیستند: بدونِ آن‌ها ۲۰۰ کتابِ بی‌امانت دوباره می‌افتادند بیرون و جمعِ صفحه‌ها کمتر از ۵۸۸٬۰۴۹ می‌شد. **درمانِ fan-out نباید خودش سطر بیندازد.**

> ✅ **چک کن:** دو عددِ خطِ آخر باید **دقیقاً** با عددهای جدولِ بالا بخوانند: جمعِ ستونِ `pages` برابرِ جمعِ همان ستون در شش سطرِ جدول، و همین‌طور `loans`. اگر جمعِ `pages` کمتر درآمد، `LEFT JOIN` را `JOIN` نوشته‌ای.

## ۷. دانه‌بندی، چیزی است که اعلام می‌شود

هر سه جدولِ این فصل ستونی به نامِ `pages` داشتند و سه عددِ متفاوت دادند. تفاوتشان در محاسبه نبود، در این بود که **یک سطر یعنی چه**:

- جدولِ اول: یک سطر = یک شعبه، و `pages` جمعِ صفحاتِ **کتاب‌های** آن شعبه.
- جدولِ دوم: یک سطر = یک شعبه، ولی `pages` جمع روی **امانت‌ها**ی آن شعبه است — عددی که هیچ اسمِ درستی ندارد.
- جدولِ سوم: مثلِ اولی، به‌علاوهٔ یک ستونِ سالمِ امانت.

**پس قبل از هر `JOIN` بپرس: بعد از این پیوند، یک سطر یعنی چه؟** اگر جوابت عوض شد، هر تجمیعی که از قبل داشتی باید بازبینی شود.

> 🔧 **اگر کار نکرد:** اگر داخلِ پرانتزِ بخشِ ۶ `GROUP BY` را جا بیندازی، خطا نمی‌گیری — `SQLite` سکوت می‌کند و کلِ جدول را یک سطر می‌کند. ولی اگر نامِ مستعارِ آن جدولِ موقت را جا بیندازی، صریح اعتراض می‌کند:

</div>

```python
try:
    q("""
        SELECT b.id, x.n
        FROM books AS b
        LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id)
               ON x.book_id = b.id
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT b.id, x.n
        FROM books AS b
        LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id)
               ON x.book_id = b.id
        LIMIT 1
    ': no such column: x.n
```

<div dir="rtl">

**ولی خطای واقعیِ این فصل همان ۱٬۹۴۶٬۱۰۳ بود که هیچ پیامی نداشت** — و بدترش، آن جدولی که مرکزی را اول نشان داد.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در جدولِ نهایی یک سطر یک شعبه است، و هر عددش روی **کتاب‌ها** حساب شده نه روی امانت‌ها. **با کدام شمارشِ دوم سنجیدی؟** با سه‌تا: `COUNT(*)` قبل و بعد از پیوند (۱٬۲۰۰ و ۳٬۹۹۸)، جمعِ ستونِ `pages` که به ۵۸۸٬۰۴۹ برگشت، و جمعِ ستونِ `loans` که ۳٬۹۹۸ شد. **چه کسی از قلم افتاد؟** در نسخهٔ غلط، ۲۰۰ کتابِ بی‌امانت — و شعبهٔ آفتاب که در هر سه جدول غایب است، چون هیچ کتابی ندارد.

> 🤖 **از دستیارت بپرس:** «چرا یک `JOIN` یک‌به‌چند باعث می‌شود `SUM` بزرگ‌تر از واقعیت شود؟» بعد این را بپرس: **«چرا `SUM(DISTINCT col)` این را درست نمی‌کند، در حالی که `COUNT(DISTINCT id)` می‌کند؟»** — جوابِ درست می‌گوید `DISTINCT` روی **مقدار** کار می‌کند نه روی سطر، و برای `id` این دو یکی می‌شوند چون `id` یکتاست. اگر جواب گفت «`SUM(DISTINCT ...)` هم درست است»، همین حالا با عددهای بخشِ ۵ ردش کن.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| cardinality | کاردینالیتی | کاردینالیتی؛ اینکه هر سطر چند جفت در طرفِ مقابل دارد |
| one-to-many | وان تو منی | یک‌به‌چند؛ یک سطر در این طرف، چند سطر در آن طرف |
| fan-out | فن‌اوت | تکثیرِ سطرها در اثرِ یک پیوندِ یک‌به‌چند |
| pre-aggregation | پری‌اگریگیشن | تجمیع قبل از پیوند، تا رابطه یک‌به‌یک شود |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `SUM(DISTINCT pages)` از عددِ واقعی کمتر شد، نه بیشتر؟

**تمرینِ اجباری:** همان تلهٔ این فصل را روی `members` بزن. پرسشی بنویس که «جمعِ سالِ عضویتِ اعضا» را با `SUM` بگیرد، یک بار فقط از `members` و یک بار بعد از `JOIN` به `loans`. **دو عدد و دو شمارشِ سطر چاپ کن.** بعد بگو **کدام عضو بیشترین سهم را در تورم داشت و چرا** — راهنمایی: پرامانت‌ترین عضو را پیدا کن.

**تمرینِ ستاره‌دار ⭐:** پرسشِ بخشِ ۶ را طوری بازنویسی کن که ستونِ سومی هم داشته باشد: **تعدادِ اعضای متمایزی که از هر شعبه کتاب برده‌اند.** بعد سؤالِ سخت را جواب بده: **آیا جمعِ این ستون روی شش شعبه باید برابرِ ۳۰۰ باشد؟** جوابت باید بگوید یک عضو می‌تواند در چند سطر شمرده شود و این چه چیزی را دربارهٔ «جمع زدنِ ستونِ یک شمارشِ متمایز» می‌گوید.

## در فصل بعد

تا اینجا همیشه دو جدولِ متفاوت را به هم وصل کردیم. فصلِ بعد یک جدول را به **خودش** وصل می‌کند — و با آن، ۲۸ جفت کتاب پیدا می‌کند که عنوان و نویسنده‌شان یکی است. بعد سراغِ `CROSS JOIN` می‌رود که عمداً همان کاری را می‌کند که این فصل تصادفی‌اش را ترسناک نامید، و نشان می‌دهد **۱٬۲۰۰ ضربدر ۳٬۹۹۸ یعنی چه.**

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t3/s03-fan-out (یادستان, Yaadestan)
