مقایسه CTEs و کوئری‌های فرعی در SQL: کدام یک بهتر است؟

مقایسه CTE و کوئری‌های فرعی در SQL: کدام یک بهتر است؟

تصور کنید که شما در حال ساختن یک پازل هستید. آیا با تصویر کلی شروع می‌کنید و قطعات را همزمان با یکدیگر متصل می‌کنید؟ یا آیا بخش‌های کوچکتری را ایجاد کرده و آنها را ترکیب می‌کنید تا تصویر کلی را نمایان کنید؟ در دنیای SQL، که یک زبان برنامه‌نویسی طراحی شده برای مدیریت و کنترل پایگاه‌های داده است، ما استراتژی‌های مشابهی برای حل مسائل داده داریم. به معرفی CTE (Common Table Expression) و Subqueries می‌پردازیم.

مقدمه

کوئری‌های SQL مانند داستان‌های کارآگاهی هستند که ما به دنبال ادله داده‌ها هستیم تا پرونده‌هایمان را حل کنیم. با این حال، برخی از پرونده‌ها (یا کوئری‌ها) نسبت به دیگران پیچیده‌تر هستند. برای ساده‌تر کردن موضوعات، از ابزارهایی مانند CTEs و Subqueries استفاده می‌کنیم. بیایید به بررسی این دو مورد بپردازیم.

Subqueries (کوئری‌های فرعی)

تصور کنید که شما باید یک کتاب را در یک کتابخانه عظیم پیدا کنید. اما به جای جستجوی مستقیم برای کتاب، ابتدا بخش صحیح را پیدا کرده، سپس قفسه مناسب را و در نهایت کتاب را پیدا می‌کنید. هر یک از این مراحل شبیه به یک جستجوی کوچک یا “کوئری فرعی” است که به ما کمک می‌کند به پاسخ نهایی برسیم.

بخشی از مسئله ما این است که باید لیست مشتریانی را بازیابی کنیم که چندین بار واریز کرده و یا یکبار در ماه برداشت یا خرید کرده‌اند. اگر می‌خواهیم به راه حل از زاویه کوئری فرعی نگاه کنیم، می‌توانیم به موارد زیر برسیم:

SELECT txn_month, COUNT(*) AS total_customers
FROM (
       SELECT customer_id, MONTH(txn_date) AS txn_month,
         SUM (CASE WHEN txn_type = 'deposit' THEN 1 ELSE 0 END) AS dep_count,
         SUM (CASE WHEN txn_type IN('purchase', 'withdrawal')
                   THEN 1 ELSE 0 END) AS pur_withd_count
       FROM transactions
       GROUP BY customer_id, MONTH(txn_date)
       ) AS dep_pur_with_cte
WHERE dep_count > 1 AND pur_withd_count > 1
GROUP BY txn_month
ORDER BY txn_month;

اگر توجه کنید، دستور FROM به یک نام جدول تکی پیوند نشده است بلکه به یک جدولی که در محل ایجاد کرده‌ایم، اشاره دارد و سپس به این جدول پس از پرانتز بسته یک نام مستعار داده می‌شود.

مزایا

• انعطاف‌پذیری: آنها می‌توانند در اقسام مختلفی از کوئری اصلی (مانند SELECT، FROM یا WHERE) استفاده شوند.
• جهانی بودن: مفهوم کوئری‌های فرعی برای تمام نسخه‌های SQL که از آن پشتیبانی می‌کنند (مانند MySQL، MSSQL، PostgreSQL و غیره) عجیب نیست.

معایب

• خوانایی: در مواردی که نیاز به چندین کوئری فرعی تو در تو است، ممکن است پیچیده و دنبال کردن آن به چالش کشیده شود، مثل یک مارپیچ در داخل مارپیچ.

CTE (Common Table Expression)

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

استفاده از رویکرد CTE برای سوال مشابه به کوئری فرعی، به شکل زیر خواهد بود:

WITH dep_pur_with_cte AS (
       SELECT customer_id, MONTH(txn_date) AS txn_month,
         SUM (CASE WHEN txn_type = 'deposit' THEN 1 ELSE 0 END) AS dep_count,
         SUM (CASE WHEN txn_type IN('purchase', 'withdrawal') 
                    THEN 1 ELSE 0 END) AS pur_withd_count
       FROM transactions
       GROUP BY customer_id, MONTH(txn_date)
       )
SELECT txn_month, COUNT(*) AS total_customers
FROM dep_pur_with_cte
WHERE dep_count > 1 AND pur_withd_count > 1
GROUP BY txn_month
ORDER BY txn_month;

اگر توجه کنید، دستور WITH به ایجاد جدول موقت برای واریزها، خریدها و برداشت‌ها کمک کرد (با نام dep_pur_with_cte) و از آن جدول موقت یک جدول اجمالی نهایی که تعداد مشتریانی که شرایط را ارضا می‌کنند را شمارش می‌کند، استخراج شد.

مزایا

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

معایب

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

SELECT customer_id, txn_amount AS tot_deposit
FROM transactions
WHERE txn_type = 'deposit' 
    AND txn_amount > (
                      SELECT avg(txn_amount) 
                      FROM transactions
                      WHERE txn_type = 'deposit');

خب پس کدوم رویکرد بهتره ؟ CTEها یا کوئری‌های فرعی

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

کارایی: در واقعیت، عملکرد بیشتر به نحوه پردازش موتور پایگاه داده به کوئری بستگی دارد. گاهی اوقات تفاوت معناداری وجود ندارد.

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

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

نتیجه‌گیری

مانند بسیاری از چیزهای مرتبط با تکنولوژی، انتخاب بین CTEها و کوئری‌های فرعی مربوط به این نیست که کدام “بهتر” است. با توجه به خاص شما است که کدام بهتر است. آیا دارید یک داستان داده‌ای پیچیده می‌سازید و به یک ساختار واضح نیاز دارید؟ CTEها ممکن است انتخاب شما باشند. آیا نیاز به استخراج یک خروجی سریع دارید؟ یک کوئری فرعی ممکن است کار را انجام دهد.

به یاد داشته باشید، هم CTEها و هم کوئری‌های فرعی ابزارهایی در کیف ابزار SQL شما هستند. مسلط شدن به زمان و نحوه استفاده از هر یک شما را به یک داده پرداز برجسته در SQL تبدیل می‌کند!

«بستیفای» ارائه فایل‌های کاربردی در زمینه های مختلف کسب و کار است. در صورتی که نیاز به سفارشی سازی یا طراحی فایل اختصاصی جدید می‌باشید، می‌توانید نسبت به ارائه درخواست با بستیفای در ارتباط باشید.

مطالعه بیشتر