# فصل ۰۹ — پروژه: یک گزارشِ گروهیِ واقعی

<div dir="rtl">

# فصل ۰۹ — پروژه: یک گزارشِ گروهیِ واقعی

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

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

و بهترین درسِ فصل از یک ستون درمی‌آید. پرسش این است: «کدام شعبه پرکارترین است؟» و همان جدول دو رتبه‌بندیِ متفاوت می‌سازد — **دو شعبه‌ای که با جمعِ خام رتبهٔ پنجم و ششم دارند، با نرخِ هر کتاب جایشان عوض می‌شود.**

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

![یک کارتِ خلاصهٔ کوچک در یک کفهٔ ترازو و یک دستهٔ بلند از برگه‌ها در کفهٔ دیگر، با تیرک کاملاً تراز](/lesson-assets/t2-s09-project-report/prompt/01-image-summary-and-stack.png)

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

- یک گزارشِ گروهی بسازی که دانه‌بندی‌اش در خودش اعلام شده
- یک ستونِ پنجره‌ای را کنارِ ستون‌های تجمیعی بنشانی
- سه شمارشِ کنترلیِ متفاوت روی یک جدول بگیری
- بگویی هر عددِ گزارشت دربارهٔ چه کسانی **نیست**

## قبل از شروع

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

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

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

</div>

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

<div dir="rtl">

## ۱. قدمِ صفر: پرسش را بنویس، دانه‌بندی را اعلام کن

پرسشِ سفارش‌دهنده: **«کدام شعبه پرکارترین است؟»**

قبل از هر پرسشی در `SQL`، دو جمله را بنویس:

1. **یک سطر از خروجی یعنی یک شعبه.** نه یک کتاب، نه یک امانت.
2. **«پرکار» هنوز تعریف نشده.** تا وقتی نگویی جمعِ امانت یا امانت به‌ازای هر کتاب، پرسش جواب ندارد — و فصلِ ۴ نشان داد این دو یک ترتیب نمی‌سازند.

پس گزارش را طوری می‌سازیم که **هر دو تعریف در آن باشند** و خواننده خودش ببیند کجا از هم جدا می‌شوند.

## ۲. جدولِ گروهی، با یک ستونِ پنجره‌ای

</div>

```python
report = q("""
    SELECT branch,
           COUNT(*)                                   AS books,
           SUM(borrowed)                              AS loans,
           ROUND(SUM(borrowed) * 1.0 / COUNT(*), 2)   AS per_book,
           ROUND(SUM(borrowed) * 100.0 / SUM(SUM(borrowed)) OVER (), 2) AS share_pct
    FROM books
    GROUP BY branch
    ORDER BY loans DESC
""")
print(report)
```

```text
  branch  books  loans  per_book  share_pct
0   نسیم    224  18364     81.98      19.50
1   بهار    214  17498     81.77      18.58
2   کوشا    200  15542     77.71      16.50
3   پویا    195  15122     77.55      16.05
4   دانش    187  13887     74.26      14.74
5  مرکزی    180  13778     76.54      14.63
```

<div dir="rtl">

**شش سطر، و هر ستونش از یک فصلِ متفاوتِ این ترم آمده.**

`COUNT(*)` و `SUM` از فصلِ ۱، `GROUP BY` از فصلِ ۲، نرخ به‌ازای واحد از فصلِ ۴، و ستونِ آخر از فصلِ ۶: `SUM(SUM(borrowed)) OVER ()` — یک تابعِ پنجره که روی نتیجهٔ گروه‌بندی نشسته و جمعِ کلِ امانت‌ها را در هر سطر می‌آورد، بدونِ هیچ زیرپرسشی.

> ✅ **چک کن:** ستونِ `per_book` را برای سطرِ اول با دست حساب کن: ۱۸۳۶۴ تقسیم بر ۲۲۴ باید بشود ۸۱٫۹۸. اگر عددت `81.0` درآمد، `* 1.0` را جا انداخته‌ای و تقسیمِ صحیحِ فصلِ ۴ اعشار را بریده. **و اگر ستونِ `share_pct` فقط عددهای گِردی مثلِ `19.0` و `18.0` داد، همان اشتباه یک جای دیگر تکرار شده: `100.0` را `100` نوشته‌ای.**

