در این فصل چه یاد میگیری#
جدولِ نرخِ بازگشتِ بهموقع روی میز است. شعبهٔ نسیم با ۷۲٫۰ درصد اولِ فهرست است و شعبهٔ مرکزی با ۶۲٫۳ درصد آخر. تصمیمِ بدیهی این است که مرکزی باید از نسیم یاد بگیرد.
بعد همان جدول را بهتفکیکِ دستهٔ کتاب میگیریم و مرکزی در هر هشت دسته از نسیم بهتر است. در کودک ۹۶٫۵ در برابرِ ۸۶٫۷، در تاریخ ۵۴٫۵ در برابرِ ۴۳٫۸، و همینطور تا آخر.
هیچکدام از این دو جدول غلط نیست. تفاوتشان در چیزی است که هیچکدام چاپش نمیکنند: مرکزی و نسیم اصلاً یکجور کتاب امانت نمیدهند.

آخر این فصل میتوانی:
- نشان بدهی ترکیبِ متفاوتِ گروهها چطور نتیجهٔ یک مقایسه را وارونه میکند
- نرخِ خام را با نرخِ تعدیلشده بسنجی و بگویی هرکدام جوابِ چه پرسشی است
- گروهِ مرجع را صریح انتخاب کنی، نه اینکه از داده دربیاید
- مطمئن شوی هر دو طرفِ یک مقایسه پنجرهٔ زمانیِ برابر داشتهاند
قبل از شروع#
از فصلِ ۲ و ۳: تعریفِ عملیاتی، پنجرهٔ زمانی، و اینکه دورهٔ ناقص عدد را خراب میکند. از ترمِ ۲: GROUP BY، درصد، و میانگینِ وزنی — که در بخشِ ۵ دقیقاً همان است. از ترمِ ۳: CTE و زیرپرسش در FROM.
تعریفِ سنجهٔ این فصل: «بازگشتِ بهموقع» یعنی returned_at <= due_at. دانهبندی: یک سطر یک امانتِ برگشته. و پنجره: فقط امانتهایی که پیش از 2025-04-01 گرفته شدهاند، تا هر امانتی فرصتِ برابری برای برگشتن داشته باشد.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
members |
۱٬۴۰۰ | آنی |
titles |
۱٬۲۰۰ | آنی |
loans |
۱۶٬۸۳۴ | کمتر از دو ثانیه |
tiny_returns (همین فصل میسازدش) |
۲۰ | آنی |
۱. بیست امانت که وارونگی را با انگشت نشان میدهد#
دو شعبهٔ خیالی، هرکدام ده امانتِ برگشته، و دو دسته کتاب: کودک که زود برمیگردد و تاریخ که دیر.
con.execute("DROP TABLE IF EXISTS tiny_returns")
con.execute("""
CREATE TABLE tiny_returns (
branch TEXT NOT NULL, category TEXT NOT NULL, on_time INTEGER NOT NULL
)
""")
con.executemany("INSERT INTO tiny_returns VALUES (?, ?, ?)",
[("الف", "کودک", 1)] * 2 +
[("الف", "تاریخ", 1)] * 4 + [("الف", "تاریخ", 0)] * 4 +
[("ب", "کودک", 1)] * 8 + [("ب", "کودک", 0)] * 1 +
[("ب", "تاریخ", 0)] * 1)
con.commit()
print("نرخ خام:")
print(q("""
SELECT branch, COUNT(*) AS loans, SUM(on_time) AS on_time,
ROUND(100.0 * SUM(on_time) / COUNT(*), 1) AS pct
FROM tiny_returns GROUP BY branch ORDER BY pct DESC
"""))
print()
print("همان، بهتفکیک دسته:")
print(q("""
SELECT category, branch, COUNT(*) AS loans, SUM(on_time) AS on_time,
ROUND(100.0 * SUM(on_time) / COUNT(*), 1) AS pct
FROM tiny_returns GROUP BY category, branch ORDER BY category, branch
"""))
نرخ خام:
branch loans on_time pct
0 ب 10 8 80.0
1 الف 10 6 60.0
همان، بهتفکیک دسته:
category branch loans on_time pct
0 تاریخ الف 8 4 50.0
1 تاریخ ب 1 0 0.0
2 کودک الف 2 2 100.0
3 کودک ب 9 8 88.9
بیست سطر، و میشود همهشان را با انگشت شمرد: ب هشت از ده، الف شش از ده. پس ب بهتر است.
ولی در دستهٔ کودک الف دو از دو است و ب هشت از نه؛ و در دستهٔ تاریخ الف چهار از هشت است و ب صفر از یک. الف در هر دو دسته بهتر است و در جمع بدتر.
دلیلش را میشود در همان جدول دید: الف هشت امانتِ تاریخ دارد و ب فقط یکی. الف بیشترِ وقتش را روی کارِ سخت گذاشته و ب روی کارِ آسان. نرخِ خام این را نمیداند و نمیتواند بداند.
۲. شش شعبه، یک نرخ، یک رتبهبندی#
حالا همان روی دادهٔ واقعی. فیلترِ borrowed_at < ? عمدی است: امانتی که همین هفتهٔ پیش گرفته شده هنوز فرصتِ دیر شدن نداشته، و گذاشتنش در این جدول یعنی دادنِ امتیازِ رایگان به شعبهای که تازه شلوغ شده.
WINDOW_END = "2025-04-01"
print(q("""
SELECT b.name AS branch,
COUNT(*) AS returned_loans,
ROUND(100.0 * SUM(l.returned_at <= l.due_at) / COUNT(*), 1) AS on_time_pct
FROM loans AS l
JOIN branches AS b ON b.id = l.branch_id
WHERE l.returned_at IS NOT NULL AND l.borrowed_at < ?
GROUP BY b.id, b.name
ORDER BY on_time_pct DESC
""", (WINDOW_END,)))
branch returned_loans on_time_pct
0 نسیم 2796 72.0
1 بهار 1685 68.1
2 دانش 2267 67.6
3 کوشا 2046 67.4
4 پویا 1726 64.8
5 مرکزی 3298 62.3
جدولِ کاملاً قابلِدفاعی است: پنجره برابر، دانهبندی اعلامشده، تعداد سطرها کنارِ هر نرخ. و رتبهبندیاش همان چیزی است که این فصل قرار است وارونه کند.
۳. همان دو شعبه، بهتفکیکِ دسته#
print(q("""
SELECT t.category,
SUM(l.branch_id = 1) AS markazi_n,
ROUND(100.0 * SUM(CASE WHEN l.branch_id = 1 THEN l.returned_at <= l.due_at END)
/ SUM(l.branch_id = 1), 1) AS markazi_pct,
SUM(l.branch_id = 2) AS nasim_n,
ROUND(100.0 * SUM(CASE WHEN l.branch_id = 2 THEN l.returned_at <= l.due_at END)
/ SUM(l.branch_id = 2), 1) AS nasim_pct
FROM loans AS l
JOIN titles AS t ON t.id = l.title_id
WHERE l.returned_at IS NOT NULL AND l.borrowed_at < ? AND l.branch_id IN (1, 2)
GROUP BY t.category
ORDER BY markazi_pct
""", (WINDOW_END,)))
category markazi_n markazi_pct nasim_n nasim_pct
0 تاریخ 1138 54.5 130 43.8
1 فلسفه 819 55.4 134 50.7
2 هنر 165 59.4 103 44.7
3 علم 350 67.1 222 62.2
4 زندگینامه 233 67.4 140 55.0
5 شعر 155 72.9 282 70.2
6 رمان 268 80.2 723 70.3
7 کودک 170 96.5 1062 86.7
هشت دسته، هشتتا از هشتتا به نفعِ مرکزی — و در جمع، مرکزی ۹٫۷ واحد عقب است.
این وارونگی اسم دارد و تصادف هم نیست. هر جا یک نرخِ کلی را بینِ دو گروه مقایسه میکنی و آن دو گروه ترکیبِ متفاوتی دارند، این اتفاق ممکن است.
🌱 ریشهاش کجاست: آنچه اینجا اندازه میگیری یک احتمالِ شرطی است — «به شرطِ اینکه کتاب از دستهٔ تاریخ باشد، چقدر احتمال دارد بهموقع برگردد» — و شرط را که عوض کنی، عدد عوض میشود. جمع زدنِ چند احتمالِ شرطی بدونِ توجه به وزنشان دقیقاً همان خطای نرخِ پایه است. اگر میخواهی این را از پایه ببینی، ریشه ترمِ ۶ فصل ۴ با یک مثالِ کاملاً ملموس بازش میکند.
۴. چرا: ترکیبِ گروهها فرق دارد#
سهمِ هر دسته از کلِ امانتهای هر شعبه:
print(q("""
SELECT t.category,
ROUND(100.0 * SUM(l.branch_id = 1) /
(SELECT COUNT(*) FROM loans WHERE branch_id = 1
AND returned_at IS NOT NULL AND borrowed_at < ?), 1) AS markazi_share,
ROUND(100.0 * SUM(l.branch_id = 2) /
(SELECT COUNT(*) FROM loans WHERE branch_id = 2
AND returned_at IS NOT NULL AND borrowed_at < ?), 1) AS nasim_share
FROM loans AS l
JOIN titles AS t ON t.id = l.title_id
WHERE l.returned_at IS NOT NULL AND l.borrowed_at < ? AND l.branch_id IN (1, 2)
GROUP BY t.category
ORDER BY markazi_share DESC
""", (WINDOW_END, WINDOW_END, WINDOW_END)))
category markazi_share nasim_share
0 تاریخ 34.5 4.6
1 فلسفه 24.8 4.8
2 علم 10.6 7.9
3 رمان 8.1 25.9
4 زندگینامه 7.1 5.0
5 کودک 5.2 38.0
6 هنر 5.0 3.7
7 شعر 4.7 10.1
تاریخ و فلسفه با هم ۵۹٫۳ درصدِ کارِ مرکزیاند و ۹٫۴ درصدِ کارِ نسیم. کودک و رمان با هم ۶۳٫۹ درصدِ کارِ نسیماند و ۱۳٫۳ درصدِ کارِ مرکزی.
این دو شعبه در واقع دو کارِ متفاوت میکنند و نرخِ خام هر دو را با یک خطکش اندازه گرفته. گروهِ مرجعِ پنهانِ آن جدول «ترکیبِ کتابهای خودِ همان شعبه» بود — یعنی هر شعبه با خطکشِ خودش سنجیده شد.
🔧 اگر کار نکرد: رایجترین اشتباهِ همین بخش این است که بهتفکیکِ دسته گروهبندی کنی و یادت برود دسته اصلاً در جدولِ
loansنیست:
try:
q("""
SELECT t.category, COUNT(*) AS n
FROM loans AS l
WHERE l.returned_at IS NOT NULL
GROUP BY t.category
""")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql '
SELECT t.category, COUNT(*) AS n
FROM loans AS l
WHERE l.returned_at IS NOT NULL
GROUP BY t.category
': no such column: t.category
دسته یک صفتِ کتاب است، نه صفتِ امانت — و همین یک جمله دلیلِ کلِ این فصل است: عاملی که نتیجه را وارونه کرد، در جدولی که نرخ از آن درآمده اصلاً وجود ندارد.
۵. نرخِ تعدیلشده: یک خطکش برای همه#
درمانش ساده است و اسمش را از ترمِ ۲ میشناسی: میانگینِ وزنی. نرخِ هر دسته را برای هر شعبه نگه میداریم، ولی بهجای وزنِ خودِ شعبه، ترکیبِ کلِ کتابخانه را وزن میگذاریم. یعنی میپرسیم: «اگر هر شش شعبه دقیقاً همان ترکیبِ کتابی را میداشتند که کلِ کتابخانه دارد، نرخشان چه میشد؟»
print(q("""
WITH cell AS (
SELECT l.branch_id, t.category,
COUNT(*) AS n,
SUM(l.returned_at <= l.due_at) AS on_time
FROM loans AS l
JOIN titles AS t ON t.id = l.title_id
WHERE l.returned_at IS NOT NULL AND l.borrowed_at < ?
GROUP BY l.branch_id, t.category
),
weight AS (SELECT category, SUM(n) AS n FROM cell GROUP BY category),
total AS (SELECT SUM(n) AS n FROM cell)
SELECT b.name AS branch,
SUM(c.n) AS returned_loans,
ROUND(100.0 * SUM(c.on_time) / SUM(c.n), 1) AS raw_pct,
ROUND(SUM(100.0 * c.on_time / c.n * w.n) / (SELECT n FROM total), 1) AS adjusted_pct
FROM cell AS c
JOIN weight AS w ON w.category = c.category
JOIN branches AS b ON b.id = c.branch_id
GROUP BY c.branch_id, b.name
ORDER BY adjusted_pct DESC
""", (WINDOW_END,)))
branch returned_loans raw_pct adjusted_pct
0 مرکزی 3298 62.3 71.8
1 کوشا 2046 67.4 68.9
2 دانش 2267 67.6 67.3
3 بهار 1685 68.1 66.3
4 نسیم 2796 72.0 63.2
5 پویا 1726 64.8 60.1
مرکزی از آخر به اول رفت و نسیم از اول به پنجم — با همان سطرها، همان تعریف، و همان پنجره.
سه چیز را همینجا صریح بگوییم، وگرنه این ستون خودش یک ادعای تازه است:
- نرخِ تعدیلشده عددِ واقعی نیست. هیچ شعبهای واقعاً ۷۱٫۸ درصد بازگشتِ بهموقع نداشته. این عددِ «اگر»ی است و فقط برای مقایسه معنا دارد.
- گروهِ مرجع یک انتخاب است. ما ترکیبِ کلِ کتابخانه را وزن گذاشتیم؛ میشد ترکیبِ یک شعبهٔ خاص یا ترکیبِ سالِ قبل را گذاشت و عددها فرق میکرد. هر جا نرخِ تعدیلشده گزارش میکنی، بنویس بر اساسِ چه ترکیبی تعدیل شده.
- ستونِ
returned_loansباید بماند. بدونِ آن، هیچکس نمیتواند این نرخها را دوباره با هم ترکیب کند — همان قاعدهٔ ترمِ ۲ فصلِ ۳.
✅ چک کن: ستونِ
raw_pctدر این جدول باید سطر به سطر همان عددهای جدولِ بخشِ ۲ باشد و ستونِreturned_loansهم همان. اگر فرق کرد، شرطِ پنجره را در یکی از دو پرسش جا انداختهای و دو جدول دربارهٔ دو مجموعهٔ متفاوت حرف میزنند.
۶. پنجرهٔ برابر برای هر دو طرف#
یک مقایسهٔ دیگر، با یک عیبِ دیگر. کوهورت یعنی گروهی که در یک بازهٔ زمانی وارد شدهاند؛ ماندگاری یعنی چند درصدشان بعد از مدتی هنوز هستند.
print(q("""
SELECT substr(joined_at, 1, 7) AS cohort,
COUNT(*) AS members,
ROUND(100.0 * SUM(left_at IS NULL
OR julianday(left_at) - julianday(joined_at) >= 90)
/ COUNT(*), 1) AS kept_90,
CAST(julianday(?) - julianday(date(substr(joined_at, 1, 7) || '-01',
'+1 month', '-1 day')) AS INTEGER) AS days_observed
FROM members
WHERE joined_at >= ?
GROUP BY cohort
ORDER BY cohort
""", (AS_OF, "2024-09-01")))
cohort members kept_90 days_observed
0 2024-09 14 100.0 255
1 2024-10 21 90.5 224
2 2024-11 18 72.2 194
3 2024-12 23 87.0 163
4 2025-01 23 95.7 132
5 2025-02 9 77.8 104
6 2025-03 17 94.1 73
7 2025-04 16 100.0 43
8 2025-05 22 100.0 12
9 2025-06 5 100.0 -18
سه کوهورتِ آخر همه ۱۰۰ درصد ماندگاری دارند، و این خبرِ خوبی نیست — خبر نیست.
ستونِ days_observed میگوید چرا: کوهورتِ ماهِ 2025-05 فقط ۱۲ روز دیده شده. کسی که هنوز ۹۰ روز از عضویتش نگذشته، فقط اگر در همین چند روزِ کوتاه رفته باشد «رفته» شمرده میشود، و بقیهٔ فرصتش برای رفتن هنوز نیامده. کوهورتِ 2025-03 با ۷۳ روز دیدهشده و ۹۴٫۱ درصد نشان میدهد که نتیجه اینجا هم ناقص است، نه فقط در آن سه ردیف. عددِ ۱۰۰ درصد بیشتر دربارهٔ تقویم حرف میزند تا دربارهٔ آدمها. و کوهورتِ آخر حتی ماهش هم تمام نشده — به همین دلیل days_observed منفی است.
درمان همان است: هر کوهورتی که پنجرهٔ کاملِ ۹۰ روز را ندیده، از جدول بیرون میرود.
print(q("""
SELECT substr(joined_at, 1, 7) AS cohort,
COUNT(*) AS members,
ROUND(100.0 * SUM(left_at IS NULL
OR julianday(left_at) - julianday(joined_at) >= 90)
/ COUNT(*), 1) AS kept_90
FROM members
WHERE joined_at >= ?
AND julianday(?) - julianday(date(substr(joined_at, 1, 7) || '-01',
'+1 month', '-1 day')) >= 90
GROUP BY cohort
ORDER BY cohort
""", ("2024-09-01", AS_OF)))
cohort members kept_90
0 2024-09 14 100.0
1 2024-10 21 90.5
2 2024-11 18 72.2
3 2024-12 23 87.0
4 2025-01 23 95.7
5 2025-02 9 77.8
چهار سطر کم شد و جدول قابلِ استفاده شد. حالا شش عددی که کنارِ هم نشستهاند واقعاً همجنساند — و ستونِ members هم هست تا کسی کوهورتِ نهنفره را با کوهورتِ بیستوسهنفره یکسان نگیرد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ بخشِ ۲ و ۵ یک سطر یک شعبه است و هر نرخش روی امانتهای برگشته حساب شده؛ در جدولِ بخشِ ۶ یک سطر یک کوهورتِ ماهانه از اعضاست. با کدام شمارشِ دوم سنجیدی؟ با سهتا: جدولِ بیستسطریِ بخشِ ۱ که وارونگی رویش شمردنی بود، ستونِ
raw_pctدر بخشِ ۵ که باید عیناً جدولِ بخشِ ۲ را بازتولید کند، و ستونِdays_observedکه نشان داد سه عددِ ۱۰۰ درصد از کجا آمدهاند. چه کسی از قلم افتاد؟ همهٔ امانتهای برنگشته و همهٔ امانتهای بعد از تاریخِ پنجره — یعنی این جدولها هیچچیزی دربارهٔ کتابی که هرگز برنگشته نمیگویند، و آن مشکلِ فصلِ ۳ همچنان سرِ جایش است.
🤖 از دستیارت بپرس: «چطور ممکن است گروهی در هر زیرگروه بهتر باشد ولی در جمع بدتر؟» بعد این را بپرس: «نرخِ تعدیلشده چیست، و برای محاسبهاش باید وزنها را از کجا بردارم؟» — جوابِ درست میگوید وزن یک انتخاب است و باید کنارِ عدد اعلام شود. اگر جواب فقط یک فرمول داد و نگفت وزن از کجا میآید، ناقص است.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| confounder | کانفاوندر | عاملِ مخدوشکننده؛ چیزی که هم بر گروه اثر دارد هم بر نتیجه |
| Simpson's paradox | سیمپسونز پارادوکس | وارونه شدنِ نتیجه وقتی زیرگروهها با هم جمع میشوند |
| crude rate | کرود ریت | نرخِ خام؛ نرخی که ترکیبِ گروهها را حساب نمیکند |
| adjusted rate | ادجاستد ریت | نرخِ تعدیلشده؛ همان نرخ با وزنِ یک ترکیبِ مرجع |
| reference group | رفرنس گروپ | گروهِ مرجع؛ ترکیبی که همه با آن سنجیده میشوند |
| cohort | کوهورت | کوهورت؛ گروهی که در یک بازهٔ زمانی وارد شدهاند |
| retention | ریتنشن | ماندگاری؛ چند درصدِ یک کوهورت بعد از مدتی هنوز هستند |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
حالا میتوانی دو عدد را منصفانه کنارِ هم بگذاری. فصلِ بعد سؤالی را جواب میدهد که چهار پروژهٔ این دوره صریحاً به تعویقش انداختند: این اختلاف واقعی است یا قرعه؟
و جوابش وارونهٔ حدسِ اول است: اختلافِ بیستواحدیِ دو کتابدار روی بیست امانت اصلاً اختلاف نیست، و اختلافِ ۹٫۶۵ واحدیِ نسیم و مرکزی هست — چون دومی از ۶٬۰۹۴ سطر ساخته شده و اولی از بیست سطر.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.