# فصل ۰۸ — قابِ پنجره: جمعِ رونده و میانگینِ متحرک

<div dir="rtl">

# فصل ۰۸ — قابِ پنجره: جمعِ رونده و میانگینِ متحرک

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

فصلِ ۷ داخلِ پنجره ترتیب گذاشت. این فصل یک قدم جلوتر می‌رود: **می‌گوید پنجره از کجا تا کجا باشد.**

«از اولِ گروه تا همین سطر» یک جمعِ رونده می‌سازد. «سه سطرِ آخر» یک میانگینِ متحرک می‌سازد. و «همین سطر و یکی قبل و یکی بعد» یک نمایِ صاف‌شده.

**و یک تله که تا اندازه‌اش نگیری باورت نمی‌شود:** وقتی `ORDER BY` داخلِ `OVER` می‌نویسی، پنجره‌ات دیگر کلِ گروه نیست — یک قابِ پیش‌فرض می‌گیرد که خودت انتخابش نکرده‌ای. همان پنج کتابی که هر پنج‌تا دقیقاً ۱۹۰ بار امانت رفته‌اند، با قابِ پیش‌فرض هر پنج‌تا شمارهٔ **۵** می‌گیرند و با قابِ سطری **۱ تا ۵**.

![یک نوارِ بلند از میله‌های هم‌فاصله با ارتفاع‌های متفاوت و قابی صُلب که رویش می‌لغزد و سرِ نوار نصفه بیرون می‌زند](/lesson-assets/t2-s08-window-frames/prompt/01-image-sliding-frame.png)

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

- با `ROWS BETWEEN` بگویی پنجره از کجا تا کجاست
- جمعِ تجمعی بسازی و از خودش یک شمارشِ کنترلی بگیری
- میانگینِ متحرکِ `k`تایی بسازی و بگویی سطرهای اولش چرا قابلِ‌اعتماد نیستند
- تفاوتِ `ROWS` و `RANGE` را روی تساوی با عدد نشان بدهی
- بگویی صاف کردن چه چیزی را نشان می‌دهد و چه چیزی را پنهان می‌کند

## قبل از شروع

**از فصلِ ۷:** `ORDER BY` داخلِ `OVER`، و اینکه بدونِ یک ستونِ یکتا ترتیب تضمین‌شده نیست.

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

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

</div>

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

<div dir="rtl">

## ۱. یک سریِ زمانی برای کار کردن

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

عبارتش طولانی است و در هر پرسش تکرار می‌شود، پس یک بار در پایتون می‌سازیمش. **دقت کن که این متنِ خودِ پرسش است، نه یک مقدار** — مقدارها همچنان با `?` می‌روند.

</div>

```python
QUARTER = ("strftime('%Y', added_at) || '-Q' || "
           "((CAST(strftime('%m', added_at) AS INTEGER) + 2) / 3)")

series = q(f"""
    SELECT {QUARTER} AS quarter,
           COUNT(*)  AS n
    FROM books
    GROUP BY quarter
    ORDER BY quarter
""")
print(series.head(5))
print("سطرها:", len(series), "| جمع:", int(series["n"].sum()))
```

```text
   quarter   n
0  2019-Q1  43
1  2019-Q2  46
2  2019-Q3  51
3  2019-Q4  53
4  2020-Q1  51
سطرها: 26 | جمع: 1200
```

<div dir="rtl">

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

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

## ۲. جمعِ تجمعی: از اول تا همین سطر

`ORDER BY` داخلِ `OVER` بگذار و ببین چه اتفاقی می‌افتد:

</div>

```python
running = q(f"""
    SELECT {QUARTER} AS quarter,
           COUNT(*)  AS n,
           SUM(COUNT(*)) OVER (ORDER BY {QUARTER}) AS running
    FROM books
    GROUP BY quarter
    ORDER BY quarter
""")
print(running.head(5))
print("...")
print(running.tail(2))
```

```text
   quarter   n  running
0  2019-Q1  43       43
1  2019-Q2  46       89
2  2019-Q3  51      140
3  2019-Q4  53      193
4  2020-Q1  51      244
...
    quarter   n  running
24  2025-Q1  49     1145
25  2025-Q2  55     1200
```

<div dir="rtl">

**ستونِ `running` در هر سطر جمعِ همهٔ سطرها از اول تا همان‌جاست، و سطرِ آخرش دقیقاً ۱٬۲۰۰ است.**