## ۳. سه شمارشِ کنترلی

یک شمارشِ کنترلی کافی نیست، چون هر کدام کلاسِ متفاوتی از خطا را می‌گیرد.

</div>

```python
whole = q("SELECT COUNT(*) AS books, SUM(borrowed) AS loans FROM books")

print("۱) جمع اجزا برابر کل؟")
print("   کتاب‌ها :", int(report["books"].sum()), "در برابر", int(whole["books"][0]))
print("   امانت‌ها:", int(report["loans"].sum()), "در برابر", int(whole["loans"][0]))
print()
print("۲) تعداد گروه‌ها برابر تعداد شعبه‌های واقعی؟")
print("   گروه‌ها:", len(report),
      "| سطرهای جدول branches:", int(q("SELECT COUNT(*) FROM branches").iloc[0, 0]))
print()
print("۳) جمع درصدها:")
print("   ", round(float(report["share_pct"].sum()), 4))
```

```text
۱) جمع اجزا برابر کل؟
   کتاب‌ها : 1200 در برابر 1200
   امانت‌ها: 94191 در برابر 94191

۲) تعداد گروه‌ها برابر تعداد شعبه‌های واقعی؟
   گروه‌ها: 6 | سطرهای جدول branches: 6

۳) جمع درصدها:
    100.0
```

<div dir="rtl">

**هر سه پاس شدند، و هر سه چیزِ متفاوتی را ثابت کردند.**

- **آزمونِ اول** ثابت می‌کند هیچ سطری در راه گم نشده. **ولی ترمِ ۱ فصلِ ۵ نشان داد که این آزمون سطرهای جابه‌جاشده را نمی‌گیرد** — جمع می‌تواند درست باشد و هر دو تکه غلط.
- **آزمونِ دوم** همان را می‌گیرد که آزمونِ اول نمی‌گیرد: اگر شعبه‌ای هیچ کتابی نداشته باشد، جمع درست می‌ماند و یک سطر بی‌صدا از گزارش غایب می‌شود. **در فصلِ ۲ همین شمارش بود که ۴۵ گروه را به‌جای ۷ گروه لو داد.**
- **آزمونِ سوم** دقیقاً ۱۰۰ درآمد، و **این خوش‌شانسی است نه قاعده**: در فصلِ ۴ همین جمع روی هشت دسته ۱۰۰٫۰۱ شد. گِرد کردن جمع را تضمین نمی‌کند و **جملهٔ درست در گزارش این است که درصدها گرد شده‌اند**، نه اینکه عدد را دستکاری کنی.

## ۴. ستونی که رتبه‌بندی را عوض می‌کند

جدولِ بخشِ ۲ بر اساسِ جمعِ خام مرتب شده. حالا هر دو رتبه را کنارِ هم بگذاریم:

</div>

```python
print(q("""
    SELECT branch,
           SUM(borrowed)                              AS loans,
           ROUND(SUM(borrowed) * 1.0 / COUNT(*), 2)   AS per_book,
           RANK() OVER (ORDER BY SUM(borrowed) DESC)  AS by_loans,
           RANK() OVER (ORDER BY SUM(borrowed) * 1.0 / COUNT(*) DESC) AS by_rate
    FROM books
    GROUP BY branch
    ORDER BY by_loans
"""))
```

```text
  branch  loans  per_book  by_loans  by_rate
0   نسیم  18364     81.98         1        1
1   بهار  17498     81.77         2        2
2   کوشا  15542     77.71         3        3
3   پویا  15122     77.55         4        4
4   دانش  13887     74.26         5        6
5  مرکزی  13778     76.54         6        5
```

<div dir="rtl">

**چهار سطرِ اول در هر دو رتبه‌بندی یکسان‌اند، و دو سطرِ آخر جایشان عوض شده.**

شعبه‌ای که پنجمینِ جمعِ خام است، از نظرِ نرخِ هر کتاب ششم می‌شود — چون کتاب‌های بیشتری دارد و همان جمع را روی کتاب‌های بیشتری پخش می‌کند.

