از پرسش تا جدول: دانه‌بندی

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

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

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

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

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

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

قبل از شروع#

از ترمِ ۳: JOIN و fan-out و اینکه یک تجمیعِ بعد از پیوند مشکوک است. از ترمِ ۲: GROUP BY و COUNT(DISTINCT ...). از ترمِ ۱: NULL و typeof و پارامترِ ?.

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

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

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

۱. سه کلمه، قبل از هر جدولی#

قبل از CREATE TABLE، سه چیز را روی کاغذ اسم می‌بری. کاغذ را جدی بگیر: هر جدولی که در این ترم بسازی، اول باید در سه خطِ فارسی گفتنی باشد.

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

مرزِ موجودیت و صفت همیشه بدیهی نیست و همان جایی است که باید بایستی و فکر کنی. «شعبه» در فایلِ صادراتیِ ما یک ستون است، یعنی با آن مثلِ یک صفتِ کتاب رفتار شده. ولی شعبه شهر دارد، تاریخِ افتتاح دارد، تعدادِ صندلی دارد — چیزی که خودش صفت دارد، صفت نیست؛ موجودیت است. فصلِ ۴ همین را با عدد نشان می‌دهد.

و طرحی که روی کاغذ می‌کشی، در ساده‌ترین حالتش این است:

   [ عضو ]                [ کتاب ]                [ شعبه ]
      |                       |                       |
      +------ امانت ----------+                       |
             (رابطه)          |                       |
                              +------ در کدام --------+

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

۲. پنج سطر که با انگشت شمردنی است#

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

con.execute("DROP TABLE IF EXISTS tiny_events")
con.execute("""
    CREATE TABLE tiny_events (
        id INTEGER PRIMARY KEY, book TEXT, member TEXT, kind TEXT
    )
""")
con.executemany("INSERT INTO tiny_events VALUES (?, ?, ?, ?)", [
    (1, "الف", "مینا", "out"),
    (2, "الف", "مینا", "in"),
    (3, "ب", "سهند", "out"),
    (4, "ب", "سهند", "in"),
    (5, "الف", "سهند", "out"),
])
con.commit()

print(q("SELECT * FROM tiny_events"))
print()
print("«چند بار کتاب امانت رفت؟» — شمردنِ سطرها:",
      int(q("SELECT COUNT(*) AS n FROM tiny_events").iloc[0, 0]))
print("همان پرسش، با دانه‌بندیِ درست           :",
      int(q("SELECT COUNT(*) AS n FROM tiny_events WHERE kind = ?", ("out",)).iloc[0, 0]))

   id book member kind
0   1  الف   مینا  out
1   2  الف   مینا   in
2   3    ب   سهند  out
3   4    ب   سهند   in
4   5  الف   سهند  out

«چند بار کتاب امانت رفت؟» — شمردنِ سطرها: 5
همان پرسش، با دانه‌بندیِ درست           : 3

پنج در برابرِ سه، و با انگشت شمردنی: مینا یک بار کتابِ الف را برد، سهند یک بار کتابِ ب را و یک بار کتابِ الف را. سه امانت، و پنج سطر.

دلیلش یک جمله است: یک سطر از این جدول یک امانت نیست، یک رویداد است. برگرداندنِ کتاب هم یک رویداد است و سطرِ خودش را دارد. کسی که COUNT(*) می‌گیرد در واقع دارد رویدادها را می‌شمارد و اسمِ «امانت» رویشان می‌گذارد.

و توجه کن که هیچ چیزی در جدول این را به تو نگفت. نامِ جدول tiny_events بود و ستونِ kind هم آنجا نشسته بود؛ ولی پرسشِ COUNT(*) هیچ اعتراضی نکرد و عددِ ۵ کاملاً معتبر به‌نظر می‌رسد.

۳. همان اشتباه، روی جدولِ واقعی#

print(q("SELECT * FROM loan_events ORDER BY id LIMIT 5"))
print()
print(q("SELECT kind, COUNT(*) AS n FROM loan_events GROUP BY kind ORDER BY kind"))
print()
print("جمع دو دسته:", int(q("SELECT COUNT(*) AS n FROM loan_events").iloc[0, 0]))

   id  catalog_id  member_id          happened_at kind
0   1        1145        169  2023-07-01 11:16:15  out
1   2        1019        121  2023-07-01 11:16:44  out
2   3        1084        254  2023-07-01 11:45:47  out
3   4        1162        161  2023-07-01 12:23:24  out
4   5         235        286  2023-07-01 12:31:09  out

  kind      n
0   in  12661
1  out  13458

جمع دو دسته: 26119

بیست‌وشش هزار و صد و نوزده سطر، که ۱۳٬۴۵۸تایشان بردن است و ۱۲٬۶۶۱تایشان برگرداندن. جمعِ دو دسته با کلِ جدول می‌خواند — همان شمارشِ کنترلیِ ترمِ ۱، که اینجا فقط می‌گوید ستونِ kind مقدارِ سومی ندارد.

حالا پرسشِ واقعی، دو بار:

