# فصل ۰۴ — مرتب‌سازی، یکتا، صفحه‌بندی

<div dir="rtl">

# فصل ۰۴ — مرتب‌سازی، یکتا، صفحه‌بندی

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

پرسشِ «فهرستِ نویسنده‌های این کتابخانه» جوابِ **۱۲۳** می‌دهد. نویسنده‌های واقعی **۱۲۰** نفرند. سه نفرشان دو بار شمرده شده‌اند، و دلیلش این است که نامشان در بعضی سطرها با صفحه‌کلیدِ عربی وارد شده — `ي` به‌جای `ی` و `ك` به‌جای `ک`. برای `SQLite` این‌ها دو حرفِ کاملاً متفاوت‌اند.

و یک عیبِ دومِ همان جنس: وقتی دسته‌ها را مرتب می‌کنی، **«هنر» قبل از «کودک» می‌آید**. در الفبای فارسی «کودک» جلوتر است. `SQLite` الفبای فارسی را نمی‌شناسد و ادعایش را هم نمی‌کند.

![دو فهرستِ کارتیِ یکسان روی یک میز که یکی از کارت‌ها در هرکدام جای متفاوتی گذاشته شده](/lesson-assets/t1-s04-order-distinct/prompt/01-image-two-card-files.png)

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

- نتیجه را با `ORDER BY` روی چند ستون مرتب کنی
- بگویی چرا «سه تای اول» تا وقتی ترتیب یکتا نباشد یک پرسشِ بی‌جواب است
- با `LIMIT` و `OFFSET` صفحه‌بندی کنی، و بدانی کجا صفحه‌بندی می‌شکند
- با `DISTINCT` فهرستِ مقدارهای یکتا را بگیری
- بگویی مرتب‌سازیِ متنِ فارسی در `SQLite` دقیقاً چه چیزی است و چه چیزی نیست

## قبل از شروع

**از فصلِ ۳:** `WHERE` و پارامترِ `?`.

**یک یادآوریِ لازم از فصلِ ۲:** `LIMIT 5` پنج سطر می‌دهد، نه «پنج سطرِ اول». آن فصل قول داد این را باز کند؛ این فصل بازش می‌کند.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-1-reading-data/session-04-order-distinct.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

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

<div dir="rtl">

## ۱. `ORDER BY`: ترتیب را باید بخواهی

`ORDER BY` آخر از همه می‌آید — بعد از `WHERE`، قبل از `LIMIT`. پیش‌فرضش صعودی است (`ASC`) و برای نزولی `DESC` می‌نویسی.

</div>

```python
print(q("""
    SELECT id, title, branch, borrowed
    FROM books
    ORDER BY borrowed DESC
    LIMIT 5
"""))
```

```text
     id            title branch  borrowed
0    46   بازگشت به خانه   کوشا       190
1   448  بازگشت به پنجره   نسیم       190
2   601     ابر پشت چراغ   بهار       190
3  1056   بازگشت به دریا   بهار       190
4  1169      پنجره و رود   پویا       190
```

<div dir="rtl">

**به ستونِ آخر نگاه کن: هر پنج سطر عددِ یکسان دارند.**

## ۲. «سه تای اول» وقتی تساوی هست، یک پرسشِ بی‌جواب است

اگر همین پرسش را با `LIMIT 3` بزنی، `SQLite` باید سه‌تا از پنج کتابِ هم‌امتیاز را انتخاب کند. **هیچ چیزی در پرسشِ تو نمی‌گوید کدام سه‌تا.**

</div>

```python
tied = int(q("SELECT COUNT(*) FROM books WHERE borrowed = ?", (190,)).iloc[0, 0])
answer_a = list(q("SELECT id FROM books ORDER BY borrowed DESC LIMIT 3")["id"])
answer_b = list(q("SELECT id FROM books ORDER BY borrowed DESC, id DESC LIMIT 3")["id"])

print("کتاب‌هایی که دقیقاً ۱۹۰ بار امانت رفته‌اند:", tied)
print("«سه کتاب پرامانت» — پاسخ الف:", answer_a)
print("«سه کتاب پرامانت» — پاسخ ب  :", answer_b)
print("هر دو پاسخ درست‌اند؟", tied > 3)
```

