جلسهی 5: شمردن و گروه کردن Counting and Grouping
تا حالا SQL ردیفها را نشانمان میداد؛ حالا از آنها حساب میکشیم: چند تا؟ جمعشان؟ میانگین؟ کمترین و بیشترین؟ با AS به ستون حسابشده اسم میدهیم، با GROUP BY برای هر گروه جدا میشماریم و با HAVING گروهها را جدا میکنیم.
در این جلسه یاد میگیری
- با
COUNT(*)ردیفها را میشماری و باASبه جواب اسم میدهی. - با
SUM،AVG،MINوMAXجمع، میانگین، کمترین و بیشترین یک ستون را میگیری. - با
GROUP BYبرای هر گروه (هر ژانر، هر شهر، هر بازیکن) یک ردیف جواب میگیری. - با
HAVINGگروهها را جدا میکنی و فرقش را باWHEREمیدانی.
درس
🔐 قانون طلایی این دوره: دادهی آدمها امانت است. پشت هر ردیف جدول یک آدم واقعی است: اسمش، کلاسش، کتابهایی که خوانده. دادهی کسی را بدون اجازه جمع نکن، به کسی نده و برای مسخره کردن یا سرک کشیدن به کار نبر. همهی دادههای این دوره ساختگیاند و هر بار که برنامه را اجرا میکنی از اول ساخته میشوند، پس هر چه میخواهی امتحان کن: چیزی خراب نمیشود.
🔁 یادت هست؟ WHERE و ORDER BY. WHERE ردیفهایی را که میخواهی جدا میکند و ORDER BY آنها را میچیند (DESC یعنی از بزرگ به کوچک). متن داخل '...' میآید. امروز هم همینها را لازم داریم: اول ردیفها را جدا میکنیم، بعد از آنها حساب میکشیم.
from db import show
show("SELECT title, year FROM books WHERE genre = 'science' ORDER BY year DESC")**شمردن با COUNT(*).** SELECT COUNT(*) FROM books بهجای فهرست کتابها فقط یک عدد میدهد: چند ردیف هست. با WHERE اول جدا میکنی، بعد میشماری. با AS به ستون جواب یک اسم خوانا بده: COUNT(*) AS total یعنی «اسم این ستون را total بگذار». در این دوره هر ستون حسابشده را با AS اسمگذاری کن.
from db import show
show("SELECT COUNT(*) AS total FROM books")
show("SELECT COUNT(*) AS story_books FROM books WHERE genre = 'story'")جمع، میانگین، کمترین، بیشترین. اینها هم مثل COUNT از یک ستون یک عدد میسازند:
- SUM(copies): جمع همهی نسخهها
- AVG(pages): میانگین صفحهها
- MIN(year): کمترین (قدیمیترین سال)
- MAX(year): بیشترین (تازهترین سال)
چند تا از آنها را میشود در یک SELECT با , نوشت.
from db import show
show("SELECT SUM(copies) AS all_copies, MIN(year) AS oldest, MAX(year) AS newest FROM books")گروه کردن با GROUP BY. «هر ژانر چند کتاب دارد؟» با GROUP BY genre ردیفها بر اساس ژانر دسته میشوند و COUNT(*) برای هر دسته جدا حساب میشود: برای هر ژانر یک ردیف جواب. ستونی که با آن گروه کردهای (genre) را هم در SELECT بیاور تا معلوم باشد هر عدد مال کدام گروه است. ORDER BY بعد از GROUP BY میآید.
from db import show
show("SELECT genre, COUNT(*) AS total FROM books GROUP BY genre ORDER BY genre")مرتب کردن گروهها با اسم AS. میتوانی گروهها را با همان اسمی که با AS گذاشتی مرتب کنی: ORDER BY kids DESC یعنی اول شهری که بیشترین دانشآموز را دارد. وقتی چند گروه عدد یکسان دارند (شیراز، تبریز و اصفهان هر کدام ۲)، یک ستون دوم بده تا ترتیب همیشه یکی باشد: ORDER BY kids DESC, city.
from db import show
show("SELECT city, COUNT(*) AS kids FROM students GROUP BY city ORDER BY kids DESC, city")جدا کردن گروهها با HAVING. «فقط ژانرهایی که دستکم ۳ کتاب دارند.» WHERE ردیفها را قبل از گروه کردن جدا میکند؛ HAVING گروهها را بعد از گروه کردن جدا میکند، پس میتواند COUNT(*) یا SUM(...) را بپرسد. ترتیب نوشتن: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ....
from db import show
show("SELECT genre, COUNT(*) AS total FROM books GROUP BY genre HAVING COUNT(*) >= 3 ORDER BY genre")اشتباه رایج: WHERE COUNT(*) >= 3 خطا میدهد، چون WHERE وقتی کار میکند که هنوز گروهی نیست که شمرده شود. شرط روی COUNT، SUM، AVG، MIN یا MAX همیشه در HAVING میآید؛ شرط روی یک ستون معمولی (مثل grade >= 8) در WHERE. اشتباه دیگر: یادت برود ستون گروه را در SELECT بیاوری؛ آن وقت عددها را میبینی ولی نمیدانی مال کدام گروهاند.
مثالهای این جلسه
- مثال پایتون (پایگاه داده با پایتون): show("SELECT title, year FROM books WHERE g…
- مثال پایتون (پایگاه داده با پایتون): show("SELECT COUNT(*) AS total FROM books")
- مثال پایتون (پایگاه داده با پایتون): show("SELECT SUM(copies) AS all_copies, MIN…
- مثال پایتون (پایگاه داده با پایتون): show("SELECT genre, COUNT(*) AS total FROM …
- مثال پایتون (پایگاه داده با پایتون): show("SELECT city, COUNT(*) AS kids FROM st…
- مثال پایتون (پایگاه داده با پایتون): show("SELECT genre, COUNT(*) AS total FROM … — شمردن و گروه کردن
چالشها
- Long Stories (آسان)
- Eighth Graders (آسان)
- Short, Long, Average (متوسط)
- Books per Genre (متوسط)
- Top Players (سخت)
- WHERE or HAVING? (سخت)
بیشتر بخوان
← جلسهی 4: پروژه: ربات کتابدار جلسهی 6: وصل کردن جدولها →