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

آخر این فصل میتوانی:
- یک پرسشِ فارسی را به یک پرسشِ سهجدولی ترجمه کنی
- قبل از هر عددی، دانهبندیِ هر سه جدول را اعلام و بررسی کنی
- ثابت کنی که نتیجهات نه تکثیر شده و نه سطری از دست داده
- گزارشی بنویسی که هر عددش سنجهاش را با خودش میآورد
قبل از شروع#
همهٔ هفت فصلِ قبل. هیچ چیزِ تازهای اینجا نیست.
دانهبندیِ سه جدولی که با آنها کار میکنیم: یک سطر از branches یک شعبه است؛ یک سطر از books یک عنوانِ ثبتشده در یک شعبه؛ یک سطر از loans یک بار امانت رفتنِ یک کتاب.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
branches |
۷ | آنی |
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
loans |
۳٬۹۹۸ | کمتر از یک ثانیه |
۱. قدمِ صفر: بشمار، بعد بپرس#
قبل از هر پرسشِ تحلیلی، چیزهایی که باید بدانی و پنج خط طول میکشند:
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]))
شعبهها : 7
کتابها : 1200
امانتها : 3998
first_loan last_loan
0 2023-01-01 2025-06-30
امانتِ بیکتاب : 0
کتابِ بیشعبه : 0
دو صفرِ آخر مهمترین خطِ این بخشاند. میگویند هر امانت به کتابی اشاره میکند که وجود دارد و هر کتاب به شعبهای که وجود دارد — یعنی هیچ JOINی در ادامه سطری را بهخاطرِ ارجاعِ خراب نمیاندازد. اگر این دو صفر نبودند، همهٔ عددهای بعدی باید با یک هشدار گزارش میشدند.
و آخرین تاریخِ امانت ۳۰ ژوئنِ ۲۰۲۵ است. پس «دوازده ماهِ گذشته» یعنی از اولِ ژوئیهٔ ۲۰۲۴ تا همان تاریخ — بازهای که خودمان انتخابش کردیم و باید در گزارش بنویسیمش، وگرنه هیچکس نمیداند عددها دربارهٔ چه دورهایاند.
۲. پاسخ#
سه مرحله: امانتهای داخلِ بازه را برای هر کتاب بشمار، هر کتاب را با تعدادش کنارِ شعبهاش بگذار، بعد گروهبندی کن. تجمیع قبل از پیوند، دقیقاً مثلِ فصلِ ۳.
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)
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
پاسخ: مرکزی، با ۲٫۲۶ امانت بهازای هر کتاب.
و آن NaN در سطرِ آخر خطا نیست، صادقترین خانهٔ این جدول است. شعبهٔ آفتاب هیچ کتابی ندارد، پس «امانت بهازای هر کتاب» برایش تعریف نشده. NULLIF(COUNT(p.id), 0) دقیقاً همین را میگوید: بهجای تقسیم بر صفر، NULL. اگر آنجا صفر مینوشتیم، جدول ادعا میکرد آفتاب بدترین شعبه است — در حالی که آفتاب اصلاً در این مقایسه شرکت ندارد.
۳. اثبات: نه تکثیر شد، نه گم شد#
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")
جمع ستون books : 1200 | کل کتابها: 1200
جمع ستون loans : 1549
امانتهای بازه : 1549
سطرهای گزارش : 7 | کل شعبهها: 7
سه آزمون، هر سه پاس.
جمعِ ستونِ books دقیقاً ۱٬۲۰۰ است — هیچ کتابی دو بار شمرده نشده و هیچ کتابی جا نمانده. جمعِ ستونِ loans دقیقاً برابرِ تعدادِ امانتهای بازه است که از یک پرسشِ کاملاً مستقل گرفتیم. و گزارش هفت سطر دارد، نه شش: شعبهٔ بیکتاب هم در جدول هست.
✅ چک کن: این سه خط را در هر گزارشی که با
JOINمیسازی بنویس. اگر جمعِbooksبیشتر از ۱٬۲۰۰ درآمد، جایی fan-out داری؛ اگر کمتر، جاییINNER JOINنوشتهای که بایدLEFTمیبود.
۴. همان گزارش، اگر سادهلوحانه نوشته میشد#
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",)))
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
هر شش شعبه دقیقاً ۱٫۰۰. جدول مرتب است، عددها تمیزند، و گزارش کاملاً بیمعنی است.
دلیلش را حالا با یک نگاه میبینی: بعد از دو JOIN، یک سطر دیگر «یک کتاب» نیست، «یک امانت» است. پس COUNT(*) امانتها را شمرد و COUNT(l.id) هم امانتها را شمرد، و تقسیمِ یک عدد بر خودش همیشه ۱ است. مرکزی ۱۹۷ کتاب دارد و این جدول میگوید ۴۴۵.
و هیچ خطایی داده نشد. تنها چیزی که مچش را میگیرد همان آزمونِ بخشِ ۳ است: جمعِ ستونِ books اینجا ۱٬۵۴۹ میشود، نه ۱٬۲۰۰.
۵. سه «بهترین» متفاوت#
قبل از نوشتنِ گزارش، یک پرسشِ آخر: آیا «پرکارترین» همان «بزرگترین» است؟
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
"""))
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
سه سنجه، سه برنده: بیشترین تعدادِ کتاب مالِ کوشاست، بیشترین تعدادِ صفحه مالِ نسیم، و بیشترین امانت بهازای هر کتاب مالِ مرکزی. هر سه از همان سه جدول درآمدند و هر سه درستاند.
پس «کدام شعبه بهترین است؟» پرسشِ کاملی نیست. جملهٔ کامل باید سنجهاش را با خودش بیاورد — و این دقیقاً همان چیزی است که یک گزارشِ خوب را از یک عددِ تنها جدا میکند.
و NaN ستونِ pages برای آفتاب یک یادآوریِ ترمِ ۱ است: SUM روی صفر سطر، صفر نمیدهد؛ NULL میدهد، چون جمعِ هیچچیزی معلوم نیست.
۶. آیا جواب به تعریفِ ما بند است؟#
یک ایرادِ منصفانه به گزارشِ بخشِ ۲: کتابی که پارسال به فهرست اضافه شده، فرصتِ کمتری برای امانت رفتن داشته. پس بیایید مخرج را منصفانهتر کنیم و فقط کتابهایی را بشماریم که قبل از شروعِ ثبتِ امانتها در فهرست بودهاند.
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"]))
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
عددها عوض شدند و ترتیب عوض نشد. این یک نتیجهٔ منفی است و باید همانقدر صریح گزارش شود که یک نتیجهٔ مثبت.
معنایش این است که رتبهبندی به این تعریفِ خاص بند نیست — و همین جمله، خودِ رتبهبندی را قابلِ اعتمادتر میکند. اگر ترتیب عوض شده بود، گزارش باید هر دو نسخه را میداد.
ولی این نتیجه را بیشتر از اندازهاش باور نکن. ایرادِ ما این بود که کتابِ تازه فرصتِ کمتری داشته. این داده ساختگی است و تاریخِ امانت در آن به added_at بند نیست:
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]))
امانتهای قبل از added_at کتاب: 786
از ۳٬۹۹۸ امانت، ۷۸۶ تا قبل از ورودِ کتابشان به فهرست ثبت شدهاند. در یک دادهٔ واقعی این خودش مسئلهای است که باید بررسی شود. پس آزمونِ این بخش اینجا فقط میگوید کنار گذاشتنِ کتابهای تازه ترتیب را عوض نمیکند، و چیزی دربارهٔ فرصتِ کمترِ آنها ثابت نمیکند.
دقت کن شرطِ تاریخ در ON نشست، نه در WHERE — درسِ فصلِ ۲. اگر در WHERE مینوشتیمش، شعبهٔ آفتاب از جدول میافتاد بیرون.
🔧 اگر کار نکرد: اگر بخواهی شعبههای کمکار را با یک شرط روی خودِ تجمیع فیلتر کنی و آن را در
WHEREبگذاری،SQLiteصریح اعتراض میکند — این همان جایی است کهHAVINGلازم است:
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)
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()
۷. گزارشِ نهایی#
سه جمله که میشود رویشان تصمیم گرفت، و هر جمله سنجه و دورهاش را با خودش میآورد:
- در دوازده ماهِ منتهی به آخرین امانتِ ثبتشده، شعبهٔ مرکزی پرکارترین بوده — ۲٫۲۶ امانت بهازای هر کتاب، در برابرِ ۰٫۷۳ برای کمکارترین. جمعِ کتابهای گزارش با کلِ فهرست میخواند و جمعِ امانتها با شمارشِ مستقلِ همان بازه.
- بزرگترین شعبه مرکزی نیست — کوشا با ۲۲۵ عنوان بیشترین کتاب را دارد و نسیم بیشترین صفحه را. «بهترین» بدونِ نامِ سنجه معنایی ندارد.
- این رتبهبندی به تعریفِ مخرج بند نیست — با محدود کردنِ کتابها به آنهایی که از قبل در فهرست بودهاند، همهٔ نرخها عوض شدند و هیچ جابهجایی در ترتیب رخ نداد.
و آنچه نمیدانیم، بخشی از همین گزارش است: شعبهٔ آفتاب در هیچکدام از این مقایسهها نیست چون هنوز کتابی ندارد؛ نمیدانیم اختلافِ ۱٫۴۵ و ۱٫۴۰ بینِ نسیم و دانش معنادار است یا قرعه — ترمِ ۶ فصلِ ۵ ابزارِ سنجیدنش را میسازد؛ و کلِ این تحلیل دربارهٔ کتابهایی است که ثبت شدهاند — کتابی که هرگز وارد فهرست نشده در هیچ عددی نیست.
📏 اندازه بگیر: یک سطر یعنی چه؟ در گزارشِ نهایی یک سطر یک شعبه است، و هر دو عددش روی کتابها حساب شده نه روی امانتها — همان تفاوتی که بخشِ ۴ نشان داد چقدر گران است. با کدام شمارشِ دوم سنجیدی؟ با سهتا: جمعِ ستونِ
booksبرابرِ ۱٬۲۰۰، جمعِ ستونِloansبرابرِ ۱٬۵۴۹ از یک پرسشِ مستقل، و ۷ سطر برابرِ ۷ شعبه. چه کسی از قلم افتاد؟ شعبهٔ آفتاب از مقایسه افتاد ولی از جدول نه — و این تفاوت عمدی است: در جدول هست تا خواننده ببیند وجود دارد، وNULLمیگوید که نرخش تعریف نشده.
🤖 از دستیارت بپرس: «چرا در گزارشِ سادهلوحانه همهٔ شعبهها نرخِ ۱٫۰۰ گرفتند؟» بعد این را بپرس: «چه آزمونی میتوانستم قبل از انتشار بزنم که این را میگرفت؟» — جوابِ درست به جمعِ ستونِ
booksدر برابرِ کلِ کتابها اشاره میکند. اگر جواب فقط گفت «پرسش را دوباره بخوان»، آزمونی پیشنهاد نداده.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| reconciliation | ریکانسیلیایشن | تطبیق؛ سنجیدنِ جمعِ یک گزارش با یک شمارشِ مستقل |
| reporting window | ریپورتینگ ویندو | بازهٔ زمانیای که گزارش دربارهٔ آن است |
| denominator | دنامینیتر | مخرج؛ چیزی که نرخ بر آن تقسیم میشود |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
ترمِ ۳ تمام شد. حالا میتوانی داده را از چند جدول کنارِ هم بیاوری و ثابت کنی که در راه نه سطری گم شده و نه تکثیر.
ولی در تمامِ این هشت فصل، اسکیما را کسِ دیگری ساخته بود و ما فقط رویش پرسش نوشتیم. ترمِ ۴ سراغِ خودِ اسکیما میرود: چطور از یک فایلِ کثیف به جدولهایی برسی که پایگاه داده خودش از قوانینشان محافظت میکند — و چطور فردا عوضشان کنی بدونِ اینکه چیزی بشکند.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.