بهینه سازی کوئری SQL Server با Alias در GROUP BY

بهینه‌سازی کوئری‌های SQL با استفاده از نام مستعار در GROUP BY در SQL Server

یکی از چالش‌های رایج در نوشتن کوئری‌های SQL پیچیده، تکرار عبارات طولانی و دشوار در هر دو بخش SELECT و GROUP BY است. این تکرار نه تنها خوانایی کد را کاهش می‌دهد بلکه نگهداری آن را نیز دشوارتر می‌کند. تصور کنید یک عبارت پیچیده دارید که باید داده‌ها را بر اساس آن گروه‌بندی کنید؛ نوشتن مجدد همان عبارت در هر دو بخش SELECT و GROUP BY می‌تواند منجر به خطاهای احتمالی و اتلاف وقت شود. این مقاله به شما نشان می‌دهد که چگونه با استفاده از نام‌های مستعار (Alias) در SQL Server و تکنیک‌هایی مانند CROSS APPLY و Common Table Expression (CTE)، کد خود را تمیزتر، خواناتر و قابل نگهداری‌تر کنید.

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

SELECT (YEAR(OrderDate) * 100) + MONTH(OrderDate) AS OrderYearMonth,
       SUM(OrderQty) AS TotalOrderQty
FROM Sales.SalesOrderDetail
GROUP BY (YEAR(OrderDate) * 100) + MONTH(OrderDate)
ORDER BY (YEAR(OrderDate) * 100) + MONTH(OrderDate);

در این کوئری، عبارت (YEAR(OrderDate) * 100) + MONTH(OrderDate) دو بار تکرار شده است: یک بار در SELECT و یک بار در GROUP BY. این عبارت برای تولید یک مقدار عددی منفرد برای ترکیب سال و ماه استفاده می‌شود، به عنوان مثال، 200507 برای جولای 2005. در حالی که برای یک عبارت کوتاه، این تکرار ممکن است مشکل بزرگی نباشد، اما برای عبارات پیچیده‌تر، مدیریت آن به سرعت دشوار می‌شود.

(YEAR(OrderDate) * 100) + MONTH(OrderDate)

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

استفاده از CROSS APPLY برای نام مستعار در GROUP BY

یکی از روش‌های موثر برای تعریف یک نام مستعار برای یک عبارت پیچیده و استفاده از آن در GROUP BY، بهره‌گیری از CROSS APPLY است. این تکنیک به شما اجازه می‌دهد تا یک جدول فرعی موقتی (که می‌تواند شامل یک عبارت محاسباتی باشد) ایجاد کرده و سپس از ستون‌های آن در کوئری اصلی استفاده کنید. با این کار، عبارت پیچیده فقط یک بار تعریف می‌شود و سپس نام مستعار آن در SELECT و GROUP BY به کار می‌رود. این رویکرد به ویژه برای عبارات محاسباتی که نیاز به گروه‌بندی دارند، بسیار مفید است.

SELECT oa.OrderYearMonth,
       SUM(sod.OrderQty) AS TotalOrderQty
FROM Sales.SalesOrderDetail AS sod
CROSS APPLY (SELECT (YEAR(sod.OrderDate) * 100) + MONTH(sod.OrderDate) AS OrderYearMonth) AS oa
GROUP BY oa.OrderYearMonth
ORDER BY oa.OrderYearMonth;

در این مثال، CROSS APPLY یک ستون مجازی به نام OrderYearMonth ایجاد می‌کند که حاوی نتیجه عبارت (YEAR(sod.OrderDate) * 100) + MONTH(sod.OrderDate) است. سپس می‌توانیم به راحتی از oa.OrderYearMonth در GROUP BY و ORDER BY استفاده کنیم. این کار کد را بسیار خواناتر و مختصرتر می‌کند.

استفاده از Common Table Expression (CTE) برای نام مستعار در GROUP BY

روش دیگر برای دستیابی به همین هدف، استفاده از Common Table Expression یا CTE است. CTEها به شما امکان می‌دهند تا یک مجموعه نتایج موقت با نام مشخص ایجاد کنید که در محدوده اجرای یک عبارت SELECT، INSERT، UPDATE، DELETE یا MERGE تعریف می‌شود. این روش نیز مانند CROSS APPLY به بهبود خوانایی و نگهداری کد کمک می‌کند.

WITH OrderData AS (
    SELECT OrderDate, OrderQty,
           (YEAR(OrderDate) * 100) + MONTH(OrderDate) AS OrderYearMonth
    FROM Sales.SalesOrderDetail
)
SELECT OrderYearMonth,
       SUM(OrderQty) AS TotalOrderQty
FROM OrderData
GROUP BY OrderYearMonth
ORDER BY OrderYearMonth;

در این کوئری، CTE به نام OrderData تعریف شده است که ستون OrderYearMonth را محاسبه می‌کند. سپس، کوئری بیرونی از این CTE استفاده کرده و OrderYearMonth را در GROUP BY به کار می‌برد. این رویکرد برای تعریف چندین ستون محاسبه شده قبل از گروه‌بندی یا فیلتر کردن داده‌ها بسیار انعطاف‌پذیر است.

