در این فصل چه یاد میگیری#
دو پرسش مینویسیم که تنها تفاوتشان یک کلمه است: در یکی WHERE نوشته شده و در دیگری HAVING. اولی میگوید ۴۷۱ کتاب، دومی میگوید ۶۰۱ کتاب. اولی هشت دسته برمیگرداند، دومی چهارتا. هیچکدام خطا نمیدهند.
و بدترین قسمتش این است که پرسشِ دوم فقط «عددِ دیگری» نمیدهد؛ جوابش به سطری بستگی دارد که تو انتخابش نکردهای. یعنی عددی که در گزارش میگذاری، از یک سطرِ دلبخواهی درآمده.
راهِحل حفظ کردنِ یک قاعده نیست. راهِحل این است که بدانی یک پرسش واقعاً به چه ترتیبی اجرا میشود — و همان ترتیب، تمامِ این فصل است.

آخر این فصل میتوانی:
- با
GROUP BYاز ۱٬۲۰۰ سطر یک جدولِ چندسطری بسازی و دانهبندیِ تازهاش را اعلام کنی - بگویی یک ستونِ گروهنشده در
SELECTمقدارش از کجا میآید - ترتیبِ منطقیِ اجرای یک پرسش را از بر بگویی و با آن رفتارهای عجیب را پیشبینی کنی
- بدانی
WHEREوHAVINGدو کارِ متفاوتاند و هرکدام کجا لازم است - ثابت کنی که جدولِ گروهیات هیچ سطری را جا نینداخته
قبل از شروع#
از فصلِ ۱: MIN/MAX، COUNT(DISTINCT ...)، تجمیع روی عبارت، و اینکه یک تجمیعِ بدونِ گروهبندی همیشه یک سطر میدهد.
یک سطر از books یعنی یک عنوانِ ثبتشده در یکی از شش شعبه. این جمله را نگه دار، چون از همین فصل به بعد دانهبندیِ خروجی مدام عوض میشود در حالی که دانهبندیِ ورودی ثابت میماند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. GROUP BY: از یک عدد به یک جدول#
در ترمِ ۱ فصلِ آخر، برای گرفتنِ سهمِ هشت دسته، هشت پرسشِ جدا زدیم و در پایتون کنارِ هم چیدیم. GROUP BY همان کار را در یک پرسش میکند: سطرها را بر اساسِ مقدارِ یک ستون دسته میکند و هر تابعِ تجمیعی را جدا برای هر دسته حساب میکند.
print(q("""
SELECT branch,
COUNT(*) AS books,
SUM(borrowed) AS loans
FROM books
GROUP BY branch
ORDER BY books DESC
"""))
branch books loans
0 نسیم 224 18364
1 بهار 214 17498
2 کوشا 200 15542
3 پویا 195 15122
4 دانش 187 13887
5 مرکزی 180 13778
۱٬۲۰۰ سطر رفت تو، ۶ سطر آمد بیرون. پرشمارترین شعبه ۲۲۴ کتاب دارد و کمشمارترین ۱۸۰.
و مهمترین جملهٔ این فصل همینجاست: دانهبندی عوض شد. یک سطر از این خروجی دیگر «یک کتاب» نیست، «یک شعبه» است. عددِ ۲۲۴ دربارهٔ کتابها حرف میزند ولی خودش یک ویژگیِ شعبه است. هر بار که GROUP BY مینویسی همین اتفاق میافتد، و اگر در گزارشت ننویسی یک سطر یعنی چه، خوانندهات حق دارد هر چیزی فکر کند.
✅ چک کن: جمعِ ستونِ
booksباید دقیقاً ۱٬۲۰۰ شود و جمعِ ستونِloansدقیقاً ۹۴٬۱۹۱ — همان دو عددی که ترمِ ۱ روی کلِ جدول گرفت. این ارزانترین شمارشِ کنترلیِ هر جدولِ گروهی است و همیشه باید بگیریاش:
grouped = q("SELECT branch, COUNT(*) AS books, SUM(borrowed) AS loans FROM books GROUP BY branch")
whole = q("SELECT COUNT(*) AS books, SUM(borrowed) AS loans FROM books")
print("جمع سطرهای جدول گروهی:", int(grouped["books"].sum()), "|", int(grouped["loans"].sum()))
print("همان دو عدد روی کل جدول:", int(whole["books"][0]), "|", int(whole["loans"][0]))
print("گروهها:", len(grouped), "| شعبههای جدول branches:",
int(q("SELECT COUNT(*) FROM branches").iloc[0, 0]))
جمع سطرهای جدول گروهی: 1200 | 94191
همان دو عدد روی کل جدول: 1200 | 94191
گروهها: 6 | شعبههای جدول branches: 6
هر دو خط خواند، و خطِ سوم یک کنترلِ دوم است: تعدادِ گروهها باید با تعدادِ شعبههای واقعی یکی باشد. اگر گروهها کمتر بودند، یعنی شعبهای هیچ کتابی ندارد و در این جدول اصلاً پیدا نمیشود — گروهی که هیچ سطری ندارد، هیچ سطری هم تولید نمیکند. فصلِ ۵ نشان میدهد این سکوت کجا واقعاً خطرناک میشود.
۲. ستونی که نه گروه است و نه تجمیع#
قاعدهٔ استانداردِ SQL ساده است: هر ستونی که در SELECT میآید یا باید در GROUP BY باشد یا داخلِ یک تابعِ تجمیعی. SQLite این قاعده را اجرا نمیکند. پرسشِ زیر در بیشترِ پایگاه دادههای دیگر خطا میدهد و اینجا با خوشرویی جواب میدهد:
print(q("""
SELECT category, id, title, borrowed, COUNT(*) AS n
FROM books
GROUP BY category
ORDER BY category
LIMIT 3
"""))
category id title borrowed n
0 تاریخ 4 مه پشت آتش 86 145
1 رمان 1 روزهای پل 33 146
2 زندگینامه 6 در جستوجوی شب 169 136
سه ستونِ وسط از یک سطرِ دلبخواهیِ هر گروه آمدهاند. SQLite مستند و صریح میگوید که در این حالت مقدارش تضمینشده نیست: میتواند بینِ دو اجرا، بینِ دو نسخه، یا با اضافه شدنِ یک index عوض شود.
خطرش در ظاهرِ خروجی است. جدولِ بالا خیلی قانعکننده به نظر میرسد — انگار دارد میگوید «پرامانتترین کتابِ هر دسته». هیچچیز چنین چیزی نگفته.
و یک استثنای مستند هست که باید بشناسی، چون هم مفید است و هم گمراهکننده:
print(q("""
SELECT category, id, borrowed, MIN(borrowed) AS lowest, COUNT(*) AS n
FROM books
GROUP BY category
ORDER BY category
LIMIT 3
"""))
category id borrowed lowest n
0 تاریخ 169 0 0 145
1 رمان 51 0 0 146
2 زندگینامه 259 0 0 136
حالا borrowed با lowest میخواند. وقتی در پرسش دقیقاً یک MIN یا یک MAX باشد، SQLite تضمین میکند ستونهای گروهنشده از همان سطری بیایند که کمینه یا بیشینه در آن پیدا شده. این یک راهِ کوتاه و واقعاً مفید برای «رکورددارِ هر گروه» است.
ولی درست به همین دلیل خطرناک است: الگو را یک بار یاد میگیری، بعد یک AVG هم به همان پرسش اضافه میکنی، و بیصدا از حالتِ تضمینشده به حالتِ دلبخواهی میافتی. فصلِ ۷ همین کار را با تابعِ پنجره انجام میدهد، جایی که «رکورددارِ هر گروه» یک ابزارِ صریح دارد و به هیچ استثنایی تکیه نمیکند.
۳. ترتیبی که واقعاً اجرا میشود#
یک پرسش را از بالا به پایین مینویسی، ولی به این ترتیب اجرا نمیشود. ترتیبِ منطقیِ اجرا این است، و بقیهٔ این فصل و نصفِ این ترم مستقیماً از آن بیرون میآید:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
بخوانش بهعنوان یک خطِ تولید: FROM سطرها را میآورد؛ WHERE سطرها را غربال میکند — قبل از اینکه هیچ گروهی وجود داشته باشد؛ GROUP BY سطرهای بازمانده را دسته میکند؛ HAVING گروهها را غربال میکند؛ SELECT ستونهای خروجی را میسازد و نامِ مستعار را همینجا تعریف میکند؛ و آخر ORDER BY مرتب میکند و LIMIT میبُرد.
سه نتیجهٔ فوری که هر سه را در این فصل با عدد میبینی: WHERE هیچوقت نمیتواند به یک تابعِ تجمیعی نگاه کند (هنوز گروهی نیست)؛ HAVING میتواند (گروهها ساخته شدهاند)؛ و نامِ مستعارِ SELECT در ORDER BY در دسترس است چون ORDER BY بعد از SELECT میآید.
۴. WHERE در برابرِ HAVING: یک کلمه، ۱۳۰ کتاب اختلاف#
پرسشِ واقعی: «در هر دسته چند کتابِ پرامانت داریم؟» — و «پرامانت» را میگوییم بیشتر از ۱۰۰ بار.
این یک شرط روی سطر است: هر کتاب یا بیشتر از ۱۰۰ بار رفته یا نرفته. پس جایش WHERE است.
right = q("""
SELECT category, COUNT(*) AS n
FROM books
WHERE borrowed > ?
GROUP BY category
ORDER BY n DESC
""", (100,))
print(right)
print("جمع:", int(right["n"].sum()))
category n
0 زندگینامه 65
1 هنر 64
2 کودک 63
3 رمان 62
4 شعر 61
5 فلسفه 56
6 علم 53
7 تاریخ 47
جمع: 471
هشت دسته، و جمعشان ۴۷۱. حالا همان پرسش با یک کلمه تفاوت:
wrong = q("""
SELECT category, COUNT(*) AS n
FROM books
GROUP BY category
HAVING borrowed > ?
ORDER BY n DESC
""", (100,))
print(wrong)
print("جمع:", int(wrong["n"].sum()))
category n
0 کودک 167
1 هنر 161
2 فلسفه 137
3 زندگینامه 136
جمع: 601
چهار دسته بهجای هشت، و ۶۰۱ بهجای ۴۷۱ — بدونِ یک کلمه خطا.
ترتیبِ اجرا کلِ ماجرا را توضیح میدهد. در پرسشِ دوم GROUP BY اول اجرا شد و هر هشت دسته را با همهٔ کتابهایشان ساخت؛ بعد HAVING روی هر گروه شرطِ borrowed > 100 را سنجید — ولی borrowed یک ستونِ سطری است، نه تجمیعی. پس SQLite همان کارِ بخشِ ۲ را کرد: از یک سطرِ دلبخواهیِ آن گروه مقدار برداشت.
نتیجه: چهار دستهای که ماندند، بهخاطرِ یک کتابِ اتفاقی ماندند نه بهخاطرِ ویژگیِ خودشان. و عددی که برایشان چاپ شد، تعدادِ کلِ کتابهای آن دسته است، نه تعدادِ کتابهای پرامانتش. خروجی سالم است، ستونها درستاند، عدد باورپذیر است، و جواب غلط است.
و HAVING کِی درست است؟ وقتی شرطت دربارهٔ خودِ گروه باشد، نه دربارهٔ یک سطر:
print(q("""
SELECT category, COUNT(*) AS n
FROM books
GROUP BY category
HAVING COUNT(*) > ?
ORDER BY n DESC
""", (150,)))
category n
0 کودک 167
1 شعر 164
2 هنر 161
«دستههایی که بیشتر از ۱۵۰ کتاب دارند» جملهای است که دربارهٔ یک کتابِ تنها اصلاً معنا ندارد. قاعدهٔ عملی: اگر شرطت را میشود برای یک سطرِ تنها سنجید، جایش WHERE است؛ اگر برای سنجیدنش باید کلِ گروه را دیده باشی، جایش HAVING است.
۵. چرا COUNT(*) در WHERE خطا میدهد#
حالا برعکسش را امتحان کنیم — شرطِ گروهی را در WHERE بگذاریم:
🔧 اگر کار نکرد: این پرسش خطا میدهد، و پیامش دقیقاً همان چیزی است که ترتیبِ اجرا پیشبینی میکند:
try:
q("SELECT category, COUNT(*) AS n FROM books WHERE COUNT(*) > ? GROUP BY category", (150,))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT category, COUNT(*) AS n FROM books WHERE COUNT(*) > ? GROUP BY category': misuse of aggregate: COUNT()
misuse of aggregate یعنی «این تابع را جایی گذاشتهای که هنوز کاری برایش وجود ندارد». وقتی WHERE اجرا میشود هیچ گروهی ساخته نشده، پس چیزی برای شمردن نیست. رفعش همیشه یکی است: شرط را به HAVING ببر.
و یک نکتهٔ مخصوصِ SQLite که باید بدانی چون بینِ موتورها فرق میکند:
print("نام مستعار در ORDER BY — همهجا کار میکند:")
print(q("SELECT category, COUNT(*) AS n FROM books GROUP BY category ORDER BY n DESC LIMIT 2"))
print()
print("نام مستعار در HAVING — SQLite قبولش میکند:")
print(q("SELECT category, COUNT(*) AS n FROM books GROUP BY category HAVING n > 160 ORDER BY n DESC"))
print()
print("نام مستعار غیرتجمیعی در WHERE — SQLite این را هم قبول میکند:")
print(q("SELECT title, borrowed * 2 AS doubled FROM books WHERE doubled > 378 ORDER BY id LIMIT 2"))
نام مستعار در ORDER BY — همهجا کار میکند:
category n
0 کودک 167
1 شعر 164
نام مستعار در HAVING — SQLite قبولش میکند:
category n
0 کودک 167
1 شعر 164
2 هنر 161
نام مستعار غیرتجمیعی در WHERE — SQLite این را هم قبول میکند:
title doubled
0 بازگشت به خانه 380
1 بازگشت به پنجره 380
هر سه کار کردند، و فقط اولی طبقِ استاندارد است.
ORDER BY بعد از SELECT اجرا میشود، پس نامِ مستعار آنجا واقعاً وجود دارد. ولی WHERE و HAVING هر دو قبل از SELECT اجرا میشوند و طبقِ استاندارد نباید نامِ مستعار را ببینند؛ SQLite از سرِ راحتیِ کاربر اجازهشان میدهد و پشتِ پرده عبارت را جای نام میگذارد.
پس این دو خطِ آخر روی SQLite کار میکنند و روی پایگاه دادهٔ بعدیات ممکن است نکنند. جملهٔ صادقانه این است: کار کردنِ یک پرسش روی یک موتور، ثابت نمیکند که پرسش درست نوشته شده.
۶. گروهبندی روی عبارت، و نامی که ستونِ دیگری را صدا میزند#
GROUP BY فقط ستون نمیگیرد؛ هر عبارتی میگیرد. این تنها راهی است که با آن میشود «هر سال» یا «هر ماه» را گروه کرد، چون در SQLite تاریخ متن است.
پرسشِ زیر کاملاً بیآزار به نظر میرسد و شمارشِ کنترلیاش هم پاس میشود:
by_year = q("""
SELECT strftime('%Y', added_at) AS year,
COUNT(*) AS n
FROM books
GROUP BY year
ORDER BY year
""")
print(by_year.head(4))
print("جمع:", int(by_year["n"].sum()), "| گروهها:", len(by_year))
year n
0 2019 20
1 2019 29
2 2019 34
3 2019 33
جمع: 1200 | گروهها: 45
جمع ۱٬۲۰۰ شد — شمارشِ کنترلی پاس شد — و ۴۵ گروه داریم برای هفت سال.
این همان الگویی است که ترمِ ۱ فصلِ ۵ هم دیدیم: آزمونِ «جمعِ اجزا برابرِ کل» خطاهایی را میگیرد که سطر گم میکنند، نه خطاهایی که سطرها را جای اشتباه میگذارند. اینجا هیچ سطری گم نشده و گروهبندی کاملاً چیزِ دیگری است.
دلیلش یک تصادفِ نام است: جدولِ books از قبل ستونی به نامِ year دارد — سالِ انتشارِ کتاب — و در GROUP BY، SQLite اول دنبالِ ستونِ واقعی میگردد و فقط اگر پیدا نکرد سراغِ نامِ مستعارِ SELECT میرود. پس گروهبندی روی سالِ انتشار انجام شد، که ۴۵ مقدارِ متفاوت دارد.
و بدترین قسمتش این است که ستونِ اولِ خروجی همچنان مثلِ سالِ افزودن به نظر میرسد: آن ستون همان ستونِ گروهنشدهٔ بخشِ ۲ است و مقدارش از یک سطرِ دلبخواهیِ هر گروه آمده.
print("مقدارهای متفاوت ستون year در جدول:",
int(q("SELECT COUNT(DISTINCT year) FROM books").iloc[0, 0]))
by_added = q("""
SELECT strftime('%Y', added_at) AS added_year,
COUNT(*) AS n
FROM books
GROUP BY added_year
ORDER BY added_year
""")
print(by_added)
print("جمع:", int(by_added["n"].sum()), "| گروهها:", len(by_added))
مقدارهای متفاوت ستون year در جدول: 45
added_year n
0 2019 193
1 2020 206
2 2021 166
3 2022 165
4 2023 188
5 2024 178
6 2025 104
جمع: 1200 | گروهها: 7
با یک نامِ مستعارِ متفاوت، هفت گروه گرفتیم و جمع باز هم ۱٬۲۰۰ است.
قاعدهٔ عملی: نامِ مستعارت را هیچوقت همنامِ یک ستونِ واقعی نگذار. اگر شک داری، خودِ عبارت را در GROUP BY تکرار کن؛ آنوقت هیچ نامی وسط نیست که بشود اشتباه گرفت.
و حالا به سطرِ آخرِ جدولِ درست نگاه کن و یک لحظه صبر کن: سالِ آخر تقریباً نصفِ بقیه است. این کاهشِ ناگهانی یک واقعیت نیست، یک لبهٔ داده است — این پایگاه داده تا اواسطِ سالِ آخر بیشتر ندارد، و آن سطر یک دورهٔ ناقص است.
اگر همین جدول را در یک گزارش بگذاری، خوانندهات از آن یک روندِ نزولی میخواند که وجود ندارد. فصلِ ۷ همین سطر را دوباره برمیدارد و نشان میدهد چطور به یک عددِ درصدیِ کاملاً گمراهکننده تبدیل میشود.
📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ بخشِ ۱ یک سطر یک شعبه است، در بخشِ ۴ یک دسته، و در بخشِ ۶ یک سالِ افزودن — سه دانهبندیِ متفاوت از یک جدولِ ورودیِ ثابت. با کدام شمارشِ دوم سنجیدی؟ با «جمعِ اجزا برابرِ کل»: جمعِ ستونِ
booksشش گروه شد ۱٬۲۰۰ و جمعِloansشد ۹۴٬۱۹۱. و با تعدادِ گروهها، که کنترلِ دومِ مستقلی است: شش گروه در برابرِ شش سطرِ جدولِbranches، و در بخشِ ۶ همین شمارش بود که ۴۵ گروه را لو داد در حالی که جمعِ سطرها هنوز درست بود. چه کسی از قلم افتاد؟ سه گروه: شعبه یا دستهای که هیچ کتابی ندارد و در خروجی هیچ سطری تولید نمیکند؛ نیمهٔ دومِ سالِ آخر که هنوز اتفاق نیفتاده و سطرش را کوتاه نشان میدهد؛ و همچنان ۲۴۰ سطرِarrivals.csv.
🤖 از دستیارت بپرس: «ترتیبِ منطقیِ اجرای یک پرسشِ
SQLچیست و چرا با ترتیبِ نوشتنش فرق دارد؟» بعد این را بپرس: «چراSQLiteاجازه میدهد ستونی که درGROUP BYنیست درSELECTبیاید، در حالی که استاندارد اجازه نمیدهد؟» — دنبالِ کلمهٔ «تضمیننشده» در جواب بگرد؛ اگر جواب فقط گفت «SQLiteسختگیر نیست» و نگفت مقدار از کجا میآید، ناقص است.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| GROUP BY | گروپ بای | بندی که سطرها را بر اساسِ یک مقدار دسته میکند |
| HAVING | هوینگ | بندی که گروهها را غربال میکند |
| logical order | لاجیکال اوردر | ترتیبی که بندهای یک پرسش واقعاً به آن اجرا میشوند |
| bare column | بر کالمن | ستونی که نه گروه است و نه تجمیع |
| grain | گرین | دانهبندی؛ اینکه یک سطرِ خروجی یعنی چه |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
حالا میتوانی برای هر گروه یک میانگین بسازی — و بلافاصله باید بپرسی آن میانگین چه چیزی را پنهان کرده. فصلِ بعد یک جدولِ دهسطری میسازد که به دو قفسهٔ پنجتایی تقسیم شده، و میانگین و میانهٔ آن دو دقیقاً برابر درمیآید در حالی که شکلشان هیچ شباهتی ندارد. بعد سراغِ همین پایگاه داده میرود و یک عددِ عجیب درمیآورد: میانگینِ میانگینهای همین جدول ۴۸٫۹۷ است، در حالی که میانگینِ واقعیِ همان ستون ۷۸٫۴۹ است.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.