در این فصل چه یاد میگیری#
این فصل دلیلِ وجودِ کلِ این ترم است.
جمعِ صفحاتِ این کتابخانه ۵۸۸٬۰۴۹ است. یک JOIN به جدولِ امانتها اضافه میکنیم — فقط برای اینکه یک ستونِ دیگر هم در گزارش باشد — و همان SUM میشود ۱٬۹۴۶٬۱۰۳. هیچ خطایی، هیچ هشداری. عدد ۳٫۳ برابر شد.
و بدتر از عددِ غلط، این است: رتبهبندیِ شعبهها وارونه میشود. شعبهای که واقعاً چهارم است اول میشود و شعبهای که سوم است آخر. گزارش فقط عددِ اشتباه نمیدهد؛ برندهٔ اشتباه میدهد.

آخر این فصل میتوانی:
- بگویی یکبهیک و یکبهچند و چندبهچند یعنی چه، و کدامشان سطر میسازد
- تورمِ یک
SUMرا با دو عدد نشان بدهی - نشانهای را که بدونِ دانستنِ اسکیما هم کار میکند تشخیص بدهی
- با تجمیع قبل از پیوند، عددِ درست را برگردانی و ثابت کنی که درست است
قبل از شروع#
از فصلِ ۱ و ۲: INNER JOIN و LEFT JOIN و بندِ ON. از ترمِ ۲: GROUP BY و SUM و COUNT(DISTINCT ...).
دانهبندی: یک سطر از books یک کتاب است؛ یک سطر از loans یک بار امانت رفتن. یک کتاب میتواند دهها سطر در loans داشته باشد — و کلِ این فصل دربارهٔ نتیجهٔ همین جمله است.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۷ | آنی |
loans |
۳٬۹۹۸ | کمتر از یک ثانیه |
۱. دو کتاب، سه امانت#
قبل از هر عددِ بزرگی، چیزی که با انگشت شمردنی است:
con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, pages INTEGER)")
con.execute("CREATE TABLE tiny_loans (id INTEGER PRIMARY KEY, book_id INTEGER)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)", [(1, "الف", 100), (2, "ب", 200)])
con.executemany("INSERT INTO tiny_loans VALUES (?, ?)", [(1, 1), (2, 2), (3, 2)])
con.commit()
print("جمع صفحهها، روی یک جدول:",
int(q("SELECT SUM(pages) AS s FROM tiny_books").iloc[0, 0]))
print()
print(q("""
SELECT t.title, t.pages
FROM tiny_books AS t
JOIN tiny_loans AS n ON n.book_id = t.id
ORDER BY t.id, n.id
"""))
print()
print("جمع صفحهها، بعد از JOIN :",
int(q("""
SELECT SUM(t.pages) AS s
FROM tiny_books AS t
JOIN tiny_loans AS n ON n.book_id = t.id
""").iloc[0, 0]))
جمع صفحهها، روی یک جدول: 300
title pages
0 الف 100
1 ب 200
2 ب 200
جمع صفحهها، بعد از JOIN : 500
۱۰۰ + ۲۰۰ + ۲۰۰ = ۵۰۰. با انگشت شمردنی، و کاملاً غلط.
کتابِ «ب» دو بار امانت رفته، پس در loans دو سطر دارد، پس JOIN دو نسخه از آن ساخت — و SUM هر دو نسخه را جمع زد. صفحههای «ب» دو بار شمرده شدند، نه به این خاطر که دو کتاب است، بلکه به این خاطر که دو امانت است.
اسمِ رابطهٔ بینِ این دو جدول یکبهچند است: یک کتاب، چند امانت. سه حالت ممکن است و فقط یکیشان بیخطر است:
- یکبهیک — هر سطر دقیقاً یک جفت دارد. تعدادِ سطرها عوض نمیشود. (
booksبهbranchesدر فصلِ ۱، از سمتِ کتاب: هر کتاب یک شعبه دارد، هرچند یک شعبه چند کتاب دارد و این پیوند از سمتِ شعبه یکبهچند است) - یکبهچند — یک سطر چند جفت دارد. تعدادِ سطرها زیاد میشود. (
booksبهloans) - چندبهچند — هر دو طرف چند جفت دارند. تعدادِ سطرها ضرب میشود.
🌱 ریشهاش کجاست: این همان اصلِ ضربِ شمردن است: اگر یک انتخاب سه حالت دارد و انتخابِ بعدی چهار حالت، تعدادِ کلِ حالتها ۱۲ است، نه ۷.
JOINهم دقیقاً همین کار را میکند — هر سطرِ چپ ضربدر تعدادِ جفتهایش. اگر میخواهی این ضرب را از پایه ببینی، ریشه ترمِ ۶ فصل ۱ دقیقاً همین است.
۲. همان اتفاق، روی دادهٔ واقعی#
before_rows = int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0])
before_sum = int(q("SELECT SUM(pages) AS s FROM books").iloc[0, 0])
after = q("""
SELECT COUNT(*) AS rows_now, SUM(b.pages) AS pages_now
FROM books AS b
JOIN loans AS l ON l.book_id = b.id
""")
print("قبل از JOIN — سطر:", before_rows, "| جمع صفحهها:", before_sum)
print("بعد از JOIN — سطر:", int(after["rows_now"][0]),
"| جمع صفحهها:", int(after["pages_now"][0]))
print("نسبت جمعها :", round(int(after["pages_now"][0]) / before_sum, 2))
قبل از JOIN — سطر: 1200 | جمع صفحهها: 588049
بعد از JOIN — سطر: 3998 | جمع صفحهها: 1946103
نسبت جمعها : 3.31
عدد ۳٫۳۱ برابر شد، و هیچکس چیزی نگفت.
۳. نشانهای که بدونِ اسکیما هم کار میکند#
اگر فقط یک جمله از این فصل قرار است در ذهنت بماند، همین است:
تعدادِ سطرها عوض شد، در حالی که تو فقط یک ستون اضافه کرده بودی.
این نشانه هیچ چیزی از اسکیما نمیخواهد. لازم نیست بدانی loans چیست یا رابطهاش چند به چند است. قبل از JOIN یک COUNT(*) بگیر، بعدش یک COUNT(*) دیگر. اگر عوض شد، هر تجمیعی که در آن پرسش داری مشکوک است.
و برعکسش هم درست است: در فصلِ ۱ همین آزمون را زدیم و عدد ۱۲۰۰ ماند ۱۲۰۰. آن JOIN بیخطر بود و ثابتش کردیم، نه اینکه فرضش کنیم.
۴. بدتر از عددِ غلط: برندهٔ غلط#
حالا همان دو پرسش، این بار بهتفکیکِ شعبه — دقیقاً همان کاری که یک گزارشِ واقعی میکند:
print("درست — فقط از books:")
print(q("""
SELECT br.name, SUM(b.pages) AS pages
FROM books AS b
JOIN branches AS br ON br.id = b.branch_id
GROUP BY br.id, br.name
ORDER BY pages DESC
"""))
print()
print("غلط — همان، بهعلاوهٔ ستون امانتها:")
print(q("""
SELECT br.name, SUM(b.pages) AS pages, COUNT(*) AS loans
FROM books AS b
JOIN branches AS br ON br.id = b.branch_id
JOIN loans AS l ON l.book_id = b.id
GROUP BY br.id, br.name
ORDER BY pages DESC
"""))
درست — فقط از books:
name pages
0 نسیم 108608
1 کوشا 106918
2 پویا 97273
3 مرکزی 94868
4 دانش 93673
5 بهار 86709
غلط — همان، بهعلاوهٔ ستون امانتها:
name pages loans
0 مرکزی 526045 1113
1 نسیم 437875 837
2 دانش 331396 676
3 کوشا 240599 506
4 بهار 231523 488
5 پویا 178665 378
مرکزی از رتبهٔ چهارم به اول پرید و پویا از سوم به آخر افتاد.
دلیلش این است که تورم برای همهٔ شعبهها یکسان نیست: کتابهای مرکزی بیشتر امانت میروند، پس بیشتر تکثیر میشوند. ستونِ pages در جدولِ دوم عملاً دارد محبوبیت را اندازه میگیرد، نه صفحه را — و عنوانِ ستون همچنان pages است.
اگر این گزارش مبنای تصمیمِ «کدام شعبه به قفسهٔ بیشتری نیاز دارد» باشد، جواب کاملاً عوض میشود.
۵. DISTINCT مسکّن است، نه درمان#
اولین چیزی که به ذهنِ همه میرسد DISTINCT است. برای شمردن جواب میدهد؛ برای جمع زدن نه.
joined = q("""
SELECT COUNT(*) AS naive,
COUNT(DISTINCT b.id) AS distinct_books,
SUM(DISTINCT b.pages) AS distinct_pages,
COUNT(DISTINCT b.pages) AS page_values
FROM books AS b
JOIN loans AS l ON l.book_id = b.id
""")
never = q("""
SELECT COUNT(*) AS books, SUM(b.pages) AS pages
FROM books AS b
LEFT JOIN loans AS l ON l.book_id = b.id
WHERE l.id IS NULL
""")
all_pages = int(q("SELECT SUM(pages) AS s FROM books").iloc[0, 0])
print("COUNT(*) بعد از JOIN :", int(joined["naive"][0]))
print("COUNT(DISTINCT b.id) :", int(joined["distinct_books"][0]))
print("کتابهای بیامانت (فصل ۲) :", int(never["books"][0]))
print()
print("SUM(DISTINCT b.pages) :", int(joined["distinct_pages"][0]))
print("جمع واقعی همان کتابها :", all_pages - int(never["pages"][0]))
print("چند کتاب، چند مقدار متمایز:", int(joined["distinct_books"][0]), "کتاب،",
int(joined["page_values"][0]), "مقدار")
COUNT(*) بعد از JOIN : 3998
COUNT(DISTINCT b.id) : 1000
کتابهای بیامانت (فصل ۲) : 200
SUM(DISTINCT b.pages) : 290121
جمع واقعی همان کتابها : 485911
چند کتاب، چند مقدار متمایز: 1000 کتاب، 598 مقدار
COUNT(DISTINCT b.id) عددِ درست را برگرداند: ۱٬۰۰۰ کتابِ متمایز. و شمارشِ دومش هم آماده است — ۱٬۰۰۰ بهعلاوهٔ ۲۰۰ کتابِ بیامانتِ فصلِ ۲ میشود ۱٬۲۰۰. درست است چون id یکتاست، پس «متمایز» دقیقاً همان «کتاب» است.
ولی SUM(DISTINCT b.pages) فاجعه است: ۲۹۰٬۱۲۱ در برابرِ ۴۸۵٬۹۱۱. حالا کمتر از واقعیت است، نه بیشتر. دلیلِ این فاصله یکی است: ۱٬۰۰۰ کتاب فقط ۵۹۸ مقدارِ متمایزِ صفحه دارند، یعنی ۴۰۲ کتاب کاملاً حذف شدند چون کتابِ دیگری هماندازهشان بود. فاصله تا جمعِ کلِ کتابخانه از این هم بیشتر است: ۲۰۰ کتابِ بیامانت از اول با INNER JOIN رفته بودند و جمعِ ۴۸۵٬۹۱۱ هم آنها را ندارد؛ با آنها میشود ۵۸۸٬۰۴۹.
DISTINCT مقدارهای تکراری را حذف میکند، و مسئلهٔ ما سطرهای تکراری بود، نه مقدارهای تکراری. برای COUNT روی یک کلیدِ یکتا این دو یکی میشوند؛ برای SUM روی هر ستونِ دیگری نه.
۶. درمان: تجمیع قبل از پیوند#
راهِ درست این است که جدولِ یکبهچند را قبل از پیوند به یک سطر برای هر کتاب تبدیل کنیم. آنوقت رابطه یکبهیک میشود و دیگر چیزی تکثیر نمیشود.
یک چیزِ تازه در پرسشِ زیر هست: بهجای نامِ جدول، یک SELECT داخلِ پرانتز آمده. آن SELECT مثلِ یک جدول رفتار میکند که همانجا ساخته میشود. فعلاً همینقدر بدان که هر پرسشی میتواند جای یک جدول بنشیند؛ فصلِ ۶ کاملاً بازش میکند و نامش را میگوید.
print(q("""
SELECT br.name,
SUM(b.pages) AS pages,
SUM(COALESCE(x.n, 0)) AS loans
FROM books AS b
JOIN branches AS br ON br.id = b.branch_id
LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
ON x.book_id = b.id
GROUP BY br.id, br.name
ORDER BY pages DESC
"""))
print()
print("جمع ستون pages:",
int(q("""
SELECT SUM(b.pages) AS s
FROM books AS b
LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
ON x.book_id = b.id
""").iloc[0, 0]), "| باید برابر 588049 باشد")
print("جمع ستون loans:",
int(q("""
SELECT SUM(COALESCE(x.n, 0)) AS s
FROM books AS b
LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
ON x.book_id = b.id
""").iloc[0, 0]), "| باید برابر 3998 باشد")
name pages loans
0 نسیم 108608 837
1 کوشا 106918 506
2 پویا 97273 378
3 مرکزی 94868 1113
4 دانش 93673 676
5 بهار 86709 488
جمع ستون pages: 588049 | باید برابر 588049 باشد
جمع ستون loans: 3998 | باید برابر 3998 باشد
هر دو ستون درستاند و رتبهبندی به همان چیزی برگشت که واقعاً هست.
و شمارشِ دوم را همانجا چاپ کردیم: جمعِ ستونِ pages دقیقاً ۵۸۸٬۰۴۹ شد — یعنی هیچ کتابی دو بار شمرده نشده — و جمعِ ستونِ loans دقیقاً ۳٬۹۹۸ — یعنی هیچ امانتی گم نشده. این دو خط تفاوتِ یک گزارش با یک ادعا هستند.
LEFT JOIN و COALESCE هم تصادفی نیستند: بدونِ آنها ۲۰۰ کتابِ بیامانت دوباره میافتادند بیرون و جمعِ صفحهها کمتر از ۵۸۸٬۰۴۹ میشد. درمانِ fan-out نباید خودش سطر بیندازد.
✅ چک کن: دو عددِ خطِ آخر باید دقیقاً با عددهای جدولِ بالا بخوانند: جمعِ ستونِ
pagesبرابرِ جمعِ همان ستون در شش سطرِ جدول، و همینطورloans. اگر جمعِpagesکمتر درآمد،LEFT JOINراJOINنوشتهای.
۷. دانهبندی، چیزی است که اعلام میشود#
هر سه جدولِ این فصل ستونی به نامِ pages داشتند و سه عددِ متفاوت دادند. تفاوتشان در محاسبه نبود، در این بود که یک سطر یعنی چه:
- جدولِ اول: یک سطر = یک شعبه، و
pagesجمعِ صفحاتِ کتابهای آن شعبه. - جدولِ دوم: یک سطر = یک شعبه، ولی
pagesجمع روی امانتهای آن شعبه است — عددی که هیچ اسمِ درستی ندارد. - جدولِ سوم: مثلِ اولی، بهعلاوهٔ یک ستونِ سالمِ امانت.
پس قبل از هر JOIN بپرس: بعد از این پیوند، یک سطر یعنی چه؟ اگر جوابت عوض شد، هر تجمیعی که از قبل داشتی باید بازبینی شود.
🔧 اگر کار نکرد: اگر داخلِ پرانتزِ بخشِ ۶
GROUP BYرا جا بیندازی، خطا نمیگیری —SQLiteسکوت میکند و کلِ جدول را یک سطر میکند. ولی اگر نامِ مستعارِ آن جدولِ موقت را جا بیندازی، صریح اعتراض میکند:
try:
q("""
SELECT b.id, x.n
FROM books AS b
LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id)
ON x.book_id = b.id
LIMIT 1
""")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql '
SELECT b.id, x.n
FROM books AS b
LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id)
ON x.book_id = b.id
LIMIT 1
': no such column: x.n
ولی خطای واقعیِ این فصل همان ۱٬۹۴۶٬۱۰۳ بود که هیچ پیامی نداشت — و بدترش، آن جدولی که مرکزی را اول نشان داد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ نهایی یک سطر یک شعبه است، و هر عددش روی کتابها حساب شده نه روی امانتها. با کدام شمارشِ دوم سنجیدی؟ با سهتا:
COUNT(*)قبل و بعد از پیوند (۱٬۲۰۰ و ۳٬۹۹۸)، جمعِ ستونِpagesکه به ۵۸۸٬۰۴۹ برگشت، و جمعِ ستونِloansکه ۳٬۹۹۸ شد. چه کسی از قلم افتاد؟ در نسخهٔ غلط، ۲۰۰ کتابِ بیامانت — و شعبهٔ آفتاب که در هر سه جدول غایب است، چون هیچ کتابی ندارد.
🤖 از دستیارت بپرس: «چرا یک
JOINیکبهچند باعث میشودSUMبزرگتر از واقعیت شود؟» بعد این را بپرس: «چراSUM(DISTINCT col)این را درست نمیکند، در حالی کهCOUNT(DISTINCT id)میکند؟» — جوابِ درست میگویدDISTINCTروی مقدار کار میکند نه روی سطر، و برایidاین دو یکی میشوند چونidیکتاست. اگر جواب گفت «SUM(DISTINCT ...)هم درست است»، همین حالا با عددهای بخشِ ۵ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| cardinality | کاردینالیتی | کاردینالیتی؛ اینکه هر سطر چند جفت در طرفِ مقابل دارد |
| one-to-many | وان تو منی | یکبهچند؛ یک سطر در این طرف، چند سطر در آن طرف |
| fan-out | فناوت | تکثیرِ سطرها در اثرِ یک پیوندِ یکبهچند |
| pre-aggregation | پریاگریگیشن | تجمیع قبل از پیوند، تا رابطه یکبهیک شود |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
تا اینجا همیشه دو جدولِ متفاوت را به هم وصل کردیم. فصلِ بعد یک جدول را به خودش وصل میکند — و با آن، ۲۸ جفت کتاب پیدا میکند که عنوان و نویسندهشان یکی است. بعد سراغِ CROSS JOIN میرود که عمداً همان کاری را میکند که این فصل تصادفیاش را ترسناک نامید، و نشان میدهد ۱٬۲۰۰ ضربدر ۳٬۹۹۸ یعنی چه.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.