# فصل ۰۶ — تابعِ پنجره: تجمیع بدونِ از دست دادنِ سطر

<div dir="rtl">

# فصل ۰۶ — تابعِ پنجره: تجمیع بدونِ از دست دادنِ سطر

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

تا اینجا هر تجمیعی یک معامله بود: عددِ گروهی می‌گرفتی و سطرها را از دست می‌دادی. ۱٬۲۰۰ سطر می‌رفت و ۸ سطر برمی‌گشت.

این فصل ابزاری می‌آورد که هر دو را با هم دارد. **همان `AVG` و همان `SUM`، ولی این بار خروجی هنوز ۱٬۲۰۰ سطر دارد** و کنارِ هر کتاب، میانگینِ دستهٔ خودش نشسته است. با همان یک پرسش می‌شود پرسید «این کتاب نسبت به دستهٔ خودش چطور است؟» — پرسشی که با `GROUP BY` اصلاً قابلِ‌بیان نبود، چون بعد از گروه‌بندی دیگر کتابی وجود نداشت.

تنها چیزی که به پرسش اضافه می‌شود یک کلمه است: `OVER`.

![یک ردیف کارتِ سرِ جای خودش در قفسه، با نوارِ شفافی که روی گروهی از آن‌ها خوابیده و یک نشانگرِ مشترک دارد، کنارِ همان گروه که به یک کارتِ ضخیم فشرده شده](/lesson-assets/t2-s06-window-basics/prompt/01-image-strip-over-cards.png)

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

- با `OVER ()` یک عددِ کلی را کنارِ هر سطر بیاوری
- با `PARTITION BY` همان کار را برای هر گروه جدا انجام بدهی
- تفاوتِ بنیادیِ تابعِ پنجره و `GROUP BY` را با تعدادِ سطرها نشان بدهی
- سهمِ هر سطر از کلِ گروهش را در همان سطر حساب کنی
- بگویی تابعِ پنجره کجای ترتیبِ منطقیِ اجرا می‌نشیند و چرا در `WHERE` کار نمی‌کند

## قبل از شروع

**از فصلِ ۲:** ترتیبِ منطقیِ اجرا. کلِ رفتارِ عجیبِ این فصل از یک نکته بیرون می‌آید: **تابعِ پنجره دیرتر از هر چیزی که تا امروز دیده‌ای اجرا می‌شود.**

**از فصلِ ۴:** سهم از کل با زیرپرسشِ اسکالر. همان کار را این فصل ساده‌تر انجام می‌دهد.

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

</div>

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

<div dir="rtl">

## ۱. پرسشی که با `GROUP BY` نمی‌شود پرسید

پرسش: **«این کتاب نسبت به میانگینِ دستهٔ خودش چقدر بالاتر یا پایین‌تر است؟»**

برای جواب دادن دو چیز لازم داری که تا امروز نمی‌شد با هم داشت: `borrowed` **این سطر**، و `AVG(borrowed)` **کلِ دسته**. `GROUP BY` دومی را می‌دهد و اولی را می‌بلعد.

`OVER` همان دومی را بدونِ بلعیدنِ اولی می‌دهد:

</div>

```python
print(q("""
    SELECT id,
           category,
           borrowed,
           ROUND(AVG(borrowed) OVER (), 4) AS all_avg
    FROM books
    ORDER BY id
    LIMIT 4
"""))
print("تعداد سطرهای خروجی:",
      len(q("SELECT id, AVG(borrowed) OVER () AS m FROM books")))
```

```text
   id category  borrowed  all_avg
0   1     رمان        33  78.4925
1   2      شعر        20  78.4925
2   3      علم         1  78.4925
3   4    تاریخ        86  78.4925
تعداد سطرهای خروجی: 1200
```

<div dir="rtl">

**ستونِ آخر در هر چهار سطر همان ۷۸٫۴۹۲۵ است — همان عددی که فصلِ ۳ روی کلِ جدول گرفت — و خروجی هنوز ۱٬۲۰۰ سطر دارد.**

`OVER ()` یعنی «این تابع را روی یک **پنجره** حساب کن، و آن پنجره کلِ نتیجه است». پرانتزِ خالی همان «کل» است.

**و این دقیقاً همان `AVG` است، نه یک تابعِ تازه.** هر تابعِ تجمیعی که تا اینجا دیدی — `COUNT` و `SUM` و `AVG` و `MIN` و `MAX` — با اضافه شدنِ `OVER` به یک تابعِ پنجره تبدیل می‌شود.

## ۲. تفاوتِ بنیادی با `GROUP BY`، با عدد

