# فصل ۰۸ — پروژه: یک پاسخ از سه جدول

<div dir="rtl">

# فصل ۰۸ — پروژه: یک پاسخ از سه جدول

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

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

پرسش: **کدام شعبه در دوازده ماهِ گذشته پرکارترین بوده؟**

و بهترین درسِ فصل از دو جای غیرمنتظره می‌آید. **اولی: نسخهٔ ساده‌لوحانهٔ همین گزارش برای هر شش شعبه عددِ ۱٫۰۰ می‌دهد** — جدولی مرتب و کاملاً بی‌معنی که هیچ‌چیزش مشکوک به‌نظر نمی‌رسد. **دومی: پرسشِ «بهترین شعبه» سه جوابِ درست دارد** — کوشا، نسیم، مرکزی — و کدامش را بگویی به این بستگی دارد که چه چیزی را شمرده‌ای.

![سه پیمانهٔ متفاوت روی یک میز که هرکدام یک قطعهٔ واحد را می‌سنجند و سه عددِ متفاوت نشان می‌دهند](/lesson-assets/t3-s08-project-join/prompt/01-image-three-measures.png)

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

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

## قبل از شروع

**همهٔ هفت فصلِ قبل.** هیچ چیزِ تازه‌ای اینجا نیست.

**دانه‌بندیِ سه جدولی که با آن‌ها کار می‌کنیم:** یک سطر از `branches` یک شعبه است؛ یک سطر از `books` یک عنوانِ ثبت‌شده در یک شعبه؛ یک سطر از `loans` یک بار امانت رفتنِ یک کتاب.

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

</div>

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

<div dir="rtl">

## ۱. قدمِ صفر: بشمار، بعد بپرس

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

</div>

```python
print("شعبه‌ها          :", int(q("SELECT COUNT(*) AS n FROM branches").iloc[0, 0]))
print("کتاب‌ها          :", int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0]))
print("امانت‌ها         :", int(q("SELECT COUNT(*) AS n FROM loans").iloc[0, 0]))
print(q("SELECT MIN(loaned_at) AS first_loan, MAX(loaned_at) AS last_loan FROM loans"))
print("امانتِ بی‌کتاب   :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM loans AS l
          LEFT JOIN books AS b ON b.id = l.book_id
          WHERE b.id IS NULL
      """).iloc[0, 0]))
print("کتابِ بی‌شعبه    :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS b
          LEFT JOIN branches AS br ON br.id = b.branch_id
          WHERE br.id IS NULL
      """).iloc[0, 0]))
```

```text
شعبه‌ها          : 7
کتاب‌ها          : 1200
امانت‌ها         : 3998
   first_loan   last_loan
0  2023-01-01  2025-06-30
امانتِ بی‌کتاب   : 0
کتابِ بی‌شعبه    : 0
```

<div dir="rtl">

**دو صفرِ آخر مهم‌ترین خطِ این بخش‌اند.** می‌گویند هر امانت به کتابی اشاره می‌کند که وجود دارد و هر کتاب به شعبه‌ای که وجود دارد — یعنی هیچ `JOIN`ی در ادامه سطری را به‌خاطرِ ارجاعِ خراب نمی‌اندازد. **اگر این دو صفر نبودند، همهٔ عددهای بعدی باید با یک هشدار گزارش می‌شدند.**

و آخرین تاریخِ امانت **۳۰ ژوئنِ ۲۰۲۵** است. پس «دوازده ماهِ گذشته» یعنی از اولِ ژوئیهٔ ۲۰۲۴ تا همان تاریخ — **بازه‌ای که خودمان انتخابش کردیم و باید در گزارش بنویسیمش**، وگرنه هیچ‌کس نمی‌داند عددها دربارهٔ چه دوره‌ای‌اند.

## ۲. پاسخ

سه مرحله: امانت‌های داخلِ بازه را برای هر کتاب بشمار، هر کتاب را با تعدادش کنارِ شعبه‌اش بگذار، بعد گروه‌بندی کن. **تجمیع قبل از پیوند، دقیقاً مثلِ فصلِ ۳.**

