# فصل ۰۷ — رتبه و «`N` تای اولِ هر گروه»

<div dir="rtl">

# فصل ۰۷ — رتبه و «`N` تای اولِ هر گروه»

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

در این کتابخانه **پنج کتاب دقیقاً ۱۹۰ بار امانت رفته‌اند** — همان پنج‌تایی که ترمِ ۱ فصلِ ۴ پیدایشان کرد و نشان داد پرسشِ «سه کتابِ پرامانت» جوابِ یکتا ندارد.

حالا سه تابعِ رتبه‌بندی روی همان پنج سطر می‌گذاریم و **سه جوابِ کاملاً متفاوت** می‌گیریم: یکی به آن‌ها ۱ تا ۵ می‌دهد، یکی به هر پنج‌تا ۱ می‌دهد و سطرِ بعدی را ۶ می‌کند، و یکی به هر پنج‌تا ۱ می‌دهد و سطرِ بعدی را ۲.

هیچ‌کدام غلط نیست. **هر سه جوابِ سه پرسشِ متفاوت‌اند، و اگر ندانی کدام را می‌پرسی، رتبه‌بندی‌ات یک عددِ دلبخواهی است.**

![یک ردیف میلهٔ اندازه‌گیری که پنج‌تای اولش دقیقاً هم‌طول‌اند، کنارِ سه ریلِ شیاردار با فاصلهٔ شیارهای متفاوت](/lesson-assets/t2-s07-window-ranking/prompt/01-image-equal-rods-three-rails.png)

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

- داخلِ `OVER` ترتیب بگذاری و بگویی چه چیزی را عوض می‌کند
- تفاوتِ `ROW_NUMBER` و `RANK` و `DENSE_RANK` را روی تساوی نشان بدهی
- «`N` تای اولِ هر گروه» را در یک پرسش بگیری
- با `LAG` و `LEAD` هر سطر را با سطرِ قبل و بعدش مقایسه کنی
- تغییرِ دوره‌به‌دوره بسازی و بگویی کدام سطرش قابلِ‌اعتماد نیست

## قبل از شروع

**از فصلِ ۶:** `OVER ()` و `PARTITION BY`، و اینکه تابعِ پنجره بعد از `WHERE` اجرا می‌شود و در `WHERE` در دسترس نیست.

**از ترمِ ۱ فصلِ ۴:** بدونِ یک ستونِ یکتا در `ORDER BY`، «سه تای اول» جوابِ یکتا ندارد. این فصل همان درس را در سطحِ رتبه تکرار می‌کند.

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

</div>

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

<div dir="rtl">

## ۱. `ORDER BY` داخلِ `OVER`

در فصلِ ۶ پنجره‌ها بی‌ترتیب بودند: یک عدد برای کلِ گروه، و اینکه سطرها به چه ترتیبی داخلش نشسته‌اند اهمیتی نداشت.

**`ORDER BY` داخلِ `OVER` همین را عوض می‌کند: سطرهای هر پنجره را مرتب می‌کند، و از آن به بعد می‌شود پرسید «این سطر چندمین است».**

دقت کن که این `ORDER BY` **داخلِ پرانتزِ `OVER`** است و ربطی به `ORDER BY` آخرِ پرسش ندارد. اولی ترتیبِ محاسبه را می‌گوید، دومی ترتیبِ نمایش را.

</div>

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

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

<div dir="rtl">

**پنج سطرِ اول همه `borrowed` برابر دارند، و سه ستونِ آخر سه چیزِ متفاوت می‌گویند.**

- **`ROW_NUMBER`** یک شماره‌گذاریِ ساده است: ۱، ۲، ۳، ۴، ۵. هیچ‌وقت تکراری نمی‌دهد، حتی وقتی سطرها یکسان باشند.
- **`RANK`** به هر پنج‌تا رتبهٔ ۱ می‌دهد، بعد **می‌پرد** به ۶ — چون پنج نفر جلوتر از سطرِ ششم بوده‌اند.
- **`DENSE_RANK`** هم به هر پنج‌تا ۱ می‌دهد، ولی نمی‌پرد: سطرِ بعدی ۲ می‌شود، چون دومین **مقدارِ متفاوت** است.

**سه پرسشِ متفاوت، سه جواب:** «چندمین سطر است؟»، «چند نفر جلوترند؟»، «چندمین مقدارِ متفاوت است؟»

## ۲. کدام را کِی، و تلهٔ `ROW_NUMBER`

اگر می‌خواهی هر سطر یک شمارهٔ یکتا بگیرد — مثلاً برای صفحه‌بندی — `ROW_NUMBER`. اگر می‌خواهی یک **رتبهٔ منصفانه** بدهی که تساوی را به رسمیت بشناسد، `RANK` یا `DENSE_RANK`.

**و اینجا تلهٔ اصلی است: `ROW_NUMBER` روی سطرهای مساوی، ترتیب را خودش انتخاب می‌کند.**

</div>

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

