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

فصل ۸ از ۸

پیشرفت ترم
۰٪

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

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

پرسش: کدام شعبه در دوازده ماهِ گذشته پرکارترین بوده؟

و بهترین درسِ فصل از دو جای غیرمنتظره می‌آید. اولی: نسخهٔ ساده‌لوحانهٔ همین گزارش برای هر شش شعبه عددِ ۱٫۰۰ می‌دهد — جدولی مرتب و کاملاً بی‌معنی که هیچ‌چیزش مشکوک به‌نظر نمی‌رسد. دومی: پرسشِ «بهترین شعبه» سه جوابِ درست دارد — کوشا، نسیم، مرکزی — و کدامش را بگویی به این بستگی دارد که چه چیزی را شمرده‌ای.

سه پیمانهٔ متفاوت روی یک میز که هرکدام یک قطعهٔ واحد را می‌سنجند و سه عددِ متفاوت نشان می‌دهند

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

  • یک پرسشِ فارسی را به یک پرسشِ سه‌جدولی ترجمه کنی
  • قبل از هر عددی، دانه‌بندیِ هر سه جدول را اعلام و بررسی کنی
  • ثابت کنی که نتیجه‌ات نه تکثیر شده و نه سطری از دست داده
  • گزارشی بنویسی که هر عددش سنجه‌اش را با خودش می‌آورد

قبل از شروع#

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

دانه‌بندیِ سه جدولی که با آن‌ها کار می‌کنیم: یک سطر از branches یک شعبه است؛ یک سطر از books یک عنوانِ ثبت‌شده در یک شعبه؛ یک سطر از loans یک بار امانت رفتنِ یک کتاب.

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

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

۱. قدمِ صفر: بشمار، بعد بپرس#

قبل از هر پرسشِ تحلیلی، چیزهایی که باید بدانی و پنج خط طول می‌کشند:

print("شعبه‌ها          :", int(q("SELECT COUNT(*) AS n FROM branches").iloc[0, 0]))
print("کتاب‌ها          :", int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0]))
print("امانت‌ها         :", int(q("SELECT COUNT(*) AS n FROM loans").iloc[0, 0]))
print(q("SELECT MIN(loaned_at) AS first_loan, MAX(loaned_at) AS last_loan FROM loans"))
print("امانتِ بی‌کتاب   :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM loans AS l
          LEFT JOIN books AS b ON b.id = l.book_id
          WHERE b.id IS NULL
      """).iloc[0, 0]))
print("کتابِ بی‌شعبه    :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS b
          LEFT JOIN branches AS br ON br.id = b.branch_id
          WHERE br.id IS NULL
      """).iloc[0, 0]))
شعبه‌ها          : 7
کتاب‌ها          : 1200
امانت‌ها         : 3998
   first_loan   last_loan
0  2023-01-01  2025-06-30
امانتِ بی‌کتاب   : 0
کتابِ بی‌شعبه    : 0

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

و آخرین تاریخِ امانت ۳۰ ژوئنِ ۲۰۲۵ است. پس «دوازده ماهِ گذشته» یعنی از اولِ ژوئیهٔ ۲۰۲۴ تا همان تاریخ — بازه‌ای که خودمان انتخابش کردیم و باید در گزارش بنویسیمش، وگرنه هیچ‌کس نمی‌داند عددها دربارهٔ چه دوره‌ای‌اند.

۲. پاسخ#

سه مرحله: امانت‌های داخلِ بازه را برای هر کتاب بشمار، هر کتاب را با تعدادش کنارِ شعبه‌اش بگذار، بعد گروه‌بندی کن. تجمیع قبل از پیوند، دقیقاً مثلِ فصلِ ۳.

