میز کار مدرن با مانیتور و بلندگو
برنامه نویسی, پایگاه داده

عبارات جدول مشترک (CTE) چیست؟

توسط . ارسال شده در

تیم‌های داده اغلب مقدار قابل‌توجهی از زمان خود را صرف کار با پرس‌وجوهای پیچیده‌ی SQL می‌کنند که می‌توانند صدها خط را دربر گیرند، که این امر اشکال‌زدایی را دشوار کرده و همکاری را مختل می‌سازد. این چالش زمانی حتی حادتر می‌شود که با ساختارهای داده‌ای سلسله‌مراتبی یا تبدیل‌های چندمرحله‌ای سروکار داریم که نیاز دارند نتایج میانی چندین بار در همان پرس‌وجو مورد ارجاع قرار گیرند.
عبارات جدول مشترک (Common Table Expressions یا به‌اختصار CTEs) این مشکل بنیادی در خوانایی و نگه‌داری را با فراهم‌کردن روشی برای شکستن پرس‌وجوهای پیچیده به اجزای منطقی و نام‌گذاری‌شده حل می‌کنند.
یک عبارت جدول مشترک، مجموعه‌نتیجه‌ای موقتی است که تنها در محدوده‌ی اجرای یک دستور واحد SQL وجود دارد و می‌تواند چندین بار در همان دستور مورد ارجاع قرار گیرد. بر خلاف زیردرخواست‌ها (subqueries)، CTEها خوانایی و قابلیت استفاده‌ی مجدد برتری ارائه می‌دهند، در حالی که از جداول موقتی به این جهت متمایز هستند که سازه‌هایی گذرا هستند که بلافاصله پس از اجرای پرس‌وجو ناپدید می‌شوند.
می‌توانید از CTEها درون دستورات SELECT، INSERT، UPDATE یا DELETE استفاده کنید، که آن‌ها را به ابزارهایی چندمنظوره برای دست‌کاری و تحلیل داده تبدیل می‌کند. آن‌ها در اصل مانند زیردرخواست‌های نام‌گذاری‌شده‌ای عمل می‌کنند که می‌توانند عملیات پیچیده را ساده کنند، سازمان‌دهی کد را بهبود بخشند، و عملیات بازگشتی را برای پردازش داده‌های سلسله‌مراتبی ممکن سازند.

ساختار دستور (CTE) Common Table Expressionsچگونه است؟

CTEها با استفاده از کلیدواژه‌ی WITH تعریف می‌شوند، که در ادامه‌ی آن نام CTE و پرس‌وجویی قرار می‌گیرد که مجموعه‌نتیجه را تعریف می‌کند. نحو (syntax) پایه، انعطاف‌پذیری لازم برای هر دو حالت ساده و پیچیده را فراهم می‌آورد:

WITH cte_name (column1, column2, ...) AS (
SELECT ...
FROM ...
WHERE ...
)

در اینجا:

  • cte_name نمایانگر شناسه‌ای است که به CTE اختصاص می‌دهید، و باید توصیفی باشد و هدف مجموعه‌نتیجه‌ی میانی را نشان دهد.

  • (column1, column2, …) نام‌های مستعار اختیاری ستون‌ها را برای خروجی CTE تعریف می‌کند، که زمانی مفید است که بخواهید ستون‌ها را از پرس‌وجوی زیرین تغییرنام دهید.

  • SELECT … FROM … WHERE … شامل منطق پرس‌وجو است که مجموعه‌نتیجه را تولید می‌کند، و می‌تواند شامل joinها، تجمیع‌ها (aggregations)، توابع پنجره‌ای (window functions) و سایر عملیات SQL باشد.

مشخص‌کردن فهرست ستون‌ها اختیاری است زمانی که نام ستون‌ها از پرس‌وجوی زیرین کافی باشد. با این حال، نام‌گذاری صریح ستون‌ها خوانایی را بهبود می‌دهد و از ابهام جلوگیری می‌کند، به‌ویژه در پرس‌وجوهای پیچیده با چندین join یا فیلدهای محاسبه‌شده.
می‌توانید چندین CTE را با استفاده از جداکننده‌ی ویرگول زنجیره کنید، که اجازه می‌دهد هر CTE بعدی به CTEهای قبلی ارجاع دهد. این قابلیت زنجیره‌سازی امکان تبدیل‌های داده‌ای گام‌به‌گام را فراهم می‌کند که بر اساس یکدیگر ساخته می‌شوند و پیشرفت منطقی روشنی را در طول عملیات تحلیلی پیچیده ایجاد می‌کنند.
تعریف CTE باید بلافاصله پیش از پرس‌وجوی اصلی که از آن استفاده می‌کند بیاید. نمی‌توانید CTEها را به‌صورت جداگانه تعریف کنید یا آن‌ها را در میان چندین دستور SQL مجزا ارجاع دهید، زیرا آن‌ها فقط در محدوده‌ی همان دستور واحدی که در آن تعریف شده‌اند وجود دارند.