دو پرسش که **دقیقاً یک عدد** تولید می‌کنند و دو چیزِ کاملاً متفاوت برمی‌گردانند:

</div>

```python
grouped = q("SELECT category, AVG(borrowed) AS m FROM books GROUP BY category")
windowed = q("SELECT id, category, AVG(borrowed) OVER (PARTITION BY category) AS m FROM books")

print("GROUP BY  → سطرهای خروجی:", len(grouped))
print("PARTITION → سطرهای خروجی:", len(windowed))
print()
print("میانگین‌های متفاوت در هر دو:",
      len(set(round(float(v), 6) for v in grouped["m"])),
      "و",
      len(set(round(float(v), 6) for v in windowed["m"])))
```

```text
GROUP BY  → سطرهای خروجی: 8
PARTITION → سطرهای خروجی: 1200

میانگین‌های متفاوت در هر دو: 8 و 8
```

<div dir="rtl">

**هشت سطر در برابرِ ۱٬۲۰۰ سطر، و در هر دو دقیقاً هشت میانگینِ متفاوت.**

این کوتاه‌ترین توصیفِ تفاوت است: **`GROUP BY` سطرها را در هم می‌ریزد و به‌جایشان یک سطر می‌گذارد؛ تابعِ پنجره سطرها را دست نمی‌زند و فقط یک ستون اضافه می‌کند.** عددهای هر دو یکی‌اند؛ **دانه‌بندیِ خروجی فرق می‌کند**، و در این دوره دانه‌بندی همیشه مهم‌تر از عدد است.

**پس کدام را کِی؟** اگر گزارشت قرار است یک سطر برای هر دسته داشته باشد، `GROUP BY`. اگر قرار است هر سطر بماند و کنارش یک عددِ زمینه‌ای بنشیند، تابعِ پنجره.

## ۳. `PARTITION BY`: یک پنجره برای هر گروه

`PARTITION BY category` یعنی «پنجره را نشکن روی کلِ جدول؛ برای هر دسته یک پنجرهٔ جدا بساز».

</div>

```python
print(q("""
    SELECT id,
           category,
           borrowed,
           ROUND(AVG(borrowed) OVER (PARTITION BY category), 2) AS cat_avg,
           COUNT(*)           OVER (PARTITION BY category)      AS cat_books,
           borrowed - ROUND(AVG(borrowed) OVER (PARTITION BY category), 2) AS gap
    FROM books
    ORDER BY id
    LIMIT 4
"""))
```

```text
   id category  borrowed  cat_avg  cat_books    gap
0   1     رمان        33    82.90        146 -49.90
1   2      شعر        20    73.82        164 -53.82
2   3      علم         1    71.88        144 -70.88
3   4    تاریخ        86    71.28        145  14.72
```

<div dir="rtl">

**حالا هر سطر می‌داند دستهٔ خودش چه شکلی است.** کتابِ اول ۳۳ بار امانت رفته در حالی که میانگینِ رمان‌ها ۸۲٫۹۰ است، پس تقریباً پنجاه واحد زیرِ میانگینِ دستهٔ خودش نشسته.

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

> ✅ **چک کن:** ستونِ `cat_books` را با جدولِ گروهیِ فصلِ ۲ بسنج. برای «رمان» باید ۱۴۶ باشد، برای «شعر» ۱۶۴. **و یک کنترلِ قوی‌تر: مقدارهای متفاوتِ ستونِ `cat_books` باید دقیقاً هشت‌تا باشند و جمعِ آن هشت مقدار ۱٬۲۰۰.** اگر جمع ۱٬۲۰۰ نشد، `PARTITION BY` را روی ستونی نوشته‌ای که فکر می‌کردی نوشته‌ای.

## ۴. سهم از کل، در همان سطر

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

</div>

```python
print(q("""
    SELECT id,
           borrowed,
           SUM(borrowed) OVER (PARTITION BY category) AS cat_loans,
           ROUND(borrowed * 100.0 / SUM(borrowed) OVER (PARTITION BY category), 3) AS share
    FROM books
    WHERE category = ?
    ORDER BY borrowed DESC, id
    LIMIT 4
""", ("زندگی‌نامه",)))
```

```text
    id  borrowed  cat_loans  share
0  601       190      11889  1.598
1  593       189      11889  1.590
2  942       189      11889  1.590
3  171       185      11889  1.556
```

<div dir="rtl">

**پرامانت‌ترین کتابِ این دسته ۱٫۶ درصدِ کلِ امانت‌های دسته را به خودش گرفته.**

