در این فصل چه یاد میگیری#
همان ستونِ borrowed را دو بار دستهبندی میکنیم. با سطلهای پنجاهتایی چهار سطر میگیریم که یک سطلِ بزرگ دارند و سه سطلِ تقریباً هماندازه — قله کاملاً داخلِ سطلِ اول قایم است و دیده نمیشود. با سطلهای بیستتایی، یک قلهٔ ۳۰۸تایی ظاهر میشود کنارِ نُه سطلی که هیچکدامشان به ۱۲۰ نمیرسند.
داده عوض نشد. تصمیمِ ما دربارهٔ عرضِ سطل عوض شد.
و یک سکوتِ بدتر هم هست: سطلی که هیچ سطری ندارد، هیچ سطری هم تولید نمیکند. در دستهبندیِ امتیازها، سطلِ «زیر ۲» اصلاً در خروجی پیدا نمیشود — نه با شمارشِ صفر، بلکه اصلاً — و اگر از روی همان خروجی نموداری بکشی، محورش از جایی شروع میشود که خودت انتخابش نکردهای.

آخر این فصل میتوانی:
- با
CASEسطل بسازی و برایش برچسبِ خوانا بگذاری - هیستوگرام را با
GROUP BYبگیری و با یک نوارِ متنی ببینیاش - نشان بدهی عرضِ سطل شکلِ توزیع را عوض میکند
- سطلهای خالی را پیدا کنی، حتی وقتی در خروجی نیستند
- صدک را بهشکلِ تقریبی بگیری، بدونِ اینکه تابعی برایش وجود داشته باشد
قبل از شروع#
از فصلِ ۳: میانگین و میانه و پراکندگی، و شمارشِ دستهای روی ده سطرِ قفسهها — این فصل همان کار را به یک ابزارِ کامل تبدیل میکند.
از ترمِ ۱: CASE WHEN و تقسیمِ صحیح. هر دو ابزارِ اصلیِ سطلبندیاند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. سطلبندی با CASE: برچسب بهجای عدد#
CASE WHEN را از ترمِ ۱ داری: یک زنجیرهٔ شرط که بهجای عدد، برچسب میسازد. حالا همان برچسب را در GROUP BY میگذاریم.
print(q("""
SELECT CASE
WHEN borrowed = 0 THEN 'هرگز'
WHEN borrowed < 20 THEN 'کم'
WHEN borrowed < 100 THEN 'متوسط'
ELSE 'پرامانت'
END AS band,
COUNT(*) AS n
FROM books
GROUP BY band
ORDER BY n DESC
"""))
band n
0 پرامانت 473
1 متوسط 419
2 کم 228
3 هرگز 80
چهار سطر گرفتیم، و ترتیبشان از روی شمارش است نه از روی معنیِ سطل.
اینجا اتفاقاً دقیقاً برعکسِ ترتیبِ طبیعی درآمده و همین گمراهکننده است: به نظر مرتب میآید، ولی مرتب نیست — با شمارشِ متفاوت هر ترتیبی ممکن بود. و ORDER BY band هم درستش نمیکند: آنوقت ترتیب، ترتیبِ نویسههای فارسی میشود که هیچ ربطی به بزرگ و کوچکِ عدد ندارد — همان دامِ ترمِ ۱ فصلِ ۴.
راهِحل این است که کنارِ برچسب یک شمارهٔ مرتبسازی هم بسازی:
print(q("""
SELECT CASE
WHEN borrowed = 0 THEN 1
WHEN borrowed < 20 THEN 2
WHEN borrowed < 100 THEN 3
ELSE 4
END AS band_id,
CASE
WHEN borrowed = 0 THEN 'هرگز'
WHEN borrowed < 20 THEN 'کم'
WHEN borrowed < 100 THEN 'متوسط'
ELSE 'پرامانت'
END AS band,
COUNT(*) AS n
FROM books
GROUP BY band_id, band
ORDER BY band_id
"""))
band_id band n
0 1 هرگز 80
1 2 کم 228
2 3 متوسط 419
3 4 پرامانت 473
حالا سطرها به ترتیبِ خودِ مقدارند و جدول خواندنی است.
✅ چک کن: جمعِ ستونِ
nباید ۱٬۲۰۰ شود. ولی این جمع چونELSEداریم همیشه ۱٬۲۰۰ میشود، حتی اگرborrowedجاییNULLباشد: چنین سطری به هیچWHENی نمیخورد و بیصدا درELSE، یعنی «پرامانت»، مینشیند. اینجاborrowedهیچجاNULLنیست، ولی روی هر ستونی کهNULLمیپذیرد همیشه یک شرطِIS NULLجدا بگذار؛ جمعِ ۱٬۲۰۰ این خطا را نمیگیرد.
۲. هیستوگرام با تقسیمِ صحیح، و نواری که دیده میشود#
برچسب برای چهار دسته خوب است. برای دیدنِ شکل باید سطلهای همعرض و زیاد داشته باشی، و نوشتنِ بیست WHEN کارِ عاقلانهای نیست. تقسیمِ صحیح همان را در یک عبارت میدهد.
def show(frame, width):
biggest = int(frame["n"].max())
for row in frame.itertuples(index=False):
bar = "█" * round(int(row.n) * 40 / biggest)
print(f" {int(row.bucket):>3}–{int(row.bucket) + width - 1:<3} {int(row.n):>4} {bar}")
print(" جمع:", int(frame["n"].sum()))
twenty = q("""
SELECT (borrowed / 20) * 20 AS bucket, COUNT(*) AS n
FROM books
GROUP BY bucket
ORDER BY bucket
""")
show(twenty, 20)
0–19 308 ████████████████████████████████████████
20–39 103 █████████████
40–59 107 ██████████████
60–79 96 ████████████
80–99 113 ███████████████
100–119 104 ██████████████
120–139 110 ██████████████
140–159 107 ██████████████
160–179 109 ██████████████
180–199 43 ██████
جمع: 1200
حالا شکل را میبینی، و شکل یک قلهٔ تنها در سرِ چپ است.
سطلِ اول ۳۰۸ سطر دارد، هشت سطلِ میانی همه بینِ ۹۶ و ۱۱۳، و سطلِ آخر ۴۳ — که بخشِ ۴ سراغش میرود. این توزیع یک تپه نیست؛ دو جمعیتِ متفاوت است که در یک ستون ذخیره شدهاند — و از ترمِ ۱ میدانیم آن دو کداماند: ۲۳۱ کتابِ بیامتیاز که بیشترین امانتشان ۲ است، و ۹۶۹ کتابِ دیگر.
میانگینِ ۷۸٫۴۹ که فصلِ ۳ گرفت، عددی است بینِ آن دو جمعیت، و به هیچکدامشان شبیه نیست.
۳. عرضِ سطل شکل را عوض میکند#
همان ستون، همان پرسش، فقط یک عدد فرق دارد:
fifty = q("""
SELECT (borrowed / 50) * 50 AS bucket, COUNT(*) AS n
FROM books
GROUP BY bucket
ORDER BY bucket
""")
show(fifty, 50)
print()
five = q("""
SELECT (borrowed / 5) * 5 AS bucket, COUNT(*) AS n
FROM books
GROUP BY bucket
ORDER BY bucket
""")
show(five.head(6), 5)
print(" (فقط شش سطل اول از", len(five), "سطل)")
0–49 472 ████████████████████████████████████████
50–99 255 ██████████████████████
100–149 264 ██████████████████████
150–199 209 ██████████████████
جمع: 1200
0–4 243 ████████████████████████████████████████
5–9 20 ███
10–14 23 ████
15–19 22 ████
20–24 29 █████
25–29 24 ████
جمع: 361
(فقط شش سطل اول از 39 سطل)
سه نمایش از یک ستون، و سه داستانِ متفاوت.
با عرضِ ۵۰ فقط چهار سطل داریم و سطلِ اول تقریباً دو برابرِ هر سه سطلِ دیگر است؛ آن قله کاملاً داخلِ سطلِ اول قایم شده و دیده نمیشود. با عرضِ ۲۰ قله ظاهر میشود. با عرضِ ۵ میبینی که قله حتی از آن هم باریکتر است: ۲۴۳ سطر فقط در بازهٔ ۰ تا ۴ جمع شدهاند، در حالی که هیچکدام از پنج سطلِ بعدی از ۲۹ بیشتر نیست.
هیچکدام از این سه دروغ نمیگویند و هیچکدام هم «نمایشِ درست» نیستند. عرضِ سطل یک تصمیمِ توست، و مثلِ مخرجِ فصلِ ۴ باید در گزارش اعلام شود.
قاعدهٔ عملی: هر توزیعی را با دستِکم دو عرضِ متفاوت نگاه کن. اگر شکلی با هر دو دیده شد، واقعی است؛ اگر فقط با یک عرضِ خاص دیده شد، به آن شک کن.
🌱 ریشهاش کجاست: هیستوگرام و همین نکته که پهنای دسته شکل را عوض میکند، درسِ ریشه ترمِ ۶ فصل ۷ است — آنجا فرقِ هیستوگرام با نمودارِ ستونی هم دقیق میشود: در نمودارِ ستونی ترتیبِ ستونها دلبخواهی است، در هیستوگرام محورِ افقی یک عددِ پیوسته است و جابهجا کردنِ دو ستون خرابش میکند.
۴. سطلِ آخر که پهنایش کمتر است#
به سطلِ آخرِ نمایشِ بیستتایی برگرد: عددش ۴۳ است، در حالی که هشت سطلِ میانی همه بینِ ۹۶ و ۱۱۳اند. آیا کتابهای خیلی پرامانت واقعاً اینقدر کماند؟
print(q("""
SELECT MAX(borrowed) AS most,
SUM(borrowed >= 180) AS in_last_bucket,
SUM(borrowed >= 160 AND borrowed < 180) AS in_previous_bucket
FROM books
"""))
most in_last_bucket in_previous_bucket
0 190 43 109
بیشترین مقدارِ ستون ۱۹۰ است، پس سطلِ آخر بهجای بیست عدد، فقط یازده عددِ ممکن دارد: ۱۸۰ تا ۱۹۰.
یعنی آن ۴۳ کوتاه است چون سطل کوتاه است، نه چون داده آنجا کم است. سطلِ قبلیاش پهنای کاملِ بیست را دارد و ۱۰۹ سطر.
این یکی از پرتکرارترین خواندنهای غلطِ هیستوگرام است، و راهِ جلوگیریاش یک جمله است: کنارِ هر هیستوگرام، کمینه و بیشینهٔ واقعیِ ستون را هم بنویس. آنوقت هر کسی میتواند خودش بفهمد کدام سطل ناقص است.
۵. سطلی که هیچ سطری ندارد#
حالا خطرناکترین سکوتِ این فصل. امتیازها را به بازههای یکییکی میشکنیم:
print(q("""
SELECT CASE
WHEN rating IS NULL THEN 'بیامتیاز'
WHEN rating < 2.0 THEN 'زیر ۲'
WHEN rating < 3.0 THEN '۲ تا ۳'
WHEN rating < 4.0 THEN '۳ تا ۴'
ELSE '۴ و بالاتر'
END AS band,
COUNT(*) AS n
FROM books
GROUP BY band
"""))
print()
print("کمترین امتیاز در جدول:", float(q("SELECT MIN(rating) FROM books").iloc[0, 0]))
band n
0 بیامتیاز 231
1 ۲ تا ۳ 148
2 ۳ تا ۴ 405
3 ۴ و بالاتر 416
کمترین امتیاز در جدول: 2.6
پنج برچسب نوشتیم و چهار سطر گرفتیم. «زیر ۲» در خروجی نیست — نه با شمارشِ صفر، بلکه اصلاً.
GROUP BY فقط از سطرهایی که وجود دارند گروه میسازد. برچسبی که به هیچ سطری نخورد، هیچوقت ساخته نمیشود. پس یک شمارشِ صفر و یک بازهٔ ناموجود، در خروجی دقیقاً یک شکل دارند: هیچ.
و همین روی سطلهای عددی هم اتفاق میافتد، حتی وسطِ توزیع:
poetry = q("""
SELECT (borrowed / 5) * 5 AS bucket, COUNT(*) AS n
FROM books
WHERE category = ?
GROUP BY bucket
ORDER BY bucket
""", ("شعر",))
expected = list(range(0, 191, 5))
present = set(int(v) for v in poetry["bucket"])
missing = [b for b in expected if b not in present]
print("سطلهای ممکن :", len(expected))
print("سطلهای موجود:", len(poetry))
print("سطلهای غایب :", missing)
print("جمع سطرها :", int(poetry["n"].sum()))
سطلهای ممکن : 39
سطلهای موجود: 36
سطلهای غایب : [55, 75, 190]
جمع سطرها : 164
سه سطل از ۳۹ سطلِ ممکن اصلاً سطری ندارند، و دو تایشان وسطِ توزیعاند.
نموداری که از این خروجی بکشی، آن دو شکاف را نشان نمیدهد؛ دو ستونِ همسایه را به هم میچسباند و توزیع را از چیزی که هست پیوستهتر نشان میدهد.
راهِ درستش این است که فهرستِ سطلهای ممکن را جدا بسازی و نتیجه را رویش بنشانی — دقیقاً همان کاری که چند خطِ پایتونیِ بالا کرد. در SQL هم میشود، با ساختنِ یک جدولِ کمکی از همهٔ سطلها؛ ابزارش CROSS JOIN است که ترمِ ۳ فصلِ ۴ میآورد. تا آنجا، همین شمارشِ «چند سطل انتظار داشتم و چندتا گرفتم» کافی است و باید عادت شود.
🔧 اگر کار نکرد: پرتکرارترین خطای این فصل، جا انداختنِ
ENDدرCASEاست، و پیامش گیجکننده به نظر میرسد:
try:
q("SELECT CASE WHEN borrowed > 100 THEN 1 ELSE 0 FROM books LIMIT 1")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT CASE WHEN borrowed > 100 THEN 1 ELSE 0 FROM books LIMIT 1': near "FROM": syntax error
پیام به FROM اشاره میکند، ولی مشکل یک کلمه قبلتر است: SQLite هنوز منتظرِ END بود و بهجایش FROM دید. در خطای نحوی همیشه به کلمهٔ قبل از جایی که پیام نشان میدهد نگاه کن.
۶. صدک، بهشکلِ تقریبی#
میانه سطرِ وسط بود. صدک همان ایده است در هر نقطهٔ دیگری: صدکِ ۹۰ یعنی مقداری که ۹۰ درصدِ سطرها از آن کمترند. و مثلِ میانه، SQLite تابعی برایش ندارد — همان ORDER BY ... LIMIT 1 OFFSET n کار را میکند.
total = int(q("SELECT COUNT(*) FROM books").iloc[0, 0])
for percent in (10, 25, 50, 75, 90):
offset = percent * total // 100 - 1
value = int(q("SELECT borrowed FROM books ORDER BY borrowed, id LIMIT 1 OFFSET ?",
(offset,)).iloc[0, 0])
print(f" صدک {percent:>2} → OFFSET {offset:>4} → {value:>3}")
صدک 10 → OFFSET 119 → 1
صدک 25 → OFFSET 299 → 16
صدک 50 → OFFSET 599 → 76
صدک 75 → OFFSET 899 → 132
صدک 90 → OFFSET 1079 → 166
پنج عدد که شکلِ توزیع را در یک خط خلاصه میکنند — و صدکِ ۱۰ برابرِ ۱ است، که همان دُمِ چپِ پرجمعیت را دوباره نشان میدهد.
چرا «تقریبی»؟ دو دلیل، و هر دو را باید در گزارش بنویسی. اول اینکه فرمولِ OFFSET یک انتخاب است، نه یک قانون: راههای متفاوتی برای گرد کردنِ جایگاهِ صدک وجود دارد و هرکدام ممکن است یک سطر جلوتر یا عقبتر بیفتند. دوم اینکه این روش برای هر صدک یک بار کلِ ستون را مرتب میکند؛ روی جدولِ بزرگ گران است و ترمِ ۵ نشان میدهد چقدر.
📏 اندازه بگیر: یک سطر یعنی چه؟ در هر جدولِ این فصل یک سطر یک سطل است — نه یک کتاب — و عددش تعدادِ کتابهای داخلِ آن سطل. با کدام شمارشِ دوم سنجیدی؟ سهتا: جمعِ ستونِ شمارش در نمایشِ بیستتایی و پنجاهتایی هر دو ۱٬۲۰۰ ماند و نشان داد عرضِ سطل هیچ سطری را گم نمیکند (در نمایشِ پنجتایی فقط شش سطلِ اول چاپ شد، پس جمعِ آن تکه ۳۶۱ است)؛ بیشینهٔ ستون (۱۹۰) توضیح داد چرا سطلِ آخر کوتاه است؛ و شمارشِ «چند سطل انتظار داشتم» در برابرِ «چندتا گرفتم» سه سطلِ غایب را پیدا کرد. چه کسی از قلم افتاد؟ سطلهایی که هیچ سطری ندارند و هیچ سطری هم در خروجی نمیسازند — از جمله کلِ بازهٔ «زیر ۲» در امتیازها؛ و همچنان ۲۴۰ سطرِ
arrivals.csv.
🤖 از دستیارت بپرس: «چطور عرضِ مناسبِ سطل را برای یک هیستوگرام انتخاب کنم؟» بعد این را بپرس: «اگر یک سطل هیچ سطری نداشته باشد، در خروجیِ
GROUP BYچه اتفاقی میافتد و چطور میتوانم مجبورش کنم با شمارشِ صفر ظاهر شود؟» — جوابِ درست به ساختنِ فهرستِ سطلها بهعنوان یک منبعِ جدا اشاره میکند؛ اگر جواب فقط گفت «COALESCEبگذار»، مسئله را نفهمیده است.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| bucket / bin | باکت / بین | سطل؛ بازهای که مقدارها در آن شمرده میشوند |
| histogram | هیستوگرام | نمودارِ شمارشِ سطلها روی یک محورِ عددی |
| bucket width | باکت ویدث | عرضِ سطل |
| distribution | دیستریبیوشن | توزیع؛ اینکه مقدارها چطور پخش شدهاند |
| percentile | پرسنتایل | صدک؛ مقداری که سهمِ مشخصی از داده از آن کمترند |
| bimodal | بایمودال | دوقلهای؛ توزیعی که دو تجمعِ جدا دارد |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
تا اینجا هر بار که تجمیع گرفتیم، سطرهای اصلی را از دست دادیم: ۱٬۲۰۰ سطر میرفت و ۶ یا ۸ سطر برمیگشت. فصلِ بعد ابزاری میآورد که هر دو را با هم دارد — هر ۱٬۲۰۰ سطر سرِ جایشان میمانند و کنارِ هرکدام یک عددِ گروهی مینشیند. مثلاً کنارِ هر کتاب، میانگینِ دستهٔ خودش، و سهمش از کلِ امانتهای آن دسته — در یک پرسش و بدونِ اینکه سطری گم شود.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.