آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۳ از ۹

پیشرفت ترم
۰٪

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

شش پرسش، هر شش‌تا کاملاً معمولی، هر شش‌تا بدونِ خطا، و هر شش‌تا با عددِ غلط.

میانگینِ مدتِ امانت ۲۲٫۴۲ روز اعلام می‌شود؛ عددِ صادقانه ۳۰٫۰۴ روز است. میانگینِ امانتِ هر عضو ۱۶٫۱۲ اعلام می‌شود؛ عددِ صادقانه ۱۲٫۰۲ است. و نمودارِ ماهانه یک سقوطِ ناگهانی نشان می‌دهد که اصلاً اتفاق نیفتاده.

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

یک ردیف قایق روی آب که چند تایشان زیر خط آب فرو رفته‌اند و یک ناظر بالای سطح فقط قایق‌های شناور را می‌شمارد

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

  • شش شکلِ رایجِ عددِ غلطِ بی‌خطا را با اسم تشخیص بدهی
  • نشان بدهی حذفِ 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
  1. شمارشِ کل — تعدادِ سطرهایی که پرسش دید، در برابرِ تعدادِ سطرهای جدول. اگر کمتر است، بپرس کدام‌ها.
  2. جمعِ اجزا برابرِ کل — سه دسته که هیچ سطری بینشان نمی‌افتد: ⁦۱۰٬۶۵۱ + ۴٬۸۵۱ + ۱٬۳۳۲ = ۱۶٬۸۳۴⁩. اگر نخواند، یک دستهٔ چهارم داری که ندیده‌ای؛ و دستهٔ سومِ همین جدول همان چیزی است که دروغِ اول و دروغِ چهارمِ این فصل رویش سوار بودند.
  3. وارسیِ دستیِ یک سطر — یک سطر را بردار و با دست حساب کن. از 2024-01-22 تا 2024-02-07 شانزده روز است، و ستون هم ۱۶ می‌گوید.

✅ چک کن: خطِ دومِ خروجی باید دقیقاً به 16834 برسد و با خطِ اول برابر باشد. اگر جمعِ اجزا کمتر شد، شرط‌هایت هم‌پوشانیِ کامل ندارند — و همان شکافْ جایی است که سطرها گم می‌شوند.

📏 اندازه بگیر: یک سطر یعنی چه؟ در همهٔ جدول‌های این فصل یک سطر یک امانت است، جز جدولِ بخشِ ۳ و ۵ که یک سطرش یک گروهِ عضو است. با کدام شمارشِ دوم سنجیدی؟ با آزمونِ سه‌گانهٔ بخشِ ۷، و پیش از آن با جدولِ هشت‌سطری که با انگشت شمردنی بود. چه کسی از قلم افتاد؟ ۱٬۳۳۲ امانتِ برنگشته، ۴۰۰ عضوی که رفته‌اند، ۸۳ عضوی که همهٔ کتاب‌های نود روزِ آخرشان هنوز دستشان است، ۱۰۱ عضوِ اینترنتی، و هجده روزِ باقی‌ماندهٔ ماهی که هنوز تمام نشده.

🤖 از دستیارت بپرس: «سوگیریِ بازماندگان در تحلیلِ داده یعنی چه و یک مثالِ ملموس بزن.» بعد این را بپرس: «اگر میانگینِ مدتِ امانت را فقط روی کتاب‌های برگشته حساب کنم، عددم بیشتر از واقعیت درمی‌آید یا کمتر — و چرا؟» — جوابِ درست می‌گوید کمتر، چون طولانی‌ترین امانت‌ها هنوز برنگشته‌اند و اصلاً در نمونه نیستند. اگر جواب گفت «فرقی نمی‌کند چون نمونه بزرگ است»، همین حالا با ۲۲٫۴۲ و ۳۰٫۰۴ ردش کن.

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

کلمه تلفظ به حروف فارسی یعنی چه
sampling bias سمپلینگ بایاس سوگیریِ نمونه؛ وقتی سطرهای باقی‌مانده نمایندهٔ همه نیستند
survivorship bias سروایورشیپ بایاس سوگیریِ بازماندگان؛ شمردنِ فقط کسانی که مانده‌اند
censored data سنسورد دیتا دادهٔ بریده؛ رویدادی که هنوز تمام نشده و مقدارِ نهایی‌اش معلوم نیست
partial period پارشال پریود دورهٔ ناقص؛ آخرین ماه یا هفته‌ای که هنوز تمام نشده
reconciliation ری‌کانسیلی‌ایشن تطبیق؛ سنجیدنِ جمعِ اجزا با یک شمارشِ مستقل از کل

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

تا اینجا دربارهٔ یک عدد حرف زدیم. فصلِ بعد سراغِ دو عدد می‌رود که کنارِ هم گذاشته شده‌اند تا دو شعبه با هم مقایسه شوند.

و با جدولی شروع می‌کند که در نگاهِ اول کاملاً روشن است: شعبهٔ نسیم با ۷۲٫۰ درصد بازگشتِ به‌موقع اول است و شعبهٔ مرکزی با ۶۲٫۳ درصد آخر. بعد نشان می‌دهد مرکزی در هر هشت دستهٔ کتاب از نسیم بهتر است.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.