naive = q("""
    SELECT COUNT(*)                  AS loans,
           COUNT(DISTINCT member_id) AS members,
           ROUND(1.0 * COUNT(*) / COUNT(DISTINCT member_id), 2) AS per_member
    FROM loan_events
""")
fixed = q("""
    SELECT COUNT(*)                  AS loans,
           COUNT(DISTINCT member_id) AS members,
           ROUND(1.0 * COUNT(*) / COUNT(DISTINCT member_id), 2) AS per_member
    FROM loan_events
    WHERE kind = ?
""", ("out",))

print("دانه‌بندیِ اشتباه — یک سطر یک رویداد:")
print(naive)
print()
print("دانه‌بندیِ درست — یک سطر یک امانت:")
print(fixed)

دانه‌بندیِ اشتباه — یک سطر یک رویداد:
   loans  members  per_member
0  26119      255      102.43

دانه‌بندیِ درست — یک سطر یک امانت:
   loans  members  per_member
0  13458      255       52.78

۱۰۲٫۴۳ در برابرِ ۵۲٫۷۸ — تقریباً دو برابر، و هیچ خطایی داده نشد.

عددِ اول پاسخِ پرسشِ دیگری است: «هر عضو به‌طور میانگین چند بار با میزِ امانت کار داشته». آن هم پرسشِ معتبری است و شاید حتی برای برنامه‌ریزیِ نیروی انسانی همان را بخواهی. مشکل این نیست که عدد غلط محاسبه شده؛ مشکل این است که عنوانش با محتوایش نمی‌خواند.

و اینجا دقیقاً همان الگوی fan-outِ ترمِ ۳ است، با یک تفاوتِ مهم. آنجا تعدادِ سطرها در اثرِ پرسشِ تو عوض می‌شد و نشانه‌اش هم همین بود: «تعدادِ سطرها عوض شد، در حالی که تو فقط یک ستون اضافه کرده بودی». اینجا هیچ JOINی در کار نیست و هیچ‌چیزی عوض نمی‌شود — تورم از قبل، در خودِ جدول، منتظرت نشسته بود. آزمونِ آن ترم این یکی را نمی‌گیرد.

✅ چک کن: ۱۳٬۴۵۸ به‌علاوهٔ ۱۲٬۶۶۱ باید دقیقاً ۲۶٬۱۱۹ شود، و ۱۳۴۵۸ تقسیم بر ۲۵۵ باید همان ۵۲٫۷۸ دربیاید. اگر عددِ دومت درنیامد، COUNT(DISTINCT member_id) را روی کلِ جدول گرفته‌ای نه روی سطرهای out — که تصادفاً اینجا فرقی نمی‌کند و در دادهٔ دیگری می‌کند.

۴. جوابِ سوم، که آن هم درست است#

قانونِ سومِ این دوره: قبل از باور، بپرس چه کسی از قلم افتاد. مخرجِ ۲۵۵ از کجا آمد؟

members = int(q("SELECT COUNT(*) AS n FROM members_raw").iloc[0, 0])
borrowers = int(q("SELECT COUNT(DISTINCT member_id) AS n FROM loan_events").iloc[0, 0])
checkouts = int(q("SELECT COUNT(*) AS n FROM loan_events WHERE kind = ?", ("out",)).iloc[0, 0])

print("اعضای ثبت‌شده              :", members)
print("اعضایی که در دفتر رویداد هستند:", borrowers)
print("اعضایی که هرگز کتابی نبردند :", members - borrowers)
print()
print("میانگین به‌ازای هر امانت‌گیرنده:", round(checkouts / borrowers, 2))
print("میانگین به‌ازای هر عضو        :", round(checkouts / members, 2))

اعضای ثبت‌شده              : 300
اعضایی که در دفتر رویداد هستند: 255
اعضایی که هرگز کتابی نبردند : 45

میانگین به‌ازای هر امانت‌گیرنده: 52.78
میانگین به‌ازای هر عضو        : 44.86

۵۲٫۷۸ و ۴۴٫۸۶، و هر دو با دانه‌بندیِ درستِ صورت حساب شده‌اند.

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

کدامش را گزارش کنی؟ به پرسش بستگی دارد و باید کنارِ عدد بنویسی‌اش. اگر می‌خواهی بدانی «کسی که از کتابخانه استفاده می‌کند چقدر استفاده می‌کند»، ۵۲٫۷۸. اگر می‌خواهی بدانی «عضویت چقدر به کار می‌آید»، ۴۴٫۸۶ — و آن ۴۵ نفر دقیقاً همان کسانی هستند که پرسش دربارهٔ آن‌هاست.

۵. دانه‌بندی را از داده دربیاور، نه از نام#

نامِ جدول یک ادعاست. راهِ مطمئن این است که بپرسی کوچک‌ترین مجموعهٔ ستون‌هایی که یک سطر را یکتا می‌کند کدام است — همان مجموعه، تعریفِ عملیاتیِ دانه‌بندی است.

