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

فصل ۹ از ۹

پیشرفت ترم
۰٪

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

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

و بهترین درسِ فصل از یک ستون درمی‌آید. پرسش این است: «کدام شعبه پرکارترین است؟» و همان جدول دو رتبه‌بندیِ متفاوت می‌سازد — دو شعبه‌ای که با جمعِ خام رتبهٔ پنجم و ششم دارند، با نرخِ هر کتاب جایشان عوض می‌شود.

هیچ‌کدام غلط نیست. دو پرسشِ متفاوت‌اند که یک جملهٔ فارسی هر دو را می‌پوشاند، و کلِ این ترم برای همین یک لحظه بود.

یک کارتِ خلاصهٔ کوچک در یک کفهٔ ترازو و یک دستهٔ بلند از برگه‌ها در کفهٔ دیگر، با تیرک کاملاً تراز

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

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

قبل از شروع#

همهٔ هشت فصلِ قبل. هیچ چیزِ تازه‌ای اینجا نیست.

دانه‌بندیِ جدولِ ورودی: یک سطر از books یعنی یک عنوانِ ثبت‌شده در یکی از شش شعبه — همان جمله‌ای که از فصلِ اولِ ترمِ ۱ تکرار می‌شود.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

جدول تعدادِ سطر زمانِ ساخت
books ۱٬۲۰۰ کمتر از یک ثانیه
branches ۶ آنی

۱. قدمِ صفر: پرسش را بنویس، دانه‌بندی را اعلام کن#

پرسشِ سفارش‌دهنده: «کدام شعبه پرکارترین است؟»

قبل از هر پرسشی در SQL، دو جمله را بنویس:

  1. یک سطر از خروجی یعنی یک شعبه. نه یک کتاب، نه یک امانت.
  2. «پرکار» هنوز تعریف نشده. تا وقتی نگویی جمعِ امانت یا امانت به‌ازای هر کتاب، پرسش جواب ندارد — و فصلِ ۴ نشان داد این دو یک ترتیب نمی‌سازند.

پس گزارش را طوری می‌سازیم که هر دو تعریف در آن باشند و خواننده خودش ببیند کجا از هم جدا می‌شوند.

۲. جدولِ گروهی، با یک ستونِ پنجره‌ای#

report = q("""
    SELECT branch,
           COUNT(*)                                   AS books,
           SUM(borrowed)                              AS loans,
           ROUND(SUM(borrowed) * 1.0 / COUNT(*), 2)   AS per_book,
           ROUND(SUM(borrowed) * 100.0 / SUM(SUM(borrowed)) OVER (), 2) AS share_pct
    FROM books
    GROUP BY branch
    ORDER BY loans DESC
""")
print(report)
  branch  books  loans  per_book  share_pct
0   نسیم    224  18364     81.98      19.50
1   بهار    214  17498     81.77      18.58
2   کوشا    200  15542     77.71      16.50
3   پویا    195  15122     77.55      16.05
4   دانش    187  13887     74.26      14.74
5  مرکزی    180  13778     76.54      14.63

شش سطر، و هر ستونش از یک فصلِ متفاوتِ این ترم آمده.

COUNT(*) و SUM از فصلِ ۱، GROUP BY از فصلِ ۲، نرخ به‌ازای واحد از فصلِ ۴، و ستونِ آخر از فصلِ ۶: SUM(SUM(borrowed)) OVER () — یک تابعِ پنجره که روی نتیجهٔ گروه‌بندی نشسته و جمعِ کلِ امانت‌ها را در هر سطر می‌آورد، بدونِ هیچ زیرپرسشی.

✅ چک کن: ستونِ per_book را برای سطرِ اول با دست حساب کن: ۱۸۳۶۴ تقسیم بر ۲۲۴ باید بشود ۸۱٫۹۸. اگر عددت 81.0 درآمد، * 1.0 را جا انداخته‌ای و تقسیمِ صحیحِ فصلِ ۴ اعشار را بریده. و اگر ستونِ share_pct فقط عددهای گِردی مثلِ 19.0 و 18.0 داد، همان اشتباه یک جای دیگر تکرار شده: 100.0 را 100 نوشته‌ای.

۳. سه شمارشِ کنترلی#

یک شمارشِ کنترلی کافی نیست، چون هر کدام کلاسِ متفاوتی از خطا را می‌گیرد.

whole = q("SELECT COUNT(*) AS books, SUM(borrowed) AS loans FROM books")

print("۱) جمع اجزا برابر کل؟")
print("   کتاب‌ها :", int(report["books"].sum()), "در برابر", int(whole["books"][0]))
print("   امانت‌ها:", int(report["loans"].sum()), "در برابر", int(whole["loans"][0]))
print()
print("۲) تعداد گروه‌ها برابر تعداد شعبه‌های واقعی؟")
print("   گروه‌ها:", len(report),
      "| سطرهای جدول branches:", int(q("SELECT COUNT(*) FROM branches").iloc[0, 0]))
