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

فصل ۴ از ۹

پیشرفت ترم
۰٪

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

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

بعد همان جدول را به‌تفکیکِ دستهٔ کتاب می‌گیریم و مرکزی در هر هشت دسته از نسیم بهتر است. در کودک ۹۶٫۵ در برابرِ ۸۶٫۷، در تاریخ ۵۴٫۵ در برابرِ ۴۳٫۸، و همین‌طور تا آخر.

هیچ‌کدام از این دو جدول غلط نیست. تفاوتشان در چیزی است که هیچ‌کدام چاپش نمی‌کنند: مرکزی و نسیم اصلاً یک‌جور کتاب امانت نمی‌دهند.

دو قیف یکسان که هرکدام از دو مخزنِ شن درشت و ریز تغذیه می‌شوند ولی با نسبت‌های وارونه، و دو تودهٔ خروجیِ کاملاً نابرابر می‌سازند

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

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

قبل از شروع#

از فصلِ ۲ و ۳: تعریفِ عملیاتی، پنجرهٔ زمانی، و اینکه دورهٔ ناقص عدد را خراب می‌کند. از ترمِ ۲: 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 ریتنشن ماندگاری؛ چند درصدِ یک کوهورت بعد از مدتی هنوز هستند

تمرین‌ها

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

در فصل بعد#

حالا می‌توانی دو عدد را منصفانه کنارِ هم بگذاری. فصلِ بعد سؤالی را جواب می‌دهد که چهار پروژهٔ این دوره صریحاً به تعویقش انداختند: این اختلاف واقعی است یا قرعه؟

و جوابش وارونهٔ حدسِ اول است: اختلافِ بیست‌واحدیِ دو کتابدار روی بیست امانت اصلاً اختلاف نیست، و اختلافِ ۹٫۶۵ واحدیِ نسیم و مرکزی هست — چون دومی از ۶٬۰۹۴ سطر ساخته شده و اولی از بیست سطر.

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

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