COMBOS = ["catalog_id", "member_id", "catalog_id, member_id",
          "catalog_id, member_id, kind", "catalog_id, member_id, happened_at"]
total = int(q("SELECT COUNT(*) AS n FROM loan_events").iloc[0, 0])

for columns in COMBOS:
    # چیزی که به متن پرسش چسبیده نام ستون است، نه مقدار — همان مرزی که ترم ۱
    # فصل ۷ کشید. اگر این نام‌ها از کاربر می‌آمدند، این کد امن نبود.
    n = int(q(f"SELECT COUNT(*) AS n FROM (SELECT DISTINCT {columns} FROM loan_events)").iloc[0, 0])
    print(f"  {columns:<34} {n:>6} ترکیب یکتا از {total} سطر")

  catalog_id                           1005 ترکیب یکتا از 26119 سطر
  member_id                             255 ترکیب یکتا از 26119 سطر
  catalog_id, member_id               12919 ترکیب یکتا از 26119 سطر
  catalog_id, member_id, kind         25098 ترکیب یکتا از 26119 سطر
  catalog_id, member_id, happened_at  26119 ترکیب یکتا از 26119 سطر

چهار ترکیبِ اول کمتر از ۲۶٬۱۱۹ دادند و پنجمی دقیقاً به آن رسید.

پس دانه‌بندیِ این جدول این است: یک سطر یعنی یک کتابِ مشخص، یک عضوِ مشخص، در یک ثانیهٔ مشخص. و همین جمله، همان چیزی است که کلِ بخشِ ۳ را توضیح می‌دهد — هیچ‌جای این تعریف کلمهٔ «امانت» نیست. اضافه شدنِ kind هم کافی نبود: ۲۵٬۰۹۸ در برابرِ ۲۶٬۱۱۹ یعنی هزار و بیست‌ویک بار، یک عضو همان کتاب را با همان نوعِ رویداد بیش از یک بار در دفتر دارد.

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

🔧 اگر کار نکرد: طبیعی‌ترین کاری که آدم بعد از دیدنِ این جدول می‌کند این است که سراغِ شناسهٔ امانت برود. SQLite صریح اعتراض می‌کند:

try:
    q("SELECT loan_id, COUNT(*) AS n FROM loan_events GROUP BY loan_id LIMIT 3")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

pandas.errors.DatabaseError
Execution failed on sql 'SELECT loan_id, COUNT(*) AS n FROM loan_events GROUP BY loan_id LIMIT 3': no such column: loan_id

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

۶. دانه‌بندیِ نتیجه هم دانه‌بندی است#

آخرین نکته، و پرکاربردترینشان: هر پرسشی که می‌نویسی یک جدولِ تازه می‌سازد، و آن جدول دانه‌بندیِ خودش را دارد. یک GROUP BY دانه‌بندی را عوض می‌کند، درست مثلِ یک JOIN.

per_month = q("""
    SELECT substr(happened_at, 1, 7) AS month, COUNT(*) AS checkouts
    FROM loan_events
    WHERE kind = ?
    GROUP BY month
    ORDER BY month
    LIMIT 3
""", ("out",))
print(per_month)
print()
print("سطرهای این نتیجه :", len(q("""
    SELECT substr(happened_at, 1, 7) AS month
    FROM loan_events WHERE kind = ? GROUP BY month
""", ("out",))))
print("جمع ستون checkouts روی همه ماه‌ها:", int(q("""
    SELECT SUM(n) AS s FROM (
        SELECT COUNT(*) AS n FROM loan_events WHERE kind = ?
        GROUP BY substr(happened_at, 1, 7)
    )
""", ("out",)).iloc[0, 0]))

     month  checkouts
0  2023-07        565
1  2023-08        595
2  2023-09        588

سطرهای این نتیجه : 24
جمع ستون checkouts روی همه ماه‌ها: 13458

یک سطر از این نتیجه دیگر یک رویداد نیست، یک ماه است — و جمعِ ستونش دقیقاً همان ۱۳٬۴۵۸ است، یعنی در راه نه سطری گم شد و نه تکثیر.

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

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

🤖 از دستیارت بپرس: «grain در مدل‌سازیِ داده یعنی چه و چرا می‌گویند اولین تصمیم است؟» بعد این را بپرس: «یک جدولِ رویداد و یک جدولِ وضعیت چه فرقی دارند و کدامشان COUNT(*) را گمراه‌کننده می‌کند؟» — جوابِ درست می‌گوید در جدولِ رویداد یک چیزِ واحد چند سطر دارد، پس شمردنِ سطر یعنی شمردنِ رویداد نه شمردنِ چیز. اگر جواب فقط گفت «به داده بستگی دارد»، ناقص است.

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

در فصل بعد#

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

و با دو عدد از همین دو جدول شروع می‌کند: عنوان و نویسنده و شعبه با هم — که هر سه‌تایش را کنارِ هم بگذاری باز ۶ بار تکرار می‌شود — و ۳۰۰ عضو که فقط ۲۸۸ شمارهٔ تلفنِ متمایز دارند.