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

آخر این فصل میتوانی:
- شش شکلِ رایجِ عددِ غلطِ بیخطا را با اسم تشخیص بدهی
- نشان بدهی حذفِ
NULLچطور بهجای سطر، آدم حذف میکند - آزمونِ سهگانه را روی هر عددی که تحویل میگیری اجرا کنی
قبل از شروع#
از فصلِ ۱ و ۲: بازسازیِ دانهبندی، شمارشِ کنترلی، و تعریفِ عملیاتی. از ترمِ ۱: NULL و COALESCE و اینکه AVG سطرِ NULL را نمیشمارد. از ترمِ ۳: INNER JOIN و سطرهایی که جفت ندارند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
members |
۱٬۴۰۰ | آنی |
loans |
۱۶٬۸۳۴ | کمتر از دو ثانیه |
tiny_loans (همین فصل میسازدش) |
۸ | آنی |
۱. هشت سطر که دروغش را میشود دید#
هشت امانت. ششتا برگشتهاند، دوتا هنوز دستِ کسی است.
con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("""
CREATE TABLE tiny_loans (
id INTEGER PRIMARY KEY, borrowed_at TEXT, returned_at TEXT
)
""")
con.executemany("INSERT INTO tiny_loans VALUES (?, ?, ?)", [
(1, "2025-05-01", "2025-05-06"), (2, "2025-05-02", "2025-05-09"),
(3, "2025-05-03", "2025-05-12"), (4, "2025-05-04", "2025-05-15"),
(5, "2025-05-05", "2025-05-11"), (6, "2025-05-06", "2025-05-14"),
(7, "2025-04-23", None), (8, "2025-04-13", None),
])
con.commit()
print(q("""
SELECT COUNT(*) AS loans,
SUM(returned_at IS NULL) AS still_out,
ROUND(AVG(julianday(returned_at) - julianday(borrowed_at)), 2) AS avg_days
FROM tiny_loans
"""))
print()
print(q("""
SELECT COUNT(*) AS loans,
ROUND(AVG(julianday(COALESCE(returned_at, ?)) - julianday(borrowed_at)), 2) AS avg_days
FROM tiny_loans
""", (AS_OF,)))
loans still_out avg_days
0 8 2 7.67
loans avg_days
0 8 19.5
۷٫۶۷ در برابرِ ۱۹٫۵، و ستونِ loans در هر دو جدول ۸ است.
اینجاست که تله میبندد: COUNT(*) میگوید هشت سطر دیده شده، پس آدم خیال میکند میانگین هم روی هشت سطر گرفته شده. ولی AVG سطری را که مقدارش NULL است در مخرجش نمیآورد — همان درسِ ترمِ ۱ فصلِ ۶، این بار در قالبِ یک گزارش.
و دو امانتی که حذف شدند تصادفی نیستند: دقیقاً همانهاییاند که از همه بیشتر بیرون ماندهاند. پس نمونهای که باقی میماند نهفقط کوچکتر، بلکه سوگیر است.
۲. دروغِ اول: فیلتری که نمونه را سوگیر میکند#
print(q("""
SELECT COUNT(*) AS returned_loans,
ROUND(AVG(julianday(returned_at) - julianday(borrowed_at)), 2) AS avg_days
FROM loans
WHERE returned_at IS NOT NULL
"""))
print()
print(q("""
SELECT COUNT(*) AS all_loans,
SUM(returned_at IS NULL) AS still_out,
ROUND(AVG(julianday(COALESCE(returned_at, ?)) - julianday(borrowed_at)), 2) AS avg_days
FROM loans
""", (AS_OF,)))
returned_loans avg_days
0 15502 22.42
all_loans still_out avg_days
0 16834 1332 30.04
۲۲٫۴۲ در برابرِ ۳۰٫۰۴ — و آن ۱٬۳۳۲ امانتِ حذفشده هشت درصدِ کلِ دادهاند.
عددِ دوم هم ادعای کاملی نیست: برای امانتی که هنوز برنگشته، «مدت» را تا تاریخِ برش شمردهایم، و آن مدت فردا بیشتر میشود. ولی عددِ دوم دستِکم میداند چه چیزی را نمیداند.
۳. دروغِ دوم: بازماندگان#
پرسشِ بیآزارِ «هر عضو بهطور میانگین چند کتاب برده؟» — یک بار روی همهٔ اعضا و یک بار فقط روی اعضای فعلی، که بهنظر تمیزتر هم میآید:
print(q("""
SELECT 'همه اعضا' AS who,
COUNT(DISTINCT m.id) AS members, COUNT(l.id) AS loans,
ROUND(1.0 * COUNT(l.id) / COUNT(DISTINCT m.id), 2) AS per_member
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
UNION ALL
SELECT 'فقط اعضای فعلی',
COUNT(DISTINCT m.id), COUNT(l.id),
ROUND(1.0 * COUNT(l.id) / COUNT(DISTINCT m.id), 2)
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
WHERE m.left_at IS NULL
"""))
who members loans per_member
0 همه اعضا 1400 16834 12.02
1 فقط اعضای فعلی 1000 16124 16.12
۱۶٫۱۲ در برابرِ ۱۲٫۰۲ — سیوچهار درصد بالاتر، فقط چون ۴۰۰ نفری که رفتند شمرده نشدند.
و دلیلش را همین دو عدد میگویند: مخرج نزدیک به سی درصد کوچک شد و صورت فقط چهار درصد. آن اعضا لزوماً کماستفاده نبودند؛ مدتِ کمتری عضو بودند، پس فرصتِ کمتری برای امانت گرفتن داشتند — و هر نمونهای که فقط بازماندهها را میبیند دقیقاً به همین دلیل بالا میرود.
اگر این عدد را برای پیشبینیِ رفتارِ عضوِ تازه به کار ببری، دربارهٔ آدمهایی حرف میزنی که موفق بودهاند و از آنها میخواهی نمایندهٔ همه باشند.
🌱 ریشهاش کجاست: این همان تلهٔ کلاسیکِ نمونهگیری است: وقتی نمونهات را از میانِ کسانی میگیری که دوام آوردهاند، نتیجهات دربارهٔ دوام آوردن ساکت است. اگر میخواهی این را از پایه و با مثالِ ملموس ببینی، ریشه ترمِ ۶ فصل ۸ دقیقاً همین است.
۴. دروغِ سوم: دورهٔ ناقصِ آخر#
نمودارِ ماهانهای که مدیریت هر ماه میبیند. آخرین ماه سقوط کرده:
print(q("""
WITH span AS (
SELECT substr(borrowed_at, 1, 7) AS month,
substr(borrowed_at, 1, 7) || '-01' AS first_day,
CASE WHEN date(substr(borrowed_at, 1, 7) || '-01', '+1 month', '-1 day') <= ?
THEN date(substr(borrowed_at, 1, 7) || '-01', '+1 month', '-1 day')
ELSE ? END AS last_day
FROM loans
WHERE borrowed_at >= ?
GROUP BY month
)
SELECT s.month,
COUNT(*) AS loans,
CAST(julianday(s.last_day) - julianday(s.first_day) + 1 AS INTEGER) AS days,
ROUND(COUNT(*) / (julianday(s.last_day) - julianday(s.first_day) + 1), 2) AS per_day
FROM loans AS l
JOIN span AS s ON s.month = substr(l.borrowed_at, 1, 7)
WHERE l.borrowed_at >= ?
GROUP BY s.month, s.first_day, s.last_day
ORDER BY s.month
""", (AS_OF, AS_OF, "2025-02-01", "2025-02-01")))
month loans days per_day
0 2025-02 963 28 34.39
1 2025-03 1081 31 34.87
2 2025-04 1016 30 33.87
3 2025-05 1100 31 35.48
4 2025-06 419 12 34.92
۴۱۹ در برابرِ ۱٬۱۰۰ — یعنی سقوطِ شصتودو درصدی. و ستونِ آخر میگوید هیچ اتفاقی نیفتاده: ۳۴٫۹۲ در روز، در همان محدودهٔ ۳۳٫۸۷ تا ۳۵٫۴۸ِ چهار ماهِ قبل.
ماهِ آخر فقط ۱۲ روز داده دارد، چون تاریخِ برشِ داده وسطِ ماه است. درمانش دو راه دارد و یکی کافی است: یا دورهٔ ناقص را از نمودار بردار، یا مثلِ اینجا به نرخِ روزانه تبدیلش کن — و در هر دو حالت تاریخِ برش را کنارِ نمودار بنویس.
۵. دروغِ چهارم و پنجم: حذفی که آدم میبَرد#
گزارشِ «کدام اعضا دیر برمیگردانند» طبیعتاً فقط به امانتهای برگشته نگاه میکند؛ برای امانتِ برنگشته که هنوز تأخیری معنا ندارد. بشماریم چند نفر با همین یک شرط از گزارش بیرون میافتند:
print(q("""
SELECT COUNT(DISTINCT member_id) AS borrowers,
COUNT(DISTINCT CASE WHEN returned_at IS NOT NULL THEN member_id END) AS in_the_report
FROM loans
WHERE borrowed_at > date(?, '-90 days')
""", (AS_OF,)))
borrowers in_the_report
0 851 768
۸۳ نفر هیچ سطری در گزارش ندارند — نه اینکه تأخیرشان صفر باشد؛ اصلاً نیستند. و اینها کسانیاند که همهٔ کتابهای این نود روزشان هنوز دستشان است. برای ۳۲ نفرشان سررسیدِ دستکم یکی از این کتابها گذشته و ۵۱ نفرِ دیگر هنوز به سررسید نرسیدهاند، پس گزارش هم بدترین تأخیرها را گم کرده و هم کسانی را که هنوز حکمی ندارند.
حالا حذفِ دوم، که از این هم بیصداتر است — یک INNER JOIN ساده به branches:
print(q("""
SELECT (SELECT COUNT(*) FROM members) AS members,
(SELECT COUNT(*) FROM members AS m JOIN branches AS b
ON b.id = m.home_branch_id) AS after_join,
(SELECT COUNT(*) FROM loans) AS loans,
(SELECT COUNT(*) FROM loans AS l JOIN members AS m ON m.id = l.member_id
JOIN branches AS b ON b.id = m.home_branch_id) AS loans_after_join
"""))
print()
print(q("""
SELECT CASE WHEN m.home_branch_id IS NULL THEN 'ثبتنام اینترنتی'
ELSE 'ثبتنام در شعبه' END AS kind,
COUNT(DISTINCT m.id) AS members,
COUNT(l.id) AS loans,
ROUND(100.0 * SUM(l.returned_at > l.due_at) /
SUM(l.returned_at IS NOT NULL), 1) AS late_pct
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY kind
"""))
members after_join loans loans_after_join
0 1400 1299 16834 15557
kind members loans late_pct
0 ثبتنام اینترنتی 101 1277 31.7
1 ثبتنام در شعبه 1299 15557 31.3
۱۰۱ عضو و ۱٬۲۷۷ امانت، کاملاً بیصدا — چون کسی که اینترنتی ثبتنام کرده شعبهٔ خانگی ندارد و INNER JOIN سطرِ بیجفت را میاندازد.
و جدولِ دوم یک صداقتِ لازم دارد: رفتارِ این گروه با بقیه فرقی ندارد (۳۱٫۷ در برابرِ ۳۱٫۳). پس دروغ این نیست که گروهِ حذفشده متفاوت است؛ دروغ این است که جمعِ گزارش ۱۵٬۵۵۷ است نه ۱۶٬۸۳۴، و هر «سهم از کل»ی که از آن جدول حساب شود هشت درصد باد کرده. و این را فقط چون شمردیم میدانیم.
۶. بدترین دروغ: پرسشی که هیچ خطایی نمیدهد#
print(q("""
SELECT ROUND(AVG(returned_at - borrowed_at), 2) AS avg_days
FROM loans WHERE returned_at IS NOT NULL
"""))
avg_days
0 0.05
صفر و پنج صدم روز. تاریخ در SQLite متن است، و تفریقِ دو متن آنها را به عدد تبدیل میکند: '2025-05-06' میشود ۲۰۲۵. پس تفریق تقریباً همیشه صفر میشود، مگر وقتی سال عوض شده باشد.
هیچ خطایی، هیچ هشداری، و عددی که حتی بیمعنا هم بهنظر نمیرسد. اگر واحدش را «روز» بنویسی، خوانندهات نتیجه میگیرد کتابها همان روز برمیگردند.
🔧 اگر کار نکرد: خطایی که واقعاً فریاد میزند در مقایسه با بالایی یک هدیه است. اگر بخواهی روی یک تجمیع فیلتر بگذاری و بهجای
HAVINGبنویسیاش درWHERE:
try:
q("""
SELECT branch_id, COUNT(*) AS n
FROM loans
WHERE AVG(julianday(returned_at) - julianday(borrowed_at)) > 20
GROUP BY branch_id
""")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql '
SELECT branch_id, COUNT(*) AS n
FROM loans
WHERE AVG(julianday(returned_at) - julianday(borrowed_at)) > 20
GROUP BY branch_id
': misuse of aggregate: AVG()
رفعش HAVING است — و درسِ واقعی این است: یک پیامِ خطا یعنی پایگاه داده جلویت را گرفت. عددِ ۰٫۰۵ یعنی نگرفت.
۷. آزمونِ سهگانه#
سه کار، قبل از اینکه هر عددی را باور کنی. هیچکدام بیش از یک خط کد نیست:
total = int(q("SELECT COUNT(*) AS n FROM loans").iloc[0, 0])
parts = q("""
SELECT SUM(returned_at IS NOT NULL AND returned_at <= due_at) AS on_time,
SUM(returned_at IS NOT NULL AND returned_at > due_at) AS late,
SUM(returned_at IS NULL) AS still_out
FROM loans
""")
on_time, late, still_out = (int(parts[c][0]) for c in ("on_time", "late", "still_out"))
print("۱. شمارش کل :", total)
print("۲. جمع اجزا :", on_time, "+", late, "+", still_out, "=", on_time + late + still_out)
print()
one = q("""
SELECT id, borrowed_at, due_at, returned_at,
julianday(returned_at) - julianday(due_at) AS days_late
FROM loans
WHERE returned_at IS NOT NULL AND returned_at > due_at
ORDER BY id
LIMIT 1
""")
print("۳. وارسی دستی یک سطر:")
print(one)
۱. شمارش کل : 16834
۲. جمع اجزا : 10651 + 4851 + 1332 = 16834
۳. وارسی دستی یک سطر:
id borrowed_at due_at returned_at days_late
0 8 2024-01-01 2024-01-22 2024-02-07 16.0
- شمارشِ کل — تعدادِ سطرهایی که پرسش دید، در برابرِ تعدادِ سطرهای جدول. اگر کمتر است، بپرس کدامها.
- جمعِ اجزا برابرِ کل — سه دسته که هیچ سطری بینشان نمیافتد: ۱۰٬۶۵۱ + ۴٬۸۵۱ + ۱٬۳۳۲ = ۱۶٬۸۳۴. اگر نخواند، یک دستهٔ چهارم داری که ندیدهای؛ و دستهٔ سومِ همین جدول همان چیزی است که دروغِ اول و دروغِ چهارمِ این فصل رویش سوار بودند.
- وارسیِ دستیِ یک سطر — یک سطر را بردار و با دست حساب کن. از
2024-01-22تا2024-02-07شانزده روز است، و ستون هم ۱۶ میگوید.
✅ چک کن: خطِ دومِ خروجی باید دقیقاً به
16834برسد و با خطِ اول برابر باشد. اگر جمعِ اجزا کمتر شد، شرطهایت همپوشانیِ کامل ندارند — و همان شکافْ جایی است که سطرها گم میشوند.
📏 اندازه بگیر: یک سطر یعنی چه؟ در همهٔ جدولهای این فصل یک سطر یک امانت است، جز جدولِ بخشِ ۳ و ۵ که یک سطرش یک گروهِ عضو است. با کدام شمارشِ دوم سنجیدی؟ با آزمونِ سهگانهٔ بخشِ ۷، و پیش از آن با جدولِ هشتسطری که با انگشت شمردنی بود. چه کسی از قلم افتاد؟ ۱٬۳۳۲ امانتِ برنگشته، ۴۰۰ عضوی که رفتهاند، ۸۳ عضوی که همهٔ کتابهای نود روزِ آخرشان هنوز دستشان است، ۱۰۱ عضوِ اینترنتی، و هجده روزِ باقیماندهٔ ماهی که هنوز تمام نشده.
🤖 از دستیارت بپرس: «سوگیریِ بازماندگان در تحلیلِ داده یعنی چه و یک مثالِ ملموس بزن.» بعد این را بپرس: «اگر میانگینِ مدتِ امانت را فقط روی کتابهای برگشته حساب کنم، عددم بیشتر از واقعیت درمیآید یا کمتر — و چرا؟» — جوابِ درست میگوید کمتر، چون طولانیترین امانتها هنوز برنگشتهاند و اصلاً در نمونه نیستند. اگر جواب گفت «فرقی نمیکند چون نمونه بزرگ است»، همین حالا با ۲۲٫۴۲ و ۳۰٫۰۴ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| sampling bias | سمپلینگ بایاس | سوگیریِ نمونه؛ وقتی سطرهای باقیمانده نمایندهٔ همه نیستند |
| survivorship bias | سروایورشیپ بایاس | سوگیریِ بازماندگان؛ شمردنِ فقط کسانی که ماندهاند |
| censored data | سنسورد دیتا | دادهٔ بریده؛ رویدادی که هنوز تمام نشده و مقدارِ نهاییاش معلوم نیست |
| partial period | پارشال پریود | دورهٔ ناقص؛ آخرین ماه یا هفتهای که هنوز تمام نشده |
| reconciliation | ریکانسیلیایشن | تطبیق؛ سنجیدنِ جمعِ اجزا با یک شمارشِ مستقل از کل |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
تا اینجا دربارهٔ یک عدد حرف زدیم. فصلِ بعد سراغِ دو عدد میرود که کنارِ هم گذاشته شدهاند تا دو شعبه با هم مقایسه شوند.
و با جدولی شروع میکند که در نگاهِ اول کاملاً روشن است: شعبهٔ نسیم با ۷۲٫۰ درصد بازگشتِ بهموقع اول است و شعبهٔ مرکزی با ۶۲٫۳ درصد آخر. بعد نشان میدهد مرکزی در هر هشت دستهٔ کتاب از نسیم بهتر است.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.