دو نکتهٔ لازم دربارهٔ همین ستون. اول اینکه `* 100.0` سرِ جایش است — بدونش همان تقسیمِ صحیحِ فصلِ ۴ صفر می‌داد. دوم اینکه **مخرجِ این درصد کلِ امانت‌های همان دسته است، نه کلِ کتابخانه**، و این یک تصمیم است که باید در گزارش نوشته شود. اگر `WHERE` را هم برداری و `PARTITION BY` را هم، همان ستون به سهم از کلِ کتابخانه تبدیل می‌شود و عددها حدودِ هشت برابر کوچک‌تر. برداشتنِ `PARTITION BY` به‌تنهایی هیچ عددی را عوض نمی‌کند، چون `WHERE` از قبل فقط همین یک دسته را نگه داشته؛ بخشِ ۵ می‌گوید چرا.

## ۵. پنجره کجای ترتیبِ اجرا می‌نشیند

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

</div>

```text
FROM → WHERE → GROUP BY → HAVING → پنجره‌ها → SELECT → ORDER BY → LIMIT
```

<div dir="rtl">

پنجره **بعد از** `WHERE` و `GROUP BY` و `HAVING` اجرا می‌شود. سه نتیجه دارد و هر سه در عمل به دردت می‌خورند:

1. **پنجره فقط سطرهایی را می‌بیند که از `WHERE` رد شده‌اند.** یعنی «میانگینِ کل» در یک پرسشِ فیلتردار، میانگینِ کلِ **فیلترشده** است، نه میانگینِ جدول.
2. **پنجره در `WHERE` و `HAVING` در دسترس نیست**، چون آن‌ها زودتر تمام شده‌اند.
3. **پنجره روی نتیجهٔ `GROUP BY` می‌نشیند**، نه روی سطرهای اصلی — که بخشِ ۶ همین را به یک ابزارِ خیلی مفید تبدیل می‌کند.

نکتهٔ اول را همین حالا اندازه می‌گیریم:

</div>

```python
print(q("""
    SELECT COUNT(*) OVER () AS rows_in_window,
           ROUND(AVG(borrowed) OVER (), 4) AS window_avg
    FROM books
    WHERE category = ?
    LIMIT 1
""", ("شعر",)))
print()
print("میانگین کل جدول:", float(q("SELECT AVG(borrowed) FROM books").iloc[0, 0]))
```

```text
   rows_in_window  window_avg
0             164     73.8171

میانگین کل جدول: 78.4925
```

<div dir="rtl">

**`OVER ()` اینجا «کلِ جدول» نیست، «کلِ ۱۶۴ سطری است که از `WHERE` رد شدند».** اسمِ `OVER ()` گمراه‌کننده است و این یکی از رایج‌ترین سوءبرداشت‌هاست.

> 🔧 **اگر کار نکرد:** طبیعی‌ترین کاری که هر کسی بعد از دیدنِ تابعِ پنجره می‌خواهد بکند این است که رویش فیلتر بگذارد. کار نمی‌کند:

</div>

```python
try:
    q("SELECT id FROM books WHERE AVG(borrowed) OVER () > ? LIMIT 3", (50,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql 'SELECT id FROM books WHERE AVG(borrowed) OVER () > ? LIMIT 3': misuse of window function AVG()
```

<div dir="rtl">

**`misuse of window function` یعنی «این را جایی گذاشته‌ای که هنوز محاسبه نشده».** دقیقاً همان شکلِ خطای `misuse of aggregate` در فصلِ ۲، یک ایستگاه جلوتر.

راهش این است که پرسش را دو مرحله کنی: اول پنجره را حساب کن، بعد روی نتیجه‌اش فیلتر بگذار. **فصلِ ۷ همین کار را با یک زیرپرسش در `FROM` انجام می‌دهد** و آنجا دقیقاً می‌گوید چه چیزی به ترمِ ۳ بدهکار می‌ماند.

## ۶. پنجره روی نتیجهٔ گروه‌بندی

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

</div>

```python
print(q("""
    SELECT category,
           COUNT(*)               AS n,
           SUM(COUNT(*)) OVER ()  AS all_books,
           ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
    FROM books
    GROUP BY category
    ORDER BY n DESC
"""))
```

```text
     category    n  all_books    pct
0        کودک  167       1200  13.92
1         شعر  164       1200  13.67
2         هنر  161       1200  13.42
3        رمان  146       1200  12.17
4       تاریخ  145       1200  12.08
5         علم  144       1200  12.00
6       فلسفه  137       1200  11.42
7  زندگی‌نامه  136       1200  11.33
```

<div dir="rtl">