REPORT = """
    WITH loans_window AS (
        SELECT book_id, COUNT(*) AS n
        FROM loans
        WHERE loaned_at >= ?
        GROUP BY book_id
    ),
    per_book AS (
        SELECT b.id, b.branch_id, b.added_at, COALESCE(w.n, 0) AS loans
        FROM books AS b
        LEFT JOIN loans_window AS w ON w.book_id = b.id
    )
    SELECT br.name,
           COUNT(p.id)                AS books,
           SUM(COALESCE(p.loans, 0))  AS loans,
           ROUND(1.0 * SUM(COALESCE(p.loans, 0)) / NULLIF(COUNT(p.id), 0), 2) AS per_book
    FROM branches AS br
    LEFT JOIN per_book AS p ON p.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY per_book DESC
"""
report = q(REPORT, ("2024-07-01",))
print(report)
    name  books  loans  per_book
0  مرکزی    197    445      2.26
1   نسیم    208    302      1.45
2   دانش    192    268      1.40
3   بهار    174    184      1.06
4   کوشا    225    201      0.89
5   پویا    204    149      0.73
6  آفتاب      0      0       NaN

پاسخ: مرکزی، با ۲٫۲۶ امانت به‌ازای هر کتاب.

و آن NaN در سطرِ آخر خطا نیست، صادق‌ترین خانهٔ این جدول است. شعبهٔ آفتاب هیچ کتابی ندارد، پس «امانت به‌ازای هر کتاب» برایش تعریف نشده. NULLIF(COUNT(p.id), 0) دقیقاً همین را می‌گوید: به‌جای تقسیم بر صفر، NULL. اگر آنجا صفر می‌نوشتیم، جدول ادعا می‌کرد آفتاب بدترین شعبه است — در حالی که آفتاب اصلاً در این مقایسه شرکت ندارد.

۳. اثبات: نه تکثیر شد، نه گم شد#

print("جمع ستون books :", int(report["books"].sum()), "| کل کتاب‌ها: 1200")
print("جمع ستون loans :", int(report["loans"].sum()))
print("امانت‌های بازه :",
      int(q("SELECT COUNT(*) AS n FROM loans WHERE loaned_at >= ?", ("2024-07-01",)).iloc[0, 0]))
print("سطرهای گزارش   :", len(report), "| کل شعبه‌ها: 7")
جمع ستون books : 1200 | کل کتاب‌ها: 1200
جمع ستون loans : 1549
امانت‌های بازه : 1549
سطرهای گزارش   : 7 | کل شعبه‌ها: 7

سه آزمون، هر سه پاس.

جمعِ ستونِ books دقیقاً ۱٬۲۰۰ است — هیچ کتابی دو بار شمرده نشده و هیچ کتابی جا نمانده. جمعِ ستونِ loans دقیقاً برابرِ تعدادِ امانت‌های بازه است که از یک پرسشِ کاملاً مستقل گرفتیم. و گزارش هفت سطر دارد، نه شش: شعبهٔ بی‌کتاب هم در جدول هست.

✅ چک کن: این سه خط را در هر گزارشی که با JOIN می‌سازی بنویس. اگر جمعِ books بیشتر از ۱٬۲۰۰ درآمد، جایی fan-out داری؛ اگر کمتر، جایی INNER JOIN نوشته‌ای که باید LEFT می‌بود.

۴. همان گزارش، اگر ساده‌لوحانه نوشته می‌شد#

print(q("""
    SELECT br.name,
           COUNT(*)    AS books,
           COUNT(l.id) AS loans,
           ROUND(1.0 * COUNT(l.id) / COUNT(*), 2) AS per_book
    FROM branches AS br
    JOIN books AS b ON b.branch_id = br.id
    JOIN loans AS l ON l.book_id = b.id
    WHERE l.loaned_at >= ?
    GROUP BY br.id, br.name
    ORDER BY per_book DESC
""", ("2024-07-01",)))
    name  books  loans  per_book
0  مرکزی    445    445       1.0
1   نسیم    302    302       1.0
2   دانش    268    268       1.0
3   بهار    184    184       1.0
4   کوشا    201    201       1.0
5   پویا    149    149       1.0

هر شش شعبه دقیقاً ۱٫۰۰. جدول مرتب است، عددها تمیزند، و گزارش کاملاً بی‌معنی است.