در فصلِ ۶ همین `SUM(COUNT(*)) OVER ()` بدونِ `ORDER BY` در **هر** سطر ۱٬۲۰۰ می‌داد. اضافه شدنِ `ORDER BY` پنجره را از «کلِ گروه» به «از اول تا همین سطر» عوض کرد — و این تغییر خودش موضوعِ بخشِ ۴ است.

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

## ۳. میانگینِ متحرک، و سطرهایی که میانگینشان `k`تایی نیست

`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` یعنی «قاب را از دو سطر قبل تا همین سطر بگیر» — سه سطر روی هم.

</div>

```python
moving = q(f"""
    SELECT {QUARTER} AS quarter,
           COUNT(*)  AS n,
           ROUND(AVG(COUNT(*)) OVER (
               ORDER BY {QUARTER}
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 2) AS moving_avg,
           COUNT(*) OVER (
               ORDER BY {QUARTER}
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ) AS rows_in_frame
    FROM books
    GROUP BY quarter
    ORDER BY quarter
""")
print(moving.head(5))
```

```text
   quarter   n  moving_avg  rows_in_frame
0  2019-Q1  43       43.00              1
1  2019-Q2  46       44.50              2
2  2019-Q3  51       46.67              3
3  2019-Q4  53       50.00              3
4  2020-Q1  51       51.67              3
```

<div dir="rtl">

**به ستونِ آخر نگاه کن: در سطرِ اول قاب فقط یک سطر دارد و در سطرِ دوم دو سطر.**

یعنی `moving_avg` در سطرِ اول اصلاً میانگین نیست — خودِ عدد است. در سطرِ دوم میانگینِ دوتاست. **از سطرِ سوم به بعد است که واقعاً یک میانگینِ سه‌فصلی داری.**

`SQLite` این را خطا نمی‌دهد و در خروجی هم علامت نمی‌زند. **پس هر بار که میانگینِ متحرک می‌سازی، ستونِ «چند سطر در قاب بود» را هم بساز** — یک `COUNT(*)` روی همان قاب، رایگان — و در گزارش یا `k − 1` سطرِ اول را کنار بگذار یا صریح علامتشان بزن.

**این دقیقاً همان تلهٔ «دورهٔ ناقص» فصلِ ۷ است، این بار در ابتدای سری به‌جای انتهایش.**

> ✅ **چک کن:** ستونِ `rows_in_frame` باید ۱ و ۲ و بعد همه‌اش ۳ باشد. اگر همه‌اش ۱ بود، `ROWS` را با `RANGE` عوض کرده‌ای. اگر از همان سطرِ اول ۳ بود، قاب را رو به آینده نوشته‌ای، مثلاً `CURRENT ROW AND 2 FOLLOWING`. در هر دو حالت عددهایت میانگینِ سه‌فصلیِ گذشته نیستند. **جا انداختنِ `ORDER BY` داخلِ `OVER` را این ستون نمی‌گیرد:** باز هم ۱ و ۲ و ۳ می‌دهد، ولی دیگر چیزی تضمین نمی‌کند که «دو سطرِ قبل» همان دو فصلِ قبل باشند، پس آن را با چشم چک کن.

## ۴. قابِ پیش‌فرض، و تلهٔ آن

حالا مهم‌ترین نکتهٔ فصل. **وقتی `ORDER BY` داخلِ `OVER` می‌نویسی و قاب را صریح نگویی، `SQLite` یک قابِ پیش‌فرض می‌گذارد** — و آن پیش‌فرض `RANGE` است، نه `ROWS`.

تفاوتشان فقط سرِ **تساوی** خودش را نشان می‌دهد:

- **`ROWS`** سطرها را می‌شمارد: «دو سطر قبل» یعنی دقیقاً دو سطر.
- **`RANGE`** مقدارها را می‌بیند: همهٔ سطرهایی که مقدارِ `ORDER BY`شان با سطرِ فعلی **برابر** است، یک‌جا داخلِ قاب می‌آیند.

روی همان پنج کتابِ ۱۹۰تایی:

</div>

```python
print(q("""
    SELECT id,
           borrowed,
           COUNT(*) OVER (ORDER BY borrowed DESC) AS default_frame,
           COUNT(*) OVER (
               ORDER BY borrowed DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS row_frame
    FROM books
    ORDER BY borrowed DESC, id
    LIMIT 7
"""))
```

```text
     id  borrowed  default_frame  row_frame
0    46       190              5          1
1   448       190              5          2
2   601       190              5          3
3  1056       190              5          4
4  1169       190              5          5
5   138       189             10          6
6   392       189             10          7
```

