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

آخر این فصل میتوانی:
- یک گزارشِ گروهی بسازی که دانهبندیاش در خودش اعلام شده
- یک ستونِ پنجرهای را کنارِ ستونهای تجمیعی بنشانی
- سه شمارشِ کنترلیِ متفاوت روی یک جدول بگیری
- بگویی هر عددِ گزارشت دربارهٔ چه کسانی نیست
قبل از شروع#
همهٔ هشت فصلِ قبل. هیچ چیزِ تازهای اینجا نیست.
دانهبندیِ جدولِ ورودی: یک سطر از books یعنی یک عنوانِ ثبتشده در یکی از شش شعبه — همان جملهای که از فصلِ اولِ ترمِ ۱ تکرار میشود.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. قدمِ صفر: پرسش را بنویس، دانهبندی را اعلام کن#
پرسشِ سفارشدهنده: «کدام شعبه پرکارترین است؟»
قبل از هر پرسشی در SQL، دو جمله را بنویس:
- یک سطر از خروجی یعنی یک شعبه. نه یک کتاب، نه یک امانت.
- «پرکار» هنوز تعریف نشده. تا وقتی نگویی جمعِ امانت یا امانت بهازای هر کتاب، پرسش جواب ندارد — و فصلِ ۴ نشان داد این دو یک ترتیب نمیسازند.
پس گزارش را طوری میسازیم که هر دو تعریف در آن باشند و خواننده خودش ببیند کجا از هم جدا میشوند.
۲. جدولِ گروهی، با یک ستونِ پنجرهای#
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)
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
شش سطر، و هر ستونش از یک فصلِ متفاوتِ این ترم آمده.
COUNT(*) و SUM از فصلِ ۱، GROUP BY از فصلِ ۲، نرخ بهازای واحد از فصلِ ۴، و ستونِ آخر از فصلِ ۶: SUM(SUM(borrowed)) OVER () — یک تابعِ پنجره که روی نتیجهٔ گروهبندی نشسته و جمعِ کلِ امانتها را در هر سطر میآورد، بدونِ هیچ زیرپرسشی.
✅ چک کن: ستونِ
per_bookرا برای سطرِ اول با دست حساب کن: ۱۸۳۶۴ تقسیم بر ۲۲۴ باید بشود ۸۱٫۹۸. اگر عددت81.0درآمد،* 1.0را جا انداختهای و تقسیمِ صحیحِ فصلِ ۴ اعشار را بریده. و اگر ستونِshare_pctفقط عددهای گِردی مثلِ19.0و18.0داد، همان اشتباه یک جای دیگر تکرار شده:100.0را100نوشتهای.
۳. سه شمارشِ کنترلی#
یک شمارشِ کنترلی کافی نیست، چون هر کدام کلاسِ متفاوتی از خطا را میگیرد.
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))
۱) جمع اجزا برابر کل؟
کتابها : 1200 در برابر 1200
امانتها: 94191 در برابر 94191
۲) تعداد گروهها برابر تعداد شعبههای واقعی؟
گروهها: 6 | سطرهای جدول branches: 6
۳) جمع درصدها:
100.0
هر سه پاس شدند، و هر سه چیزِ متفاوتی را ثابت کردند.
- آزمونِ اول ثابت میکند هیچ سطری در راه گم نشده. ولی ترمِ ۱ فصلِ ۵ نشان داد که این آزمون سطرهای جابهجاشده را نمیگیرد — جمع میتواند درست باشد و هر دو تکه غلط.
- آزمونِ دوم همان را میگیرد که آزمونِ اول نمیگیرد: اگر شعبهای هیچ کتابی نداشته باشد، جمع درست میماند و یک سطر بیصدا از گزارش غایب میشود. در فصلِ ۲ همین شمارش بود که ۴۵ گروه را بهجای ۷ گروه لو داد.
- آزمونِ سوم دقیقاً ۱۰۰ درآمد، و این خوششانسی است نه قاعده: در فصلِ ۴ همین جمع روی هشت دسته ۱۰۰٫۰۱ شد. گِرد کردن جمع را تضمین نمیکند و جملهٔ درست در گزارش این است که درصدها گرد شدهاند، نه اینکه عدد را دستکاری کنی.
۴. ستونی که رتبهبندی را عوض میکند#
جدولِ بخشِ ۲ بر اساسِ جمعِ خام مرتب شده. حالا هر دو رتبه را کنارِ هم بگذاریم:
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
"""))
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
چهار سطرِ اول در هر دو رتبهبندی یکساناند، و دو سطرِ آخر جایشان عوض شده.
شعبهای که پنجمینِ جمعِ خام است، از نظرِ نرخِ هر کتاب ششم میشود — چون کتابهای بیشتری دارد و همان جمع را روی کتابهای بیشتری پخش میکند.
کدام رتبهبندی درست است؟ هر دو، و هرکدام جوابِ پرسشِ متفاوتی:
- جمعِ خام جوابِ «کدام شعبه بیشترین بار را جابهجا کرده؟» است. اگر تصمیمت دربارهٔ نیروی انسانی یا فضاست، این عدد را میخواهی.
- نرخِ هر کتاب جوابِ «در کدام شعبه هر کتاب بهطورِ میانگین بیشتر امانت رفته؟» است. (برای «یک کتابِ معمولی» بهمعنای دقیق باید میانه را بگیری؛ فصلِ ۳ فرقشان را نشان داد.) اگر تصمیمت دربارهٔ خریدِ کتابِ تازه است، این یکی.
و یک هشدارِ لازم دربارهٔ نرخ: اختلافِ دو شعبهٔ آخر کمتر از سه امانت بهازای هر کتاب است. اینکه چنین اختلافی معنادار است یا فقط قرعه، پرسشی است که با ابزارهای این ترم جواب ندارد — ترمِ ۶ فصلِ ۵ ابزارش را میسازد، و تا آنوقت گزارشِ صادقانه عدد را میدهد و همین جمله را هم میدهد.
۵. چه کسی از قلم افتاد#
هر عددِ بالا دربارهٔ کتابهایی است که در فهرستاند. حالا صریح بگوییم دربارهٔ چه کسانی نیست:
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)
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
سه چیز از این خروجی بیرون میآید و هر سه باید در گزارش باشند:
- بینِ ۱۷ و ۲۲ درصدِ کتابهای هر شعبه امتیاز ندارند. پس هر جملهای دربارهٔ کیفیت — که در گزارشِ ما نیست — فقط دربارهٔ همان چهارپنجمِ امتیازدار خواهد بود. ستونهای
booksوloansاز این ماجرا سالماند، چون هیچکدام بهratingکاری ندارند. - داده وسطِ سال تمام میشود. پس هر مقایسهٔ سالانهای روی این جدول سالِ آخرش ناقص است — همان تلهٔ فصلِ ۷.
- ۲۴۰ کتاب هنوز روی دیسکاند و در هیچ عددی از این گزارش نیستند.
🔧 اگر کار نکرد: طبیعیترین ستونی که برای این گزارش میخواهی، تعدادِ صندلیهای هر شعبه است تا بشود «امانت بهازای هر صندلی» را حساب کرد. در دسترس نیست:
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)
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
seats ستونِ جدولِ branches است، نه books. تا وقتی نتوانی دو جدول را کنارِ هم بیاوری، هر سنجهای که ستونهایش در دو جدولِ متفاوتاند از دسترست بیرون است — و این دقیقاً همان چیزی است که ترمِ ۳ حلش میکند.
۶. گزارشِ نهایی#
سه جمله که میشود رویشان تصمیم گرفت، و هر جمله عددش و محدودیتش را با خودش میآورد:
- پرشمارترین شعبه ۱۹٫۵۰ درصدِ کلِ امانتها را دارد و کمسهمترین ۱۴٫۶۳ درصد — یعنی سهمِ شعبهها نسبتاً نزدیک است و هیچ شعبهای مسلط نیست. جمعِ شش سهم دقیقاً ۱۰۰ شد و جمعِ شش شمارش ۱٬۲۰۰.
- رتبهبندی به تعریفِ «پرکار» بستگی دارد، و دو شعبهٔ انتهای جدول با دو تعریف جایشان عوض میشود. گزارش هر دو ستون را دارد تا خواننده مجبور نباشد حدس بزند کدام تعریف به کار رفته.
- نرخِ امانت بهازای هر کتاب بینِ ۷۴٫۲۶ و ۸۱٫۹۸ است — بازهای که پهنایش کمتر از هشت امانت بهازای هر کتاب است.
و آنچه نمیدانیم، بخشی از همین گزارش است: نمیدانیم اختلافِ دو شعبهٔ آخر معنادار است یا قرعه؛ نمیدانیم کتابها چه مدت در فهرست بودهاند و نرخِ ما این را حساب نمیکند؛ نمیدانیم ۲۴۰ کتابِ واردنشده جدول را چقدر عوض میکنند؛ و نمیتوانیم امانت را به ظرفیتِ شعبه بسنجیم، چون آن ستون در جدولِ دیگری است.
📏 اندازه بگیر: یک سطر یعنی چه؟ یک شعبه — و همهٔ ستونهای گزارش دربارهٔ کتابهای همان شعبهاند، نه دربارهٔ امانتها بهعنوان رویدادهای جدا. با کدام شمارشِ دوم سنجیدی؟ سهتا و هر سه از یک جنس نیستند: جمعِ اجزا در برابرِ کل (۱٬۲۰۰ و ۹۴٬۱۹۱)، تعدادِ گروهها در برابرِ سطرهای
branches(۶ و ۶)، و جمعِ درصدها (۱۰۰٫۰). چه کسی از قلم افتاد؟ ۲۳۱ کتابِ بیامتیاز که در ستونهای این گزارش دخالتی ندارند ولی در هر سنجهٔ کیفی میافتادند بیرون؛ نیمهٔ دومِ سالِ آخر؛ ۲۴۰ سطرِarrivals.csv؛ و هر کتابی که هرگز ثبت نشده و دربارهٔ آن حتی نمیتوانیم بگوییم چندتاست.
🤖 از دستیارت بپرس: «برای یک گزارشِ گروهی چه شمارشهای کنترلیای پیشنهاد میکنی؟» بعد این را بپرس: «اگر جمعِ اجزا با کل بخواند، آیا ثابت میشود گروهبندی درست بوده؟» — جوابِ درست «نه» است و باید مثالی بزند که سطرها در گروهِ اشتباه نشستهاند؛ اگر جواب گفت «بله»، با عددهای فصلِ ۲ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| report grain | ریپورت گرین | دانهبندیِ گزارش؛ اینکه یک سطرش یعنی چه |
| control count | کنترل کانت | شمارشِ دومی که درستیِ عددِ اول را میسنجد |
| operational definition | آپریشنال دفینیشن | تعریفِ دقیقِ اینکه یک سنجه با چه چیزی اندازه گرفته میشود |
| coverage | کاورج | اینکه گزارش چه سهمی از واقعیت را پوشش میدهد |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
ترمِ ۲ تمام شد. حالا میتوانی از میلیونها سطر یک جدولِ چندسطری بسازی و برای هر عددش بگویی از چند سطر ساخته شده. ولی این کلِ ترم روی یک جدول کار کرد — و آخرین خطای همین فصل نشان داد که چرا آن محدودیت واقعی است: ستونی که لازم داشتیم در جدولِ دیگری بود.
ترمِ ۳ چند جدول را کنارِ هم میآورد، و بلافاصله با خطرناکترین اتفاقِ این کار روبهرو میشود: پیوندی که بیصدا سطرها را تکثیر میکند و همان SUM درستِ امروز را متورم میکند.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.