```text
پنج سطر اول، بدون کلید یکتا: [46, 448, 601, 1056, 1169]
پنج سطر اول، با کلید یکتا  : [46, 448, 601, 1056, 1169]
چند کتاب دقیقاً ۱۹۰ بار رفته‌اند؟ 5
```

<div dir="rtl">

**در این اجرا هر دو یک جواب دادند — و این یک نتیجهٔ منفی است که باید صادقانه نوشته شود.**

آنچه ثابت شد این است که `SQLite` **در این اجرا** همان ترتیب را انتخاب کرد. آنچه ثابت **نشد** این است که مجبور بوده. پنج کاندید برای پنج جای اول هست و هر جایگشتی از آن‌ها به‌اندازهٔ همین یکی درست است.

**قاعده همان قاعدهٔ ترمِ ۱ است: هر جا `ORDER BY` داخلِ `OVER` می‌نویسی، آن‌قدر ستون اضافه کن که ترتیب یکتا شود.** `, id` هیچ هزینه‌ای ندارد و ابهام را کامل حذف می‌کند.

## ۳. «`N` تای اولِ هر گروه»

این پرکاربردترین کاربردِ رتبه است: **دو کتابِ پرامانتِ هر دسته.** با `GROUP BY` نمی‌شود گرفتش — فصلِ ۲ نشان داد که ستونِ گروه‌نشده مقدارش دلبخواهی است — ولی با `PARTITION BY` به‌اضافهٔ `ORDER BY` مستقیم درمی‌آید:

</div>

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

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

<div dir="rtl">

**رتبه‌ها داخلِ هر دسته از یک شروع می‌شوند، و خروجی هنوز هر ۱٬۲۰۰ سطر را دارد.**

حالا فقط سطرهای با رتبهٔ ۱ و ۲ را می‌خواهیم. اولین چیزی که به ذهن می‌رسد یک `WHERE` است، و فصلِ ۶ توضیح داد چرا کار نمی‌کند:

> 🔧 **اگر کار نکرد:** پیامِ خطا این بار خیلی صریح است و کلمهٔ «نامِ مستعار» هم در آن هست:

</div>

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

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

<div dir="rtl">

`WHERE` قبل از پنجره‌ها اجرا می‌شود، پس وقتی `WHERE` می‌خواهد `rn` را بخواند هنوز چیزی به آن نام وجود ندارد.

**راهِ‌حل این است که پرسش را دو مرحله کنی:** پرسشِ رتبه‌بندی را داخلِ پرانتز بگذار و انگار یک جدول باشد، بعد روی همان جدول `WHERE` بزن.

</div>

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

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

<div dir="rtl">

**شانزده سطر: دو کتاب برای هر یک از هشت دسته.**

آن `( ... )` توی `FROM` یک **زیرپرسش** است — پرسشی که نتیجه‌اش مثلِ یک جدولِ موقت رفتار می‌کند. **فعلاً همین‌قدر بدان که هر پرسشی می‌تواند جای یک جدول در `FROM` بنشیند؛ ترمِ ۳ فصلِ ۶ کلِ خانوادهٔ زیرپرسش‌ها را باز می‌کند** و می‌گوید کِی گران می‌شوند و راهِ خواناترش چیست.

> ✅ **چک کن:** خروجی باید دقیقاً ۱۶ سطر باشد: ۸ دسته ضربدر ۲. **و یک کنترلِ دوم که مهم‌تر است: هیچ دسته‌ای نباید بیشتر از دو سطر داشته باشد.** اگر داشت، `ROW_NUMBER` را با `RANK` عوض کرده‌ای — و `RANK` روی تساوی به چند سطر یک رتبه می‌دهد، پس `rn <= 2` می‌تواند سه سطر یا بیشتر برگرداند. **کدامش را می‌خواهی؟ اگر «دقیقاً دو سطر» می‌خواهی `ROW_NUMBER`، اگر «همهٔ کسانی که در دو رتبهٔ اول‌اند» می‌خواهی `RANK`.**

## ۴. `LAG` و `LEAD`: نگاه به سطرِ قبل و بعد

`LAG(x)` مقدارِ `x` را از **سطرِ قبلیِ همان پنجره** می‌آورد و `LEAD(x)` از سطرِ بعدی. ترتیبِ «قبل» و «بعد» را همان `ORDER BY` داخلِ `OVER` تعیین می‌کند.

و چون فصلِ ۶ نشان داد پنجره روی نتیجهٔ `GROUP BY` می‌نشیند، می‌شود این را مستقیم روی یک جدولِ گروهی گذاشت:

</div>

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

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

<div dir="rtl">

**سطرِ اول `previous_year` ندارد و سطرِ آخر `next_year` ندارد — هر دو `NaN` شده‌اند، یعنی `NULL`.**

این کاملاً درست است: **سطرِ اول قبلی ندارد، پس مقدارش «نامعلوم» است، نه صفر.** و از ترمِ ۱ فصلِ ۶ می‌دانی که `NULL` در هر محاسبه‌ای بعدش می‌ماند و در هر تجمیعی بی‌صدا کنار می‌رود.