```text
کتاب‌هایی که دقیقاً ۱۹۰ بار امانت رفته‌اند: 5
«سه کتاب پرامانت» — پاسخ الف: [46, 448, 601]
«سه کتاب پرامانت» — پاسخ ب  : [1169, 1056, 601]
هر دو پاسخ درست‌اند؟ True
```

<div dir="rtl">

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

قاعدهٔ سختی که از اینجا به بعد رعایتش می‌کنیم: **هر جا `LIMIT` روی نتیجهٔ مرتب‌شده می‌گذاری، ستونِ مرتب‌سازی را آن‌قدر ادامه بده که ترتیب یکتا شود** — معمولاً با اضافه کردنِ یک ستونِ شناسه در آخر. `ORDER BY borrowed DESC, id ASC` هیچ ابهامی ندارد.

> ⚠️ **مواظب باش:** بدترین شکلِ این مشکل وقتی است که هیچ `ORDER BY` ننویسی. آن‌وقت `SQLite` **هیچ تعهدی** به ترتیب ندارد و مجاز است هر بار ترتیبِ دیگری بدهد. در عمل معمولاً ترتیبِ ثابتی می‌دهد و همین خطرناک است: کدت ماه‌ها کار می‌کند و روزی که پایگاه داده تصمیم بگیرد پرسش را جورِ دیگری اجرا کند، بی‌صدا می‌شکند.

## ۳. مرتب‌سازیِ چندستونی

چند ستون را با کاما پشتِ هم می‌نویسی. هرکدام `ASC` یا `DESC`ِ خودش را دارد.

</div>

```python
print(q("""
    SELECT category, title, borrowed
    FROM books
    ORDER BY category ASC, borrowed DESC
    LIMIT 6
"""))
```

```text
  category           title  borrowed
0    تاریخ    برف پشت چراغ       185
1    تاریخ  بازگشت به جنگل       184
2    تاریخ   چراغ در باران       181
3    تاریخ   بازگشت به باد       180
4    تاریخ      روزهای باد       175
5    تاریخ    ستاره و دریا       174
```

<div dir="rtl">

اول بر اساسِ دسته صعودی، و **داخلِ هر دسته** بر اساسِ امانت نزولی. چون `LIMIT 6` گذاشتیم فقط اولین دسته را می‌بینیم — و همین‌جا یک نکتهٔ ظریف هست: **اینکه «تاریخ» اولین دسته درآمده، خودش نتیجهٔ همان مرتب‌سازیِ نویسه‌ای است که در بخشِ ۶ سراغش می‌رویم.**

## ۴. `LIMIT ... OFFSET`: صفحه‌بندی

`OFFSET n` یعنی «`n` سطرِ اول را رد کن». صفحهٔ اول `LIMIT 3` است، صفحهٔ دوم `LIMIT 3 OFFSET 3`.

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

</div>

```python
page_1 = list(q("SELECT id FROM books ORDER BY borrowed DESC LIMIT 3")["id"])
page_2 = list(q("SELECT id FROM books ORDER BY borrowed DESC LIMIT 3 OFFSET 3")["id"])
stable_1 = list(q("SELECT id FROM books ORDER BY borrowed DESC, id ASC LIMIT 3")["id"])
stable_2 = list(q("SELECT id FROM books ORDER BY borrowed DESC, id ASC LIMIT 3 OFFSET 3")["id"])

print("بدون کلید یکتا — صفحه ۱:", page_1, "| صفحه ۲:", page_2)
print("با کلید یکتا   — صفحه ۱:", stable_1, "| صفحه ۲:", stable_2)
print("سطر تکراری بین دو صفحه:", len(set(page_1) & set(page_2)))
```

```text
بدون کلید یکتا — صفحه ۱: [46, 448, 601] | صفحه ۲: [1056, 1169, 138]
با کلید یکتا   — صفحه ۱: [46, 448, 601] | صفحه ۲: [1056, 1169, 138]
سطر تکراری بین دو صفحه: 0
```

<div dir="rtl">

**نتیجهٔ این آزمایش منفی بود و همان‌قدر مهم است که مثبت می‌بود: روی این داده و در این اجرا، هر دو شکل یک جواب دادند و هیچ سطری تکرار نشد.**