</div>

```python
REPORT = """
    WITH loans_window AS (
        SELECT book_id, COUNT(*) AS n
        FROM loans
        WHERE loaned_at >= ?
        GROUP BY book_id
    ),
    per_book AS (
        SELECT b.id, b.branch_id, b.added_at, COALESCE(w.n, 0) AS loans
        FROM books AS b
        LEFT JOIN loans_window AS w ON w.book_id = b.id
    )
    SELECT br.name,
           COUNT(p.id)                AS books,
           SUM(COALESCE(p.loans, 0))  AS loans,
           ROUND(1.0 * SUM(COALESCE(p.loans, 0)) / NULLIF(COUNT(p.id), 0), 2) AS per_book
    FROM branches AS br
    LEFT JOIN per_book AS p ON p.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY per_book DESC
"""
report = q(REPORT, ("2024-07-01",))
print(report)
```

```text
    name  books  loans  per_book
0  مرکزی    197    445      2.26
1   نسیم    208    302      1.45
2   دانش    192    268      1.40
3   بهار    174    184      1.06
4   کوشا    225    201      0.89
5   پویا    204    149      0.73
6  آفتاب      0      0       NaN
```

<div dir="rtl">

**پاسخ: مرکزی، با ۲٫۲۶ امانت به‌ازای هر کتاب.**

و آن `NaN` در سطرِ آخر خطا نیست، **صادق‌ترین خانهٔ این جدول است.** شعبهٔ آفتاب هیچ کتابی ندارد، پس «امانت به‌ازای هر کتاب» برایش تعریف نشده. `NULLIF(COUNT(p.id), 0)` دقیقاً همین را می‌گوید: به‌جای تقسیم بر صفر، `NULL`. **اگر آنجا صفر می‌نوشتیم، جدول ادعا می‌کرد آفتاب بدترین شعبه است — در حالی که آفتاب اصلاً در این مقایسه شرکت ندارد.**

## ۳. اثبات: نه تکثیر شد، نه گم شد

</div>

```python
print("جمع ستون books :", int(report["books"].sum()), "| کل کتاب‌ها: 1200")
print("جمع ستون loans :", int(report["loans"].sum()))
print("امانت‌های بازه :",
      int(q("SELECT COUNT(*) AS n FROM loans WHERE loaned_at >= ?", ("2024-07-01",)).iloc[0, 0]))
print("سطرهای گزارش   :", len(report), "| کل شعبه‌ها: 7")
```

```text
جمع ستون books : 1200 | کل کتاب‌ها: 1200
جمع ستون loans : 1549
امانت‌های بازه : 1549
سطرهای گزارش   : 7 | کل شعبه‌ها: 7
```

<div dir="rtl">

**سه آزمون، هر سه پاس.**

جمعِ ستونِ `books` دقیقاً ۱٬۲۰۰ است — **هیچ کتابی دو بار شمرده نشده و هیچ کتابی جا نمانده.** جمعِ ستونِ `loans` دقیقاً برابرِ تعدادِ امانت‌های بازه است که از یک پرسشِ کاملاً مستقل گرفتیم. و گزارش هفت سطر دارد، نه شش: **شعبهٔ بی‌کتاب هم در جدول هست.**

> ✅ **چک کن:** این سه خط را در هر گزارشی که با `JOIN` می‌سازی بنویس. اگر جمعِ `books` بیشتر از ۱٬۲۰۰ درآمد، جایی fan-out داری؛ اگر کمتر، جایی `INNER JOIN` نوشته‌ای که باید `LEFT` می‌بود.

## ۴. همان گزارش، اگر ساده‌لوحانه نوشته می‌شد

</div>

```python
print(q("""
    SELECT br.name,
           COUNT(*)    AS books,
           COUNT(l.id) AS loans,
           ROUND(1.0 * COUNT(l.id) / COUNT(*), 2) AS per_book
    FROM branches AS br
    JOIN books AS b ON b.branch_id = br.id
    JOIN loans AS l ON l.book_id = b.id
    WHERE l.loaned_at >= ?
    GROUP BY br.id, br.name
    ORDER BY per_book DESC
""", ("2024-07-01",)))
```