**پس هر ستونی که با `LAG` می‌سازی، دستِ‌کم یک سطرِ `NULL` دارد** و باید تصمیم بگیری با آن چه می‌کنی — نشانش بدهی، حذفش کنی، یا با `COALESCE` جایگزینش کنی. **هر سه انتخاب‌اند و باید در گزارش نوشته شوند؛ فقط یکی از آن‌ها غلط است: وانمود کردن به اینکه صفر بوده.**

## ۵. تغییرِ دوره‌به‌دوره

فصلِ ۴ تغییرِ سال‌به‌سال را در پایتون حساب کرد و قول داد ابزارِ `SQL`ی‌اش را اینجا بدهد. حالا کلِ محاسبه در یک پرسش جا می‌شود:

</div>

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

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

<div dir="rtl">

**همان شش عددِ فصلِ ۴، این بار بدونِ یک خط پایتون.**

سه چیز در همین جدول هست که باید دیده شوند:

1. **`* 100.0` سرِ جایش است.** بدونش همان تقسیمِ صحیحِ فصلِ ۴ برمی‌گشت و ستونِ درصد پر از صفر می‌شد.
2. **سطرِ اول `NULL` است و همان درست است.** سالِ اول قبلی ندارد.
3. **سطرِ آخر یک عددِ درست‌محاسبه‌شده و کاملاً گمراه‌کننده است.** آن سال ناقص است — این پایگاه داده تا اواسطش بیشتر ندارد — و مقایسه‌اش با یک سالِ کامل مثلِ مقایسهٔ شش ماه با دوازده ماه است.

**و هیچ‌چیزی در خروجی این را نمی‌گوید.** تنها راهِ فهمیدنش این است که خودت لبهٔ داده را نگاه کرده باشی:

</div>

```python
print(q("""
    SELECT MIN(added_at) AS first_day,
           MAX(added_at) AS last_day
    FROM books
"""))
```

```text
    first_day    last_day
0  2019-01-01  2025-06-30
```

<div dir="rtl">

**داده وسطِ سال تمام می‌شود.** پس آن ۴۱ درصد کاهش، سنجشِ نصفِ یک سال در برابرِ یک سالِ کامل است.

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

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

> 🤖 **از دستیارت بپرس:** «فرقِ `RANK` و `DENSE_RANK` در `SQL` چیست و هرکدام جوابِ چه پرسشی‌اند؟» بعد این را بپرس: **«اگر بخواهم دقیقاً سه سطرِ اولِ هر گروه را بگیرم و در گروهی چهار سطر مساوی باشند، `RANK` چند سطر برمی‌گرداند؟»** — جوابِ درست «چهارتا» است و باید توضیح بدهد چرا؛ اگر دستیارت گفت «سه‌تا»، همین حالا با پنج کتابِ ۱۹۰تایی امتحانش کن.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| ROW_NUMBER | رو نامبر | شمارهٔ یکتای هر سطر داخلِ پنجره |
| RANK | رنک | رتبه با تساوی و با پرش |
| DENSE_RANK | دنس رنک | رتبه با تساوی و بدونِ پرش |
| LAG / LEAD | لگ / لید | مقدارِ سطرِ قبلی / بعدیِ همان پنجره |
| tie | تای | تساوی؛ چند سطر با مقدارِ یکسان |
| period over period | پریود اوور پریود | مقایسهٔ هر دوره با دورهٔ قبلش |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `RANK` بعد از پنج سطرِ مساوی به ۶ پرید و `DENSE_RANK` به ۲؟

**تمرینِ اجباری:** برای هر **شعبه** سه کتابِ پرامانتش را بگیر. **خروجی باید ۱۸ سطر باشد و هیچ شعبه‌ای بیشتر از سه سطر نداشته باشد؛ هر دو را بررسی کن.** بعد همان پرسش را با `DENSE_RANK` تکرار کن و **تعدادِ سطرها را با ۱۸ بسنج**. اگر بیشتر شد، بنویس کدام شعبه بیشتر داد و چرا. **اگر دقیقاً ۱۸ ماند، آن هم یک نتیجه است** و باید بنویسی که یعنی در هیچ شعبه‌ای بینِ سه کتابِ اول تساوی نبوده.

**تمرینِ ستاره‌دار ⭐:** جدولِ تغییرِ سال‌به‌سال را دوباره بساز، ولی این بار **سالِ ناقصِ آخر را با `WHERE` بیرون بگذار**. بعد هر دو جدول را کنارِ هم بگذار و بنویس کدام ستون‌ها عوض شدند و کدام نشدند. حالا سؤالِ سخت: **آیا حذفِ آن سال، خودش یک تصمیمِ بی‌طرف است؟** جوابت باید بگوید یک خوانندهٔ گزارش از دیدنِ جدولِ کوتاه‌تر چه چیزی را نمی‌فهمد.

## در فصل بعد

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

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t2/s07-window-ranking (یادستان, Yaadestan)
