# فصل ۰۵ — توزیع را با `SQL` ببین

<div dir="rtl">

# فصل ۰۵ — توزیع را با `SQL` ببین

## در این فصل چه یاد می‌گیری

همان ستونِ `borrowed` را دو بار دسته‌بندی می‌کنیم. با سطل‌های پنجاه‌تایی چهار سطر می‌گیریم که یک سطلِ بزرگ دارند و سه سطلِ تقریباً هم‌اندازه — قله کاملاً داخلِ سطلِ اول قایم است و دیده نمی‌شود. با سطل‌های بیست‌تایی، یک **قلهٔ ۳۰۸تایی** ظاهر می‌شود کنارِ نُه سطلی که هیچ‌کدامشان به ۱۲۰ نمی‌رسند.

**داده عوض نشد. تصمیمِ ما دربارهٔ عرضِ سطل عوض شد.**

و یک سکوتِ بدتر هم هست: سطلی که هیچ سطری ندارد، هیچ سطری هم تولید نمی‌کند. در دسته‌بندیِ امتیازها، سطلِ «زیر ۲» اصلاً در خروجی پیدا نمی‌شود — نه با شمارشِ صفر، بلکه اصلاً — و اگر از روی همان خروجی نموداری بکشی، محورش از جایی شروع می‌شود که خودت انتخابش نکرده‌ای.

![دو ردیف قفسهٔ دانه‌بندی روی یک تودهٔ یکسان، یکی با چند خانهٔ پهن و دیگری با خانه‌های باریکِ زیاد، و دو پروفیل پرشدنِ کاملاً متفاوت](/lesson-assets/t2-s05-buckets-and-distribution/prompt/01-image-two-sorting-racks.png)

آخر این فصل می‌توانی:

- با `CASE` سطل بسازی و برایش برچسبِ خوانا بگذاری
- هیستوگرام را با `GROUP BY` بگیری و با یک نوارِ متنی ببینی‌اش
- نشان بدهی عرضِ سطل شکلِ توزیع را عوض می‌کند
- سطل‌های خالی را پیدا کنی، حتی وقتی در خروجی نیستند
- صدک را به‌شکلِ تقریبی بگیری، بدونِ اینکه تابعی برایش وجود داشته باشد

## قبل از شروع

**از فصلِ ۳:** میانگین و میانه و پراکندگی، و شمارشِ دسته‌ای روی ده سطرِ قفسه‌ها — این فصل همان کار را به یک ابزارِ کامل تبدیل می‌کند.

**از ترمِ ۱:** `CASE WHEN` و تقسیمِ صحیح. هر دو ابزارِ اصلیِ سطل‌بندی‌اند.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-2-aggregation/session-05-buckets-and-distribution.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `books` | ۱٬۲۰۰ | کمتر از یک ثانیه |
| `branches` | ۶ | آنی |

<div dir="rtl">

## ۱. سطل‌بندی با `CASE`: برچسب به‌جای عدد

`CASE WHEN` را از ترمِ ۱ داری: یک زنجیرهٔ شرط که به‌جای عدد، برچسب می‌سازد. حالا همان برچسب را در `GROUP BY` می‌گذاریم.

</div>

```python
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
"""))
```

```text
      band    n
0  پرامانت  473
1    متوسط  419
2       کم  228
3     هرگز   80
```

<div dir="rtl">

**چهار سطر گرفتیم، و ترتیبشان از روی شمارش است نه از روی معنیِ سطل.**

اینجا اتفاقاً دقیقاً برعکسِ ترتیبِ طبیعی درآمده و همین گمراه‌کننده است: به نظر مرتب می‌آید، ولی مرتب نیست — با شمارشِ متفاوت هر ترتیبی ممکن بود. و `ORDER BY band` هم درستش نمی‌کند: آن‌وقت ترتیب، ترتیبِ نویسه‌های فارسی می‌شود که هیچ ربطی به بزرگ و کوچکِ عدد ندارد — همان دامِ ترمِ ۱ فصلِ ۴.

**راهِ‌حل این است که کنارِ برچسب یک شمارهٔ مرتب‌سازی هم بسازی:**

</div>

```python
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
"""))
```

```text
   band_id     band    n
0        1     هرگز   80
1        2       کم  228
2        3    متوسط  419
3        4  پرامانت  473
```

<div dir="rtl">

**حالا سطرها به ترتیبِ خودِ مقدارند و جدول خواندنی است.**

> ✅ **چک کن:** جمعِ ستونِ `n` باید ۱٬۲۰۰ شود. ولی این جمع چون `ELSE` داریم همیشه ۱٬۲۰۰ می‌شود، حتی اگر `borrowed` جایی `NULL` باشد: چنین سطری به هیچ `WHEN`ی نمی‌خورد و بی‌صدا در `ELSE`، یعنی «پرامانت»، می‌نشیند. اینجا `borrowed` هیچ‌جا `NULL` نیست، ولی روی هر ستونی که `NULL` می‌پذیرد **همیشه** یک شرطِ `IS NULL` جدا بگذار؛ جمعِ ۱٬۲۰۰ این خطا را نمی‌گیرد.