```text
    name  books  loans  per_book
0  مرکزی    445    445       1.0
1   نسیم    302    302       1.0
2   دانش    268    268       1.0
3   بهار    184    184       1.0
4   کوشا    201    201       1.0
5   پویا    149    149       1.0
```

<div dir="rtl">

**هر شش شعبه دقیقاً ۱٫۰۰. جدول مرتب است، عددها تمیزند، و گزارش کاملاً بی‌معنی است.**

دلیلش را حالا با یک نگاه می‌بینی: بعد از دو `JOIN`، یک سطر دیگر «یک کتاب» نیست، «یک امانت» است. پس `COUNT(*)` امانت‌ها را شمرد و `COUNT(l.id)` هم امانت‌ها را شمرد، و تقسیمِ یک عدد بر خودش همیشه ۱ است. **مرکزی ۱۹۷ کتاب دارد و این جدول می‌گوید ۴۴۵.**

**و هیچ خطایی داده نشد.** تنها چیزی که مچش را می‌گیرد همان آزمونِ بخشِ ۳ است: جمعِ ستونِ `books` اینجا ۱٬۵۴۹ می‌شود، نه ۱٬۲۰۰.

## ۵. سه «بهترین» متفاوت

قبل از نوشتنِ گزارش، یک پرسشِ آخر: **آیا «پرکارترین» همان «بزرگ‌ترین» است؟**

</div>

```python
print(q("""
    SELECT br.name, COUNT(b.id) AS books, SUM(b.pages) AS pages
    FROM branches AS br
    LEFT JOIN books AS b ON b.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
```

```text
    name  books     pages
0   کوشا    225  106918.0
1   نسیم    208  108608.0
2   پویا    204   97273.0
3  مرکزی    197   94868.0
4   دانش    192   93673.0
5   بهار    174   86709.0
6  آفتاب      0       NaN
```

<div dir="rtl">

**سه سنجه، سه برنده:** بیشترین **تعدادِ کتاب** مالِ کوشاست، بیشترین **تعدادِ صفحه** مالِ نسیم، و بیشترین **امانت به‌ازای هر کتاب** مالِ مرکزی. هر سه از همان سه جدول درآمدند و هر سه درست‌اند.

**پس «کدام شعبه بهترین است؟» پرسشِ کاملی نیست.** جملهٔ کامل باید سنجه‌اش را با خودش بیاورد — و این دقیقاً همان چیزی است که یک گزارشِ خوب را از یک عددِ تنها جدا می‌کند.

**و `NaN` ستونِ `pages` برای آفتاب یک یادآوریِ ترمِ ۱ است:** `SUM` روی صفر سطر، صفر نمی‌دهد؛ `NULL` می‌دهد، چون جمعِ هیچ‌چیزی معلوم نیست.

## ۶. آیا جواب به تعریفِ ما بند است؟

یک ایرادِ منصفانه به گزارشِ بخشِ ۲: کتابی که پارسال به فهرست اضافه شده، فرصتِ کمتری برای امانت رفتن داشته. پس بیایید مخرج را منصفانه‌تر کنیم و **فقط کتاب‌هایی را بشماریم که قبل از شروعِ ثبتِ امانت‌ها در فهرست بوده‌اند.**

</div>

```python
fair = q("""
    WITH loans_window AS (
        SELECT book_id, COUNT(*) AS n
        FROM loans
        WHERE loaned_at >= ?
        GROUP BY book_id
    ),
    per_book AS (
        SELECT b.id, b.branch_id, b.added_at, COALESCE(w.n, 0) AS loans
        FROM books AS b
        LEFT JOIN loans_window AS w ON w.book_id = b.id
    )
    SELECT br.name,
           COUNT(p.id)               AS books,
           SUM(COALESCE(p.loans, 0)) AS loans,
           ROUND(1.0 * SUM(COALESCE(p.loans, 0)) / NULLIF(COUNT(p.id), 0), 2) AS per_book
    FROM branches AS br
    LEFT JOIN per_book AS p ON p.branch_id = br.id AND p.added_at < ?
    GROUP BY br.id, br.name
    ORDER BY per_book DESC
""", ("2024-07-01", "2023-01-01"))
print(fair)
print()
print("ترتیب شعبه‌ها در دو تعریف یکی است؟",
      list(report["name"]) == list(fair["name"]))
```

