چگونه از Common Table Expressions (CTE) برای کوئری‌های پیچیده استفاده کنیم؟

یکی از ابزارهای قدرتمند در Common Table Expressions (CTE) ،SQL Server است که به شما اجازه می‌دهد کوئری‌های پیچیده و خوانا‌تری بنویسید.
CTE به عنوان یک مجموعه موقت از داده‌ها عمل می‌کند که می‌تواند در یک کوئری استفاده شود و به شما کمک می‌کند تا کوئری‌های تودرتو را ساده‌تر کنید.
در این مقاله، یاد می‌گیرید چگونه از CTE برای حل مسائل پیچیده در پایگاه‌داده‌های SQL Server استفاده کنید و بهبود قابل‌توجهی در ساختار و کارایی کوئری‌های خود ایجاد کنید.

۱. تعریف Common Table Expression

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

ساختار کلی CTE:

WITH CTE_Name (Column1, Column2, ...)
    AS
    (
        SELECT Column1, Column2
        FROM SomeTable
        WHERE SomeCondition
    )
    SELECT * FROM CTE_Name;

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

۲. استفاده از CTE برای کوئری‌های تودرتو

CTE می‌تواند به شما کمک کند تا کوئری‌های تودرتو (Nested Queries) را ساده‌تر و خواناتر کنید.
در کوئری‌های پیچیده، به جای استفاده از زیرکوئری‌ها، می‌توانید از CTE برای جداسازی بخش‌های مختلف کوئری استفاده کنید.

مثال ۱: ساده‌سازی کوئری تودرتو

-- تعریف CTE برای جمع‌آوری فروش هر مشتری
    WITH CustomerSales AS
    (
        SELECT CustomerID, SUM(TotalAmount) AS TotalSales
        FROM Orders
        GROUP BY CustomerID
    )
    SELECT c.CustomerName, cs.TotalSales
    FROM Customers c
    JOIN CustomerSales cs ON c.CustomerID = cs.CustomerID;

این مثال نشان می‌دهد که چگونه از CTE برای ساده‌سازی کوئری‌های تودرتو استفاده کنیم.

۳. استفاده از CTE بازگشتی (Recursive CTE)

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

مثال ۲: استفاده از CTE بازگشتی برای محاسبه سلسله‌مراتب سازمانی

-- محاسبه سلسله‌مراتب سازمانی با استفاده از CTE بازگشتی
    WITH OrgHierarchy AS
    (
        SELECT EmployeeID, ManagerID, EmployeeName
        FROM Employees
        WHERE ManagerID IS NULL

        UNION ALL

        SELECT e.EmployeeID, e.ManagerID, e.EmployeeName
        FROM Employees e
        JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
    )
    SELECT * FROM OrgHierarchy;

در این مثال، از CTE بازگشتی برای نمایش سلسله‌مراتب سازمانی استفاده شده است.

۴. CTE برای تقسیم‌بندی داده‌ها

یکی دیگر از کاربردهای CTE در SQL Server، تقسیم‌بندی داده‌ها به دسته‌های مختلف است.
می‌توانید با استفاده از CTE، داده‌ها را به صورت پویا در دسته‌های مختلف قرار دهید و سپس این دسته‌ها را در کوئری اصلی استفاده کنید.

مثال ۳: تقسیم‌بندی داده‌ها بر اساس مجموع فروش

-- استفاده از CTE برای تقسیم‌بندی داده‌ها
    WITH SalesRank AS
    (
        SELECT CustomerID, TotalAmount,
               ROW_NUMBER() OVER (ORDER BY TotalAmount DESC) AS SalesRank
        FROM Orders
    )
    SELECT * FROM SalesRank WHERE SalesRank <= 10;

این کوئری ۱۰ مشتری برتر بر اساس مجموع فروش را نمایش می‌دهد.

۵. استفاده از CTE برای حذف رکوردهای تکراری

CTE می‌تواند به شما کمک کند تا رکوردهای تکراری را شناسایی و حذف کنید.
با استفاده از CTE، می‌توانید داده‌ها را بر اساس معیارهای خاصی مرتب کنید و رکوردهای تکراری را حذف کنید.

مثال ۴: حذف رکوردهای تکراری با استفاده از CTE

-- شناسایی و حذف رکوردهای تکراری با CTE
    WITH DuplicateRecords AS
    (
        SELECT CustomerID, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) AS RowNum
        FROM Customers
    )
    DELETE FROM DuplicateRecords WHERE RowNum > 1;

این کوئری رکوردهای تکراری مشتریان را شناسایی کرده و حذف می‌کند.

نکته پایانی

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

مقاله‌های مرتبط
چه زمانی از EXISTS به جای IN استفاده کنیم؟

استفاده از INDEX برای بهبود سرعت اجرای Query‌ها<br/>