دلیلش را حالا با یک نگاه می‌بینی: بعد از دو JOIN، یک سطر دیگر «یک کتاب» نیست، «یک امانت» است. پس COUNT(*) امانت‌ها را شمرد و COUNT(l.id) هم امانت‌ها را شمرد، و تقسیمِ یک عدد بر خودش همیشه ۱ است. مرکزی ۱۹۷ کتاب دارد و این جدول می‌گوید ۴۴۵.

و هیچ خطایی داده نشد. تنها چیزی که مچش را می‌گیرد همان آزمونِ بخشِ ۳ است: جمعِ ستونِ books اینجا ۱٬۵۴۹ می‌شود، نه ۱٬۲۰۰.

۵. سه «بهترین» متفاوت#

قبل از نوشتنِ گزارش، یک پرسشِ آخر: آیا «پرکارترین» همان «بزرگ‌ترین» است؟

print(q("""
    SELECT br.name, COUNT(b.id) AS books, SUM(b.pages) AS pages
    FROM branches AS br
    LEFT JOIN books AS b ON b.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
    name  books     pages
0   کوشا    225  106918.0
1   نسیم    208  108608.0
2   پویا    204   97273.0
3  مرکزی    197   94868.0
4   دانش    192   93673.0
5   بهار    174   86709.0
6  آفتاب      0       NaN

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

پس «کدام شعبه بهترین است؟» پرسشِ کاملی نیست. جملهٔ کامل باید سنجه‌اش را با خودش بیاورد — و این دقیقاً همان چیزی است که یک گزارشِ خوب را از یک عددِ تنها جدا می‌کند.

و NaN ستونِ pages برای آفتاب یک یادآوریِ ترمِ ۱ است: SUM روی صفر سطر، صفر نمی‌دهد؛ NULL می‌دهد، چون جمعِ هیچ‌چیزی معلوم نیست.

۶. آیا جواب به تعریفِ ما بند است؟#

یک ایرادِ منصفانه به گزارشِ بخشِ ۲: کتابی که پارسال به فهرست اضافه شده، فرصتِ کمتری برای امانت رفتن داشته. پس بیایید مخرج را منصفانه‌تر کنیم و فقط کتاب‌هایی را بشماریم که قبل از شروعِ ثبتِ امانت‌ها در فهرست بوده‌اند.

fair = q("""
    WITH loans_window AS (
        SELECT book_id, COUNT(*) AS n
        FROM loans
        WHERE loaned_at >= ?
        GROUP BY book_id
    ),
    per_book AS (
        SELECT b.id, b.branch_id, b.added_at, COALESCE(w.n, 0) AS loans
        FROM books AS b
        LEFT JOIN loans_window AS w ON w.book_id = b.id
    )
    SELECT br.name,
           COUNT(p.id)               AS books,
           SUM(COALESCE(p.loans, 0)) AS loans,
           ROUND(1.0 * SUM(COALESCE(p.loans, 0)) / NULLIF(COUNT(p.id), 0), 2) AS per_book
    FROM branches AS br
    LEFT JOIN per_book AS p ON p.branch_id = br.id AND p.added_at < ?
    GROUP BY br.id, br.name
    ORDER BY per_book DESC
""", ("2024-07-01", "2023-01-01"))
print(fair)
print()
print("ترتیب شعبه‌ها در دو تعریف یکی است؟",
      list(report["name"]) == list(fair["name"]))
    name  books  loans  per_book
0  مرکزی    118    263      2.23
1   نسیم    133    221      1.66
2   دانش    116    150      1.29
3   بهار    107    117      1.09
4   کوشا    127    115      0.91
5   پویا    133     98      0.74
6  آفتاب      0      0       NaN

ترتیب شعبه‌ها در دو تعریف یکی است؟ True

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

معنایش این است که رتبه‌بندی به این تعریفِ خاص بند نیست — و همین جمله، خودِ رتبه‌بندی را قابلِ اعتمادتر می‌کند. اگر ترتیب عوض شده بود، گزارش باید هر دو نسخه را می‌داد.

ولی این نتیجه را بیشتر از اندازه‌اش باور نکن. ایرادِ ما این بود که کتابِ تازه فرصتِ کمتری داشته. این داده ساختگی است و تاریخِ امانت در آن به added_at بند نیست:

print("امانت‌های قبل از added_at کتاب:", int(q("""
    SELECT COUNT(*) AS n FROM loans AS l JOIN books AS b ON b.id = l.book_id
    WHERE l.loaned_at < b.added_at
""").iloc[0, 0]))
امانت‌های قبل از added_at کتاب: 786

از ۳٬۹۹۸ امانت، ۷۸۶ تا قبل از ورودِ کتابشان به فهرست ثبت شده‌اند. در یک دادهٔ واقعی این خودش مسئله‌ای است که باید بررسی شود. پس آزمونِ این بخش اینجا فقط می‌گوید کنار گذاشتنِ کتاب‌های تازه ترتیب را عوض نمی‌کند، و چیزی دربارهٔ فرصتِ کمترِ آن‌ها ثابت نمی‌کند.

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

🔧 اگر کار نکرد: اگر بخواهی شعبه‌های کم‌کار را با یک شرط روی خودِ تجمیع فیلتر کنی و آن را در WHERE بگذاری، SQLite صریح اعتراض می‌کند — این همان جایی است که HAVING لازم است:

try:
    q("""
        SELECT br.name, COUNT(b.id) AS books
        FROM branches AS br
        LEFT JOIN books AS b ON b.branch_id = br.id
        WHERE COUNT(b.id) > ?
        GROUP BY br.id, br.name
    """, (100,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT br.name, COUNT(b.id) AS books
        FROM branches AS br
        LEFT JOIN books AS b ON b.branch_id = br.id
        WHERE COUNT(b.id) > ?
        GROUP BY br.id, br.name
    ': misuse of aggregate: COUNT()

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

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

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

و آنچه نمی‌دانیم، بخشی از همین گزارش است: شعبهٔ آفتاب در هیچ‌کدام از این مقایسه‌ها نیست چون هنوز کتابی ندارد؛ نمی‌دانیم اختلافِ ۱٫۴۵ و ۱٫۴۰ بینِ نسیم و دانش معنادار است یا قرعه — ترمِ ۶ فصلِ ۵ ابزارِ سنجیدنش را می‌سازد؛ و کلِ این تحلیل دربارهٔ کتاب‌هایی است که ثبت شده‌اند — کتابی که هرگز وارد فهرست نشده در هیچ عددی نیست.

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

🤖 از دستیارت بپرس: «چرا در گزارشِ ساده‌لوحانه همهٔ شعبه‌ها نرخِ ۱٫۰۰ گرفتند؟» بعد این را بپرس: «چه آزمونی می‌توانستم قبل از انتشار بزنم که این را می‌گرفت؟» — جوابِ درست به جمعِ ستونِ books در برابرِ کلِ کتاب‌ها اشاره می‌کند. اگر جواب فقط گفت «پرسش را دوباره بخوان»، آزمونی پیشنهاد نداده.

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

کلمه تلفظ به حروف فارسی یعنی چه
reconciliation ری‌کانسیلی‌ایشن تطبیق؛ سنجیدنِ جمعِ یک گزارش با یک شمارشِ مستقل
reporting window ریپورتینگ ویندو بازهٔ زمانی‌ای که گزارش دربارهٔ آن است
denominator دنامینیتر مخرج؛ چیزی که نرخ بر آن تقسیم می‌شود

تمرین‌ها

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

در فصل بعد#

ترمِ ۳ تمام شد. حالا می‌توانی داده را از چند جدول کنارِ هم بیاوری و ثابت کنی که در راه نه سطری گم شده و نه تکثیر.

ولی در تمامِ این هشت فصل، اسکیما را کسِ دیگری ساخته بود و ما فقط رویش پرسش نوشتیم. ترمِ ۴ سراغِ خودِ اسکیما می‌رود: چطور از یک فایلِ کثیف به جدول‌هایی برسی که پایگاه داده خودش از قوانینشان محافظت می‌کند — و چطور فردا عوضشان کنی بدونِ اینکه چیزی بشکند.

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

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