CTE بازگشتی: پیمایش سلسله‌مراتب با عمق نامشخص

یادت هست در درس SELF JOIN، جدول employees را به خودش وصل کردیم تا نام مدیر هر کارمند را ببینیم؟ آن روش یک محدودیت جدی داشت: فقط یک سطح بالاتر می‌رفت. اگر بخواهیم کل زنجیره‌ی مدیریتی (مدیر، مدیرِ مدیر، و همین‌طور تا بالا) را ببینیم — بدون این‌که از قبل بدانیم چند سطح عمق دارد — SELF JOIN معمولی دیگر کافی نیست. این‌جا دقیقاً جایی است که CTE بازگشتی به کار می‌آید.

ساختار: عضو پایه + عضو بازگشتی

یک CTE بازگشتی از دو بخش که با UNION ALL به هم وصل شده‌اند تشکیل می‌شود:

• عضو پایه (Anchor Member): نقطه‌ی شروع؛ معمولاً ردیف (یا ردیف‌های) بالای سلسله‌مراتب.
• عضو بازگشتی (Recursive Member): پرس‌وجویی که به خودِ CTE ارجاع می‌دهد و هر بار یک سطح پایین‌تر می‌رود.

مثال: کل زنجیره‌ی مدیریتی هر کارمند

بیا سطح سلسله‌مراتبی هر کارمند را (مدیرعامل = سطح ۰) با یک CTE بازگشتی حساب کنیم:

WITH org_chart AS (
  -- عضو پایه: کسی که مدیر ندارد (بالای سازمان)
  SELECT id, full_name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- عضو بازگشتی: هر کارمندی که مدیرش همین الان در org_chart پیدا شده
  SELECT e.id, e.full_name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT full_name, level FROM org_chart ORDER BY level;

موتور پایگاه‌داده این پرس‌وجو را این‌طور اجرا می‌کند: اول عضو پایه (مدیرعامل) پیدا می‌شود؛ بعد عضو بازگشتی، کارمندانی که مستقیم زیر او هستند را پیدا می‌کند؛ سپس دوباره اجرا می‌شود تا کارمندانِ زیرِ آن‌ها را هم پیدا کند — و همین‌طور ادامه، تا دیگر ردیف جدیدی پیدا نشود. بیا ببینیم:

تفاوت نحو مهم: SQL Server در برابر SQLite

-- در SQL Server واقعی، این‌طور نوشته می‌شود (بدون RECURSIVE):
WITH org_chart AS (
  ...
)
SELECT * FROM org_chart;

جلوگیری از حلقه‌ی بی‌نهایت

اگر داده‌ی سلسله‌مراتبی به‌اشتباه دچار «چرخه» شود (مثلاً کارمندی که ناخواسته مدیرِ مدیرِ خودش شده)، عضو بازگشتی می‌تواند تا ابد ادامه پیدا کند. SQL Server به‌طور پیش‌فرض بعد از ۱۰۰ بار تکرار، خودش با خطا متوقف می‌شود (قابل تغییر با OPTION (MAXRECURSION n))، و در بسیاری از موتورها (از جمله SQLite) می‌توان با یک شرط توقف صریح (مثل همان WHERE n < 5 که در نمونه‌ی زیر می‌بینی) از این اتفاق جلوگیری کرد.

کاربرد دیگر: تولید یک دنباله‌ی عددی

CTE بازگشتی محدود به داده‌ی سلسله‌مراتبی نیست؛ می‌توان از آن برای تولید یک دنباله‌ی ساده هم استفاده کرد:

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

بیا امتحان کنیم:

جمع‌بندی این درس

• CTE بازگشتی از یک عضو پایه و یک عضو بازگشتی (متصل با UNION ALL) تشکیل می‌شود.
• برای داده‌ی سلسله‌مراتبی با عمق نامشخص (مثل چارت سازمانی)، جایگزین قدرتمندتر SELF JOIN است.
• SQL Server از WITH name AS (...) ساده استفاده می‌کند؛ SQLite برای بازگشت به WITH RECURSIVE نیاز دارد.
• همیشه یک شرط توقف صریح (مثل WHERE n < 10) بگذار تا از حلقه‌ی بی‌نهایت جلوگیری شود.

در آخرین درس این فصل، سراغ توابع پنجره‌ای می‌رویم: چطور رتبه‌بندی و محاسبات تجمیعی انجام دهیم، بدون این‌که مثل GROUP BY ردیف‌ها را در هم فشرده کنیم.