# فصل ۰۸ — پروژه: سه پاسخ از یک محمولهٔ خام

<div dir="rtl">

# فصل ۰۸ — پروژه: سه پاسخ از یک محمولهٔ خام

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

هیچ ابزارِ تازه‌ای. این فصل کارِ کامل است: **همان ۲۴۰ کتابِ `arrivals.csv`، سه پرسش، سه پاسخ، و برای هر پاسخ یک شمارشِ دوم و یک جملهٔ صریح دربارهٔ اینکه چه کسی از قلم افتاد.**

و بهترین درسِ این فصل از پرسشِ سوم درمی‌آید. سؤال این است: «این محموله چقدرش واقعاً تازه است؟» و دو جوابِ کاملاً درست می‌گیریم: **۹۹ عنوان از ۱۴۱ عنوانِ محموله از قبل در فهرست هست** — یعنی ۷۰ درصدش تکراری به نظر می‌رسد — ولی وقتی معیار را از «عنوان» به «عنوان و نویسنده» عوض می‌کنیم، عدد می‌شود **۸ از ۲۴۰**، یعنی سه درصد.

**هیچ‌کدام غلط نیست. دو پرسشِ متفاوت‌اند که یک جملهٔ فارسی هر دو را می‌پوشاند.** و کلِ این ترم برای همین یک لحظه بود.

![دو خط‌کشِ مدرج با فاصلهٔ درجه‌بندیِ متفاوت که روی یک قطعهٔ واحد گذاشته شده‌اند](/lesson-assets/t1-s08-project-first-answers/prompt/01-image-two-rulers.png)

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

- یک فایلِ خام را وارد کنی و اسکیمای سالمش را بسازی
- سه پرسشِ واقعی را به `SQL` ترجمه کنی و جوابشان را با یک شمارشِ دوم بسنجی
- بگویی هر عددت از کدام سطرها ساخته شده و کدام سطرها را ندیده
- تشخیص بدهی کِی یک پرسشِ فارسی بیش از یک ترجمه دارد

## قبل از شروع

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

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

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

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `books` | ۱٬۲۰۰ | کمتر از یک ثانیه |
| `arrivals` (از `CSV` ساخته می‌شود) | ۲۴۰ | آنی |

<div dir="rtl">

## ۱. قدمِ صفر: وارد کن، بعد ادعا کن

</div>

```python
import csv

with CSV_PATH.open(encoding="utf-8") as fh:
    incoming = list(csv.DictReader(fh))

digits = dict(zip("۰۱۲۳۴۵۶۷۸۹", "0123456789"))
rows = []
for r in incoming:
    year = "".join(digits.get(ch, ch) for ch in r["year"])
    rows.append((r["title"], r["author"], r["category"], int(r["pages"]),
                 int(year), int(r["price_text"]), r["donor"] or None))

con.execute("DROP TABLE IF EXISTS arrivals")
con.execute("""
    CREATE TABLE arrivals (
        id       INTEGER PRIMARY KEY,
        title    TEXT,
        author   TEXT,
        category TEXT,
        pages    INTEGER,
        year     INTEGER,
        price    INTEGER,
        donor    TEXT
    )
""")
con.executemany("""
    INSERT INTO arrivals (title, author, category, pages, year, price, donor)
    VALUES (?, ?, ?, ?, ?, ?, ?)
""", rows)
con.commit()

print("سطرهای فایل  :", len(incoming))
print("سطرهای جدول  :", int(q("SELECT COUNT(*) FROM arrivals").iloc[0, 0]))
print("year متنی    :",
      int(q("SELECT COUNT(*) FROM arrivals WHERE typeof(year) = ?", ("text",)).iloc[0, 0]))
print("price متنی   :",
      int(q("SELECT COUNT(*) FROM arrivals WHERE typeof(price) = ?", ("text",)).iloc[0, 0]))
print("donor نامعلوم:",
      int(q("SELECT COUNT(*) FROM arrivals WHERE donor IS NULL").iloc[0, 0]))
```

