مرتب‌سازی، یکتا، صفحه‌بندی

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

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

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

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

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

قبل از شروع#

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

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

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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

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)

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

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

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

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

در فصل بعد#

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