## ۲. هیستوگرام با تقسیمِ صحیح، و نواری که دیده می‌شود

برچسب برای چهار دسته خوب است. برای دیدنِ **شکل** باید سطل‌های هم‌عرض و زیاد داشته باشی، و نوشتنِ بیست `WHEN` کارِ عاقلانه‌ای نیست. تقسیمِ صحیح همان را در یک عبارت می‌دهد.

</div>

```python
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)
```

```text
    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
```

<div dir="rtl">

**حالا شکل را می‌بینی، و شکل یک قلهٔ تنها در سرِ چپ است.**

سطلِ اول ۳۰۸ سطر دارد، هشت سطلِ میانی همه بینِ ۹۶ و ۱۱۳، و سطلِ آخر ۴۳ — که بخشِ ۴ سراغش می‌رود. **این توزیع یک تپه نیست؛ دو جمعیتِ متفاوت است که در یک ستون ذخیره شده‌اند** — و از ترمِ ۱ می‌دانیم آن دو کدام‌اند: ۲۳۱ کتابِ بی‌امتیاز که بیشترین امانتشان ۲ است، و ۹۶۹ کتابِ دیگر.

**میانگینِ ۷۸٫۴۹ که فصلِ ۳ گرفت، عددی است بینِ آن دو جمعیت، و به هیچ‌کدامشان شبیه نیست.**

## ۳. عرضِ سطل شکل را عوض می‌کند

همان ستون، همان پرسش، فقط یک عدد فرق دارد:

</div>

```python
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), "سطل)")
```

```text
    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 سطل)
```

<div dir="rtl">

**سه نمایش از یک ستون، و سه داستانِ متفاوت.**

با عرضِ ۵۰ فقط چهار سطل داریم و سطلِ اول تقریباً دو برابرِ هر سه سطلِ دیگر است؛ آن قله کاملاً داخلِ سطلِ اول قایم شده و دیده نمی‌شود. با عرضِ ۲۰ قله ظاهر می‌شود. با عرضِ ۵ می‌بینی که قله حتی از آن هم باریک‌تر است: **۲۴۳ سطر فقط در بازهٔ ۰ تا ۴ جمع شده‌اند**، در حالی که هیچ‌کدام از پنج سطلِ بعدی از ۲۹ بیشتر نیست.

**هیچ‌کدام از این سه دروغ نمی‌گویند و هیچ‌کدام هم «نمایشِ درست» نیستند.** عرضِ سطل یک تصمیمِ توست، و مثلِ مخرجِ فصلِ ۴ باید در گزارش اعلام شود.

**قاعدهٔ عملی: هر توزیعی را با دستِ‌کم دو عرضِ متفاوت نگاه کن.** اگر شکلی با هر دو دیده شد، واقعی است؛ اگر فقط با یک عرضِ خاص دیده شد، به آن شک کن.

> 🌱 **ریشه‌اش کجاست:** هیستوگرام و همین نکته که پهنای دسته شکل را عوض می‌کند، درسِ [ریشه ترمِ ۶ فصل ۷](/courses/risheh/lessons/term-6-probability-statistics/session-07-distributions) است — آنجا فرقِ هیستوگرام با نمودارِ ستونی هم دقیق می‌شود: در نمودارِ ستونی ترتیبِ ستون‌ها دلبخواهی است، در هیستوگرام محورِ افقی یک عددِ پیوسته است و جابه‌جا کردنِ دو ستون خرابش می‌کند.

## ۴. سطلِ آخر که پهنایش کمتر است

به سطلِ آخرِ نمایشِ بیست‌تایی برگرد: عددش ۴۳ است، در حالی که هشت سطلِ میانی همه بینِ ۹۶ و ۱۱۳‌اند. آیا کتاب‌های خیلی پرامانت واقعاً این‌قدر کم‌اند؟

</div>

```python
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
"""))
```

```text
   most  in_last_bucket  in_previous_bucket
0   190              43                 109
```

<div dir="rtl">

**بیشترین مقدارِ ستون ۱۹۰ است، پس سطلِ آخر به‌جای بیست عدد، فقط یازده عددِ ممکن دارد: ۱۸۰ تا ۱۹۰.**

یعنی آن ۴۳ کوتاه است چون **سطل کوتاه است**، نه چون داده آنجا کم است. سطلِ قبلی‌اش پهنای کاملِ بیست را دارد و ۱۰۹ سطر.