**کدام رتبه‌بندی درست است؟ هر دو، و هرکدام جوابِ پرسشِ متفاوتی:**

- **جمعِ خام** جوابِ «کدام شعبه بیشترین بار را جابه‌جا کرده؟» است. اگر تصمیمت دربارهٔ نیروی انسانی یا فضاست، این عدد را می‌خواهی.
- **نرخِ هر کتاب** جوابِ «در کدام شعبه هر کتاب به‌طورِ میانگین بیشتر امانت رفته؟» است. (برای «یک کتابِ معمولی» به‌معنای دقیق باید میانه را بگیری؛ فصلِ ۳ فرقشان را نشان داد.) اگر تصمیمت دربارهٔ خریدِ کتابِ تازه است، این یکی.

**و یک هشدارِ لازم دربارهٔ نرخ:** اختلافِ دو شعبهٔ آخر کمتر از سه امانت به‌ازای هر کتاب است. **این‌که چنین اختلافی معنادار است یا فقط قرعه، پرسشی است که با ابزارهای این ترم جواب ندارد** — ترمِ ۶ فصلِ ۵ ابزارش را می‌سازد، و تا آن‌وقت گزارشِ صادقانه عدد را می‌دهد و همین جمله را هم می‌دهد.

## ۵. چه کسی از قلم افتاد

هر عددِ بالا دربارهٔ کتاب‌هایی است که **در فهرست‌اند**. حالا صریح بگوییم دربارهٔ چه کسانی نیست:

</div>

```python
print(q("""
    SELECT branch,
           COUNT(*)                                  AS books,
           SUM(rating IS NULL)                       AS unrated,
           ROUND(SUM(rating IS NULL) * 100.0 / COUNT(*), 2) AS unrated_pct
    FROM books
    GROUP BY branch
    ORDER BY unrated_pct DESC
"""))
print()
print("لبه‌های داده:")
print(q("SELECT MIN(added_at) AS first_day, MAX(added_at) AS last_day FROM books"))
print("سطرهای arrivals.csv که هنوز وارد نشده‌اند:",
      len(CSV_PATH.read_text(encoding="utf-8").splitlines()) - 1)
```

```text
  branch  books  unrated  unrated_pct
0   پویا    195       42        21.54
1   کوشا    200       43        21.50
2   نسیم    224       42        18.75
3   دانش    187       35        18.72
4   بهار    214       38        17.76
5  مرکزی    180       31        17.22

لبه‌های داده:
    first_day    last_day
0  2019-01-01  2025-06-30
سطرهای arrivals.csv که هنوز وارد نشده‌اند: 240
```

<div dir="rtl">

سه چیز از این خروجی بیرون می‌آید و هر سه باید در گزارش باشند:

1. **بینِ ۱۷ و ۲۲ درصدِ کتاب‌های هر شعبه امتیاز ندارند.** پس هر جمله‌ای دربارهٔ کیفیت — که در گزارشِ ما نیست — فقط دربارهٔ همان چهارپنجمِ امتیازدار خواهد بود. ستون‌های `books` و `loans` از این ماجرا سالم‌اند، چون هیچ‌کدام به `rating` کاری ندارند.
2. **داده وسطِ سال تمام می‌شود.** پس هر مقایسهٔ سالانه‌ای روی این جدول سالِ آخرش ناقص است — همان تلهٔ فصلِ ۷.
3. **۲۴۰ کتاب هنوز روی دیسک‌اند و در هیچ عددی از این گزارش نیستند.**

> 🔧 **اگر کار نکرد:** طبیعی‌ترین ستونی که برای این گزارش می‌خواهی، تعدادِ صندلی‌های هر شعبه است تا بشود «امانت به‌ازای هر صندلی» را حساب کرد. در دسترس نیست:

</div>

```python
try:
    q("SELECT branch, SUM(borrowed) * 1.0 / seats AS per_seat FROM books GROUP BY branch")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql 'SELECT branch, SUM(borrowed) * 1.0 / seats AS per_seat FROM books GROUP BY branch': no such column: seats
```

<div dir="rtl">