```text
سطرهای فایل  : 240
سطرهای جدول  : 240
year متنی    : 0
price متنی   : 0
donor نامعلوم: 38
```

<div dir="rtl">

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

آن ۳۸تا را همین حالا ثبت کن، چون **هر پرسشی که از `donor` استفاده کند، این ۳۸ سطر را نمی‌بیند.**

> ✅ **چک کن:** اگر `year متنی` صفر نبود، حلقهٔ تبدیلِ رقم را جا انداخته‌ای. اگر `سطرهای جدول` بیشتر از ۲۴۰ بود، سلول را دو بار اجرا کرده‌ای بدونِ اینکه `DROP TABLE` عمل کند.

## ۲. پرسشِ اول: سهمِ هر دسته در این محموله

`SQL` هنوز ابزارِ گروه‌بندی به ما نداده — آن کارِ ترمِ ۲ است. پس همان کار را با هشت پرسشِ جدا انجام می‌دهیم:

</div>

```python
categories = list(q("SELECT DISTINCT category FROM arrivals ORDER BY category")["category"])
counts = []
for name in categories:
    n = int(q("SELECT COUNT(*) FROM arrivals WHERE category = ?", (name,)).iloc[0, 0])
    counts.append((name, n))

for name, n in sorted(counts, key=lambda pair: -pair[1]):
    print(f"  {name:<12} {n:>4}  {'█' * (n // 2)}")
print()
print("جمع همهٔ دسته‌ها:", sum(n for _, n in counts), "| سطرهای جدول: 240")
print("دسته‌های بدون مقدار:",
      int(q("SELECT COUNT(*) FROM arrivals WHERE category IS NULL").iloc[0, 0]))
```

```text
  هنر            45  ██████████████████████
  کودک           32  ████████████████
  علم            31  ███████████████
  فلسفه          31  ███████████████
  رمان           29  ██████████████
  شعر            25  ████████████
  تاریخ          24  ████████████
  زندگی‌نامه     23  ███████████

جمع همهٔ دسته‌ها: 240 | سطرهای جدول: 240
دسته‌های بدون مقدار: 0
```

<div dir="rtl">

**پاسخ: «هنر» با ۴۵ کتاب بیشترین سهم را دارد.**

**شمارشِ دوم:** جمعِ هشت دسته دقیقاً ۲۴۰ شد. **چه کسی از قلم افتاد:** هیچ‌کس — `category IS NULL` صفر است. این یک نتیجهٔ منفی است و باید همان‌قدر صریح گزارش شود که یک نتیجهٔ مثبت؛ در فصلِ ۶ دیدیم که وقتی این عدد صفر نباشد، همین جمع بی‌صدا کمتر از کل می‌شود.

**ولی یک احتیاطِ لازم:** ۴۵ در برابرِ ۳۲ اختلافِ بزرگی به نظر می‌رسد، و روی ۲۴۰ سطر لزوماً نیست. اینکه چه اختلافی معنادار است و چه اختلافی فقط قرعه، سؤالی است که با ابزارهای این ترم جواب ندارد — **ترمِ ۶ فصلِ ۵ دقیقاً همین را جواب می‌دهد.** تا آن‌وقت، **گزارشِ صادقانه عدد را می‌دهد و همین جمله را هم می‌دهد.**

## ۳. پرسشِ دوم: این محموله از فهرست تازه‌تر است؟

</div>

