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

آخر این فصل میتوانی:
- یک پرسشِ چندمرحلهای را با
WITHبنویسی و مرحلههایش را نامگذاری کنی - هر مرحله را جدا اجرا کنی و با یک شمارش بسنجیاش
- یک
CTEبازگشتی بنویسی که یک سلسلهمراتب را میپیماید - ثابت کنی که پیمایش هیچ گرهای را جا نینداخته
قبل از شروع#
از فصلِ ۶: زیرپرسش در FROM. CTE همان است با یک نام و یک جای بهتر.
از فصلِ ۴ و ۵: CROSS JOIN و UNION ALL — هر دو در این فصل لازم میشوند.
دانهبندی: هر مرحلهٔ یک CTE دانهبندیِ خودش را دارد و این تغییرِ دانهبندی دقیقاً همان چیزی است که نامگذاری قابلِ دیدنش میکند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۷ | آنی |
loans |
۳٬۹۹۸ | کمتر از یک ثانیه |
sections |
۱۲ | آنی |
۱. پرسشی که خواندنش از وسط شروع میشود#
پرسش این است: در کدام شعبهها نرخِ امانت بهازای هر کتاب از میانگینِ کلِ کتابخانه بالاتر است؟
با ابزارِ فصلِ ۶ نوشتنی است، ولی اینطور درمیآید:
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)
name loans_per_book
0 مرکزی 5.65
1 نسیم 4.02
2 دانش 3.52
جوابش درست است. مشکل این است که برای فهمیدنش باید از داخلیترین پرانتز شروع کنی و بیایی بیرون — و همان عبارتِ SUM(COALESCE(x.n, 0)) / COUNT(*) دو بار نوشته شده، یک بار در SELECT و یک بار در HAVING. اگر روزی یکیشان را عوض کنی و آن یکی را یادت برود، پرسش بیصدا چیزِ دیگری میشود.
۲. همان پرسش، مرحله به مرحله#
WITH قبل از SELECT میآید و هر مرحله را با یک نام تعریف میکند. مرحلهها بهترتیب خوانده میشوند و هرکدام میتواند از قبلیها استفاده کند.
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))
name loans_per_book
0 مرکزی 5.65
1 نسیم 4.02
2 دانش 3.52
همان نتیجهٔ بخش ۱؟ True
همان سه شعبه، همان سه عدد — و این بار از بالا به پایین خواندنی.
چهار نامی که گذاشتیم مستندسازیاند، نه تزئین. loans_per_book میگوید «برای هر کتاب، چند امانت»؛ book_stats میگوید «هر کتاب با تعدادِ امانتش»؛ branch_stats میگوید «هر شعبه با دو عددش». کسی که این پرسش را شش ماه بعد باز میکند، بدونِ اجرا کردنش میفهمد هر مرحله دانهبندیاش چیست.
overall هم نکتهٔ خودش را دارد: یک CTE که فقط یک سطر و یک ستون دارد — و برای رساندنش به همهٔ سطرها از CROSS JOIN فصلِ ۴ استفاده کردیم. اینجا CROSS JOIN دقیقاً همان کاری را میکند که باید: یک عدد را کنارِ هر سطر میگذارد. شش سطر ضربدر یک سطر میشود شش سطر، پس هیچ تکثیری در کار نیست.
⚠️ مواظب باش: یک
CROSS JOINفقط وقتی بیخطر است که طرفِ دیگرش دقیقاً یک سطر داشته باشد. اگرoverallبهخاطرِ یک اشتباه دو سطر برگرداند، نتیجه بیصدا دو برابر میشود — همان fan-out فصلِ ۳، این بار از راهی که بهنظر بیخطر میآمد.
۳. اجرای تکهتکه: هر مرحله را جدا بشمار#
اینجا سودِ اصلیِ CTE است. میتوانی بهجای مرحلهٔ آخر، از هر مرحلهٔ میانی SELECT بگیری — بدونِ اینکه چیزی از پرسش را دست بزنی.
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"))
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
سه عدد، سه دانهبندی، و هر سه قابلِ بررسی:
loans_per_bookباید یک سطر برای هر کتابی داشته باشد که دستِکم یک امانت دارد — ۱٬۰۰۰، و میدانیم ۲۰۰ کتاب بیامانتاند.book_statsباید یک سطر برای هر کتاب داشته باشد — ۱٬۲۰۰. اگر اینجا ۱٬۰۰۰ میشد، یعنیLEFT JOINراJOINنوشته بودیم.branch_statsباید یک سطر برای هر شعبهای داشته باشد که کتاب دارد — ۶.
و خطِ آخر مستقل از همهٔ اینها میسنجد: جمعِ books باید ۱٬۲۰۰ و جمعِ loans باید ۳٬۹۹۸ باشد. هر دو درستاند، یعنی در این چهار مرحله نه سطری گم شد و نه سطری تکثیر.
✅ چک کن: اگر
book_statsعددی جز ۱۲۰۰ داد، مرحلهٔ دومت کتاب میاندازد یا تکثیر میکند و هیچ عددی بعد از آن قابلِ اعتماد نیست. این تنها راهِ پیدا کردنِ محلِ دقیقِ خرابی در یک پرسشِ چندمرحلهای است.
۴. CTE بازگشتی: پیمودنِ یک سلسلهمراتب#
جدولِ sections بخشهای کتابخانه را نگه میدارد و هر بخش به بخشِ بالادستیاش اشاره میکند:
print(q("SELECT id, name, parent_id FROM sections ORDER BY id"))
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
«شعر معاصر» زیرِ «شعر» است، «شعر» زیرِ «ادبیات»، و «ادبیات» زیرِ «کتابخانه». برای رسیدن از هر بخش به ریشهاش، سه JOIN لازم است — ولی از کجا بدانی سهتا؟ اگر فردا کسی یک طبقهٔ دیگر اضافه کند، پرسشت بیصدا ناقص میشود.
WITH RECURSIVE این را حل میکند. ساختارش دو تکه دارد که با UNION ALL به هم وصل شدهاند: تکهٔ اول نقطهٔ شروع است، و تکهٔ دوم به خودِ CTE اشاره میکند و تا وقتی سطرِ تازهای پیدا شود ادامه میدهد.
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
"""))
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 داستان کودک کتابخانه > کودک > داستان کودک
دوازده سطر و چهار طبقه، با یک پرسش که هیچجا عددِ ۴ در آن نوشته نشده.
سه چیز در همین پرسش اتفاق افتاد و هر سه ارزشِ نام بردن دارند: depth با هر قدم یکی بالا رفت، path با || مسیرِ کامل را ساخت، و JOIN tree AS t — پیوندِ CTE به خودش — همان چیزی است که «بازگشتی» را ممکن میکند.
✅ چک کن: تعدادِ سطرهای نتیجه باید دقیقاً برابرِ تعدادِ سطرهای
sectionsباشد — ۱۲ و ۱۲. اگر کمتر بود، بخشی از درخت اصلاً پیموده نشده: یعنی بخشی وجود دارد کهparent_idش به گرهای اشاره میکند که نیست، یا چند بخش در یک حلقهاند که از ریشه به آنها راهی نیست، پس هیچوقت به آنها نمیرسیم. این همان «چه کسی از قلم افتاد» است، در شکلِ سلسلهمراتبیاش.
🔧 اگر کار نکرد: اگر تکهٔ اول — نقطهٔ شروع — را جا بیندازی، پرسش هیچ جایی برای شروع ندارد و
SQLiteصریح اعتراض میکند:
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)
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
ولی خطای خطرناکترِ CTE بازگشتی خطا نمیدهد: اگر دادهات حلقه داشته باشد، یعنی بخشی که در نهایت به خودش برمیگردد، پیمایشی که داخلِ حلقه میافتد تا ابد سطر تولید میکند. پیمایشِ همین بخش از ریشه به پایین به حلقه نمیرسد، چون هر بخش فقط یک والد دارد، و فقط چند سطر کم میآورد؛ خطرِ اصلی برای پیمایشِ رو به بالاست. پس همان آزمونِ بالا را بزن: تعدادِ سطرهای نتیجه نباید از تعدادِ سطرهای جدول بیشتر شود، و برای اینکه پرسشی که تمام نمیشود هم جواب بدهد، LIMIT را یکی بیشتر از تعدادِ سطرهای جدول بگذار.
و یک صداقت دربارهٔ همین بخش: این تنها جای آوند است که به CTE بازگشتی نیاز داریم، و در بیستوهفت فصلِ باقیماندهٔ دوره دیگر بهکارش نمیبریم. آوردیمش چون هر دادهای که ستونِ «والد» دارد بدونِ آن پیمودنی نیست — دستهبندیِ تودرتو، نمودارِ سازمانی، پاسخبهپاسخ — نه چون در ادامهٔ این دوره لازمش داری. اگر روزی به چنین دادهای برخوردی، همین بخش را دوباره باز کن.
📏 اندازه بگیر: یک سطر یعنی چه؟ در
loans_per_bookیک کتابِ امانترفته، درbook_statsیک کتاب، درbranch_statsیک شعبه، و درtreeیک بخش از سلسلهمراتب. چهار دانهبندیِ متفاوت در یک پرسش — و نامگذاری تنها چیزی است که این را قابلِ دیدن میکند. با کدام شمارشِ دوم سنجیدی؟ با سهتا: ۱٬۰۰۰ بهعلاوهٔ ۲۰۰ برابرِ ۱٬۲۰۰؛ جمعِ ستونهایbranch_statsکه ۱٬۲۰۰ و ۳٬۹۹۸ شد؛ و ۱۲ سطرِtreeبرابرِ ۱۲ سطرِsections. چه کسی از قلم افتاد؟ شعبهٔ آفتاب، که چون کتابی ندارد درbranch_statsسطری ندارد — ولی «میانگینِ کل» امانت بهازای هر کتاب است و مخرجش ۱٬۲۰۰ کتاب، پس نبودنِ آفتاب آن را عوض نمیکند؛ چیزی که گم میشود سطرِ خودِ آفتاب است.
🤖 از دستیارت بپرس: «چه فرقی بینِ یک
CTEو یک زیرپرسش درFROMهست؟» بعد این را بپرس: «چرا اجرای تکهتکهٔ مرحلههای یکCTEبرای پیدا کردنِ باگ بهتر از خواندنِ کلِ پرسش است؟» — جوابِ درست به این اشاره میکند که هر مرحله شمارشِ خودش را دارد و میشود محلِ دقیقِ خرابی را پیدا کرد. اگر جواب فقط از خوانایی حرف زد، نصفِ ماجرا را گفته.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| CTE | سیتیای | مرحلهٔ نامدارِ یک پرسش، که با WITH تعریف میشود |
| WITH | ویت | کلیدواژهای که مرحلههای پرسش را تعریف میکند |
| recursive CTE | ریکرسیو سیتیای | CTEای که به خودش اشاره میکند و یک سلسلهمراتب را میپیماید |
| anchor | انکر | تکهٔ اولِ یک CTE بازگشتی؛ نقطهٔ شروع |
| hierarchy | هایرارکی | سلسلهمراتب؛ دادهای که هر سطرش به سطرِ بالادستیاش اشاره میکند |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
هفت فصل ابزار جمع کردیم. فصلِ آخر هیچ چیزِ تازهای یاد نمیدهد و بهجایش یک کارِ کامل تحویل میدهد: یک پرسشِ واقعی، سه جدول، یک پاسخ — و اثباتِ اینکه در راه نه سطری تکثیر شده و نه سطری گم.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.