# فصل ۰۷ — `WITH`: پرسشِ بلند را خوانا کن

<div dir="rtl">

# فصل ۰۷ — `WITH`: پرسشِ بلند را خوانا کن

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

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

`WITH` چنین پرسشی را به **مرحله‌های نام‌دار** می‌شکند که از بالا به پایین خوانده می‌شوند. ولی سودِ اصلی‌اش خوانایی نیست، **اشکال‌یابی** است: هر مرحله را می‌شود جدا اجرا کرد و شمرد. در این فصل چهار مرحله می‌سازیم و از سه‌تایشان سه عدد می‌گیریم — **۱٬۰۰۰ و ۱٬۲۰۰ و ۶** — و هر عدد ثابت می‌کند مرحلهٔ قبلی کارِ درستی کرده.

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

![چند حوضچهٔ پله‌ای که آب از هرکدام به بعدی می‌ریزد و هر پله شیرِ نمونه‌برداریِ خودش را دارد](/lesson-assets/t3-s07-cte/prompt/01-image-stepped-basins.png)

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

- یک پرسشِ چندمرحله‌ای را با `WITH` بنویسی و مرحله‌هایش را نام‌گذاری کنی
- هر مرحله را جدا اجرا کنی و با یک شمارش بسنجی‌اش
- یک `CTE` بازگشتی بنویسی که یک سلسله‌مراتب را می‌پیماید
- ثابت کنی که پیمایش هیچ گره‌ای را جا نینداخته

## قبل از شروع

**از فصلِ ۶:** زیرپرسش در `FROM`. `CTE` همان است با یک نام و یک جای بهتر.

**از فصلِ ۴ و ۵:** `CROSS JOIN` و `UNION ALL` — هر دو در این فصل لازم می‌شوند.

**دانه‌بندی:** هر مرحلهٔ یک `CTE` دانه‌بندیِ خودش را دارد و **این تغییرِ دانه‌بندی دقیقاً همان چیزی است که نام‌گذاری قابلِ دیدنش می‌کند.**

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

</div>

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

<div dir="rtl">

## ۱. پرسشی که خواندنش از وسط شروع می‌شود

پرسش این است: **در کدام شعبه‌ها نرخِ امانت به‌ازای هر کتاب از میانگینِ کلِ کتابخانه بالاتر است؟**

با ابزارِ فصلِ ۶ نوشتنی است، ولی این‌طور درمی‌آید:

</div>

```python
nested = q("""
    SELECT br.name,
           ROUND(1.0 * SUM(COALESCE(x.n, 0)) / COUNT(*), 2) AS loans_per_book
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
           ON x.book_id = b.id
    GROUP BY br.id, br.name
    HAVING 1.0 * SUM(COALESCE(x.n, 0)) / COUNT(*)
           > (SELECT 1.0 * (SELECT COUNT(*) FROM loans) / (SELECT COUNT(*) FROM books))
    ORDER BY loans_per_book DESC
""")
print(nested)
```

```text
    name  loans_per_book
0  مرکزی            5.65
1   نسیم            4.02
2   دانش            3.52
```

<div dir="rtl">

جوابش درست است. **مشکل این است که برای فهمیدنش باید از داخلی‌ترین پرانتز شروع کنی و بیایی بیرون** — و همان عبارتِ `SUM(COALESCE(x.n, 0)) / COUNT(*)` دو بار نوشته شده، یک بار در `SELECT` و یک بار در `HAVING`. اگر روزی یکی‌شان را عوض کنی و آن یکی را یادت برود، پرسش بی‌صدا چیزِ دیگری می‌شود.

## ۲. همان پرسش، مرحله به مرحله

`WITH` قبل از `SELECT` می‌آید و هر مرحله را با یک نام تعریف می‌کند. مرحله‌ها به‌ترتیب خوانده می‌شوند و هرکدام می‌تواند از قبلی‌ها استفاده کند.

</div>

```python
STAGES = """
    WITH loans_per_book AS (
        SELECT book_id, COUNT(*) AS n
        FROM loans
        GROUP BY book_id
    ),
    book_stats AS (
        SELECT b.branch_id, COALESCE(lpb.n, 0) AS loans
        FROM books AS b
        LEFT JOIN loans_per_book AS lpb ON lpb.book_id = b.id
    ),
    branch_stats AS (
        SELECT branch_id, COUNT(*) AS books, SUM(loans) AS loans
        FROM book_stats
        GROUP BY branch_id
    ),
    overall AS (
        SELECT 1.0 * SUM(loans) / SUM(books) AS loans_per_book
        FROM branch_stats
    )
"""

with_cte = q(STAGES + """
    SELECT br.name,
           ROUND(1.0 * s.loans / s.books, 2) AS loans_per_book
    FROM branch_stats AS s
    JOIN branches AS br ON br.id = s.branch_id
    CROSS JOIN overall AS o
    WHERE 1.0 * s.loans / s.books > o.loans_per_book
    ORDER BY loans_per_book DESC
""")
print(with_cte)
print()
print("همان نتیجهٔ بخش ۱؟", with_cte.equals(nested))
```

