پروژه: سه پاسخ از یک محمولهٔ خام

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

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

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

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

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

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

قبل از شروع#

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

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

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

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

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]))

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

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

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

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

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

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

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]))

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

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

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

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

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

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

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]))

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

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

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

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

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

چه کسی از قلم افتاد: هیچ سطری، چون year در هیچ‌کدام از دو جدول NULL نیست — ولی این را باید بررسی می‌کردیم و در بخشِ ۱ بررسی کردیم. اگر همان‌جا year در چند سطر خالی می‌بود، AVG بی‌صدا مخرجش را عوض می‌کرد و ما هیچ‌وقت نمی‌فهمیدیم.

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

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]), "سطر")

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

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

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

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

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

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")

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

در فصل بعد#

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