در این فصل چه یاد میگیری#
در این کتابخانه پنج کتاب دقیقاً ۱۹۰ بار امانت رفتهاند — همان پنجتایی که ترمِ ۱ فصلِ ۴ پیدایشان کرد و نشان داد پرسشِ «سه کتابِ پرامانت» جوابِ یکتا ندارد.
حالا سه تابعِ رتبهبندی روی همان پنج سطر میگذاریم و سه جوابِ کاملاً متفاوت میگیریم: یکی به آنها ۱ تا ۵ میدهد، یکی به هر پنجتا ۱ میدهد و سطرِ بعدی را ۶ میکند، و یکی به هر پنجتا ۱ میدهد و سطرِ بعدی را ۲.
هیچکدام غلط نیست. هر سه جوابِ سه پرسشِ متفاوتاند، و اگر ندانی کدام را میپرسی، رتبهبندیات یک عددِ دلبخواهی است.

آخر این فصل میتوانی:
- داخلِ
OVERترتیب بگذاری و بگویی چه چیزی را عوض میکند - تفاوتِ
ROW_NUMBERوRANKوDENSE_RANKرا روی تساوی نشان بدهی - «
Nتای اولِ هر گروه» را در یک پرسش بگیری - با
LAGوLEADهر سطر را با سطرِ قبل و بعدش مقایسه کنی - تغییرِ دورهبهدوره بسازی و بگویی کدام سطرش قابلِاعتماد نیست
قبل از شروع#
از فصلِ ۶: OVER () و PARTITION BY، و اینکه تابعِ پنجره بعد از WHERE اجرا میشود و در WHERE در دسترس نیست.
از ترمِ ۱ فصلِ ۴: بدونِ یک ستونِ یکتا در ORDER BY، «سه تای اول» جوابِ یکتا ندارد. این فصل همان درس را در سطحِ رتبه تکرار میکند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. ORDER BY داخلِ OVER#
در فصلِ ۶ پنجرهها بیترتیب بودند: یک عدد برای کلِ گروه، و اینکه سطرها به چه ترتیبی داخلش نشستهاند اهمیتی نداشت.
ORDER BY داخلِ OVER همین را عوض میکند: سطرهای هر پنجره را مرتب میکند، و از آن به بعد میشود پرسید «این سطر چندمین است».
دقت کن که این ORDER BY داخلِ پرانتزِ OVER است و ربطی به ORDER BY آخرِ پرسش ندارد. اولی ترتیبِ محاسبه را میگوید، دومی ترتیبِ نمایش را.
print(q("""
SELECT id,
borrowed,
ROW_NUMBER() OVER (ORDER BY borrowed DESC, id) AS rn,
RANK() OVER (ORDER BY borrowed DESC) AS rk,
DENSE_RANK() OVER (ORDER BY borrowed DESC) AS dr
FROM books
ORDER BY borrowed DESC, id
LIMIT 7
"""))
id borrowed rn rk dr
0 46 190 1 1 1
1 448 190 2 1 1
2 601 190 3 1 1
3 1056 190 4 1 1
4 1169 190 5 1 1
5 138 189 6 6 2
6 392 189 7 6 2
پنج سطرِ اول همه borrowed برابر دارند، و سه ستونِ آخر سه چیزِ متفاوت میگویند.
ROW_NUMBERیک شمارهگذاریِ ساده است: ۱، ۲، ۳، ۴، ۵. هیچوقت تکراری نمیدهد، حتی وقتی سطرها یکسان باشند.RANKبه هر پنجتا رتبهٔ ۱ میدهد، بعد میپرد به ۶ — چون پنج نفر جلوتر از سطرِ ششم بودهاند.DENSE_RANKهم به هر پنجتا ۱ میدهد، ولی نمیپرد: سطرِ بعدی ۲ میشود، چون دومین مقدارِ متفاوت است.
سه پرسشِ متفاوت، سه جواب: «چندمین سطر است؟»، «چند نفر جلوترند؟»، «چندمین مقدارِ متفاوت است؟»
۲. کدام را کِی، و تلهٔ ROW_NUMBER#
اگر میخواهی هر سطر یک شمارهٔ یکتا بگیرد — مثلاً برای صفحهبندی — ROW_NUMBER. اگر میخواهی یک رتبهٔ منصفانه بدهی که تساوی را به رسمیت بشناسد، RANK یا DENSE_RANK.
و اینجا تلهٔ اصلی است: ROW_NUMBER روی سطرهای مساوی، ترتیب را خودش انتخاب میکند.
without_key = q("""
SELECT id FROM books
ORDER BY borrowed DESC
LIMIT 5
""")
with_key = q("""
SELECT id, ROW_NUMBER() OVER (ORDER BY borrowed DESC, id) AS rn
FROM books
ORDER BY rn
LIMIT 5
""")
print("پنج سطر اول، بدون کلید یکتا:", list(without_key["id"]))
print("پنج سطر اول، با کلید یکتا :", list(with_key["id"]))
print("چند کتاب دقیقاً ۱۹۰ بار رفتهاند؟",
int(q("SELECT COUNT(*) FROM books WHERE borrowed = ?", (190,)).iloc[0, 0]))
پنج سطر اول، بدون کلید یکتا: [46, 448, 601, 1056, 1169]
پنج سطر اول، با کلید یکتا : [46, 448, 601, 1056, 1169]
چند کتاب دقیقاً ۱۹۰ بار رفتهاند؟ 5
در این اجرا هر دو یک جواب دادند — و این یک نتیجهٔ منفی است که باید صادقانه نوشته شود.
آنچه ثابت شد این است که SQLite در این اجرا همان ترتیب را انتخاب کرد. آنچه ثابت نشد این است که مجبور بوده. پنج کاندید برای پنج جای اول هست و هر جایگشتی از آنها بهاندازهٔ همین یکی درست است.
قاعده همان قاعدهٔ ترمِ ۱ است: هر جا ORDER BY داخلِ OVER مینویسی، آنقدر ستون اضافه کن که ترتیب یکتا شود. , id هیچ هزینهای ندارد و ابهام را کامل حذف میکند.
۳. «N تای اولِ هر گروه»#
این پرکاربردترین کاربردِ رتبه است: دو کتابِ پرامانتِ هر دسته. با GROUP BY نمیشود گرفتش — فصلِ ۲ نشان داد که ستونِ گروهنشده مقدارش دلبخواهی است — ولی با PARTITION BY بهاضافهٔ ORDER BY مستقیم درمیآید:
ranked = q("""
SELECT category,
title,
borrowed,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY borrowed DESC, id) AS rn
FROM books
ORDER BY category, rn
""")
print(ranked.head(6))
print("سطرهای خروجی:", len(ranked))
category title borrowed rn
0 تاریخ برف پشت چراغ 185 1
1 تاریخ بازگشت به جنگل 184 2
2 تاریخ چراغ در باران 181 3
3 تاریخ بازگشت به باد 180 4
4 تاریخ روزهای باد 175 5
5 تاریخ ستاره و دریا 174 6
سطرهای خروجی: 1200
رتبهها داخلِ هر دسته از یک شروع میشوند، و خروجی هنوز هر ۱٬۲۰۰ سطر را دارد.
حالا فقط سطرهای با رتبهٔ ۱ و ۲ را میخواهیم. اولین چیزی که به ذهن میرسد یک WHERE است، و فصلِ ۶ توضیح داد چرا کار نمیکند:
🔧 اگر کار نکرد: پیامِ خطا این بار خیلی صریح است و کلمهٔ «نامِ مستعار» هم در آن هست:
try:
q("""
SELECT id, ROW_NUMBER() OVER (ORDER BY borrowed DESC) AS rn
FROM books
WHERE rn <= ?
""", (3,))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql '
SELECT id, ROW_NUMBER() OVER (ORDER BY borrowed DESC) AS rn
FROM books
WHERE rn <= ?
': misuse of aliased window function rn
WHERE قبل از پنجرهها اجرا میشود، پس وقتی WHERE میخواهد rn را بخواند هنوز چیزی به آن نام وجود ندارد.
راهِحل این است که پرسش را دو مرحله کنی: پرسشِ رتبهبندی را داخلِ پرانتز بگذار و انگار یک جدول باشد، بعد روی همان جدول WHERE بزن.
print(q("""
SELECT category, title, borrowed, rn
FROM (
SELECT category,
title,
borrowed,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY borrowed DESC, id) AS rn
FROM books
)
WHERE rn <= ?
ORDER BY category, rn
""", (2,)))
category title borrowed rn
0 تاریخ برف پشت چراغ 185 1
1 تاریخ بازگشت به جنگل 184 2
2 رمان بازگشت به دریا 190 1
3 رمان در جستوجوی رود 189 2
4 زندگینامه ابر پشت چراغ 190 1
5 زندگینامه روزهای پنجره 189 2
6 شعر در جستوجوی آتش 188 1
7 شعر ابر پشت خانه 186 2
8 علم پنجره و رود 190 1
9 علم بازگشت به ستاره 189 2
10 فلسفه بازگشت به خانه 190 1
11 فلسفه در جستوجوی ابر 189 2
12 هنر بازگشت به پنجره 190 1
13 هنر کوچه پشت آینه 188 2
14 کودک در جستوجوی جنگل 188 1
15 کودک خانه پشت خاک 181 2
شانزده سطر: دو کتاب برای هر یک از هشت دسته.
آن ( ... ) توی FROM یک زیرپرسش است — پرسشی که نتیجهاش مثلِ یک جدولِ موقت رفتار میکند. فعلاً همینقدر بدان که هر پرسشی میتواند جای یک جدول در FROM بنشیند؛ ترمِ ۳ فصلِ ۶ کلِ خانوادهٔ زیرپرسشها را باز میکند و میگوید کِی گران میشوند و راهِ خواناترش چیست.
✅ چک کن: خروجی باید دقیقاً ۱۶ سطر باشد: ۸ دسته ضربدر ۲. و یک کنترلِ دوم که مهمتر است: هیچ دستهای نباید بیشتر از دو سطر داشته باشد. اگر داشت،
ROW_NUMBERرا باRANKعوض کردهای — وRANKروی تساوی به چند سطر یک رتبه میدهد، پسrn <= 2میتواند سه سطر یا بیشتر برگرداند. کدامش را میخواهی؟ اگر «دقیقاً دو سطر» میخواهیROW_NUMBER، اگر «همهٔ کسانی که در دو رتبهٔ اولاند» میخواهیRANK.
۴. LAG و LEAD: نگاه به سطرِ قبل و بعد#
LAG(x) مقدارِ x را از سطرِ قبلیِ همان پنجره میآورد و LEAD(x) از سطرِ بعدی. ترتیبِ «قبل» و «بعد» را همان ORDER BY داخلِ OVER تعیین میکند.
و چون فصلِ ۶ نشان داد پنجره روی نتیجهٔ GROUP BY مینشیند، میشود این را مستقیم روی یک جدولِ گروهی گذاشت:
print(q("""
SELECT strftime('%Y', added_at) AS added_year,
COUNT(*) AS n,
LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)) AS previous_year,
LEAD(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)) AS next_year
FROM books
GROUP BY added_year
ORDER BY added_year
"""))
added_year n previous_year next_year
0 2019 193 NaN 206.0
1 2020 206 193.0 166.0
2 2021 166 206.0 165.0
3 2022 165 166.0 188.0
4 2023 188 165.0 178.0
5 2024 178 188.0 104.0
6 2025 104 178.0 NaN
سطرِ اول previous_year ندارد و سطرِ آخر next_year ندارد — هر دو NaN شدهاند، یعنی NULL.
این کاملاً درست است: سطرِ اول قبلی ندارد، پس مقدارش «نامعلوم» است، نه صفر. و از ترمِ ۱ فصلِ ۶ میدانی که NULL در هر محاسبهای بعدش میماند و در هر تجمیعی بیصدا کنار میرود.
پس هر ستونی که با LAG میسازی، دستِکم یک سطرِ NULL دارد و باید تصمیم بگیری با آن چه میکنی — نشانش بدهی، حذفش کنی، یا با COALESCE جایگزینش کنی. هر سه انتخاباند و باید در گزارش نوشته شوند؛ فقط یکی از آنها غلط است: وانمود کردن به اینکه صفر بوده.
۵. تغییرِ دورهبهدوره#
فصلِ ۴ تغییرِ سالبهسال را در پایتون حساب کرد و قول داد ابزارِ SQLیاش را اینجا بدهد. حالا کلِ محاسبه در یک پرسش جا میشود:
print(q("""
SELECT strftime('%Y', added_at) AS added_year,
COUNT(*) AS n,
COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)) AS change,
ROUND(
(COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at))) * 100.0
/ LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)), 2
) AS pct
FROM books
GROUP BY added_year
ORDER BY added_year
"""))
added_year n change pct
0 2019 193 NaN NaN
1 2020 206 13.0 6.74
2 2021 166 -40.0 -19.42
3 2022 165 -1.0 -0.60
4 2023 188 23.0 13.94
5 2024 178 -10.0 -5.32
6 2025 104 -74.0 -41.57
همان شش عددِ فصلِ ۴، این بار بدونِ یک خط پایتون.
سه چیز در همین جدول هست که باید دیده شوند:
* 100.0سرِ جایش است. بدونش همان تقسیمِ صحیحِ فصلِ ۴ برمیگشت و ستونِ درصد پر از صفر میشد.- سطرِ اول
NULLاست و همان درست است. سالِ اول قبلی ندارد. - سطرِ آخر یک عددِ درستمحاسبهشده و کاملاً گمراهکننده است. آن سال ناقص است — این پایگاه داده تا اواسطش بیشتر ندارد — و مقایسهاش با یک سالِ کامل مثلِ مقایسهٔ شش ماه با دوازده ماه است.
و هیچچیزی در خروجی این را نمیگوید. تنها راهِ فهمیدنش این است که خودت لبهٔ داده را نگاه کرده باشی:
print(q("""
SELECT MIN(added_at) AS first_day,
MAX(added_at) AS last_day
FROM books
"""))
first_day last_day
0 2019-01-01 2025-06-30
داده وسطِ سال تمام میشود. پس آن ۴۱ درصد کاهش، سنجشِ نصفِ یک سال در برابرِ یک سالِ کامل است.
قاعدهٔ عملی: هر بار که یک ستونِ تغییرِ دورهبهدوره میسازی، اولین کارت این باشد که کمینه و بیشینهٔ ستونِ تاریخ را چاپ کنی — و اگر دورهٔ آخر ناقص است، یا از جدول بیرونش بگذار یا صریح علامتش بزن. این اشتباه هیچ پیامِ خطایی ندارد و بیشترین هزینهٔ این فصل را در گزارشهای واقعی داده است.
📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشهای ۱ تا ۳ یک سطر یک کتاب است و رتبهاش داخلِ دستهٔ خودش معنا دارد، نه در کلِ کتابخانه. در بخشهای ۴ و ۵ یک سطر یک سال است. با کدام شمارشِ دوم سنجیدی؟ سهتا: تعدادِ کتابهای مساوی در صدر (۵) که توضیح داد چرا سه تابعِ رتبه سه جواب دادند؛ شمارشِ سطرهای «دو تای اولِ هر دسته» که باید دقیقاً ۱۶ باشد؛ و کمینه و بیشینهٔ
added_atکه ناقص بودنِ سالِ آخر را ثابت کرد. چه کسی از قلم افتاد؟ سطرِ اولِ هر ستونِLAGکهNULLاست و در هر تجمیعی بعدش شمرده نمیشود؛ نیمهٔ دومِ سالِ آخر که اتفاق نیفتاده ولی در جدول مثلِ یک سقوط دیده میشود؛ و همچنان ۲۴۰ سطرِarrivals.csv.
🤖 از دستیارت بپرس: «فرقِ
RANKوDENSE_RANKدرSQLچیست و هرکدام جوابِ چه پرسشیاند؟» بعد این را بپرس: «اگر بخواهم دقیقاً سه سطرِ اولِ هر گروه را بگیرم و در گروهی چهار سطر مساوی باشند،RANKچند سطر برمیگرداند؟» — جوابِ درست «چهارتا» است و باید توضیح بدهد چرا؛ اگر دستیارت گفت «سهتا»، همین حالا با پنج کتابِ ۱۹۰تایی امتحانش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| ROW_NUMBER | رو نامبر | شمارهٔ یکتای هر سطر داخلِ پنجره |
| RANK | رنک | رتبه با تساوی و با پرش |
| DENSE_RANK | دنس رنک | رتبه با تساوی و بدونِ پرش |
| LAG / LEAD | لگ / لید | مقدارِ سطرِ قبلی / بعدیِ همان پنجره |
| tie | تای | تساوی؛ چند سطر با مقدارِ یکسان |
| period over period | پریود اوور پریود | مقایسهٔ هر دوره با دورهٔ قبلش |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
تا اینجا پنجره یا کلِ گروه بود یا مقایسه با یک سطرِ همسایه. فصلِ بعد قابِ پنجره را باز میکند: میشود گفت «از اولِ گروه تا همین سطر» یا «سه سطرِ آخر». با همان ابزار، جمعِ رونده و میانگینِ متحرک ساخته میشوند — و بلافاصله معلوم میشود که قابِ پیشفرض همان چیزی نیست که فکر میکردی: همان پنج کتابِ ۱۹۰تایی با قابِ پیشفرض هر پنجتا عددِ ۵ میگیرند و با قابِ سطری ۱ تا ۵.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.