**`SUM(COUNT(*)) OVER ()` را از داخل به بیرون بخوان:** اول `GROUP BY` هشت سطر می‌سازد و در هر سطر `COUNT(*)` را حساب می‌کند؛ بعد پنجره روی همان هشت سطر می‌نشیند و جمعشان را می‌گیرد. جوابش ۱٬۲۰۰ است — همان `COUNT(*)` کلِ جدول، ولی از راهی کاملاً متفاوت.

**و همین دو مسیر، یک شمارشِ دومِ رایگان است:** اگر `SUM(COUNT(*)) OVER ()` با `COUNT(*)` کلِ جدول نخواند، یعنی `WHERE` یا `GROUP BY` سطری را انداخته بیرون.

ستونِ آخر همان درصدهای فصلِ ۴ است — ۱۳٫۹۲ و ۱۳٫۶۷ و بقیه — **که آنجا با یک زیرپرسشِ اسکالر گرفتیمشان و اینجا بدونِ هیچ زیرپرسشی.** دو مسیر، یک جواب.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در بخش‌های ۱ تا ۵ یک سطر **همچنان یک کتاب است** — و این کلِ نکتهٔ تابعِ پنجره است. فقط در بخشِ ۶ که `GROUP BY` هم در کار است، یک سطر یک دسته می‌شود. **با کدام شمارشِ دوم سنجیدی؟** سه‌تا: تعدادِ سطرهای خروجیِ دو مسیر (۸ در برابرِ ۱٬۲۰۰) با تعدادِ **مقدارهای متفاوتِ** میانگین (۸ در هر دو)؛ ستونِ `cat_books` در برابرِ جدولِ گروهیِ فصلِ ۲؛ و `SUM(COUNT(*)) OVER ()` که ۱٬۲۰۰ داد، همان عددِ `COUNT(*)` کلِ جدول. **چه کسی از قلم افتاد؟** هر سطری که `WHERE` انداخته باشدش بیرون، **از خودِ پنجره هم بیرون است** — در بخشِ ۵ دیدیم که میانگینِ «کل» آنجا میانگینِ ۱۶۴ سطر بود، نه ۱٬۲۰۰ سطر؛ و همچنان ۲۴۰ سطرِ `arrivals.csv`.

> 🤖 **از دستیارت بپرس:** «تفاوتِ یک تابعِ تجمیعی و همان تابع با `OVER` چیست؟» بعد این را بپرس: **«اگر پرسشم `WHERE` داشته باشد، `AVG(x) OVER ()` میانگینِ کلِ جدول را می‌دهد یا میانگینِ سطرهای فیلترشده را؟»** — جوابِ درست «سطرهای فیلترشده» است و باید به ترتیبِ اجرا اشاره کند؛ اگر جواب گفت «کلِ جدول»، همین حالا با عددهای بخشِ ۵ ردش کن.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| window function | ویندو فانکشن | تابعی که روی گروهی از سطرها حساب می‌کند بدونِ اینکه سطرها را یکی کند |
| OVER | اوور | بندی که پنجره را تعریف می‌کند |
| PARTITION BY | پارتیشن بای | تقسیمِ سطرها به پنجره‌های جدا |
| partition | پارتیشن | همان پنجره؛ گروهی که تابع رویش حساب می‌کند |
| running context | رانینگ کانتکست | عددِ زمینه‌ای که کنارِ هر سطر می‌نشیند |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `AVG(borrowed) OVER ()` در یک پرسشِ فیلتردار، میانگینِ کلِ جدول را نمی‌دهد؟

**تمرینِ اجباری:** پرسشی بنویس که برای هر کتاب چهار ستون بدهد: `id`، `borrowed`، میانگینِ **شعبهٔ** خودش، و فاصله‌اش از آن میانگین. بعد **دو شمارشِ کنترلی بگیر**: تعدادِ سطرهای خروجی باید ۱٬۲۰۰ باشد، و مقدارهای متفاوتِ ستونِ میانگین باید ۶ باشند. **جوابِ سؤالِ اصلی را بنویس: چند کتاب بالای میانگینِ شعبهٔ خودشان‌اند، و چرا این عدد نصفِ ۱٬۲۰۰ نیست؟**

**تمرینِ ستاره‌دار ⭐:** همان ستونِ «سهم از کل» بخشِ ۴ را دو بار بساز — یک بار با `PARTITION BY category` و یک بار بدونِ آن. **برای یک کتابِ مشخص هر دو عدد را چاپ کن و نسبتشان را حساب کن.** حالا سؤالِ سخت: **آن نسبت برای همهٔ کتاب‌های یک دسته یکسان است؟ و برای کتاب‌های دسته‌های مختلف چطور؟** جوابت باید بگوید آن نسبت دقیقاً چه چیزی را اندازه می‌گیرد.

## در فصل بعد

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

</div>

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