چرا باید از عبارات جدول مشترک (CTEها) در جریان‌های کاری داده‌ای خود استفاده کنید؟

CTEها به شما این امکان را می‌دهند که به‌طور مؤثر پرس‌وجوهای پیچیده را در کد SQL طولانی مدیریت کنید، در حالی که چندین مزیت راهبردی برای جریان‌های کاری مهندسی داده ارائه می‌دهند.
تیم‌های داده‌ی مدرن از CTEها نه‌تنها برای سازمان‌دهی پرس‌وجوها، بلکه به‌عنوان بلوک‌های سازنده‌ی بنیادی برای خطوط تبدیل داده‌ای قابل نگه‌داری استفاده می‌کنند.

می‌توانید بلوک‌های مختلف کد را برچسب‌گذاری کنید تا هدف هر بخش از پرس‌وجوهای طولانی را درک کنید، و به این ترتیب SQL خود‌مستندشونده‌ای (self-documenting SQL) ایجاد نمایید که بار ذهنی را در طول بازبینی‌های کد و جلسات اشکال‌زدایی کاهش می‌دهد.
این برچسب‌گذاری به‌ویژه در محیط‌های مشارکتی که چندین عضو تیم باید پرس‌وجوهای تحلیلی پیچیده را درک و اصلاح کنند ارزشمند می‌شود.

آن‌ها سازمان‌دهی منطقی پرس‌وجوهای SQL را تسهیل می‌کنند، که شناسایی و رفع خطاها را با جداکردن مراحل تبدیل خاص آسان‌تر می‌سازد.
زمانی که یک پرس‌وجوی پیچیده نتایج غیرمنتظره‌ای تولید می‌کند، می‌توانید هر CTE را به‌صورت جداگانه بررسی کنید تا دقیقاً مشخص شود که منطق در کجا از کار افتاده است، که زمان اشکال‌زدایی را به‌طور چشمگیری کاهش می‌دهد.

می‌توانید از همان CTE در چندین بخش از یک پرس‌وجو بدون بازنویسی مکرر همان زیردرخواست استفاده کنید، و از اصل DRY (Don’t Repeat Yourself — خود را تکرار نکنید) پیروی کنید که سربار نگه‌داری را کاهش می‌دهد.
این قابلیت استفاده‌ی مجدد در پرس‌وجوهای تحلیلی که نیاز دارند منطق فیلترینگ یا تجمیع مشابهی را در بخش‌های مختلف خروجی نهایی اعمال کنند حیاتی می‌شود.

CTEها همچنین به‌عنوان پله‌هایی برای تبدیل‌های پیچیده عمل می‌کنند که بیان آن‌ها در یک پرس‌وجوی یکپارچه و بزرگ دشوار است.
با تقسیم تبدیل‌ها به مراحل متوالی و منطقی، کدی قابل نگه‌داری‌تر ایجاد می‌کنید که سایر اعضای تیم می‌توانند با اطمینان آن را درک و اصلاح کنند.

چگونه عبارت جدول مشترک (CTE Syntax) را ساختاربندی می‌کنید؟

CTEها با استفاده از کلیدواژه‌ی WITH تعریف می‌شوند، که در ادامه‌ی آن نام CTE و پرس‌وجویی قرار می‌گیرد که مجموعه‌نتیجه (result set) را تعریف می‌کند. نحو (syntax) پایه، انعطاف‌پذیری لازم را هم برای سناریوهای ساده و هم پیچیده فراهم می‌سازد:

WITH cte_name (column1, column2, ...) AS (
SELECT ...
FROM ...
WHERE ...
)

در اینجا:

  • cte_name نشان‌دهنده‌ی شناسه‌ای است که شما به CTE اختصاص می‌دهید، که باید توصیفی باشد و هدف مجموعه‌نتیجه‌ی میانی را مشخص کند.

  • (column1, column2, …) فهرست اختیاری نام‌های مستعار ستون‌ها برای خروجی CTE را تعریف می‌کند، که در مواقعی مفید است که نیاز دارید ستون‌ها را از پرس‌وجوی اصلی تغییرنام دهید.

  • SELECT … FROM … WHERE … شامل منطق پرس‌وجویی است که مجموعه‌نتیجه را تولید می‌کند، و می‌تواند شامل joinها، تجمیع‌ها (aggregations)، توابع پنجره‌ای (window functions) و سایر عملیات SQL باشد.