**این یکی از پرتکرارترین خواندن‌های غلطِ هیستوگرام است**، و راهِ جلوگیری‌اش یک جمله است: **کنارِ هر هیستوگرام، کمینه و بیشینهٔ واقعیِ ستون را هم بنویس.** آن‌وقت هر کسی می‌تواند خودش بفهمد کدام سطل ناقص است.

## ۵. سطلی که هیچ سطری ندارد

حالا خطرناک‌ترین سکوتِ این فصل. امتیازها را به بازه‌های یکی‌یکی می‌شکنیم:

</div>

```python
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]))
```

```text
         band    n
0   بی‌امتیاز  231
1      ۲ تا ۳  148
2      ۳ تا ۴  405
3  ۴ و بالاتر  416

کمترین امتیاز در جدول: 2.6
```

<div dir="rtl">

**پنج برچسب نوشتیم و چهار سطر گرفتیم. «زیر ۲» در خروجی نیست — نه با شمارشِ صفر، بلکه اصلاً.**

`GROUP BY` فقط از سطرهایی که وجود دارند گروه می‌سازد. برچسبی که به هیچ سطری نخورد، هیچ‌وقت ساخته نمی‌شود. **پس یک شمارشِ صفر و یک بازهٔ ناموجود، در خروجی دقیقاً یک شکل دارند: هیچ.**

و همین روی سطل‌های عددی هم اتفاق می‌افتد، حتی وسطِ توزیع:

</div>

```python
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()))
```

```text
سطل‌های ممکن : 39
سطل‌های موجود: 36
سطل‌های غایب : [55, 75, 190]
جمع سطرها    : 164
```

<div dir="rtl">

**سه سطل از ۳۹ سطلِ ممکن اصلاً سطری ندارند، و دو تایشان وسطِ توزیع‌اند.**

نموداری که از این خروجی بکشی، آن دو شکاف را نشان نمی‌دهد؛ **دو ستونِ همسایه را به هم می‌چسباند و توزیع را از چیزی که هست پیوسته‌تر نشان می‌دهد.**

راهِ درستش این است که فهرستِ سطل‌های ممکن را جدا بسازی و نتیجه را رویش بنشانی — دقیقاً همان کاری که چند خطِ پایتونیِ بالا کرد. **در `SQL` هم می‌شود، با ساختنِ یک جدولِ کمکی از همهٔ سطل‌ها؛ ابزارش `CROSS JOIN` است که ترمِ ۳ فصلِ ۴ می‌آورد.** تا آنجا، همین شمارشِ «چند سطل انتظار داشتم و چندتا گرفتم» کافی است و باید عادت شود.

> 🔧 **اگر کار نکرد:** پرتکرارترین خطای این فصل، جا انداختنِ `END` در `CASE` است، و پیامش گیج‌کننده به نظر می‌رسد:

</div>

```python
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)
```

```text
pandas.errors.DatabaseError
Execution failed on sql 'SELECT CASE WHEN borrowed > 100 THEN 1 ELSE 0 FROM books LIMIT 1': near "FROM": syntax error
```

<div dir="rtl">

پیام به `FROM` اشاره می‌کند، ولی مشکل یک کلمه قبل‌تر است: `SQLite` هنوز منتظرِ `END` بود و به‌جایش `FROM` دید. **در خطای نحوی همیشه به کلمهٔ **قبل** از جایی که پیام نشان می‌دهد نگاه کن.**

## ۶. صدک، به‌شکلِ تقریبی

میانه سطرِ وسط بود. **صدک** همان ایده است در هر نقطهٔ دیگری: صدکِ ۹۰ یعنی مقداری که ۹۰ درصدِ سطرها از آن کمترند. و مثلِ میانه، `SQLite` تابعی برایش ندارد — همان `ORDER BY ... LIMIT 1 OFFSET n` کار را می‌کند.

</div>

```python
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}")
```

```text
  صدک 10 → OFFSET  119 →   1
  صدک 25 → OFFSET  299 →  16
  صدک 50 → OFFSET  599 →  76
  صدک 75 → OFFSET  899 → 132
  صدک 90 → OFFSET 1079 → 166
```

<div dir="rtl">

**پنج عدد که شکلِ توزیع را در یک خط خلاصه می‌کنند** — و صدکِ ۱۰ برابرِ ۱ است، که همان دُمِ چپِ پرجمعیت را دوباره نشان می‌دهد.

