جداول موقت در SQL و استفاده از CTE

جداول موقت در SQL و استفاده از CTE

در SQL، جداول موقت برای ذخیره موقت داده‌ها استفاده می‌شوند که می‌توانند در طول یک جلسه یا اجرای یک کوئری مفید باشند.

CTE یا Common Table Expression یکی از روش‌های قدرتمند برای ایجاد جداول موقت است که به خوانایی و مدیریت کوئری‌ها کمک می‌کند.

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

انواع روش‌های ایجاد جداول موقت در SQL

• جداول موقت (Temporary Tables)

  • با استفاده از کلمه کلیدی # یا ## جداول موقت ایجاد می‌شوند.
  • مناسب برای ذخیره داده‌هایی که در طول چندین کوئری استفاده می‌شوند.
    
    

CREATE TABLE #TemporaryTable (
    ID INT,
    Name NVARCHAR(50)
);

INSERT INTO #TemporaryTable VALUES (1, 'Ali'), (2, 'Sara');

SELECT * FROM #TemporaryTable;

• جداول مشتق شده (Derived Tables)

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

SELECT * 
FROM (
    SELECT ID, Name
    FROM Employees
) AS DerivedTable;

• CTE (Common Table Expression)

  • جداول موقتی که در یک بلوک WITH تعریف شده و فقط در یک کوئری یا عملیات خاص معتبر هستند.
  • ساده و خوانا برای کوئری‌های پیچیده.

CTE غیر بازگشتی (Non-Recursive CTE)

یک CTE غیر بازگشتی برای تعریف موقت داده‌هایی استفاده می‌شود که به حلقه یا بازگشت نیاز ندارند.

• نحوه ایجاد یک CTE غیر بازگشتی

  1. تعریف با استفاده از کلمه کلیدی WITH.

  2. مشخص کردن نام CTE.

  3. نوشتن یک کوئری برای تولید داده‌های CTE.

مثال:

فرض کنید جدولی به نام Employees داریم و می‌خواهیم لیستی از کارمندان را فیلتر کنیم.

WITH FilteredEmployees AS (
    SELECT ID, Name, Department
    FROM Employees
    WHERE Department = 'IT'
)
SELECT * 
FROM FilteredEmployees;

• مزیت: داده‌های انتخابی تنها در کوئری زیرین (SELECT) در دسترس هستند.
• محدودیت: قابل استفاده در چندین کوئری همزمان نیست.

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

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

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

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

 

 

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

CTE بازگشتی برای حل مسائل بازگشتی مانند ساختارهای درختی (مثل سلسله‌مراتب سازمانی) استفاده می‌شود.

این نوع CTE از یک شرط اولیه و یک شرط بازگشتی تشکیل شده است.

• نحوه ایجاد یک CTE بازگشتی

   

  1. تعریف با استفاده از کلمه کلیدی WITH.

      

  2. شرط اولیه (Anchor Query): داده‌های اولیه برای شروع.

      

  3. شرط بازگشتی (Recursive Query): از داده‌های CTE برای ساختار بازگشتی استفاده می‌کند.

      

  4. شرط خاتمه (Termination Condition): محدود کردن سطح بازگشت برای جلوگیری از حلقه بی‌پایان.

مثال:

فرض کنید جدولی به نام Employees داریم که شامل اطلاعات سلسله‌مراتب مدیریتی است.

WITH Hierarchy AS (
    -- شرط اولیه: پیدا کردن مدیر اصلی
    SELECT ID, Name, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- شرط بازگشتی: پیدا کردن زیردستان هر مدیر
    SELECT e.ID, e.Name, e.ManagerID
    FROM Employees e
    INNER JOIN Hierarchy h
    ON e.ManagerID = h.ID
)
SELECT * 
FROM Hierarchy;

Anchor Query: مدیرانی را انتخاب می‌کند که ManagerID ندارند.
Recursive Query: به ترتیب زیردستان هر مدیر را اضافه می‌کند.
Termination Condition: به صورت پیش‌فرض SQL بازگشت‌ها را محدود می‌کند (معمولاً 100 سطح).

مزایای استفاده از CTE

• خوانایی بالا

  برای کوئری‌های پیچیده خوانایی بیشتری نسبت به زیرکوئری‌ها ایجاد می‌کند.

• کاهش کدنویسی تکراری

  می‌توان کوئری‌های موقتی را یک‌بار تعریف و چندین بار استفاده کرد.

• حل مسائل پیچیده بازگشتی

  برای مسائل سلسله‌مراتبی مانند ساختار درختی یا گراف ایده‌آل است.

• عدم ایجاد سربار در پایگاه داده

  CTE‌ها نیازی به ذخیره‌سازی فیزیکی در دیتابیس ندارند.
  
  
  

نکات بهینه‌سازی و بهترین روش‌ها در CTE

• برای کوئری‌های کوتاه و قابل فهم

  از CTE استفاده کنید تا خوانایی و نگهداری بهتر شود.

• برای مسائل سلسله‌مراتبی

  CTE بازگشتی یک ابزار ایده‌آل است.

• پیشگیری از بی‌نهایت بازگشت

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

• ترکیب با سایر قابلیت‌ها

  می‌توانید CTE را با Window Functions یا Aggregate Functions ترکیب کنید.

CTE یکی از بهترین ابزارها برای مدیریت و ساده‌سازی کوئری‌های پیچیده در SQL است.

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

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

مثال پروژه محور از جداول موقت در SQL و استفاده از CTE

سناریو پروژه:
فرض کنید یک سیستم مدیریت فروش داریم و می‌خواهیم لیستی از فروشندگان را پیدا کنیم که مجموع فروش آن‌ها در یک ماه خاص از یک مقدار مشخص (مثلاً ۱۰,۰۰۰ دلار) بیشتر است.

مرحله ۱: تعریف جدول‌های داده‌ها

فرض می‌کنیم دو جدول داریم:

• Sales (شامل اطلاعات فروش):

  Columns: SaleID, SellerID, SaleAmount, SaleDate

• Sellers (شامل اطلاعات فروشندگان):

  Columns: SellerID, SellerName

مرحله ۲: ایجاد CTE برای محاسبه مجموع فروش

در این مرحله، از CTE استفاده می‌کنیم تا مجموع فروش هر فروشنده در ماه مورد نظر را محاسبه کنیم.

WITH MonthlySales AS (
    SELECT 
        SellerID,
        SUM(SaleAmount) AS TotalSales
    FROM 
        Sales
    WHERE 
        SaleDate BETWEEN '2023-12-01' AND '2023-12-31'
    GROUP BY 
        SellerID
)
SELECT 
    s.SellerName,
    ms.TotalSales
FROM 
    MonthlySales ms
JOIN 
    Sellers s
ON 
    ms.SellerID = s.SellerID
WHERE 
    ms.TotalSales > 10000;

توضیحات کد:

CTE (MonthlySales):

مجموع فروش هر فروشنده در بازه زمانی دسامبر 2023 محاسبه می‌شود.
داده‌ها به ازای هر SellerID گروه‌بندی و جمع زده می‌شود.

کوئری اصلی:

خروجی CTE با جدول Sellers ترکیب می‌شود تا نام فروشندگان به همراه مجموع فروش نمایش داده شود.
شرط WHERE ms.TotalSales > 10000 فروشندگان با فروش بالا را فیلتر می‌کند.