```text
    name  loans_per_book
0  مرکزی            5.65
1   نسیم            4.02
2   دانش            3.52

همان نتیجهٔ بخش ۱؟ True
```

<div dir="rtl">

**همان سه شعبه، همان سه عدد — و این بار از بالا به پایین خواندنی.**

چهار نامی که گذاشتیم مستندسازی‌اند، نه تزئین. `loans_per_book` می‌گوید «برای هر کتاب، چند امانت»؛ `book_stats` می‌گوید «هر کتاب با تعدادِ امانتش»؛ `branch_stats` می‌گوید «هر شعبه با دو عددش». **کسی که این پرسش را شش ماه بعد باز می‌کند، بدونِ اجرا کردنش می‌فهمد هر مرحله دانه‌بندی‌اش چیست.**

`overall` هم نکتهٔ خودش را دارد: **یک `CTE` که فقط یک سطر و یک ستون دارد** — و برای رساندنش به همهٔ سطرها از `CROSS JOIN` فصلِ ۴ استفاده کردیم. اینجا `CROSS JOIN` دقیقاً همان کاری را می‌کند که باید: یک عدد را کنارِ هر سطر می‌گذارد. **شش سطر ضربدر یک سطر می‌شود شش سطر**، پس هیچ تکثیری در کار نیست.

> ⚠️ **مواظب باش:** یک `CROSS JOIN` فقط وقتی بی‌خطر است که طرفِ دیگرش **دقیقاً یک سطر** داشته باشد. اگر `overall` به‌خاطرِ یک اشتباه دو سطر برگرداند، نتیجه بی‌صدا دو برابر می‌شود — همان fan-out فصلِ ۳، این بار از راهی که به‌نظر بی‌خطر می‌آمد.

## ۳. اجرای تکه‌تکه: هر مرحله را جدا بشمار

اینجا سودِ اصلیِ `CTE` است. **می‌توانی به‌جای مرحلهٔ آخر، از هر مرحلهٔ میانی `SELECT` بگیری** — بدونِ اینکه چیزی از پرسش را دست بزنی.

</div>

```python
for stage in ("loans_per_book", "book_stats", "branch_stats"):
    print(f"{stage:<16}:", len(q(STAGES + f" SELECT * FROM {stage}")), "سطر")
print()
print(q(STAGES + " SELECT * FROM branch_stats ORDER BY branch_id"))
print()
print(q(STAGES + " SELECT SUM(books) AS books, SUM(loans) AS loans FROM branch_stats"))
```

```text
loans_per_book  : 1000 سطر
book_stats      : 1200 سطر
branch_stats    : 6 سطر

   branch_id  books  loans
0          1    197   1113
1          2    208    837
2          3    192    676
3          4    174    488
4          5    225    506
5          6    204    378

   books  loans
0   1200   3998
```

<div dir="rtl">

**سه عدد، سه دانه‌بندی، و هر سه قابلِ بررسی:**

- `loans_per_book` باید یک سطر برای هر کتابی داشته باشد که **دستِ‌کم یک امانت دارد** — ۱٬۰۰۰، و می‌دانیم ۲۰۰ کتاب بی‌امانت‌اند.
- `book_stats` باید یک سطر برای **هر** کتاب داشته باشد — ۱٬۲۰۰. اگر اینجا ۱٬۰۰۰ می‌شد، یعنی `LEFT JOIN` را `JOIN` نوشته بودیم.
- `branch_stats` باید یک سطر برای هر شعبه‌ای داشته باشد که کتاب دارد — ۶.

و خطِ آخر مستقل از همهٔ این‌ها می‌سنجد: **جمعِ `books` باید ۱٬۲۰۰ و جمعِ `loans` باید ۳٬۹۹۸ باشد.** هر دو درست‌اند، یعنی در این چهار مرحله نه سطری گم شد و نه سطری تکثیر.

> ✅ **چک کن:** اگر `book_stats` عددی جز ۱۲۰۰ داد، مرحلهٔ دومت کتاب می‌اندازد یا تکثیر می‌کند و **هیچ عددی بعد از آن قابلِ اعتماد نیست.** این تنها راهِ پیدا کردنِ محلِ دقیقِ خرابی در یک پرسشِ چندمرحله‌ای است.

