خلاصه سازی داده ها در SQL Server جایگزین های COMPUTE BY با GROUP BY و Window Functions

چگونه در SQL Server داده‌ها را خلاصه‌سازی کنیم: بررسی COMPUTE BY و جایگزین‌ها

clause COMPUTE BY در SQL Server، ابزاری قدرتمند برای تولید خلاصه‌های جمعی در کنار سطرهای جزئیات است. این قابلیت به ویژه برای گزارش‌گیری که نیاز به مشاهده همزمان جزئیات و مجموع‌های فرعی دارد، مفید بود. هرچند این دستور همچنان در نسخه‌های جدید SQL Server پشتیبانی می‌شود، اما استفاده از آن منسوخ شده (deprecated) تلقی می‌گردد و گزینه‌های مدرن‌تر و انعطاف‌پذیرتری برای دستیابی به نتایج مشابه برای تجزیه و تحلیل داده و گزارش‌گیری وجود دارد.

برای درک بهتر عملکرد COMPUTE BY، به مثال زیر توجه کنید. این کوئری فروش محصولات را برای هر سفارش نمایش می‌دهد و در عین حال، مجموع مقادیر هر سفارش را نیز محاسبه می‌کند:

SELECT 
    SalesOrderID, 
    ProductID, 
    OrderQty 
FROM 
    Sales.SalesOrderDetail 
ORDER BY 
    SalesOrderID 
COMPUTE SUM(OrderQty) BY SalesOrderID;

همانطور که مشاهده می‌کنید، COMPUTE BY در کنار سطرهای جزئیات، یک سطر جداگانه برای مجموع هر SalesOrderID اضافه می‌کند. این سطر شامل مقدار مجموع برای OrderQty است. این ساختار برای گزارش‌دهی سریع و خلاصه کردن داده‌ها بدون نیاز به کوئری‌های پیچیده یا توابع پنجره‌ای بسیار کاربردی بود.

چرا COMPUTE BY منسوخ شده است؟

با وجود کارایی، COMPUTE BY دارای محدودیت‌هایی است:

• **عدم انعطاف‌پذیری:** قابلیت‌های آن نسبت به توابع گروه بندی پیشرفته (مانند ROLLUP, CUBE, GROUPING SETS) محدودتر است.
• **پیچیدگی در پردازش:** نتایج COMPUTE BY در یک ستون موقت Expression قرار می‌گرفت و برای پردازش بیشتر (مثلاً در یک گزارش) نیاز به منطق اضافه‌تر داشت.
• **بهره‌وری کمتر:** در برخی سناریوها، عملکرد آن بهینه نبود و می‌توانست به پردازش سنگین منجر شود.

جایگزین‌های مدرن برای COMPUTE BY

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

1. استفاده از GROUP BY با ROLLUP، CUBE و GROUPING SETS

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

مثال با ROLLUP برای محاسبه مجموع کل و مجموع فرعی بر اساس SalesOrderID و ProductID:

SELECT 
    SalesOrderID, 
    ProductID, 
    SUM(OrderQty) AS TotalOrderQty 
FROM 
    Sales.SalesOrderDetail 
GROUP BY ROLLUP(SalesOrderID, ProductID);

این کوئری علاوه بر مجموع‌های هر SalesOrderID و ProductID، مجموع کل هر SalesOrderID و همچنین مجموع کل نهایی را ارائه می‌دهد. سطرهایی که NULL در ProductID یا SalesOrderID دارند، نشان‌دهنده سطرهای جمع‌بندی هستند.

مثال با CUBE برای ایجاد خلاصه‌های جامع در تمام ابعاد ممکن:

SELECT 
    SalesOrderID, 
    ProductID, 
    SUM(OrderQty) AS TotalOrderQty 
FROM 
    Sales.SalesOrderDetail 
GROUP BY CUBE(SalesOrderID, ProductID);

CUBE تمام ترکیب‌های ممکن از گروه‌بندی را محاسبه می‌کند، از جمله مجموع کل برای هر SalesOrderID، هر ProductID و مجموع کل کلی.

مثال با GROUPING SETS برای تعریف دقیق مجموعه گروه‌بندی‌های مورد نیاز:

SELECT 
    SalesOrderID, 
    ProductID, 
    SUM(OrderQty) AS TotalOrderQty 
FROM 
    Sales.SalesOrderDetail 
GROUP BY GROUPING SETS 
(
    (SalesOrderID, ProductID), 
    (SalesOrderID), 
    ()
);

GROUPING SETS انعطاف‌پذیری بیشتری را برای مشخص کردن دقیقاً کدام مجموعه‌ها (به عنوان مثال، مجموع بر اساس SalesOrderID و ProductID، فقط بر اساس SalesOrderID، و مجموع کل) نیاز دارید، فراهم می‌کند. این رویکرد به شما امکان می‌دهد تا خروجی دقیقاً منطبق با نیازهای گزارش‌گیری خود را داشته باشید.

2. توابع پنجره‌ای (Window Functions)

توابع پنجره‌ای مانند SUM() OVER() راه دیگری برای تولید مجموع‌های فرعی و تجمعی بدون تأثیر بر گروه‌بندی اصلی کوئری هستند. این توابع برای محاسبه رانینگ توتال (running total)، میانگین متحرک و سایر محاسبات تجمعی در یک مجموعه داده خاص بسیار مناسب‌اند.

مثال با تابع پنجره‌ای برای محاسبه مجموع OrderQty برای هر SalesOrderID در کنار جزئیات سطر:

SELECT 
    SalesOrderID, 
    ProductID, 
    OrderQty,
    SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS SalesOrderTotal
FROM 
    Sales.SalesOrderDetail 
ORDER BY 
    SalesOrderID, ProductID;

در این مثال، SUM(OrderQty) OVER (PARTITION BY SalesOrderID) مجموع OrderQty را برای هر SalesOrderID محاسبه کرده و این مقدار را در هر سطر مربوط به آن SalesOrderID تکرار می‌کند. این رویکرد به شما اجازه می‌دهد تا هم جزئیات و هم مجموع‌ها را در یک مجموعه نتیجه یکپارچه داشته باشید، بدون اینکه سطرهای اضافی برای خلاصه‌ها ایجاد شود.

نتیجه‌گیری: انتخاب بهترین روش برای خلاصه‌سازی داده‌ها

در حالی که COMPUTE BY در گذشته برای تولید گزارش‌های خلاصه‌سازی کارآمد بود، امروزه با ظهور ROLLUP، CUBE، GROUPING SETS و توابع پنجره‌ای، گزینه‌های بسیار قدرتمندتر و انعطاف‌پذیرتری برای تجزیه و تحلیل داده و گزارش‌گیری در SQL Server در دسترس است. انتخاب بهترین روش بستگی به نیازهای خاص شما دارد:

• اگر به خلاصه‌های چندبعدی و جامع نیاز دارید، CUBE بهترین گزینه است.
• برای خلاصه‌های سلسله‌مراتبی و مجموع‌های فرعی، ROLLUP مناسب است.
• اگر دقیقاً می‌دانید کدام مجموعه‌های گروه‌بندی را می‌خواهید، GROUPING SETS انعطاف‌پذیری لازم را فراهم می‌کند.
• و برای نمایش تجمعات در کنار داده‌های جزئیات بدون تغییر ساختار سطرها، توابع پنجره‌ای عالی عمل می‌کنند.

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