CTE: نام‌گذاری زیرپرس‌وجو برای خوانایی بیشتر

در درس زیرپرس‌وجوها، جدول مشتق‌شده را دیدیم — یک زیرپرس‌وجوی کامل، مستقیم داخل FROM. کار می‌کند، اما وقتی پرس‌وجو پیچیده‌تر می‌شود، این زیرپرس‌وجوهای تودرتو به‌سرعت شلوغ و سخت‌خوان می‌شوند. CTE (مخفف Common Table Expression) دقیقاً همین مشکل را حل می‌کند: به یک زیرپرس‌وجو یک نام موقت می‌دهد تا بتوانی مثل یک جدول واقعی به آن ارجاع بدهی.

ساختار پایه: WITH ... AS (...)

WITH category_avgs AS (
  SELECT category, AVG(price) AS avg_price
  FROM sales
  GROUP BY category
)
SELECT * FROM category_avgs;

این پرس‌وجو دقیقاً همان کاری را می‌کند که یک جدول مشتق‌شده می‌کرد، اما ساختارش مثل «اول این را حساب کن، بعد رویش کار کن» می‌خواند — خیلی نزدیک‌تر به شکل فکر آدم. بیا ببینیم:

چرا CTE از جدول مشتق‌شده بهتر است؟

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

WITH category_avgs AS (
  SELECT category, AVG(price) AS avg_price
  FROM sales
  GROUP BY category
)
SELECT AVG(avg_price) AS AvgOfCategoryAverages
FROM category_avgs;

همان نتیجه‌ی درس قبل، اما بدون تو در تو کردن پرانتزها. حالا بیا یک قدم جلوتر برویم و چند CTE را زنجیره کنیم.

زنجیره‌کردن چند CTE

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

WITH category_totals AS (
  SELECT category, SUM(price * quantity) AS revenue
  FROM sales
  GROUP BY category
),
above_avg AS (
  SELECT category, revenue
  FROM category_totals
  WHERE revenue > (SELECT AVG(revenue) FROM category_totals)
)
SELECT * FROM above_avg;

توجه کن category_totals دوبار استفاده شده (یک‌بار مستقیم، یک‌بار داخل زیرپرس‌وجوی میانگین) بدون این‌که منطق SUM(price * quantity) را دوباره بنویسیم. بیا امتحان کنیم:

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

• WITH name AS (...) یک زیرپرس‌وجو را با یک نام موقت تعریف می‌کند که بعد از آن، مثل یک جدول واقعی قابل‌استفاده است.
• CTE از جدول مشتق‌شده خواناتر است، مخصوصاً وقتی بخواهی به همان نتیجه‌ی میانی چندبار ارجاع بدهی.
• چند CTE را می‌توان با کاما زنجیره کرد؛ هرکدام می‌تواند به CTEهای قبل از خودش ارجاع بدهد.

در درس بعدی سراغ نوع خاصی از CTE می‌رویم که خودش را بازگشتی صدا می‌زند — دقیقاً همان چیزی که برای پیمایش کامل سلسله‌مراتب سازمانی (که در درس SELF JOIN دیدیم) لازم است.