<div dir="rtl">

**ستونِ پیش‌فرض به هر پنج سطرِ مساوی عددِ ۵ می‌دهد؛ ستونِ سطری ۱ تا ۵.**

منطقِ `RANGE` کاملاً باحساب است: سطرهای مساوی از نظرِ `ORDER BY` **هم‌رتبه‌اند**، پس هیچ دلیلی نیست یکی‌شان زودتر از دیگری داخلِ قاب بیاید. ولی اگر تو دنبالِ «شمارهٔ سطر» بودی، این عدد دقیقاً همان چیزی نیست که می‌خواستی.

**و دقت کن که سطرِ ششم در ستونِ پیش‌فرض ۱۰ شد، نه ۶** — چون هر پنج کتابِ ۱۸۹تایی با هم داخلِ قاب آمدند، با اینکه `LIMIT 7` فقط دوتایشان را نشان می‌دهد.

**قاعدهٔ عملی این فصل: هر جا `ORDER BY` داخلِ `OVER` می‌نویسی، قاب را هم صریح بنویس.** اگر مقدارهای ستونِ ترتیب یکتا باشند، `ROWS` و `RANGE` یک جواب می‌دهند و صریح نوشتن هزینه‌ای ندارد؛ اگر یکتا نباشند، همین یک کلمه جوابت را عوض می‌کند.

> 🔧 **اگر کار نکرد:** دو سرِ قاب باید به ترتیب نوشته شوند — اول ابتدای قاب، بعد انتهایش. برعکسش خطا می‌دهد:

</div>

```python
try:
    q("""
        SELECT id, SUM(borrowed) OVER (
            ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 PRECEDING
        ) AS bad
        FROM books
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT id, SUM(borrowed) OVER (
            ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 PRECEDING
        ) AS bad
        FROM books
        LIMIT 1
    ': unsupported frame specification
```

<div dir="rtl">

`unsupported frame specification` یعنی «انتهای قاب قبل از ابتدایش افتاده». ترتیبِ درست همیشه از گذشته به آینده است: `2 PRECEDING` بعد `CURRENT ROW` بعد `1 FOLLOWING`.

## ۵. قابی که آینده را هم می‌بیند

`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` قاب را دورِ سطرِ فعلی وسط‌چین می‌کند: یکی قبل، خودش، یکی بعد.

</div>

```python
centred = q(f"""
    SELECT {QUARTER} AS quarter,
           COUNT(*)  AS n,
           ROUND(AVG(COUNT(*)) OVER (
               ORDER BY {QUARTER}
               ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
           ), 2) AS centred_avg
    FROM books
    GROUP BY quarter
    ORDER BY quarter
""")
print(centred.head(4))
```

```text
   quarter   n  centred_avg
0  2019-Q1  43        44.50
1  2019-Q2  46        46.67
2  2019-Q3  51        50.00
3  2019-Q4  53        51.67
```

<div dir="rtl">

**عددهای این ستون همان عددهای میانگینِ متحرکِ بخشِ ۳ هستند، یک سطر بالاتر: ۴۴٫۵۰ و ۴۶٫۶۷ و ۵۰٫۰۰. پس میانگینِ وسط‌چین صاف‌تر نیست؛ فقط قله‌ها را از جای زمانی‌شان عقب نمی‌اندازد. و یک شرطِ سنگین دارد: به سطرِ بعدی نگاه می‌کند.**

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

**این تفاوت را همیشه در گزارش بنویس**، چون از روی خودِ عدد اصلاً پیدا نیست.

## ۶. صاف کردن چه چیزی را پنهان می‌کند

میانگینِ متحرک نوسان را کم می‌کند و این دقیقاً کاری است که ازش می‌خواهی. **ولی همان کار، چیزهایی را هم پاک می‌کند که شاید مهم بوده‌اند.**

</div>

```python
compare = q(f"""
    SELECT {QUARTER} AS quarter,
           COUNT(*)  AS n,
           ROUND(AVG(COUNT(*)) OVER (
               ORDER BY {QUARTER}
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 2) AS moving_avg
    FROM books
    GROUP BY quarter
    ORDER BY quarter
""")
print("دامنهٔ ستون خام       :", int(compare["n"].min()), "تا", int(compare["n"].max()))
print("دامنهٔ میانگین متحرک  :", round(float(compare["moving_avg"].min()), 2),
      "تا", round(float(compare["moving_avg"].max()), 2))
print("بزرگ‌ترین جهش خام     :",
      int(max(abs(compare["n"][i] - compare["n"][i - 1]) for i in range(1, len(compare)))))
print("بزرگ‌ترین جهش صاف‌شده :",
      round(max(abs(compare["moving_avg"][i] - compare["moving_avg"][i - 1])
                for i in range(1, len(compare))), 2))
```