```text
    name  books  loans  per_book
0  مرکزی    118    263      2.23
1   نسیم    133    221      1.66
2   دانش    116    150      1.29
3   بهار    107    117      1.09
4   کوشا    127    115      0.91
5   پویا    133     98      0.74
6  آفتاب      0      0       NaN

ترتیب شعبه‌ها در دو تعریف یکی است؟ True
```

<div dir="rtl">

**عددها عوض شدند و ترتیب عوض نشد. این یک نتیجهٔ منفی است و باید همان‌قدر صریح گزارش شود که یک نتیجهٔ مثبت.**

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

**ولی این نتیجه را بیشتر از اندازه‌اش باور نکن.** ایرادِ ما این بود که کتابِ تازه فرصتِ کمتری داشته. این داده ساختگی است و تاریخِ امانت در آن به `added_at` بند نیست:

</div>

```python
print("امانت‌های قبل از added_at کتاب:", int(q("""
    SELECT COUNT(*) AS n FROM loans AS l JOIN books AS b ON b.id = l.book_id
    WHERE l.loaned_at < b.added_at
""").iloc[0, 0]))
```

```text
امانت‌های قبل از added_at کتاب: 786
```

<div dir="rtl">

از ۳٬۹۹۸ امانت، ۷۸۶ تا قبل از ورودِ کتابشان به فهرست ثبت شده‌اند. در یک دادهٔ واقعی این خودش مسئله‌ای است که باید بررسی شود. پس آزمونِ این بخش اینجا فقط می‌گوید کنار گذاشتنِ کتاب‌های تازه ترتیب را عوض نمی‌کند، و چیزی دربارهٔ فرصتِ کمترِ آن‌ها ثابت نمی‌کند.

**دقت کن شرطِ تاریخ در `ON` نشست، نه در `WHERE`** — درسِ فصلِ ۲. اگر در `WHERE` می‌نوشتیمش، شعبهٔ آفتاب از جدول می‌افتاد بیرون.

> 🔧 **اگر کار نکرد:** اگر بخواهی شعبه‌های کم‌کار را با یک شرط روی خودِ تجمیع فیلتر کنی و آن را در `WHERE` بگذاری، `SQLite` صریح اعتراض می‌کند — این همان جایی است که `HAVING` لازم است:

</div>

```python
try:
    q("""
        SELECT br.name, COUNT(b.id) AS books
        FROM branches AS br
        LEFT JOIN books AS b ON b.branch_id = br.id
        WHERE COUNT(b.id) > ?
        GROUP BY br.id, br.name
    """, (100,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT br.name, COUNT(b.id) AS books
        FROM branches AS br
        LEFT JOIN books AS b ON b.branch_id = br.id
        WHERE COUNT(b.id) > ?
        GROUP BY br.id, br.name
    ': misuse of aggregate: COUNT()
```

<div dir="rtl">

## ۷. گزارشِ نهایی

سه جمله که می‌شود رویشان تصمیم گرفت، و هر جمله سنجه و دوره‌اش را با خودش می‌آورد:

