جداول مشتق شده  derived table در SQL

جداول مشتق شده (Derived Tables) در پایگاه داده SQL

یکی از مفاهیم مهم در SQL که به بهینه‌سازی کوئری‌ها کمک می‌کند، جداول مشتق شده (Derived Tables) است.

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

Derived Tables به ما این امکان را می‌دهند که به داده‌ها درون یک کوئری دسترسی پیدا کنیم بدون اینکه نیاز به ایجاد یک جدول فیزیکی داشته باشیم.

یک Derived Table یک Subquery است که در بخش FROM یک کوئری اصلی قرار گرفته و داده‌های حاصل از آن مانند یک جدول مستقل عمل می‌کند.

به این ترتیب، از نتایج Subquery مستقیماً در کوئری اصلی استفاده می‌شود.

مثال ساده از یک جدول مشتق شده

SELECT DerivedTable.EmployeeID, DerivedTable.AvgSalary
FROM (
    SELECT EmployeeID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY EmployeeID
) AS DerivedTable;

در این مثال:

یک Derived Table با نام DerivedTable ایجاد شده است که شامل EmployeeID و میانگین حقوق کارمندان است.

کوئری اصلی، داده‌های این جدول را مورد استفاده قرار می‌دهد.

مزایای استفاده از Derived Tables

• افزایش خوانایی و ساختارمند کردن کوئری‌ها

  تقسیم کوئری‌های پیچیده به بخش‌های کوچک‌تر باعث خوانایی بیشتر آن‌ها می‌شود.

• کاهش نیاز به ایجاد جداول موقت فیزیکی

  در بسیاری از موارد، استفاده از Derived Tables جایگزین ایجاد Temporary Tables می‌شود.

• بهینه‌سازی و کاهش بار پردازشی

  استفاده مناسب از Derived Tables می‌تواند بهینه‌سازی پردازش داده‌ها را تسهیل کند.

ایجاد و استفاده از Derived Tables

ساختار کلی یک جدول مشتق شده

SELECT column_names
FROM (
    SELECT column_names
    FROM table_name
    WHERE condition
) AS DerivedTable;

مثال عملی از Derived Table

فرض کنید یک جدول Orders داریم که شامل اطلاعات مربوط به سفارشات مشتریان است و می‌خواهیم میانگین مبلغ سفارشات را برای هر مشتری محاسبه کنیم و سپس مشتریانی را نمایش دهیم که میانگین سفارش آن‌ها بیشتر از ۱۰۰۰ دلار است.

SELECT CustomerID, AvgOrderAmount
FROM (
    SELECT CustomerID, AVG(OrderAmount) AS AvgOrderAmount
    FROM Orders
    GROUP BY CustomerID
) AS DerivedTable
WHERE AvgOrderAmount > 1000;

در این مثال

ابتدا یک Derived Table ایجاد شده است که میانگین مبلغ سفارشات را برای هر مشتری محاسبه می‌کند.

کوئری اصلی فقط مشتریانی را فیلتر می‌کند که میانگین سفارش آن‌ها بیشتر از ۱۰۰۰ دلار باشد.

 

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

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

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

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

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

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

 

 تفاوت بین Derived Tables و Common Table Expressions (CTE)

CTE یا عبارات جدولی مشترک یک روش مشابه برای ایجاد مجموعه داده‌های موقت است.

تفاوت اصلی Derived Tables و CTE در قابلیت استفاده مجدد است:

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

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

مثال استفاده از CTE به جای Derived Table

WITH OrderAvg AS (
    SELECT CustomerID, AVG(OrderAmount) AS AvgOrderAmount
    FROM Orders
    GROUP BY CustomerID
)
SELECT CustomerID, AvgOrderAmount
FROM OrderAvg
WHERE AvgOrderAmount > 1000;

Nested Derived Tables (جدول‌های مشتق شده تو در تو)

گاهی اوقات ممکن است نیاز داشته باشیم چندین Derived Table را درون یکدیگر قرار دهیم.

مثال از Nested Derived Tables

SELECT FinalTable.CustomerID, FinalTable.AvgOrderAmount
FROM (
    SELECT CustomerID, AVG(OrderAmount) AS AvgOrderAmount
    FROM (
        SELECT CustomerID, OrderAmount
        FROM Orders
        WHERE OrderDate >= '2023-01-01'
    ) AS FilteredOrders
    GROUP BY CustomerID
) AS FinalTable
WHERE AvgOrderAmount > 1000;

در این مثال:

ابتدا سفارشاتی که بعد از 2023-01-01 ثبت شده‌اند در FilteredOrders انتخاب شده‌اند.

سپس میانگین مقدار سفارشات در FinalTable محاسبه شده است.

بهینه‌سازی Derived Tables

برای افزایش عملکرد هنگام استفاده از Derived Tables، نکات زیر پیشنهاد می‌شود:

• استفاده از ایندکس‌ها

اطمینان حاصل کنید که ستون‌های مورد استفاده در Derived Tables دارای ایندکس‌های مناسب هستند.

• اجتناب از استفاده غیرضروری از Derived Tables

در برخی موارد می‌توان از JOIN به جای Derived Table استفاده کرد.

• محدود کردن تعداد رکوردهای بازگشتی

استفاده از WHERE و LIMIT برای کاهش حجم داده‌ها در Derived Table.

مثال پروژه محور  تحلیل فروش با جداول مشتق شده 

فرض کنید یک فروشگاه آنلاین داریم و می‌خواهیم مشتریانی را پیدا کنیم که مجموع خریدشان در هر ماه بیش از ۳۰۰ دلار بوده است.

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

SELECT CustomerID, OrderMonth, TotalSpent  
FROM (  
    SELECT CustomerID,  
           FORMAT(OrderDate, 'yyyy-MM') AS OrderMonth,  
           SUM(TotalAmount) AS TotalSpent  
    FROM Orders  
    GROUP BY CustomerID, FORMAT(OrderDate, 'yyyy-MM')  
) AS MonthlySales  
WHERE TotalSpent > 300;

توضیح کد

جدول مشتق شده (MonthlySales):

مشتریان را بر اساس ماه دسته‌بندی می‌کند.
مجموع خرید هر مشتری در هر ماه را محاسبه می‌کند.

فیلترگذاری

فقط مشتریانی که در یک ماه بیش از ۳۰۰ دلار خرید داشته‌اند، نمایش داده می‌شوند.

نتیجه اجرای کوئری (مثال خروجی)

CustomerID | OrderMonth | TotalSpent  
-----------|------------|------------  
101        | 2024-01    | 400  
102        | 2024-01    | 400  
103        | 2024-02    | 350  

این روش کمک می‌کند تا به‌راحتی تحلیل ماهانه فروش را انجام دهیم.