`seats` ستونِ جدولِ `branches` است، نه `books`. **تا وقتی نتوانی دو جدول را کنارِ هم بیاوری، هر سنجه‌ای که ستون‌هایش در دو جدولِ متفاوت‌اند از دسترست بیرون است** — و این دقیقاً همان چیزی است که ترمِ ۳ حلش می‌کند.

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

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

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

**و آنچه نمی‌دانیم، بخشی از همین گزارش است:** نمی‌دانیم اختلافِ دو شعبهٔ آخر معنادار است یا قرعه؛ نمی‌دانیم کتاب‌ها چه مدت در فهرست بوده‌اند و نرخِ ما این را حساب نمی‌کند؛ نمی‌دانیم ۲۴۰ کتابِ واردنشده جدول را چقدر عوض می‌کنند؛ و نمی‌توانیم امانت را به ظرفیتِ شعبه بسنجیم، چون آن ستون در جدولِ دیگری است.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** یک شعبه — و همهٔ ستون‌های گزارش دربارهٔ کتاب‌های همان شعبه‌اند، نه دربارهٔ امانت‌ها به‌عنوان رویدادهای جدا. **با کدام شمارشِ دوم سنجیدی؟** سه‌تا و هر سه از یک جنس نیستند: جمعِ اجزا در برابرِ کل (۱٬۲۰۰ و ۹۴٬۱۹۱)، تعدادِ گروه‌ها در برابرِ سطرهای `branches` (۶ و ۶)، و جمعِ درصدها (۱۰۰٫۰). **چه کسی از قلم افتاد؟** ۲۳۱ کتابِ بی‌امتیاز که در ستون‌های این گزارش دخالتی ندارند ولی در هر سنجهٔ کیفی می‌افتادند بیرون؛ نیمهٔ دومِ سالِ آخر؛ ۲۴۰ سطرِ `arrivals.csv`؛ و هر کتابی که هرگز ثبت نشده و دربارهٔ آن حتی نمی‌توانیم بگوییم چندتاست.

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

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

</div>

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

<div dir="rtl">

## تمرین‌ها

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

**تمرینِ اجباری:** همین گزارش را برای **دسته** بساز به‌جای شعبه، با همان پنج ستون. **هر سه شمارشِ کنترلی را هم بگیر.** بعد جوابِ سؤالِ اصلی را بنویس: **کدام دسته با جمعِ خام و با نرخِ هر کتاب بیشترین جابه‌جاییِ رتبه را دارد، و چند رتبه؟** و اگر جمعِ درصدها این بار دقیقاً ۱۰۰ نشد، بنویس چرا و چه کارش می‌کنی.

**تمرینِ ستاره‌دار ⭐:** یک ستونِ ششم به گزارشِ شعبه‌ها اضافه کن: **سهمِ هر شعبه از کتاب‌ها** (نه از امانت‌ها). حالا دو ستونِ سهم داری. **برای هر شعبه اختلافِ این دو سهم را حساب کن و بنویس کدام شعبه‌ها سهمشان از امانت بیشتر از سهمشان از کتاب است.** بعد سؤالِ سخت: **این اختلاف با ستونِ نرخِ هر کتاب چه رابطه‌ای دارد؟** جوابت باید بگوید آیا این دو همیشه یک ترتیب می‌سازند یا نه، و چرا.

## در فصل بعد

ترمِ ۲ تمام شد. حالا می‌توانی از میلیون‌ها سطر یک جدولِ چندسطری بسازی و برای هر عددش بگویی از چند سطر ساخته شده. **ولی این کلِ ترم روی یک جدول کار کرد** — و آخرین خطای همین فصل نشان داد که چرا آن محدودیت واقعی است: ستونی که لازم داشتیم در جدولِ دیگری بود.

ترمِ ۳ چند جدول را کنارِ هم می‌آورد، **و بلافاصله با خطرناک‌ترین اتفاقِ این کار روبه‌رو می‌شود: پیوندی که بی‌صدا سطرها را تکثیر می‌کند و همان `SUM` درستِ امروز را متورم می‌کند.**

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t2/s09-project-report (یادستان, Yaadestan)