مشخص کردن فهرست ستون‌ها اختیاری است زمانی که نام ستون‌ها از پرس‌وجوی زیرین کافی باشند.
با این حال، نام‌گذاری صریح ستون‌ها باعث بهبود خوانایی و جلوگیری از ابهام می‌شود، به‌ویژه در پرس‌وجوهای پیچیده‌ای که شامل چندین join یا فیلد محاسبه‌شده هستند.

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

تعریف CTE باید بلافاصله پیش از پرس‌وجوی اصلی که از آن استفاده می‌کند بیاید.
شما نمی‌توانید CTEها را به‌صورت جداگانه تعریف کرده یا آن‌ها را در میان چندین دستور SQL مجزا ارجاع دهید، زیرا آن‌ها تنها در محدوده‌ی همان دستور واحدی که در آن تعریف شده‌اند وجود دارند.

انواع اصلی عبارات جدول مشترک (CTEs) کدام‌اند؟

در مهندسی داده، انواع مختلفی از CTEها وجود دارند که هرکدام کاربردها و الگوهای تبدیل خاصی را در جریان‌های کاری داده‌ی مدرن ارائه می‌کنند.

CTEهای غیر بازگشتی (Non-recursive CTEs)

CTEهای غیر بازگشتی به‌طور مکرر به خودشان ارجاع نمی‌دهند.
آن‌ها معمولاً در طول تبدیل‌ها، فیلترسازی یا عملیات تجمیع مورد استفاده قرار می‌گیرند، جایی که نیاز دارید مجموعه‌نتیجه‌های میانی ایجاد کنید تا وضوح و قابلیت استفاده‌ی مجدد افزایش یابد.