## ۴. `CTE` بازگشتی: پیمودنِ یک سلسله‌مراتب

جدولِ `sections` بخش‌های کتابخانه را نگه می‌دارد و هر بخش به بخشِ بالادستی‌اش اشاره می‌کند:

</div>

```python
print(q("SELECT id, name, parent_id FROM sections ORDER BY id"))
```

```text
    id         name  parent_id
0    1     کتابخانه        NaN
1    2       ادبیات        1.0
2    3         علوم        1.0
3    4         کودک        1.0
4    5          شعر        2.0
5    6         رمان        2.0
6    7   شعر کلاسیک        5.0
7    8    شعر معاصر        5.0
8    9        فیزیک        3.0
9   10         زیست        3.0
10  11    تاریخ علم        3.0
11  12  داستان کودک        4.0
```

<div dir="rtl">

«شعر معاصر» زیرِ «شعر» است، «شعر» زیرِ «ادبیات»، و «ادبیات» زیرِ «کتابخانه». **برای رسیدن از هر بخش به ریشه‌اش، سه `JOIN` لازم است — ولی از کجا بدانی سه‌تا؟** اگر فردا کسی یک طبقهٔ دیگر اضافه کند، پرسشت بی‌صدا ناقص می‌شود.

`WITH RECURSIVE` این را حل می‌کند. ساختارش دو تکه دارد که با `UNION ALL` به هم وصل شده‌اند: **تکهٔ اول نقطهٔ شروع است، و تکهٔ دوم به خودِ `CTE` اشاره می‌کند** و تا وقتی سطرِ تازه‌ای پیدا شود ادامه می‌دهد.

</div>

```python
print(q("""
    WITH RECURSIVE tree(id, name, depth, path) AS (
        SELECT id, name, 1, name
        FROM sections
        WHERE parent_id IS NULL

        UNION ALL

        SELECT s.id, s.name, t.depth + 1, t.path || ' > ' || s.name
        FROM sections AS s
        JOIN tree AS t ON s.parent_id = t.id
    )
    SELECT depth, name, path
    FROM tree
    ORDER BY path
"""))
```

```text
    depth         name                                  path
0       1     کتابخانه                              کتابخانه
1       2       ادبیات                     کتابخانه > ادبیات
2       3         رمان              کتابخانه > ادبیات > رمان
3       3          شعر               کتابخانه > ادبیات > شعر
4       4    شعر معاصر   کتابخانه > ادبیات > شعر > شعر معاصر
5       4   شعر کلاسیک  کتابخانه > ادبیات > شعر > شعر کلاسیک
6       2         علوم                       کتابخانه > علوم
7       3    تاریخ علم           کتابخانه > علوم > تاریخ علم
8       3         زیست                کتابخانه > علوم > زیست
9       3        فیزیک               کتابخانه > علوم > فیزیک
10      2         کودک                       کتابخانه > کودک
11      3  داستان کودک         کتابخانه > کودک > داستان کودک
```

<div dir="rtl">

**دوازده سطر و چهار طبقه، با یک پرسش که هیچ‌جا عددِ ۴ در آن نوشته نشده.**

سه چیز در همین پرسش اتفاق افتاد و هر سه ارزشِ نام بردن دارند: `depth` با هر قدم یکی بالا رفت، `path` با `||` مسیرِ کامل را ساخت، و `JOIN tree AS t` — پیوندِ `CTE` به **خودش** — همان چیزی است که «بازگشتی» را ممکن می‌کند.

> ✅ **چک کن:** تعدادِ سطرهای نتیجه باید دقیقاً برابرِ تعدادِ سطرهای `sections` باشد — ۱۲ و ۱۲. **اگر کمتر بود، بخشی از درخت اصلاً پیموده نشده:** یعنی بخشی وجود دارد که `parent_id`ش به گره‌ای اشاره می‌کند که نیست، یا چند بخش در یک حلقه‌اند که از ریشه به آن‌ها راهی نیست، پس هیچ‌وقت به آن‌ها نمی‌رسیم. **این همان «چه کسی از قلم افتاد» است، در شکلِ سلسله‌مراتبی‌اش.**

> 🔧 **اگر کار نکرد:** اگر تکهٔ اول — نقطهٔ شروع — را جا بیندازی، پرسش هیچ جایی برای شروع ندارد و `SQLite` صریح اعتراض می‌کند:

</div>

```python
try:
    q("""
        WITH RECURSIVE t(id) AS (
            SELECT s.id FROM sections AS s JOIN t ON s.parent_id = t.id
        )
        SELECT * FROM t
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        WITH RECURSIVE t(id) AS (
            SELECT s.id FROM sections AS s JOIN t ON s.parent_id = t.id
        )
        SELECT * FROM t
    ': circular reference: t
```