پس آیا نگرانی بی‌مورد بود؟ نه، و دلیلش را دقیق بگویم. آنچه ثابت شد این است که `SQLite` **در این اجرا** انتخابِ یکسانی کرد. آنچه ثابت نشد این است که مجبور بوده. بخشِ ۲ همین حالا نشان داد که پنج کاندید برای سه جای اول هست؛ پس دو اجرای متفاوت **می‌توانند** دو انتخاب بکنند بدونِ اینکه هیچ‌کدام غلط باشد.

**قاعده‌ای که از این بیرون می‌آید: به رفتاری که مشاهده کرده‌ای ولی تضمین‌شده نیست، تکیه نکن.** نسخهٔ بعدیِ کتابخانه، یا حتی اضافه شدنِ یک سطر به جدول، می‌تواند تصمیمِ اجرا را عوض کند. هزینهٔ نوشتنِ `, id ASC` صفر است.

## ۵. `DISTINCT`: مقدارهای یکتا

`DISTINCT` بعد از `SELECT` می‌آید و سطرهای تکراریِ **خروجی** را یکی می‌کند.

</div>

```python
plain = list(q("SELECT DISTINCT category FROM books")["category"])
sorted_out = list(q("SELECT DISTINCT category FROM books ORDER BY category")["category"])

print("DISTINCT بدون ORDER BY:", " · ".join(plain))
print("DISTINCT با ORDER BY  :", " · ".join(sorted_out))
print("چند مقدار یکتا؟", len(plain))
```

```text
DISTINCT بدون ORDER BY: رمان · شعر · علم · تاریخ · زندگی‌نامه · فلسفه · کودک · هنر
DISTINCT با ORDER BY  : تاریخ · رمان · زندگی‌نامه · شعر · علم · فلسفه · هنر · کودک
چند مقدار یکتا؟ 8
```

<div dir="rtl">

**`DISTINCT` مرتب نمی‌کند.** خیلی‌ها فرض می‌کنند می‌کند، چون در بعضی پایگاه داده‌ها خروجی اتفاقاً مرتب درمی‌آید. اگر ترتیب می‌خواهی، `ORDER BY` بنویس.

## ۶. مرتب‌سازیِ فارسی: ترتیبی که ترتیبِ الفبا نیست

خطِ دومِ خروجیِ بالا را دوباره بخوان. آخرش این است: **… فلسفه · هنر · کودک**.

در الفبای فارسی ترتیب این است: … ف، ق، **ک**، گ، ل، م، ن، و، **ه**، ی. یعنی «کودک» باید **قبل از** «هنر» بیاید. `SQLite` برعکس داد.

این باگ نیست. `SQLite` متن را بر اساسِ **شمارهٔ یونیکدِ نویسه‌ها** مرتب می‌کند، و شمارهٔ حرف‌های فارسی با ترتیبِ الفبای فارسی نمی‌خواند:

</div>

```python
print("ک فارسی:", ord("ک"), "| ك عربی:", ord("ك"))
print("ی فارسی:", ord("ی"), "| ي عربی:", ord("ي"))
print("کدام کوچک‌تر است؟ عربی:", ord("ي") < ord("ی"))
print("ه:", ord("ه"), "| ک فارسی:", ord("ک"), "| «هنر» زودتر؟", ord("ه") < ord("ک"))
```

```text
ک فارسی: 1705 | ك عربی: 1603
ی فارسی: 1740 | ي عربی: 1610
کدام کوچک‌تر است؟ عربی: True
ه: 1607 | ک فارسی: 1705 | «هنر» زودتر؟ True
```

<div dir="rtl">

حرفِ «ه» شمارهٔ ۱۶۰۷ دارد و «ک» شمارهٔ ۱۷۰۵ — پس «هنر» زودتر می‌آید. **دلیلش چیدمانِ یونیکد است:** حرف‌های پایهٔ عربی در ابتدای بلوکِ عربی نشسته‌اند و شکل‌های فارسیِ `ک` و `ی` در محدودهٔ بالاتری از همان بلوک قرار گرفته‌اند.