```text
دامنهٔ ستون خام       : 34 تا 63
دامنهٔ میانگین متحرک  : 37.67 تا 54.33
بزرگ‌ترین جهش خام     : 20
بزرگ‌ترین جهش صاف‌شده : 6.67
```

<div dir="rtl">

**دامنه از یک بازهٔ پهن به یک بازهٔ باریک آمد، و بزرگ‌ترین جهش تقریباً یک‌سوم شد.**

اگر پرسشت «روندِ کلی چیست؟» باشد، این خوب است. اگر پرسشت «آیا اتفاقِ غیرعادی‌ای افتاد؟» باشد، **میانگینِ متحرک همان اتفاق را پاک کرده.**

**و این دقیقاً همان درسِ فصلِ ۵ است با ابزارِ دیگری:** عرضِ سطل شکلِ توزیع را عوض می‌کرد، طولِ قاب شکلِ روند را. هر دو تصمیمِ توأند، هیچ‌کدام از داده نمی‌آیند، و هر دو باید در گزارش اعلام شوند.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در بخش‌های ۱ و ۲ و ۳ و ۵ و ۶ یک سطر یک **فصلِ سه‌ماهه** است، نه یک کتاب. فقط در بخشِ ۴ برگشتیم به سطحِ کتاب. **با کدام شمارشِ دوم سنجیدی؟** سه‌تا: جمعِ ستونِ `n` روی ۲۶ سطر که ۱٬۲۰۰ شد؛ سطرِ آخرِ جمعِ تجمعی که مستقل از آن هم ۱٬۲۰۰ داد؛ و ستونِ `rows_in_frame` که ثابت کرد قاب واقعاً سه سطری است — مگر در دو سطرِ اول. **چه کسی از قلم افتاد؟** دو سطرِ اولِ ستونِ میانگینِ متحرک که میانگینِ سه‌فصلی نیستند و اگر جدا علامتشان نزنی مثلِ بقیه خوانده می‌شوند؛ نوسان‌هایی که صاف کردن پاکشان کرد؛ و همچنان ۲۴۰ سطرِ `arrivals.csv`.

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

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| frame | فریم | قاب؛ بخشی از پنجره که تابع رویش حساب می‌کند |
| ROWS BETWEEN | رُوز بیتوین | قاب بر اساسِ شمارشِ سطر |
| RANGE | رنج | قاب بر اساسِ مقدارِ ستونِ ترتیب |
| running total | رانینگ توتال | جمعِ تجمعی از اول تا سطرِ فعلی |
| moving average | موینگ اوریج | میانگینِ متحرکِ `k`تایی |
| UNBOUNDED PRECEDING | آنباوندد پریسیدینگ | از اولِ پنجره |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا سطرِ ششمِ ستونِ پیش‌فرض ۱۰ شد و نه ۶؟

**تمرینِ اجباری:** جمعِ تجمعیِ `borrowed` را روی همان سریِ فصل‌های سه‌ماهه بساز. **سطرِ آخرش باید دقیقاً ۹۴٬۱۹۱ شود** — همان عددِ کلِ جدول. بعد یک ستونِ میانگینِ متحرکِ **چهارفصلی** هم اضافه کن و **ستونِ «چند سطر در قاب بود» را کنارش بگذار**. جوابِ سؤالِ اصلی را بنویس: **چند سطرِ اول قابلِ‌اعتماد نیستند و چرا این عدد با فصلِ سه‌تایی فرق دارد؟**

**تمرینِ ستاره‌دار ⭐:** همان جمعِ تجمعی را این بار **برای هر شعبه جدا** بساز (`PARTITION BY branch` به‌اضافهٔ `ORDER BY` روی فصل). **بررسی کن که سطرِ آخرِ هر شعبه با جمعِ امانتِ همان شعبه در فصلِ ۲ بخواند.** حالا سؤالِ سخت: **اگر شعبه‌ای در یک فصلِ سه‌ماهه هیچ کتابی اضافه نکرده باشد، در این جدول چه اتفاقی می‌افتد؟** جوابت باید به فصلِ ۵ و سطلِ خالی برگردد.

## در فصل بعد

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

</div>

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