WITH cte_name AS (
SELECT *
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name
WHERE additional_condition;

  • WITH تعریف CTE را آغاز می‌کند.

  • cte_name به‌عنوان شناسه‌ای عمل می‌کند که در عملیات بعدی برای ارجاع به CTE استفاده می‌شود.

  • (SELECT * FROM table_name WHERE condition) محتوای CTE را از طریق عملیات استاندارد پرس‌وجوی SQL تعریف می‌کند.

  • SELECT * FROM cte_name نمایانگر پرس‌وجوی اصلی است که داده‌ها را از CTE بازیابی می‌کند.

CTEهای غیر بازگشتی در تجزیه‌ی پرس‌وجوهای تحلیلی پیچیده به گام‌های قابل مدیریت مهارت دارند.
برای مثال، ممکن است از یک CTE غیر بازگشتی برای محاسبه‌ی شاخص ارزش طول عمر مشتری (Customer Lifetime Value) در یک گام استفاده کنید، سپس در پرس‌وجوی اصلی فیلترها و joinهای اضافی را اعمال کنید بدون اینکه منطق تجمیع پیچیده را دوباره بنویسید.

CTEهای بازگشتی (Recursive CTEs)

یک CTE بازگشتی به خودش ارجاع می‌دهد و امکان اجرای مکرر پرس‌وجوها را فراهم می‌سازد، که نتیجه را در مراحل مختلف می‌سازد.
این قابلیت برای داده‌های سلسله‌مراتبی مانند نمودارهای سازمانی، دسته‌بندی محصولات یا هر ساختار رابطه‌ی والد-فرزند ضروری است.

WITH expression_name (column_list) AS (
-- Anchor member
initial_queryUNION ALL— Recursive member that references expression_name
recursive_query
)
SELECT *
FROM expression_name;

  • expression_name به‌عنوان شناسه‌ای برای CTE بازگشتی عمل می‌کند.

  • (column_list) فهرست اختیاری و صریحی از ستون‌ها در مجموعه‌نتیجه ارائه می‌دهد.

  • Anchor member نقطه‌ی آغاز بازگشت را با داده‌های حالت پایه تعیین می‌کند.

  • UNION ALL مجموعه‌نتیجه‌های عضو پایه و عضو بازگشتی را ترکیب می‌کند.

  • Recursive member شامل منطق خودارجاعی است که هر تکرار را پردازش می‌کند.

CTEهای بازگشتی از طریق اجرای تکرارشونده عمل می‌کنند، جایی که هر تکرار نتایج تکرار قبلی را پردازش می‌کند تا زمانی که دیگر ردیف جدیدی تولید نشود.
این رویکرد امکان پیمایش ساختارهای سلسله‌مراتبی را بدون دانستن عمق از پیش فراهم می‌سازد، که آن را برای گزارش‌های سازمانی، تحلیل صورتحساب مواد (Bill of Materials)، و سناریوهای تحلیل شبکه‌ای بسیار ارزشمند می‌سازد.

CTEهای چندگانه (Multiple CTEs)

می‌توانید چندین CTE را در یک پرس‌وجو تعریف کنید، که هرکدام می‌توانند بر اساس قبلی ساخته شوند، و بدین ترتیب خطوط تبدیل داده‌ی پیچیده‌ای را در درون یک دستور SQL ایجاد می‌کنند:

WITH cte1 AS (
SELECT ...
),
cte2 AS (
SELECT ...
FROM cte1
),
cte3 AS (
SELECT ...
FROM cte2
)
SELECT *
FROM cte3;

در اینجا، cte1، cte2، cte3 نمایانگر CTEهای جداگانه‌ای هستند که به‌صورت زنجیره‌ای در یک خط تبدیل متوالی به هم متصل شده‌اند.
هر CTE می‌تواند به هر CTE تعریف‌شده‌ی قبلی در همان عبارت WITH ارجاع دهد، که تبدیل‌های داده‌ای پیچیده را ممکن می‌سازد در حالی که خوانایی را از طریق جداسازی منطقی حفظ می‌کند.

CTEهای چندگانه به‌ویژه در سناریوهای مهندسی داده مفید هستند، جایی که نیاز دارید تبدیل‌های متوالی مانند پاک‌سازی داده، غنی‌سازی (enrichment)، و تجمیع را در یک پرس‌وجوی تحلیلی انجام دهید.
این الگو نیاز به جداول موقتی را کاهش می‌دهد در حالی که جداسازی روشن میان مراحل تبدیل را حفظ می‌کند.

مثال‌های کاربردی رایج برای عبارات جدول مشترک (CTEs) کدام‌اند؟

CTEها چالش‌های تحلیلی و عملیاتی خاصی را برطرف می‌کنند که اغلب در جریان‌های کاری پردازش داده پدید می‌آیند.
درک این موارد استفاده به شما کمک می‌کند تا فرصت‌هایی را برای بهبود قابلیت نگه‌داری و عملکرد پرس‌وجو شناسایی کنید.

CTEهای بازگشتی به پرس‌وجوی مجموعه‌داده‌های سلسله‌مراتبی مانند نمودارهای سازمانی کارکنان کمک می‌کنند، جایی که نیاز دارید روابط مدیر ـ زیردست را بدون دانستن عمق سازمان طی کنید.
برای مثال، می‌توانید همه‌ی کارکنانی را که به یک مدیر اجرایی خاص در چندین سطح سلسله‌مراتبی گزارش می‌دهند بیابید، با استفاده از یک CTE بازگشتی که از مدیر هدف شروع کرده و به‌صورت تکراری زیردستان مستقیم و غیرمستقیم او را پیدا می‌کند.

CTEها به‌عنوان جایگزینی برای ایجاد نماهای موقتی (temporary views) در پایگاه داده عمل می‌کنند، و کارکردی مشابه را بدون سربار مدیریت اشیاء پایگاه‌داده‌ی دائمی فراهم می‌سازند.
این رویکرد به‌ویژه در جریان‌های کاری تحلیلی مفید است، جایی که به مجموعه‌نتیجه‌های میانی نیاز دارید اما نمی‌خواهید طرح پایگاه‌داده (schema) را با اشیاء موقتی که نیاز به پاک‌سازی دارند، شلوغ کنید.

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

CTEها الگوهای تجمیع پیچیده‌ای را ممکن می‌سازند، جایی که باید داده‌ها را در چندین سطح درون همان پرس‌وجو تجمیع کنید.
گزارش‌های درآمدی اغلب به داده‌های جزئی (line-item) و همچنین سطوح مختلف تجمیع (بر اساس محصول، منطقه، بازه‌ی زمانی) نیاز دارند، که CTEها می‌توانند آن را به‌صورت ظریف و منظم مدیریت کنند، با فراهم کردن نماهای تجمیع‌شده‌ی مختلف که پرس‌وجوی اصلی می‌تواند آن‌ها را ترکیب کند.

بررسی کیفیت داده‌ها از CTEها بهره‌مند می‌شود زمانی که نیاز دارید ناهنجاری‌های داده را پیش از انجام عملیات تحلیلی اصلی شناسایی و مدیریت کنید.
می‌توانید از CTEها برای شناسایی مقادیر پرت (outliers)، مقادیر گم‌شده، یا قالب‌های ناسازگار داده استفاده کنید، و سپس این معیارهای کیفیت را در پرس‌وجوی تحلیلی اصلی خود مورد ارجاع قرار دهید تا نتایج مستحکم و قابل اعتماد حاصل شود.

عبارات جدول مشترک (CTEها) چگونه با ساختارهای جایگزین SQL مقایسه می‌شوند؟

CTEها به هدف‌های مشابهی با زیردرخواست‌ها (subqueries) و نماها (views) خدمت می‌کنند، اما مزایای متمایزی را از نظر خوانایی، قابلیت نگه‌داری، و محدوده‌ی اجرا ارائه می‌دهند.
درک تفاوت‌ها به شما کمک می‌کند تا بهترین ساختار را برای نیازهای خاص پروژه‌ی داده‌ی خود انتخاب کنید.

مقایسه با زیردرخواست‌ها (Subqueries)

CTEها به عنوان زیردرخواست‌های نام‌گذاری‌شده عمل می‌کنند که می‌توانند در همان پرس‌وجو به چندین بخش ارجاع داده شوند، در حالی که زیردرخواست‌های سنتی فقط در محلِ درون یک عبارت SQL تعبیه می‌شوند.

-- استفاده از زیردرخواست
SELECT *
FROM (
SELECT customer_id, SUM(order_total) AS total
FROM orders
GROUP BY customer_id
) AS subquery
WHERE total > ۱۰۰۰;— استفاده از CTE
WITH customer_totals AS (
SELECT customer_id, SUM(order_total) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > ۱۰۰۰;

CTEها وضوح بیشتری ارائه می‌کنند، زیرا هر مرحله‌ی منطقی به‌صورت جداگانه با نام توصیفی تعریف شده است.
این وضوح زمانی که منطق پرس‌وجو رشد می‌کند یا شامل چندین مرحله‌ی تبدیل می‌شود، بسیار سودمند است.

از سوی دیگر، زیردرخواست‌ها ممکن است باعث شود ساختار کلی پرس‌وجو فشرده‌تر و دشوارتر برای اشکال‌زدایی شود، زیرا هر لایه درون دیگری تودرتو قرار دارد، و فهم جریان داده را دشوارتر می‌سازد.

مقایسه با نماها (Views)

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

-- تعریف نما
CREATE VIEW customer_totals AS
SELECT customer_id, SUM(order_total) AS total
FROM orders
GROUP BY customer_id;— استفاده از نما
SELECT *
FROM customer_totals
WHERE total > ۱۰۰۰;

CTEها نسبت به نماها سبک‌تر هستند زیرا به مجوزهای مدیریت طرح پایگاه‌داده یا نگهداری اشیاء ذخیره‌شده نیاز ندارند.
این امر آن‌ها را برای تحلیل‌های موقتی، گزارش‌گیری‌های یک‌باره، یا آزمایش سریع مدل‌های داده‌ای جدید ایدئال می‌سازد.

نماها، در مقابل، مناسب‌تر برای مواردی هستند که نیاز دارید منطق پرس‌وجوی استاندارد را در سراسر تیم‌ها یا برنامه‌های کاربردی مشترک‌سازی کنید، زیرا در سطح طرح پایگاه‌داده ماندگار هستند و می‌توانند بهینه‌سازی‌های اضافی توسط سیستم مدیریت پایگاه‌داده دریافت کنند.

CTEها همچنین نسبت به نماها انعطاف‌پذیری بیشتری دارند، زیرا می‌توانید آن‌ها را درجا تعریف کنید، بدون اینکه به‌روزرسانی یا تغییر ساختار پایگاه‌داده را لازم داشته باشند.
این موضوع به‌ویژه در محیط‌های مشارکتی یا sandbox داده سودمند است، جایی که مهندسان داده و تحلیل‌گران باید آزمایش سریع انجام دهند بدون آنکه بر طرح اصلی تأثیر بگذارند.

عبارات جدول مشترک (CTEs) چگونه خوانایی و قابلیت نگه‌داری پرس‌وجوها را بهبود می‌دهند؟

CTEها پرس‌وجوهای پیچیده‌ی SQL را با تقسیم آن‌ها به بخش‌های منطقی که هرکدام هدف مشخصی دارند، ساده‌تر می‌سازند.
این ساختار، فهم منطق، اشکال‌زدایی خطاها و به‌روزرسانی اجزای خاص را بدون خطر تأثیر بر کل سیستم آسان‌تر می‌کند.

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

WITH filtered_orders AS (
SELECT *
FROM orders
WHERE order_date >= '۲۰۲۵-۰۱-۰۱'
),
aggregated_sales AS (
SELECT customer_id, SUM(order_total) AS total_sales
FROM filtered_orders
GROUP BY customer_id
)
SELECT *
FROM aggregated_sales
WHERE total_sales > ۱۰۰۰;

در این مثال:

  • filtered_orders پرس‌وجوی مربوط به فیلترینگ را کپسوله می‌کند.

  • aggregated_sales تجمیع را به‌صورت جداگانه انجام می‌دهد.

  • پرس‌وجوی نهایی فقط روی منطق کسب‌وکار تمرکز دارد (مشتریانی که فروش کل بالای ۱۰۰۰ دارند).

این تفکیک مسئولیت‌ها باعث می‌شود که هر مرحله به‌طور مستقل قابل بررسی و اشکال‌زدایی باشد.
اگر خروجی نهایی اشتباه باشد، می‌توانید به‌راحتی ابتدا filtered_orders را بررسی کنید تا مطمئن شوید فیلتر درست اعمال شده است، بدون نیاز به جست‌وجوی یک پرس‌وجوی پیچیده‌ی تو در تو.

CTEها مستندسازی ضمنی (implicit documentation) را فراهم می‌کنند، زیرا نام‌های CTE (مثل filtered_orders یا aggregated_sales) نقش توضیحات درون‌کد را ایفا می‌کنند.
تیم‌های داده از این ویژگی برای اجرای استانداردهای کدنویسی استفاده می‌کنند که در آن هر CTE باید با نام توصیفی و منطق واضحی همراه باشد.

در جریان‌های کاری داده‌ای مشارکتی، این وضوح منجر به بازبینی سریع‌تر کد، کاهش خطرات ناشی از ویرایش اشتباه بخش‌های مرتبط، و افزایش اعتماد به منطق تبدیل داده‌ها می‌شود.
همچنین، این ساختار به مهندسان تازه‌وارد کمک می‌کند تا منطق موجود در خطوط پردازش داده (data pipelines) را سریع‌تر درک کنند.

CTEها همچنین نگه‌داری پرس‌وجوها را آسان‌تر می‌کنند، زیرا اصلاح یک بخش از پرس‌وجو نیازی به بازنویسی کل پرس‌وجوی تودرتو ندارد.
برای مثال، اگر بخواهید شرط فیلترینگ در مرحله‌ی اول را تغییر دهید، فقط کافی است بلوک filtered_orders را ویرایش کنید، بدون آنکه نگران تأثیر مستقیم بر سایر مراحل باشید.

در نتیجه، CTEها اصل طراحی ماژولار را به SQL می‌آورند، مشابه همان چیزی که در مهندسی نرم‌افزار برای نگه‌داری بهتر کد مورد استفاده قرار می‌گیرد.

عبارات جدول مشترک (CTEs) چگونه عملکرد و بهینه‌سازی پرس‌وجو را بهبود می‌بخشند؟

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

اجتناب از تکرار منطق پرهزینه

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

WITH expensive_calc AS (
SELECT customer_id, SUM(order_total) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM expensive_calc
WHERE total > ۱۰۰۰
UNION ALL
SELECT *
FROM expensive_calc
WHERE total BETWEEN ۵۰۰ AND ۱۰۰۰;

در این مثال، محاسبه‌ی گران‌قیمت (تجمیع سفارش‌ها) تنها یک بار تعریف شده و دوبار استفاده می‌شود.
این کار نه‌تنها باعث وضوح بیشتر می‌شود، بلکه احتمال اجرای مکرر زیردرخواست یکسان را در برخی سیستم‌ها از بین می‌برد.

امکان بهینه‌سازی بهتر توسط موتور SQL

اکثر بهینه‌سازهای مدرن SQL (مانند PostgreSQL، SQL Server و Snowflake) می‌توانند CTEها را “inline” کنند — به این معنا که موتور اجرای پرس‌وجو، منطق CTE را در بدنه‌ی پرس‌وجوی اصلی ادغام کرده و آن را مانند زیردرخواست عادی بهینه می‌کند.
این فرآیند به بهینه‌ساز اجازه می‌دهد مسیرهای اجرایی کارآمدتری انتخاب کند و از شاخص‌ها (indexes) یا آمارها به شکلی مؤثرتر بهره ببرد.

در نسخه‌های قدیمی‌تر SQL Server، CTEها گاهی به‌عنوان “materialized” در نظر گرفته می‌شدند (یعنی ابتدا اجرا می‌شدند و نتیجه موقت ذخیره می‌شد)، که می‌توانست سربار ایجاد کند.
اما در سیستم‌های مدرن، این رفتار معمولاً بهینه شده است و موتور تصمیم می‌گیرد که آیا باید نتیجه را موقتاً ذخیره کند یا درجا (inline) اجرا کند.

ساده‌سازی بهینه‌سازی دستی پرس‌وجو

CTEها به مهندسان داده اجازه می‌دهند که منطق پیچیده‌ی پرس‌وجو را به بخش‌های جدا تقسیم کرده و هر بخش را به‌طور مستقل برای بهینه‌سازی تحلیل کنند.
به‌جای آن‌که با یک پرس‌وجوی عظیم و چندلایه سروکار داشته باشید، می‌توانید هر CTE را جداگانه اجرا و بررسی کنید تا عملکرد آن را بسنجید.

برای مثال، می‌توانید زمان اجرای هر CTE را اندازه‌گیری کرده، شاخص‌های مناسب برای جداول مرتبط بسازید، و سپس بهینه‌سازی‌ها را در مرحله‌ی خاصی اعمال کنید بدون آنکه منطق کلی پرس‌وجو را تغییر دهید.

هشدار درباره‌ی هزینه‌ی احتمالی در برخی سیستم‌ها

در برخی پایگاه‌داده‌ها، به‌ویژه سیستم‌هایی که CTEها را همیشه materialize می‌کنند (مانند نسخه‌های قدیمی‌تر PostgreSQL یا SQL Server)، استفاده‌ی بیش‌ازحد از CTEها می‌تواند باعث کاهش عملکرد شود، زیرا هر CTE ممکن است به‌صورت کامل محاسبه و در حافظه ذخیره گردد.
در چنین شرایطی، بهتر است برای قطعاتی از منطق که چندان پیچیده نیستند، از زیردرخواست‌های معمولی استفاده شود.

توصیه‌ی کلی این است که CTEها را در وهله‌ی اول برای وضوح و نگه‌داری به‌کار ببرید و سپس عملکرد آن‌ها را در محیط اجرایی خاص خود اندازه‌گیری کنید.

به‌طور خلاصه، CTEها با کاهش تکرار منطق، افزایش قابلیت بهینه‌سازی خودکار موتور SQL، و تسهیل درک مراحل میانی، به بهبود عملکرد کمک می‌کنند — به‌ویژه در سناریوهای تحلیلی که خوانایی و پشتیبانی در اولویت قرار دارند.

چگونه می‌توانید از عبارات جدول مشترک (CTEs) در جریان‌های کاری مهندسی داده استفاده کنید؟

CTEها ابزار قدرتمندی برای مهندسی داده هستند، زیرا امکان تقسیم مراحل پیچیده‌ی پردازش داده به بخش‌های منطقی، قابل نگه‌داری و قابل تست را فراهم می‌کنند.
آن‌ها به تیم‌های داده اجازه می‌دهند جریان‌های کاری خود را به‌صورت ماژولار بسازند و مراحل مختلف پردازش داده را مستقل از هم مدیریت کنند.

پاک‌سازی و استانداردسازی داده‌ها

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

WITH standardized_data AS (
SELECT
LOWER(TRIM(name)) AS name_clean,
COALESCE(email, '') AS email_clean,
customer_id
FROM raw_customers
),
deduplicated_data AS (
SELECT *
FROM standardized_data
WHERE ROW_NUMBER() OVER (PARTITION BY email_clean ORDER BY customer_id) = ۱
)
SELECT *
FROM deduplicated_data;

در این مثال:

  • standardized_data داده‌ها را پاک‌سازی و استاندارد می‌کند.

  • deduplicated_data رکوردهای تکراری را حذف می‌کند.

  • پرس‌وجوی نهایی فقط داده‌های پاک‌سازی‌شده و بدون تکرار را ارائه می‌دهد.

تجمیع و محاسبات میانی

CTEها همچنین برای محاسبات میانی و تجمیع داده‌ها قبل از مرحله‌ی نهایی تحلیل مفید هستند.
این قابلیت اجازه می‌دهد محاسبات پیچیده در CTEها انجام شوند و سپس نتایج آن‌ها به‌صورت شفاف در پرس‌وجوی اصلی مورد استفاده قرار گیرد.

WITH customer_sales AS (
SELECT customer_id, SUM(order_value) AS total_sales
FROM orders
GROUP BY customer_id
),
high_value_customers AS (
SELECT *
FROM customer_sales
WHERE total_sales > ۱۰۰۰
)
SELECT *
FROM high_value_customers;

ترکیب داده‌ها و مراحل متوالی

CTEها برای ایجاد خطوط پردازش داده‌ای متوالی نیز کاربرد دارند، جایی که مرحله‌ی بعدی به نتایج مرحله‌ی قبلی وابسته است.
این روش باعث می‌شود مراحل تبدیل داده به‌صورت ماژولار، قابل تست و قابل نگه‌داری باشند و همچنین تغییرات در یک مرحله بدون تأثیر مستقیم بر سایر مراحل اعمال شود.

تسهیل جریان‌های کاری تحلیلی پیچیده

در جریان‌های کاری تحلیلی پیچیده، CTEها کمک می‌کنند که پرس‌وجوها خواناتر و قابل درک‌تر شوند و هر مرحله‌ی منطقی با نام توصیفی مشخص شود.
این ویژگی برای تیم‌های داده که چندین مهندس و تحلیل‌گر روی همان مجموعه داده کار می‌کنند، اهمیت ویژه‌ای دارد، زیرا امکان بازبینی سریع، اشکال‌زدایی، و به‌روزرسانی مستقل مراحل را فراهم می‌کند.

چگونه CTEها از فرآیندهای استخراج، تبدیل و بارگذاری (ETL) پشتیبانی می‌کنند؟

ETL فرآیندی است که داده‌ها را از منابع متعدد استخراج، آن‌ها را تبدیل و سپس در مقصد متمرکز برای تحلیل و گزارش‌گیری بارگذاری می‌کند.
CTEها به‌ویژه در مرحله‌ی تبدیل (Transform) مفید هستند، زیرا امکان ساده‌سازی پرس‌وجوهای پیچیده‌ی SQL و افزایش قابلیت استفاده مجدد آن‌ها در مراحل مختلف پردازش داده را فراهم می‌کنند.

بارگذاری مرحله‌ای و اتصال داده‌ها

در یک خط لوله‌ی ETL، ابزارهایی مانند Airbyte می‌توانند حرکت داده‌ها را از منابع مختلف به‌صورت خودکار مدیریت کنند.
این ابزار بیش از ۶۰۰ کانکتور از پیش‌ساخته دارد تا داده‌ها را از پایگاه‌های داده، APIها، و برنامه‌های SaaS استخراج و بارگذاری کند.

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

مثال استفاده از CTEها در ETL

CTEها در مراحل ETL که شامل پاک‌سازی داده‌ها، استانداردسازی، حذف تکرار، اعمال منطق کسب‌وکار، و محاسبه شاخص‌های مشتق (derived metrics) هستند، بسیار مفیدند.
این مراحل می‌توانند به‌صورت متوالی و در یک پرس‌وجوی SQL قابل نگه‌داری انجام شوند.

WITH standardized_data AS (
SELECT
LOWER(TRIM(name)) AS name_clean,
COALESCE(email, '') AS email_clean,
customer_id
FROM raw_customers
),
deduplicated_data AS (
SELECT *
FROM standardized_data
WHERE ROW_NUMBER() OVER (PARTITION BY email_clean ORDER BY customer_id) = ۱
),
customer_metrics AS (
SELECT
customer_id,
SUM(order_value) AS lifetime_value,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_metrics;

در این مثال:

  • standardized_data داده‌ها را پاک‌سازی و استاندارد می‌کند.

  • deduplicated_data رکوردهای تکراری را حذف می‌کند.

  • customer_metrics شاخص‌های مشتری مانند ارزش طول عمر و تعداد سفارش را محاسبه می‌کند.

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

نتیجه‌گیری

عبارات جدول مشترک (CTEs) در SQL ابزارهای چندمنظوره‌ای هستند که به شما کمک می‌کنند پرس‌وجوهای بزرگ را با تقسیم آن‌ها به بخش‌های منطقی و قابل مدیریت، سازمان‌دهی کنید.
این ویژگی باعث افزایش خوانایی و قابلیت نگه‌داری کد می‌شود و به‌ویژه در عملیات یکپارچه‌سازی داده و تبدیل و پاک‌سازی داده‌ها که نیازمند پردازش چندمرحله‌ای پیچیده هستند، بسیار ارزشمند است.

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

CTEها یک پیشرفت اساسی در قابلیت‌های SQL محسوب می‌شوند و به متخصصان داده اجازه می‌دهند پرس‌وجوهای واضح‌تر، قابل نگه‌داری‌تر و قدرتمندتری بنویسند، در حالی که از عملیات تحلیلی پیچیده پشتیبانی می‌کنند که با ساختارهای سنتی SQL دشوار است.
با افزایش حجم و پیچیدگی داده‌ها، تسلط بر تکنیک‌های CTE برای ساخت سیستم‌های پردازش داده مقیاس‌پذیر و قابل اعتماد ارزش فزاینده‌ای پیدا می‌کند.

قبلی۴ مثال از طرح‌های پایگاه داده برای کاربردهای مختلف چیست؟
بعدیپایگاه داده فایل مسطح (Flat File Database) چیست؟
سبد خرید
علاقه‌مندی‌ها
مشاهدات اخیر
دسته بندی ها