**و خطِ سومِ همان خروجی، کلِ بخشِ بعد را توضیح می‌دهد:** `ي` عربی شمارهٔ کوچک‌تری از `ی` فارسی دارد، پس نامی که با نویسهٔ عربی نوشته شده در فهرستِ مرتب‌شده جای کاملاً دیگری می‌نشیند.

> 💡 **نکته:** راهِ درستِ مرتب‌سازیِ الفبایی فارسی در `SQLite` این است که خودت یک `collation` تعریف کنی و به موتور بدهی. این کار از حوصلهٔ این ترم بیرون است و صادقانه‌ترین جمله همین است: **تا وقتی چنین چیزی نساخته‌ای، فهرستِ مرتب‌شده‌ات مرتبِ یونیکد است، نه مرتبِ الفبای فارسی — و اگر آن فهرست را به کاربر نشان می‌دهی، باید بدانی.**

## ۷. ۱۲۳ نام برای ۱۲۰ نفر

حالا همان `DISTINCT` را روی ستونِ نویسنده بزنیم:

</div>

```python
names = list(q("SELECT DISTINCT author FROM books ORDER BY author")["author"])
normalised = {name.replace("ي", "ی").replace("ك", "ک") for name in names}

print("نام‌های یکتا در جدول            :", len(names))
print("بعد از یکسان کردن نویسه‌ها       :", len(normalised))
print("یعنی چند نفر دو بار شمرده شده‌اند:", len(names) - len(normalised))
```

```text
نام‌های یکتا در جدول            : 123
بعد از یکسان کردن نویسه‌ها       : 120
یعنی چند نفر دو بار شمرده شده‌اند: 3
```

<div dir="rtl">

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

</div>

```python
persian = int(q("SELECT COUNT(*) FROM books WHERE author = ?", ("مریم کریمی",)).iloc[0, 0])
arabic = int(q("SELECT COUNT(*) FROM books WHERE author = ?", ("مريم كريمي",)).iloc[0, 0])

print("author = «مریم کریمی» (ی و ک فارسی):", persian)
print("author = «مريم كريمي» (ي و ك عربی) :", arabic)
print("کتاب‌های واقعی این نویسنده         :", persian + arabic)
print()
print("جایگاه «مريم كريمي» در فهرست مرتب‌شده:", names.index("مريم كريمي"))
print("جایگاه «مریم کریمی» در فهرست مرتب‌شده:", names.index("مریم کریمی"))
print("چند نام دیگر بینشان نشسته‌اند       :",
      names.index("مریم کریمی") - names.index("مريم كريمي") - 1)
```

```text
author = «مریم کریمی» (ی و ک فارسی): 3
author = «مريم كريمي» (ي و ك عربی) : 6
کتاب‌های واقعی این نویسنده         : 9

جایگاه «مريم كريمي» در فهرست مرتب‌شده: 89
جایگاه «مریم کریمی» در فهرست مرتب‌شده: 95
چند نام دیگر بینشان نشسته‌اند       : 5
```

<div dir="rtl">

**سه در برابرِ نُه.** اگر گزارشت بپرسد «این نویسنده چند کتاب در فهرست دارد؟» و تو نامش را با صفحه‌کلیدِ فارسی تایپ کنی، جوابِ ۳ می‌گیری. عددِ درست ۹ است. **و هیچ خطایی نمی‌گیری.**

و در فهرستِ مرتب‌شده، آن دو نام **پنج نامِ دیگر** فاصله دارند — یعنی حتی اگر فهرست را با چشم مرور کنی، به‌سادگی متوجه نمی‌شوی که یک نفرند.