<div dir="rtl">

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

**و یک صداقت دربارهٔ همین بخش:** این تنها جای آوند است که به `CTE` بازگشتی نیاز داریم، و در بیست‌وهفت فصلِ باقی‌ماندهٔ دوره دیگر به‌کارش نمی‌بریم. آوردیمش چون **هر داده‌ای که ستونِ «والد» دارد بدونِ آن پیمودنی نیست** — دسته‌بندیِ تودرتو، نمودارِ سازمانی، پاسخ‌به‌پاسخ — نه چون در ادامهٔ این دوره لازمش داری. اگر روزی به چنین داده‌ای برخوردی، همین بخش را دوباره باز کن.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در `loans_per_book` یک کتابِ امانت‌رفته، در `book_stats` یک کتاب، در `branch_stats` یک شعبه، و در `tree` یک بخش از سلسله‌مراتب. **چهار دانه‌بندیِ متفاوت در یک پرسش** — و نام‌گذاری تنها چیزی است که این را قابلِ دیدن می‌کند. **با کدام شمارشِ دوم سنجیدی؟** با سه‌تا: ۱٬۰۰۰ به‌علاوهٔ ۲۰۰ برابرِ ۱٬۲۰۰؛ جمعِ ستون‌های `branch_stats` که ۱٬۲۰۰ و ۳٬۹۹۸ شد؛ و ۱۲ سطرِ `tree` برابرِ ۱۲ سطرِ `sections`. **چه کسی از قلم افتاد؟** شعبهٔ آفتاب، که چون کتابی ندارد در `branch_stats` سطری ندارد — ولی «میانگینِ کل» امانت به‌ازای هر **کتاب** است و مخرجش ۱٬۲۰۰ کتاب، پس نبودنِ آفتاب آن را عوض نمی‌کند؛ چیزی که گم می‌شود سطرِ خودِ آفتاب است.

> 🤖 **از دستیارت بپرس:** «چه فرقی بینِ یک `CTE` و یک زیرپرسش در `FROM` هست؟» بعد این را بپرس: **«چرا اجرای تکه‌تکهٔ مرحله‌های یک `CTE` برای پیدا کردنِ باگ بهتر از خواندنِ کلِ پرسش است؟»** — جوابِ درست به این اشاره می‌کند که هر مرحله شمارشِ خودش را دارد و می‌شود محلِ دقیقِ خرابی را پیدا کرد. اگر جواب فقط از خوانایی حرف زد، نصفِ ماجرا را گفته.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| CTE | سی‌تی‌ای | مرحلهٔ نام‌دارِ یک پرسش، که با `WITH` تعریف می‌شود |
| WITH | ویت | کلیدواژه‌ای که مرحله‌های پرسش را تعریف می‌کند |
| recursive CTE | ریکرسیو سی‌تی‌ای | `CTE`ای که به خودش اشاره می‌کند و یک سلسله‌مراتب را می‌پیماید |
| anchor | انکر | تکهٔ اولِ یک `CTE` بازگشتی؛ نقطهٔ شروع |
| hierarchy | هایرارکی | سلسله‌مراتب؛ داده‌ای که هر سطرش به سطرِ بالادستی‌اش اشاره می‌کند |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `book_stats` باید ۱٬۲۰۰ سطر داشته باشد و نه ۱٬۰۰۰؟

**تمرینِ اجباری:** پرسشِ بخشِ ۲ را با یک مرحلهٔ تازه کامل کن: `member_stats` که برای هر عضو تعدادِ امانت‌هایش را می‌دهد. **بعد هر پنج مرحله را جدا اجرا کن و تعدادِ سطرهایشان را چاپ کن.** برای مرحلهٔ تازه بنویس **چند سطر انتظار داری و چرا** — و اگر عددِ واقعی با انتظارت نخواند، قبل از ادامه دادن دلیلش را پیدا کن.

**تمرینِ ستاره‌دار ⭐:** `CTE` بازگشتی را برعکس کن: به‌جای رفتن از ریشه به پایین، از یک بخشِ مشخص — مثلاً «شعر معاصر» — به سمتِ **بالا** برو تا به ریشه برسی. **مسیر را چاپ کن و تعدادِ گام‌ها را بشمار.** بعد سؤالِ سخت را جواب بده: **اگر `parent_id`ِ یکی از بخش‌های میانی به اشتباه به بخشی پایین‌ترش اشاره می‌کرد، این پرسش چه می‌کرد؟** جوابت باید بگوید چه چیزی را می‌شود قبل از اجرا شمرد تا جلویش گرفته شود.

## در فصل بعد

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

</div>

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