print()
print("۳) جمع درصدها:")
print("   ", round(float(report["share_pct"].sum()), 4))
۱) جمع اجزا برابر کل؟
   کتاب‌ها : 1200 در برابر 1200
   امانت‌ها: 94191 در برابر 94191

۲) تعداد گروه‌ها برابر تعداد شعبه‌های واقعی؟
   گروه‌ها: 6 | سطرهای جدول branches: 6

۳) جمع درصدها:
    100.0

هر سه پاس شدند، و هر سه چیزِ متفاوتی را ثابت کردند.

  • آزمونِ اول ثابت می‌کند هیچ سطری در راه گم نشده. ولی ترمِ ۱ فصلِ ۵ نشان داد که این آزمون سطرهای جابه‌جاشده را نمی‌گیرد — جمع می‌تواند درست باشد و هر دو تکه غلط.
  • آزمونِ دوم همان را می‌گیرد که آزمونِ اول نمی‌گیرد: اگر شعبه‌ای هیچ کتابی نداشته باشد، جمع درست می‌ماند و یک سطر بی‌صدا از گزارش غایب می‌شود. در فصلِ ۲ همین شمارش بود که ۴۵ گروه را به‌جای ۷ گروه لو داد.
  • آزمونِ سوم دقیقاً ۱۰۰ درآمد، و این خوش‌شانسی است نه قاعده: در فصلِ ۴ همین جمع روی هشت دسته ۱۰۰٫۰۱ شد. گِرد کردن جمع را تضمین نمی‌کند و جملهٔ درست در گزارش این است که درصدها گرد شده‌اند، نه اینکه عدد را دستکاری کنی.

۴. ستونی که رتبه‌بندی را عوض می‌کند#

جدولِ بخشِ ۲ بر اساسِ جمعِ خام مرتب شده. حالا هر دو رتبه را کنارِ هم بگذاریم:

print(q("""
    SELECT branch,
           SUM(borrowed)                              AS loans,
           ROUND(SUM(borrowed) * 1.0 / COUNT(*), 2)   AS per_book,
           RANK() OVER (ORDER BY SUM(borrowed) DESC)  AS by_loans,
           RANK() OVER (ORDER BY SUM(borrowed) * 1.0 / COUNT(*) DESC) AS by_rate
    FROM books
    GROUP BY branch
    ORDER BY by_loans
"""))
  branch  loans  per_book  by_loans  by_rate
0   نسیم  18364     81.98         1        1
1   بهار  17498     81.77         2        2
2   کوشا  15542     77.71         3        3
3   پویا  15122     77.55         4        4
4   دانش  13887     74.26         5        6
5  مرکزی  13778     76.54         6        5

چهار سطرِ اول در هر دو رتبه‌بندی یکسان‌اند، و دو سطرِ آخر جایشان عوض شده.

شعبه‌ای که پنجمینِ جمعِ خام است، از نظرِ نرخِ هر کتاب ششم می‌شود — چون کتاب‌های بیشتری دارد و همان جمع را روی کتاب‌های بیشتری پخش می‌کند.

کدام رتبه‌بندی درست است؟ هر دو، و هرکدام جوابِ پرسشِ متفاوتی:

  • جمعِ خام جوابِ «کدام شعبه بیشترین بار را جابه‌جا کرده؟» است. اگر تصمیمت دربارهٔ نیروی انسانی یا فضاست، این عدد را می‌خواهی.
  • نرخِ هر کتاب جوابِ «در کدام شعبه هر کتاب به‌طورِ میانگین بیشتر امانت رفته؟» است. (برای «یک کتابِ معمولی» به‌معنای دقیق باید میانه را بگیری؛ فصلِ ۳ فرقشان را نشان داد.) اگر تصمیمت دربارهٔ خریدِ کتابِ تازه است، این یکی.

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

۵. چه کسی از قلم افتاد#

هر عددِ بالا دربارهٔ کتاب‌هایی است که در فهرست‌اند. حالا صریح بگوییم دربارهٔ چه کسانی نیست:

print(q("""
    SELECT branch,
           COUNT(*)                                  AS books,
           SUM(rating IS NULL)                       AS unrated,
           ROUND(SUM(rating IS NULL) * 100.0 / COUNT(*), 2) AS unrated_pct
    FROM books
    GROUP BY branch
    ORDER BY unrated_pct DESC
"""))
print()
print("لبه‌های داده:")
print(q("SELECT MIN(added_at) AS first_day, MAX(added_at) AS last_day FROM books"))
print("سطرهای arrivals.csv که هنوز وارد نشده‌اند:",
      len(CSV_PATH.read_text(encoding="utf-8").splitlines()) - 1)
  branch  books  unrated  unrated_pct
0   پویا    195       42        21.54
1   کوشا    200       43        21.50
2   نسیم    224       42        18.75
3   دانش    187       35        18.72
4   بهار    214       38        17.76
5  مرکزی    180       31        17.22

لبه‌های داده:
    first_day    last_day
0  2019-01-01  2025-06-30
سطرهای arrivals.csv که هنوز وارد نشده‌اند: 240