مثال پیشرفته: محاسبه درصد و استفاده از نام مستعار

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

SELECT (YEAR(OrderDate) * 100) + MONTH(OrderDate) AS OrderYearMonth,
       SUM(CASE WHEN ProductSubcategoryID = 1 THEN OrderQty END) AS BikesSold,
       SUM(OrderQty) AS TotalSold,
       (SUM(CASE WHEN ProductSubcategoryID = 1 THEN OrderQty END) * 1.0 / SUM(OrderQty)) * 100 AS BikesSoldPercent
FROM Sales.SalesOrderDetail
WHERE ProductSubcategoryID IN (1,2,3)
GROUP BY (YEAR(OrderDate) * 100) + MONTH(OrderDate)
ORDER BY (YEAR(OrderDate) * 100) + MONTH(OrderDate);

در این کوئری، عبارت (YEAR(OrderDate) * 100) + MONTH(OrderDate) و همچنین عبارت محاسبه درصد تکرار شده‌اند. عبارت محاسبه درصد که در ادامه آمده است، طولانی و پیچیده است و در صورت تکرار، مدیریت آن سخت خواهد بود:

(SUM(CASE WHEN ProductSubcategoryID = 1 THEN OrderQty END) * 1.0 / SUM(OrderQty)) * 100

با استفاده از CROSS APPLY، می‌توانیم حداقل بخش OrderYearMonth را ساده‌سازی کنیم:

SELECT oa.OrderYearMonth,
       SUM(sod.OrderQty) AS TotalSold,
       (SUM(CASE WHEN sod.ProductSubcategoryID = 1 THEN sod.OrderQty END) * 1.0 / SUM(sod.OrderQty)) * 100 AS BikesSoldPercent
FROM Sales.SalesOrderDetail AS sod
CROSS APPLY (SELECT (YEAR(sod.OrderDate) * 100) + MONTH(sod.OrderDate) AS OrderYearMonth) AS oa
WHERE sod.ProductSubcategoryID IN (1,2,3)
GROUP BY oa.OrderYearMonth
ORDER BY oa.OrderYearMonth;

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

WITH OrderData AS (
    SELECT OrderDate, OrderQty, ProductSubcategoryID,
           (YEAR(OrderDate) * 100) + MONTH(OrderDate) AS OrderYearMonth,
           CASE WHEN ProductSubcategoryID = 1 THEN OrderQty END AS BikesQty,
           OrderQty AS TotalQty
    FROM Sales.SalesOrderDetail
    WHERE ProductSubcategoryID IN (1,2,3)
)
SELECT OrderYearMonth,
       SUM(BikesQty) AS BikesSold,
       SUM(TotalQty) AS TotalSold,
       (SUM(BikesQty) * 1.0 / SUM(TotalQty)) * 100 AS BikesSoldPercent
FROM OrderData
GROUP BY OrderYearMonth
ORDER BY OrderYearMonth;

در این کوئری پیشرفته، CTE به نام OrderData نه تنها OrderYearMonth را تعریف می‌کند، بلکه ستون‌های BikesQty و TotalQty را نیز بر اساس منطق CASE تعریف می‌کند. این کار اجازه می‌دهد تا محاسبات پیچیده درصد در کوئری بیرونی بسیار ساده‌تر و با استفاده از این نام‌های مستعار انجام شود. این روش، حداکثر خوانایی و انعطاف‌پذیری را برای کوئری‌های پیچیده فراهم می‌کند.

مزایای استفاده از نام مستعار در GROUP BY

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

• نگهداری آسان‌تر: اگر نیاز به تغییر در منطق عبارت پیچیده باشد، فقط کافی است آن را در یک مکان (در CROSS APPLY یا CTE) ویرایش کنید. این کار زمان و احتمال خطا را کاهش می‌دهد.

• افزایش سازگاری: اطمینان حاصل می‌شود که دقیقاً همان عبارت در تمام بخش‌های کوئری (SELECT، GROUP BY، ORDER BY) استفاده می‌شود.

• بهینه‌سازی عملکرد (در برخی موارد): اگرچه SQL Server optimizer در شناسایی عبارات تکراری و بهینه‌سازی آنها بسیار هوشمند است، اما در برخی سناریوها، تعریف صریح نام‌های مستعار می‌تواند به بهینه‌سازی کمک کند یا حداقل تأثیری منفی نداشته باشد.

نکات و ملاحظات

• این تکنیک‌ها در SQL Server 2005 و نسخه‌های بعدی آن قابل استفاده هستند.

• هنگامی که از نام مستعار در GROUP BY استفاده می‌کنید، می‌توانید از همان نام مستعار در بخش HAVING نیز استفاده کنید تا نتایج گروه‌بندی شده را فیلتر کنید.

• نام‌های مستعار را نمی‌توان به طور مستقیم در یک عبارت GROUP BY استفاده کرد، به همین دلیل نیاز به CROSS APPLY یا CTE داریم تا عبارت را “ماتریالیزه” کنیم.