```python
new_avg = float(q("SELECT AVG(year) FROM arrivals").iloc[0, 0])
old_avg = float(q("SELECT AVG(year) FROM books").iloc[0, 0])
new_mid = int(q("SELECT year FROM arrivals ORDER BY year LIMIT 1 OFFSET 119").iloc[0, 0])
old_mid = int(q("SELECT year FROM books ORDER BY year LIMIT 1 OFFSET 599").iloc[0, 0])

print("میانگین سال — محمولهٔ تازه:", round(new_avg, 2))
print("میانگین سال — فهرست فعلی  :", round(old_avg, 2))
print("سال میانی  — محمولهٔ تازه :", new_mid)
print("سال میانی  — فهرست فعلی   :", old_mid)
print()
print("قدیمی‌ترین و تازه‌ترین محموله:",
      int(q("SELECT year FROM arrivals ORDER BY year ASC LIMIT 1").iloc[0, 0]),
      "تا",
      int(q("SELECT year FROM arrivals ORDER BY year DESC LIMIT 1").iloc[0, 0]))
print("قدیمی‌ترین و تازه‌ترین فهرست :",
      int(q("SELECT year FROM books ORDER BY year ASC LIMIT 1").iloc[0, 0]),
      "تا",
      int(q("SELECT year FROM books ORDER BY year DESC LIMIT 1").iloc[0, 0]))
```

```text
میانگین سال — محمولهٔ تازه: 2021.48
میانگین سال — فهرست فعلی  : 2001.53
سال میانی  — محمولهٔ تازه : 2022
سال میانی  — فهرست فعلی   : 2001

قدیمی‌ترین و تازه‌ترین محموله: 2018 تا 2025
قدیمی‌ترین و تازه‌ترین فهرست : 1980 تا 2024
```

<div dir="rtl">

**پاسخ: بله، حدودِ بیست سال.**

**شمارشِ دوم:** میانگین را با **سالِ میانی** سنجیدیم — سطرِ وسط وقتی همه را مرتب کنی، که با `LIMIT 1 OFFSET` از فصلِ ۴ گرفتیم. میانگین می‌گوید ۲۰۲۱٫۴۸ و ۲۰۰۱٫۵۳؛ سالِ میانی می‌گوید ۲۰۲۲ و ۲۰۰۱. **دو معیارِ مستقل، یک نتیجه — و این چیزی است که به آن اعتماد می‌کنیم، نه به یکی‌شان.**

خطِ آخر هم دامنه را نشان می‌دهد و کاملاً بی‌هم‌پوشانی نیست: محموله از ۲۰۱۸ شروع می‌شود و فهرست تا ۲۰۲۴ می‌رود. **پس جملهٔ درست این نیست که «همهٔ کتاب‌های تازه از همهٔ کتاب‌های فهرست جدیدترند»؛ این است که «مرکزِ محموله حدودِ بیست سال جلوتر است».**

**چه کسی از قلم افتاد:** هیچ سطری، چون `year` در هیچ‌کدام از دو جدول `NULL` نیست: `COUNT(year)` در `arrivals` برابرِ ۲۴۰ و در `books` برابرِ ۱۲۰۰ است، درست برابرِ تعدادِ سطرها. **این شمارش لازم است، چون اگر `year` در چند سطر خالی می‌بود، `AVG` بی‌صدا مخرجش را عوض می‌کرد و ما هیچ‌وقت نمی‌فهمیدیم.**

## ۴. پرسشِ سوم: این‌ها واقعاً کتاب‌های تازه‌اند؟

</div>

```python
new_titles = list(q("SELECT DISTINCT title FROM arrivals")["title"])
old_titles = set(q("SELECT DISTINCT title FROM books")["title"])

already = [t for t in new_titles if t in old_titles]
print("عنوان‌های یکتا در محموله      :", len(new_titles))
print("از این‌ها، چندتا از قبل هستند؟:", len(already))
print("عنوان کاملاً تازه            :", len(new_titles) - len(already))
print()
sample = already[0]
print("نمونه:", sample)
print("  در فهرست فعلی:",
      int(q("SELECT COUNT(*) FROM books WHERE title = ?", (sample,)).iloc[0, 0]), "سطر")
print("  در محموله    :",
      int(q("SELECT COUNT(*) FROM arrivals WHERE title = ?", (sample,)).iloc[0, 0]), "سطر")
```