سه چیز از این خروجی بیرون می‌آید و هر سه باید در گزارش باشند:

  1. بینِ ۱۷ و ۲۲ درصدِ کتاب‌های هر شعبه امتیاز ندارند. پس هر جمله‌ای دربارهٔ کیفیت — که در گزارشِ ما نیست — فقط دربارهٔ همان چهارپنجمِ امتیازدار خواهد بود. ستون‌های books و loans از این ماجرا سالم‌اند، چون هیچ‌کدام به rating کاری ندارند.
  2. داده وسطِ سال تمام می‌شود. پس هر مقایسهٔ سالانه‌ای روی این جدول سالِ آخرش ناقص است — همان تلهٔ فصلِ ۷.
  3. ۲۴۰ کتاب هنوز روی دیسک‌اند و در هیچ عددی از این گزارش نیستند.

🔧 اگر کار نکرد: طبیعی‌ترین ستونی که برای این گزارش می‌خواهی، تعدادِ صندلی‌های هر شعبه است تا بشود «امانت به‌ازای هر صندلی» را حساب کرد. در دسترس نیست:

try:
    q("SELECT branch, SUM(borrowed) * 1.0 / seats AS per_seat FROM books GROUP BY branch")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT branch, SUM(borrowed) * 1.0 / seats AS per_seat FROM books GROUP BY branch': no such column: seats

seats ستونِ جدولِ branches است، نه books. تا وقتی نتوانی دو جدول را کنارِ هم بیاوری، هر سنجه‌ای که ستون‌هایش در دو جدولِ متفاوت‌اند از دسترست بیرون است — و این دقیقاً همان چیزی است که ترمِ ۳ حلش می‌کند.

۶. گزارشِ نهایی#

سه جمله که می‌شود رویشان تصمیم گرفت، و هر جمله عددش و محدودیتش را با خودش می‌آورد:

  1. پرشمارترین شعبه ۱۹٫۵۰ درصدِ کلِ امانت‌ها را دارد و کم‌سهم‌ترین ۱۴٫۶۳ درصد — یعنی سهمِ شعبه‌ها نسبتاً نزدیک است و هیچ شعبه‌ای مسلط نیست. جمعِ شش سهم دقیقاً ۱۰۰ شد و جمعِ شش شمارش ۱٬۲۰۰.
  2. رتبه‌بندی به تعریفِ «پرکار» بستگی دارد، و دو شعبهٔ انتهای جدول با دو تعریف جایشان عوض می‌شود. گزارش هر دو ستون را دارد تا خواننده مجبور نباشد حدس بزند کدام تعریف به کار رفته.
  3. نرخِ امانت به‌ازای هر کتاب بینِ ۷۴٫۲۶ و ۸۱٫۹۸ است — بازه‌ای که پهنایش کمتر از هشت امانت به‌ازای هر کتاب است.

و آنچه نمی‌دانیم، بخشی از همین گزارش است: نمی‌دانیم اختلافِ دو شعبهٔ آخر معنادار است یا قرعه؛ نمی‌دانیم کتاب‌ها چه مدت در فهرست بوده‌اند و نرخِ ما این را حساب نمی‌کند؛ نمی‌دانیم ۲۴۰ کتابِ واردنشده جدول را چقدر عوض می‌کنند؛ و نمی‌توانیم امانت را به ظرفیتِ شعبه بسنجیم، چون آن ستون در جدولِ دیگری است.

📏 اندازه بگیر: یک سطر یعنی چه؟ یک شعبه — و همهٔ ستون‌های گزارش دربارهٔ کتاب‌های همان شعبه‌اند، نه دربارهٔ امانت‌ها به‌عنوان رویدادهای جدا. با کدام شمارشِ دوم سنجیدی؟ سه‌تا و هر سه از یک جنس نیستند: جمعِ اجزا در برابرِ کل (۱٬۲۰۰ و ۹۴٬۱۹۱)، تعدادِ گروه‌ها در برابرِ سطرهای branches (۶ و ۶)، و جمعِ درصدها (۱۰۰٫۰). چه کسی از قلم افتاد؟ ۲۳۱ کتابِ بی‌امتیاز که در ستون‌های این گزارش دخالتی ندارند ولی در هر سنجهٔ کیفی می‌افتادند بیرون؛ نیمهٔ دومِ سالِ آخر؛ ۲۴۰ سطرِ arrivals.csv؛ و هر کتابی که هرگز ثبت نشده و دربارهٔ آن حتی نمی‌توانیم بگوییم چندتاست.

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

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

کلمه تلفظ به حروف فارسی یعنی چه
report grain ریپورت گرین دانه‌بندیِ گزارش؛ اینکه یک سطرش یعنی چه
control count کنترل کانت شمارشِ دومی که درستیِ عددِ اول را می‌سنجد
operational definition آپریشنال دفینیشن تعریفِ دقیقِ اینکه یک سنجه با چه چیزی اندازه گرفته می‌شود
coverage کاورج اینکه گزارش چه سهمی از واقعیت را پوشش می‌دهد

تمرین‌ها

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

در فصل بعد#

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

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

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

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