1. **در دوازده ماهِ منتهی به آخرین امانتِ ثبت‌شده، شعبهٔ مرکزی پرکارترین بوده** — ۲٫۲۶ امانت به‌ازای هر کتاب، در برابرِ ۰٫۷۳ برای کم‌کارترین. جمعِ کتاب‌های گزارش با کلِ فهرست می‌خواند و جمعِ امانت‌ها با شمارشِ مستقلِ همان بازه.
2. **بزرگ‌ترین شعبه مرکزی نیست** — کوشا با ۲۲۵ عنوان بیشترین کتاب را دارد و نسیم بیشترین صفحه را. «بهترین» بدونِ نامِ سنجه معنایی ندارد.
3. **این رتبه‌بندی به تعریفِ مخرج بند نیست** — با محدود کردنِ کتاب‌ها به آن‌هایی که از قبل در فهرست بوده‌اند، همهٔ نرخ‌ها عوض شدند و هیچ جابه‌جایی در ترتیب رخ نداد.

**و آنچه نمی‌دانیم، بخشی از همین گزارش است:** شعبهٔ آفتاب در هیچ‌کدام از این مقایسه‌ها نیست چون هنوز کتابی ندارد؛ نمی‌دانیم اختلافِ ۱٫۴۵ و ۱٫۴۰ بینِ نسیم و دانش معنادار است یا قرعه — ترمِ ۶ فصلِ ۵ ابزارِ سنجیدنش را می‌سازد؛ و کلِ این تحلیل دربارهٔ کتاب‌هایی است که **ثبت شده‌اند** — کتابی که هرگز وارد فهرست نشده در هیچ عددی نیست.

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

> 🤖 **از دستیارت بپرس:** «چرا در گزارشِ ساده‌لوحانه همهٔ شعبه‌ها نرخِ ۱٫۰۰ گرفتند؟» بعد این را بپرس: **«چه آزمونی می‌توانستم قبل از انتشار بزنم که این را می‌گرفت؟»** — جوابِ درست به جمعِ ستونِ `books` در برابرِ کلِ کتاب‌ها اشاره می‌کند. اگر جواب فقط گفت «پرسش را دوباره بخوان»، آزمونی پیشنهاد نداده.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| reconciliation | ری‌کانسیلی‌ایشن | تطبیق؛ سنجیدنِ جمعِ یک گزارش با یک شمارشِ مستقل |
| reporting window | ریپورتینگ ویندو | بازهٔ زمانی‌ای که گزارش دربارهٔ آن است |
| denominator | دنامینیتر | مخرج؛ چیزی که نرخ بر آن تقسیم می‌شود |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: در گزارشِ بخشِ ۴، ستونِ `books` واقعاً چه چیزی را شمرده بود؟

**تمرینِ اجباری:** همان گزارش را برای **اعضا** بنویس، نه شعبه‌ها: هر شهر چند عضو دارد و اعضایش در بازهٔ دوازده‌ماهه چند کتاب برده‌اند. **هر سه آزمونِ بخشِ ۳ را رویش بزن** — جمعِ اعضا برابرِ ۳۰۰، جمعِ امانت‌ها برابرِ عددِ مستقلِ بازه، و تعدادِ سطرها برابرِ تعدادِ شهرهای متمایز. اگر آزمونی نخواند، قبل از گزارش دادن درستش کن.

**تمرینِ ستاره‌دار ⭐:** گزارشِ بخشِ ۲ را با یک تعریفِ سومِ منصفانه هم بگیر: **به‌جای «به‌ازای هر کتاب»، «به‌ازای هر صندلی»** — ستونِ `seats` در `branches`. بعد سؤالِ سخت را جواب بده: **این تعریف ترتیب را عوض می‌کند؟ و کدام‌یک از سه تعریف به پرسشِ «کدام شعبه به بودجهٔ بیشتری نیاز دارد» نزدیک‌تر است؟** جوابت باید بگوید هر مخرج چه چیزی را ثابت فرض می‌کند.

## در فصل بعد

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

ولی در تمامِ این هشت فصل، اسکیما را کسِ دیگری ساخته بود و ما فقط رویش پرسش نوشتیم. **ترمِ ۴ سراغِ خودِ اسکیما می‌رود:** چطور از یک فایلِ کثیف به جدول‌هایی برسی که پایگاه داده خودش از قوانینشان محافظت می‌کند — و چطور فردا عوضشان کنی بدونِ اینکه چیزی بشکند.

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t3/s08-project-join (یادستان, Yaadestan)