```text
عنوان‌های یکتا در محموله      : 141
از این‌ها، چندتا از قبل هستند؟: 99
عنوان کاملاً تازه            : 42

نمونه: سنگ در باران
  در فهرست فعلی: 13 سطر
  در محموله    : 3 سطر
```

<div dir="rtl">

**نودونه از ۱۴۱ عنوان از قبل در فهرست هست — یعنی هفتاد درصدِ محموله تکراری به نظر می‌رسد.**

قبل از اینکه این را گزارش کنی، به دانه‌بندی برگرد. **این عدد دربارهٔ «عنوان» است، نه دربارهٔ «کتاب».** و عنوان چیزِ یکتایی نیست: همان نمونه در فهرست ۱۳ سطر دارد و در محموله ۳ سطر.

پس همان پرسش را با معیارِ سخت‌گیرانه‌تری بپرسیم:

</div>

```python
same_title_and_author = 0
for row in q("SELECT title, author FROM arrivals").itertuples(index=False):
    n = int(q("SELECT COUNT(*) FROM books WHERE title = ? AND author = ?",
              (row.title, row.author)).iloc[0, 0])
    if n > 0:
        same_title_and_author += 1

print("سطرهای محموله                          :", 240)
print("سطرهایی که عنوان و نویسنده‌شان از قبل هست:", same_title_and_author)
print("یعنی نسخهٔ تکراری احتمالی               :", same_title_and_author, "از 240")
```

```text
سطرهای محموله                          : 240
سطرهایی که عنوان و نویسنده‌شان از قبل هست: 8
یعنی نسخهٔ تکراری احتمالی               : 8 از 240
```

<div dir="rtl">

**هفتاد درصد شد سه درصد.**

هر دو عدد از همان دو جدول آمدند و هیچ‌کدام اشتباه محاسبه نشده. **تفاوت در تعریف است:** «عنوانی که تکراری است» یک چیز است و «کتابی که از قبل داریم» چیزِ دیگری. جملهٔ فارسیِ «چقدرِ این محموله تازه است؟» هر دو را می‌پوشاند و همین ابهام، منشأِ عددِ غلط در گزارش‌های واقعی است.

**و عددِ ۸ هم هنوز جوابِ قطعی نیست**، چون در فصلِ ۴ دیدیم که همان نامِ نویسنده ممکن است با دو نویسهٔ متفاوت نوشته شده باشد. یعنی ۸ **کفِ** تعدادِ تکراری‌هاست، نه عددِ دقیقش. ابزارِ درستِ این مقایسه `JOIN` است که ترمِ ۳ می‌آورد.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در جدولِ دسته‌ها یک سطر یک دسته است؛ در پرسشِ سوم دو دانه‌بندیِ متفاوت داشتیم — یکی «عنوانِ یکتا» و یکی «سطرِ محموله» — و همین تفاوت، ۷۰ درصد را به ۳ درصد رساند. **با کدام شمارشِ دوم سنجیدی؟** جمعِ هشت دسته که ۲۴۰ شد؛ میانگینِ سال در برابرِ سالِ میانی که هر دو یک نتیجه دادند؛ و معیارِ «عنوان» در برابرِ معیارِ «عنوان و نویسنده». **چه کسی از قلم افتاد؟** ۳۸ کتاب که اهداکننده‌شان نامعلوم است و از هر پرسشی روی `donor` بیرون می‌مانند؛ نسخه‌های تکراری‌ای که ممکن است به‌خاطرِ نویسهٔ عربیِ نامِ نویسنده شمرده نشده باشند؛ و هر کتابی که هنوز به هیچ‌کدام از این دو فایل نرسیده.

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

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