**چرا «تقریبی»؟** دو دلیل، و هر دو را باید در گزارش بنویسی. اول اینکه فرمولِ `OFFSET` یک انتخاب است، نه یک قانون: راه‌های متفاوتی برای گرد کردنِ جایگاهِ صدک وجود دارد و هرکدام ممکن است یک سطر جلوتر یا عقب‌تر بیفتند. دوم اینکه این روش برای هر صدک یک بار کلِ ستون را مرتب می‌کند؛ روی جدولِ بزرگ گران است و ترمِ ۵ نشان می‌دهد چقدر.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در هر جدولِ این فصل یک سطر یک **سطل** است — نه یک کتاب — و عددش تعدادِ کتاب‌های داخلِ آن سطل. **با کدام شمارشِ دوم سنجیدی؟** سه‌تا: جمعِ ستونِ شمارش در نمایشِ بیست‌تایی و پنجاه‌تایی هر دو ۱٬۲۰۰ ماند و نشان داد عرضِ سطل هیچ سطری را گم نمی‌کند (در نمایشِ پنج‌تایی فقط شش سطلِ اول چاپ شد، پس جمعِ آن تکه ۳۶۱ است)؛ بیشینهٔ ستون (۱۹۰) توضیح داد چرا سطلِ آخر کوتاه است؛ و شمارشِ «چند سطل انتظار داشتم» در برابرِ «چندتا گرفتم» سه سطلِ غایب را پیدا کرد. **چه کسی از قلم افتاد؟** سطل‌هایی که هیچ سطری ندارند و هیچ سطری هم در خروجی نمی‌سازند — از جمله کلِ بازهٔ «زیر ۲» در امتیازها؛ و همچنان ۲۴۰ سطرِ `arrivals.csv`.

> 🤖 **از دستیارت بپرس:** «چطور عرضِ مناسبِ سطل را برای یک هیستوگرام انتخاب کنم؟» بعد این را بپرس: **«اگر یک سطل هیچ سطری نداشته باشد، در خروجیِ `GROUP BY` چه اتفاقی می‌افتد و چطور می‌توانم مجبورش کنم با شمارشِ صفر ظاهر شود؟»** — جوابِ درست به ساختنِ فهرستِ سطل‌ها به‌عنوان یک منبعِ جدا اشاره می‌کند؛ اگر جواب فقط گفت «`COALESCE` بگذار»، مسئله را نفهمیده است.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| bucket / bin | باکت / بین | سطل؛ بازه‌ای که مقدارها در آن شمرده می‌شوند |
| histogram | هیستوگرام | نمودارِ شمارشِ سطل‌ها روی یک محورِ عددی |
| bucket width | باکت ویدث | عرضِ سطل |
| distribution | دیستریبیوشن | توزیع؛ اینکه مقدارها چطور پخش شده‌اند |
| percentile | پرسنتایل | صدک؛ مقداری که سهمِ مشخصی از داده از آن کمترند |
| bimodal | بای‌مودال | دوقله‌ای؛ توزیعی که دو تجمعِ جدا دارد |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا سطلِ آخرِ نمایشِ بیست‌تایی فقط ۴۳ سطر داشت؟

**تمرینِ اجباری:** هیستوگرامِ `pages` را با دو عرضِ متفاوت بگیر — یکی صدتایی و یکی پنجاه‌تایی — و **هر دو را با همان تابعِ نواری چاپ کن**. بعد سه چیز را بنویس: جمعِ هر دو، کمینه و بیشینهٔ واقعیِ ستون، و اینکه کدام سطل‌ها ناقص‌اند. **جوابِ سؤالِ اصلی: آیا شکلی که می‌بینی با هر دو عرض یکی است؟** اگر بود، این یک نتیجهٔ معتبر است و باید بنویسی که یعنی توزیعِ `pages` با توزیعِ `borrowed` فرق دارد.

**تمرینِ ستاره‌دار ⭐:** برای هر شعبه، صدکِ ۹۰ امانت را حساب کن. **دقت کن که `OFFSET` برای هر شعبه فرق می‌کند، چون تعدادِ کتاب‌هایشان یکی نیست.** بعد سؤالِ سخت را جواب بده: **آیا شعبه‌ای که بالاترین میانگین را دارد، بالاترین صدکِ ۹۰ را هم دارد؟** اگر نه، توضیح بده کدام‌یک از این دو به پرسشِ «کدام شعبه کتاب‌های پرطرفدارتری دارد» نزدیک‌تر است و چرا.

## در فصل بعد

تا اینجا هر بار که تجمیع گرفتیم، سطرهای اصلی را از دست دادیم: ۱٬۲۰۰ سطر می‌رفت و ۶ یا ۸ سطر برمی‌گشت. فصلِ بعد ابزاری می‌آورد که **هر دو را با هم دارد** — هر ۱٬۲۰۰ سطر سرِ جایشان می‌مانند و کنارِ هرکدام یک عددِ گروهی می‌نشیند. **مثلاً کنارِ هر کتاب، میانگینِ دستهٔ خودش، و سهمش از کلِ امانت‌های آن دسته — در یک پرسش و بدونِ اینکه سطری گم شود.**

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t2/s05-buckets-and-distribution (یادستان, Yaadestan)