> ✅ **چک کن:** ۳ + ۶ باید بشود ۹، و ۱۲۳ منهای ۱۲۰ باید بشود ۳. اگر عددهایت فرق کرد، `replace` را روی هر دو نویسه زده‌ای یا فقط یکی؟ هر دو لازم‌اند: هم `ي` و هم `ك`.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در `SELECT DISTINCT author` یک سطر یعنی «یک رشتهٔ متفاوت در ستونِ نویسنده» — و دقیقاً همین‌جاست که فریب می‌خوری، چون تو فکر می‌کنی یعنی «یک نویسنده». **با کدام شمارشِ دوم سنجیدی؟** با یکسان‌سازیِ نویسه‌ها در پایتون: ۱۲۳ در برابرِ ۱۲۰. و در سطحِ یک نفر: ۳ + ۶ = ۹. **چه کسی از قلم افتاد؟** ۶ کتاب از ۹ کتابِ آن نویسنده، در هر پرسشی که نامش را با نویسهٔ فارسی می‌نویسد. و همچنان: ۲۴۰ سطرِ `arrivals.csv`، و ۲۳۱ سطری که `rating` ندارند.

> 🔧 **اگر کار نکرد:** غلطِ تایپی در نامِ ستونِ `ORDER BY` همان‌طور خطا می‌دهد که در `SELECT`:

</div>

```python
try:
    q("SELECT title FROM books ORDER BY borrowd DESC LIMIT 3")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql 'SELECT title FROM books ORDER BY borrowd DESC LIMIT 3': no such column: borrowd
```

<div dir="rtl">

**ولی خطای واقعاً خطرناکِ این فصل پیام ندارد:** `ORDER BY` روی ستونی که تساوی دارد، بدونِ کلیدِ یکتا. آن یکی هیچ‌وقت پیام نمی‌دهد و در بخشِ ۲ دیدی که چه بلایی سرِ جواب می‌آورد.

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

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| ORDER BY | اوردر بای | بندی که ترتیبِ سطرهای خروجی را تعیین می‌کند |
| ASC / DESC | اسندینگ / دیسندینگ | صعودی / نزولی |
| DISTINCT | دیستینکت | حذفِ سطرهای تکراری از خروجی |
| OFFSET | آفست | چند سطرِ اول رد شود |
| tie-breaker | تای بریکر | ستونی که تساوی را می‌شکند و ترتیب را یکتا می‌کند |
| collation | کولیشن | قاعدهٔ مقایسه و مرتب‌سازیِ متن |
| code point | کد پوینت | شمارهٔ یکتای هر نویسه در یونیکد |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا «هنر» قبل از «کودک» آمد؟

**تمرینِ اجباری:** پنج کتابِ کم‌امانت‌ترین را بگیر (`ORDER BY borrowed ASC LIMIT 5`). بعد **بشمار چند کتاب دقیقاً همان کمترین مقدار را دارند** و جواب بده: آیا پرسشِ «پنج کتابِ کم‌امانت‌ترین» روی این جدول جوابِ یکتا دارد؟ بعد همان پرسش را با یک کلیدِ یکتا بازنویسی کن و **هر دو خروجی را کنارِ هم چاپ کن**.

**تمرینِ ستاره‌دار ⭐:** فهرستِ یکتای شعبه‌ها را با `DISTINCT` بگیر و با `ORDER BY` مرتب کن. بعد **همان فهرست را در پایتون با `sorted()` مرتب کن و دو خروجی را مقایسه کن.** یکی‌اند؟ حالا سؤالِ سخت: `sorted()`ِ پایتون هم بر اساسِ شمارهٔ یونیکد مرتب می‌کند. **اگر بخواهی ترتیبِ درستِ الفبای فارسی را بگیری، باید چه چیزی به `sorted()` بدهی؟** جوابت را بنویس و امتحانش کن — و اگر جواب داد، بنویس چرا این راه‌حل در `SQL` به این سادگی در دسترس نیست.

## در فصل بعد

سه فصل است داریم می‌گوییم «ستونِ `price_text` متن است، نه عدد» و رد می‌شویم. فصلِ بعد سراغش می‌رود و نشان می‌دهد مقایسهٔ متنی چه بلایی سرِ جواب می‌آورد: پرسشِ «کتاب‌های گران‌تر از ۵۰۰٬۰۰۰» با مقایسهٔ مستقیم **۳۸۴** کتاب می‌دهد و با تبدیلِ نوع **۲۰۲** کتاب. و همان‌جا با `replace` همان سه نویسنده را در `SQL` سرِ جایشان برمی‌گردانیم و ۱۲۳ دوباره ۱۲۰ می‌شود.

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t1/s04-order-distinct (یادستان, Yaadestan)