1. **محمولهٔ ۲۴۰تایی به‌سمتِ هنر سنگین است** — ۴۵ کتاب از ۲۴۰، در برابرِ ۲۳ کتاب برای کم‌سهم‌ترین دسته. جمعِ دسته‌ها با کلِ محموله می‌خواند و هیچ کتابی بی‌دسته نیست.
2. **این محموله حدودِ بیست سال از فهرستِ فعلی تازه‌تر است** — میانگینِ سال ۲۰۲۱٫۴۸ در برابرِ ۲۰۰۱٫۵۳، و سالِ میانی ۲۰۲۲ در برابرِ ۲۰۰۱. دو معیارِ مستقل یک نتیجه دادند.
3. **دستِ‌کم ۸ کتاب از ۲۴۰ احتمالاً نسخهٔ دومِ چیزی است که داریم** — نه ۹۹تا. عددِ ۹۹ دربارهٔ عنوان‌هاست، نه کتاب‌ها. و ۸ کف است نه سقف، چون نامِ نویسنده در این داده یکدست نیست.

**و آنچه هنوز نمی‌دانیم، بخشی از همین گزارش است:** ۳۸ کتاب اهداکننده‌شان نامعلوم است؛ نمی‌دانیم اختلافِ ۴۵ و ۲۳ معنادار است یا قرعه؛ و نمی‌دانیم عددِ واقعیِ تکراری‌ها چند است.

> 🔧 **اگر کار نکرد:** پرتکرارترین خطای این فصل این است که پرسشی را از فصلِ قبل کپی کنی و ستونی را صدا بزنی که در `arrivals` وجود ندارد:

</div>

```python
try:
    q("SELECT COUNT(*) FROM arrivals WHERE borrowed > ?", (100,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql 'SELECT COUNT(*) FROM arrivals WHERE borrowed > ?': no such column: borrowed
```

<div dir="rtl">

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

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

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| control count | کنترل کانت | شمارشِ دومی که درستیِ عددِ اول را می‌سنجد |
| operational definition | آپریشنال دفینیشن | تعریفِ دقیقِ اینکه یک سنجه با چه چیزی اندازه گرفته می‌شود |
| median | مدیان | سالِ میانی؛ مقدارِ وسطِ فهرستِ مرتب‌شده |
| deduplication | دی‌دوپلیکیشن | پیدا کردن و یکی کردنِ سطرهای تکراری |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا ۹۹ و ۸ هر دو درست‌اند؟

**تمرینِ اجباری:** پرسشِ چهارم را خودت بپرس و کاملش کن: **«گران‌ترین دستهٔ این محموله کدام است؟»** جواب را با `AVG(price)` برای هر دسته بگیر، **بعد همان را با یک معیارِ دوم بسنج** — مثلاً قیمتِ میانیِ هر دسته با `ORDER BY ... LIMIT 1 OFFSET`. اگر دو معیار یک دسته را نشان دادند، بنویس. **اگر ندادند، آن هم یک نتیجه است و باید نوشته شود** — و همان‌جا بگو کدام معیار به پرسشِ «گران» نزدیک‌تر است و چرا.

**تمرینِ ستاره‌دار ⭐:** عددِ ۸ را دقیق‌تر کن. نامِ نویسنده را قبل از مقایسه با `replace` یکدست کن (فصلِ ۵) و دوباره بشمار. **عدد چقدر بالا رفت؟** بعد سؤالِ سخت را جواب بده: **این عددِ تازه سقف است یا هنوز کف؟** جوابت باید دستِ‌کم یک راهِ دیگر را نام ببرد که هنوز می‌تواند دو ثبت از یک کتاب را از هم جدا نگه دارد.

## در فصل بعد

ترمِ ۱ تمام شد. حالا می‌توانی سطر بیرون بکشی، ولی هر بار که خواستی از سطرها **گروه** بسازی — سهمِ هر دسته، میانگینِ هر شعبه، فروشِ هر ماه — مجبور شدی هشت پرسشِ جدا بزنی و در پایتون کنارِ هم بچینی. ترمِ ۲ با `GROUP BY` این کار را در یک پرسش انجام می‌دهد، **و بلافاصله سراغِ سؤالی می‌رود که این فصل باز گذاشت: وقتی می‌گویی «میانگین»، میانگین روی چه چیزی؟**

</div>

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