در این فصل چه یاد میگیری#
یک پرسشِ دهخطی تحویلت دادهاند و گفتهاند «این گزارشِ ماهانه است، فقط تاریخش را عوض کن». پرسش اجرا میشود، خطا نمیدهد، و جدولِ مرتبی میسازد که ستونِ اولش branch است و ستونِ دومش active_members. برای شعبهٔ مرکزی عددِ active_members میشود ۱٬۲۰۲.
شعبهٔ مرکزی روی کاغذ ۲۹۹ عضو دارد.
هیچچیزی خراب نیست. پرسش دقیقاً همان کاری را میکند که نوشته شده؛ فقط اسمِ ستونش با کاری که میکند نمیخواند. این فصل دربارهٔ عادتی است که جلوی این را میگیرد: قبل از باور کردنِ عددِ یک پرسش، اول بازسازی کن که یک سطرِ خروجیاش یعنی چه.
آخر این فصل میتوانی:
- یک پرسشِ ناآشنا را از
FROMبه بالا بخوانی و دانهبندیِ خروجیاش را بگویی - همان پرسش را با
CTEتکهتکه کنی و هر مرحله را با یک شمارش بسنجی - ستونی را که با نامش نمیخواند با عدد لو بدهی
- فیلترِ پنهانِ داخلِ
ONرا پیدا کنی و بگویی چند سطر را انداخته بیرون
قبل از شروع#
از ترمِ ۳: INNER JOIN، شرط در ON در برابرِ شرط در WHERE، fan-out، و CTE با WITH. از ترمِ ۲: GROUP BY، COUNT(DISTINCT ...) و ترتیبِ منطقیِ اجرا. از ترمِ ۱: NULL و پارامترِ ?.
این ترم از جای دیگری شروع میکند. تا اینجا پرسشها را خودت مینوشتی و میدانستی هر بند چرا آنجاست. در کارِ واقعی بیشترِ پرسشهایی که به دستت میرسند مالِ کسِ دیگریاند — کسی که رفته، و توضیحی هم ننوشته.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
members |
۱٬۴۰۰ | آنی |
titles |
۱٬۲۰۰ | آنی |
loans |
۱۶٬۸۳۴ | کمتر از دو ثانیه |
visits |
۲۵٬۰۱۴ | کمتر از دو ثانیه |
همان دامنهٔ ترمهای قبل، ولی یک برشِ تازه از داده با جدولهای تازه — titles بهجای books، شش شعبه، و چهار ستون که کلِ این ترم رویشان بنا شده: members.left_at برای کسی که عضویتش تمام شده، members.home_branch_id که برای ثبتنامِ اینترنتی NULL است، loans.returned_at که برای کتابِ هنوز برنگشته NULL است، و loans.due_at یعنی تاریخی که کتاب باید برگردد — سررسید، که موقعِ امانت گرفتن ثبت میشود و بعداً عوض نمیشود. عددهای این ترم با ترمهای قبل یکی نیستند و هیچ ادعایی در این ترم به آنها گره نمیخورد. داده ساختگی است و با یک SEED ثابت ساخته میشود، پس روی هر ماشینی همان عددها را میگیری.
۱. پرسشی که تحویلت دادهاند#
این متنِ دقیقِ چیزی است که به دستت رسیده. فعلاً فقط بخوانش؛ اجرا در بلوکِ بعدی است.
SELECT b.name AS branch,
COUNT(*) AS active_members,
ROUND(AVG(julianday(l.returned_at) - julianday(l.borrowed_at)), 1) AS avg_days
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
JOIN loans AS l ON l.member_id = m.id
WHERE l.borrowed_at >= ?
GROUP BY b.id, b.name
ORDER BY active_members DESC
حالا همان را اجرا میکنیم. متنِ پرسش را یک بار داخلِ یک متغیر میگذاریم تا در بقیهٔ فصل هم دستمان باشد، و تاریخ — که یک مقدار است — با پارامترِ ? میرود.
INHERITED = """
SELECT b.name AS branch,
COUNT(*) AS active_members,
ROUND(AVG(julianday(l.returned_at) - julianday(l.borrowed_at)), 1) AS avg_days
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
JOIN loans AS l ON l.member_id = m.id
WHERE l.borrowed_at >= ?
GROUP BY b.id, b.name
ORDER BY active_members DESC
"""
print(q(INHERITED, ("2025-01-01",)))
branch active_members avg_days
0 مرکزی 1202 22.2
1 نسیم 1071 19.0
2 دانش 801 21.1
3 کوشا 724 19.3
4 پویا 704 20.8
5 بهار 561 20.8
جدولِ تمیزی است. شش سطر، سه ستون، مرتبشده. هیچ هشداری هم نداد.
ارزانترین آزمونی که میشود روی چنین جدولی زد این است: عددش را با عددی بسنج که از جای دیگری میدانی. تعدادِ اعضای هر شعبه چنین عددی است.
print(q("""
SELECT b.name, COUNT(*) AS members_on_file
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id
GROUP BY b.id, b.name
ORDER BY members_on_file DESC
"""))
name members_on_file
0 مرکزی 299
1 نسیم 277
2 دانش 220
3 کوشا 187
4 پویا 160
5 بهار 156
۱٬۲۰۲ در برابرِ ۲۹۹ — چهار برابرِ کلِ اعضای آن شعبه، و همهٔ آن ۱٬۲۰۲ نفر قرار است «فعال» باشند.
عددِ ستونِ active_members نمیتواند از تعدادِ کلِ اعضا بیشتر باشد، مگر اینکه آن ستون اصلاً اعضا را نشمارد. پس چیزی جز شمردنِ اعضا میکند — و کارِ باقیماندهٔ این فصل این است که با عدد بگوییم چه.
۲. از FROM به بالا بخوان#
عادتی که همه دارند این است که پرسش را از خطِ اول میخوانند. در SQL خطِ اول آخرین چیزی است که اتفاق میافتد. ترتیبِ منطقیِ اجرایی که در ترمِ ۲ دیدی همین را میگوید: FROM و JOIN اول، بعد WHERE، بعد GROUP BY، و SELECT تقریباً آخر.
پس همان ترتیب را برای خواندن هم به کار ببر و در هر مرحله یک جملهٔ فارسی بنویس که میگوید الان یک سطر یعنی چه:
FROM branches— یک سطر یعنی یک شعبه.JOIN members— یک سطر یعنی یک عضو (که شعبهٔ خانگیاش پر است و عضویتش تمام نشده).JOIN loans— یک سطر یعنی یک امانت.WHERE l.borrowed_at >= ?— همان، محدود به امانتهای بعد از یک تاریخ.GROUP BY b.id— یک سطر یعنی یک شعبه، وCOUNT(*)تعدادِ سطرهای مرحلهٔ ۴ را میشمارد.
و مرحلهٔ ۴ سطرش امانت است، نه عضو. کلِ ماجرا همین یک جمله است. نامِ active_members را کسی روی ستون گذاشته که هنگامِ نوشتنِ SELECT دیگر یادش نبوده در FROM چه ساخته.
💡 نکته: این دقیقاً همان fan-outِ ترمِ ۳ است، ولی اینجا هیچ
SUMی در کار نیست که عددش متورم شود — خودِCOUNT(*)قربانی است. هر جا بعد از یکJOINیکبهچندCOUNT(*)میبینی، بپرس «شمارشِ چه چیزی؟».
۳. تکهتکه اجرا کن، و هر مرحله را بشمار#
حدس زدن کافی نیست. همان پنج مرحله را واقعاً اجرا میکنیم و تعدادِ سطرش را چاپ میکنیم:
print("1. branches :",
int(q("SELECT COUNT(*) AS n FROM branches").iloc[0, 0]))
print("2. + members، شرط اول ON :",
int(q("""
SELECT COUNT(*) AS n FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id
""").iloc[0, 0]))
print("3. + شرط دوم ON: left_at IS NULL :",
int(q("""
SELECT COUNT(*) AS n FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
""").iloc[0, 0]))
print("4. + loans و WHERE :",
int(q("""
SELECT COUNT(*) AS n FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
JOIN loans AS l ON l.member_id = m.id
WHERE l.borrowed_at >= ?
""", ("2025-01-01",)).iloc[0, 0]))
1. branches : 6
2. + members، شرط اول ON : 1299
3. + شرط دوم ON: left_at IS NULL : 930
4. + loans و WHERE : 5063
چهار عدد، و هر پرشش یک داستان دارد: ۶ به ۱٬۲۹۹ یعنی ۱٬۲۹۹ عضو شعبهٔ خانگی دارند؛ ۱٬۲۹۹ به ۹۳۰ یعنی ۳۶۹ عضو همینجا افتادند بیرون و بخشِ ۵ سراغِ همانها میرود؛ و ۹۳۰ به ۵٬۰۶۳ یعنی هر عضو بهطور متوسط بیش از پنج سطر ساخته.
۵٬۰۶۳ همان جمعِ ستونِ active_members است. بیایید اثباتش کنیم — و این بار بهجای چهار پرسشِ جدا، همان زنجیره را با CTE میسازیم تا هر مرحله اسم داشته باشد:
staged = q("""
WITH kept AS (
SELECT b.name AS branch, m.id AS member_id
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
),
joined AS (
SELECT k.branch, k.member_id, l.id AS loan_id
FROM kept AS k
JOIN loans AS l ON l.member_id = k.member_id
WHERE l.borrowed_at >= ?
)
SELECT branch,
COUNT(*) AS rows_out,
COUNT(DISTINCT member_id) AS members,
COUNT(DISTINCT loan_id) AS loans
FROM joined
GROUP BY branch
ORDER BY rows_out DESC
""", ("2025-01-01",))
print(staged)
print()
print("جمع ستون rows_out:", int(staged["rows_out"].sum()))
print("جمع ستون loans :", int(staged["loans"].sum()))
branch rows_out members loans
0 مرکزی 1202 208 1202
1 نسیم 1071 183 1071
2 دانش 801 145 801
3 کوشا 724 127 724
4 پویا 704 112 704
5 بهار 561 94 561
جمع ستون rows_out: 5063
جمع ستون loans : 5063
ستونِ rows_out مو به مو همان active_members است، و ستونِ loans هم دقیقاً همان. یعنی آن ستون از اول تعدادِ امانتها بود.
عددِ درست در ستونِ سوم نشسته: ۲۰۸ عضو در شعبهٔ مرکزی، نه ۱٬۲۰۲.
🔧 اگر کار نکرد: طبیعیترین اشتباهِ تکهتکه کردنِ یک پرسش این است که یک مرحله ستونی را جلو نمیبَرَد که مرحلهٔ بعدی لازمش دارد.
SQLiteصریح اعتراض میکند:
try:
q("""
WITH kept AS (
SELECT b.name AS branch, m.id AS member_id
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
)
SELECT branch, AVG(julianday(returned_at) - julianday(borrowed_at)) AS avg_days
FROM kept
GROUP BY branch
""")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql '
WITH kept AS (
SELECT b.name AS branch, m.id AS member_id
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
)
SELECT branch, AVG(julianday(returned_at) - julianday(borrowed_at)) AS avg_days
FROM kept
GROUP BY branch
': no such column: borrowed_at
یک CTE هر چیزی را که در SELECTش نیاورده باشی، دور میریزد. رفعش این است که ستون را در همان مرحله به فهرست اضافه کنی. و این خطا خبرِ خوبی است: خطای واقعیِ این فصل آن یکی بود که هیچ پیامی نداشت.
۴. ستونی که با نامش نمیخواند#
میدانیم عدد تعدادِ امانت است. حالا با انگشت نشان بدهیم که تورم از کجا میآید — کافی است ببینیم یک عضو چند سطر میسازد:
one = q("""
SELECT m.id AS member_id, COUNT(*) AS rows_from_this_member
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id AND m.left_at IS NULL
JOIN loans AS l ON l.member_id = m.id
WHERE l.borrowed_at >= ? AND b.name = ?
GROUP BY m.id
ORDER BY rows_from_this_member DESC, m.id
LIMIT 3
""", ("2025-01-01", "مرکزی"))
print(one)
print()
print("سه عضو، و این تعداد سطر:", int(one["rows_from_this_member"].sum()))
member_id rows_from_this_member
0 1285 20
1 24 18
2 603 18
سه عضو، و این تعداد سطر: 56
سه نفر، پنجاهوشش سطر. عضوِ شمارهٔ ۱۲۸۵ بهتنهایی بیست بار در آن ستون شمرده شده.
این همان وارسیِ دستیِ یک سطر است که در کلِ این ترم تکرار میشود: یک سطرِ خروجی را بردار و ببین از کدام سطرهای ورودی ساخته شده. اگر نتوانستی این کار را بکنی، هنوز نمیدانی آن عدد چیست.
✅ چک کن: ستونِ
rows_outو ستونِloansدر جدولِ بخشِ ۳ باید سطر به سطر برابر باشند، و جمعشان ۵٬۰۶۳. اگرloansکمتر درآمد، یعنی یک امانت بیش از یک بار شمرده شده — که یعنیJOINدیگری هم در کار است.
۵. فیلترِ پنهان در ON#
حالا آن ۳۶۹ عضوی که در مرحلهٔ ۳ ناپدید شدند. عبارتِ AND m.left_at IS NULL داخلِ ON است، نه در WHERE — و برای یک INNER JOIN این دو دقیقاً یک کار میکنند. تفاوتشان فقط در دیده شدن است: چشمِ آدم دنبالِ فیلتر در WHERE میگردد، و اینجا فیلتر جای دیگری قایم شده.
بشماریم چه کسانی را پرسش اصلاً ندید:
print("کل امانتهای این بازه :",
int(q("SELECT COUNT(*) AS n FROM loans WHERE borrowed_at >= ?",
("2025-01-01",)).iloc[0, 0]))
print("امانتهایی که پرسش اصلا دید :", int(staged["rows_out"].sum()))
print("امانتهای اعضای بدون شعبهٔ خانگی :",
int(q("""
SELECT COUNT(*) AS n FROM loans AS l
JOIN members AS m ON m.id = l.member_id
WHERE l.borrowed_at >= ? AND m.home_branch_id IS NULL
""", ("2025-01-01",)).iloc[0, 0]))
print("امانتهای اعضایی که عضویتشان تمام شده:",
int(q("""
SELECT COUNT(*) AS n FROM loans AS l
JOIN members AS m ON m.id = l.member_id
WHERE l.borrowed_at >= ?
AND m.home_branch_id IS NOT NULL AND m.left_at IS NOT NULL
""", ("2025-01-01",)).iloc[0, 0]))
کل امانتهای این بازه : 5597
امانتهایی که پرسش اصلا دید : 5063
امانتهای اعضای بدون شعبهٔ خانگی : 424
امانتهای اعضایی که عضویتشان تمام شده: 110
۵٬۰۶۳ بهعلاوهٔ ۴۲۴ بهعلاوهٔ ۱۱۰ میشود ۵٬۵۹۷ — دقیقاً کلِ امانتهای آن بازه. جمعِ اجزا با کل خواند، پس هیچ سطرِ چهارمی جا نمانده و دو دلیلِ کنار گذاشته شدن همین دوتاست.
و هر دو دلیل، دو تصمیماند که کسِ دیگری گرفته و ننوشته:
- کسی که با ثبتنامِ اینترنتی عضو شده شعبهٔ خانگی ندارد، پس
INNER JOINبهbranchesبیصدا حذفش میکند. ۴۲۴ امانت. - کسی که عضویتش تمام شده هم حذف شده، حتی برای امانتهایی که وقتی عضو بود گرفت. ۱۱۰ امانت.
هیچکدام لزوماً غلط نیستند. شاید گزارش عمداً فقط دربارهٔ اعضای فعلی است. ولی این باید در عنوانِ گزارش نوشته شود، نه در خطِ ششمِ یک پرسش.
۶. چه چیزی باید تحویل بدهی#
جدولِ درست، با نامِ درستِ ستونها و بدونِ فیلترِ قایمشده:
honest = q("""
SELECT b.name AS branch,
COUNT(DISTINCT m.id) AS members_who_borrowed,
COUNT(*) AS loans,
SUM(l.returned_at IS NULL) AS still_out
FROM branches AS b
JOIN members AS m ON m.home_branch_id = b.id
JOIN loans AS l ON l.member_id = m.id
WHERE l.borrowed_at >= ?
GROUP BY b.id, b.name
ORDER BY members_who_borrowed DESC
""", ("2025-01-01",))
print(honest)
print()
print("جمع ستون loans:", int(honest["loans"].sum()))
print("امانتهای اعضای شعبهدار در همین بازه:",
int(q("""
SELECT COUNT(*) AS n FROM loans AS l
JOIN members AS m ON m.id = l.member_id
WHERE l.borrowed_at >= ? AND m.home_branch_id IS NOT NULL
""", ("2025-01-01",)).iloc[0, 0]))
branch members_who_borrowed loans still_out
0 مرکزی 215 1234 216
1 نسیم 184 1075 180
2 دانش 151 830 149
3 کوشا 133 742 120
4 پویا 117 717 131
5 بهار 98 575 91
جمع ستون loans: 5173
امانتهای اعضای شعبهدار در همین بازه: 5173
۲۱۵ عضو در مرکزی، نه ۱٬۲۰۲ — و جمعِ ستونِ امانتها با شمارشِ مستقل خواند.
ستونِ still_out هم عمداً اضافه شده: در مرکزی ۲۱۶ امانت از ۱٬۲۳۴ هنوز برنگشتهاند، و ستونِ avg_daysِ پرسشِ اولیه هیچکدامشان را حساب نکرده بود، چون AVG سطرِ NULL را نادیده میگیرد. آن عدد میانگینِ مدتِ امانت نیست؛ میانگینِ مدتِ امانتهایی است که برگشتهاند — و فصلِ ۳ نشان میدهد این تفاوت چقدر است.
📏 اندازه بگیر: یک سطر یعنی چه؟ در پرسشِ تحویلی، یک سطرِ مرحلهٔ آخر یک شعبه است ولی
COUNT(*)ش امانتها را میشمارد؛ در جدولِ آخر یک سطر یک شعبه است و هر ستونش صریح میگوید چه چیزی را شمرده. با کدام شمارشِ دوم سنجیدی؟ با سهتا: تعدادِ اعضای ثبتشدهٔ هر شعبه (۲۹۹ در برابرِ ۱٬۲۰۲)، جمعِ اجزا در برابرِ کل (۵٬۰۶۳ + ۴۲۴ + ۱۱۰ = ۵٬۵۹۷)، و وارسیِ دستیِ یک عضو که بیست سطر ساخته بود. چه کسی از قلم افتاد؟ اعضایی که شعبهٔ خانگی ندارند و ۴۲۴ امانتشان و ۳۶۹ عضوی که عضویتشان تمام شده — و بهعلاوه هر امانتی که هنوز برنگشته، از ستونِavg_days.
🤖 از دستیارت بپرس: «چطور یک پرسشِ
SQLرا که خودم ننوشتهام قدمبهقدم بخوانم؟» بعد این را بپرس: «شرط گذاشتن درONبا شرط گذاشتن درWHEREچه فرقی دارد — برایINNER JOINو برایLEFT JOIN؟» — جوابِ درست میگوید برایINNER JOINنتیجه یکی است و برایLEFT JOINنه. اگر جواب گفت «هیچ فرقی ندارد»، ناقص است.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| query review | کوئری ریویو | بازخوانیِ پرسشِ کسِ دیگری، قبل از باور کردنِ عددش |
| staged execution | استیجد اگزکیوشن | اجرای تکهتکه؛ هر مرحله جدا اجرا و شمرده میشود |
| control count | کنترل کانت | شمارشِ کنترلی؛ عددِ مستقلی که عددِ اصلی را میسنجد |
| hidden filter | هیدن فیلتر | شرطی که بهجای WHERE داخلِ ON نشسته و دیده نمیشود |
| mislabelled column | میسلیبلد کالمن | ستونی که نامش چیزی میگوید و محتوایش چیزِ دیگری |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
این فصل ستونی را گرفت که نامش با محتوایش نمیخواند. فصلِ بعد یک قدم عقبتر میرود: قبل از اینکه چیزی بشماری، باید تعریف کنی چه چیزی را میشماری.
و با دو عدد شروع میکند که هر دو جوابِ «چند عضوِ فعال داریم؟» هستند، هر دو با پنجرهٔ زمانیِ یکسان حساب شدهاند، و هیچکدام هم غلط نیست: ۸۵۱ و ۹۵۵.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.