ROLLUP در SQL Server جایگزین COMPUTE BY در گزارش تحلیلی

کاربرد ROLLUP در SQL Server: جایگزینی قدرتمند برای COMPUTE BY در گزارش‌های تحلیلی

بند COMPUTE BY در نسخه‌های قبلی SQL Server بسیار مفید بود، اما اکنون منسوخ شده است. این بند برای افزودن ردیف‌های خلاصه به مجموعه‌های نتیجه، مانند مجموع کل، تعداد و میانگین استفاده می‌شد. با وجود منسوخ شدن، همچنان می‌توانید آن را در SQL Server 2008 و نسخه‌های بعدی برای سازگاری عقب‌رو استفاده کنید، اما در نسخه آتی SQL Server حذف خواهد شد. در صورت استفاده از آن با بندهای دیگر مانند TOP، OFFSET، FOR XML، FOR JSON و INTO، با پیام خطا مواجه خواهید شد.

برای نشان دادن عملکرد COMPUTE BY، یک مثال ساده از پایگاه داده AdventureWorks2014 را در نظر بگیرید. این کوئری، اطلاعات مربوط به سفارشات محصول را بر اساس TerritoryID مرتب‌سازی می‌کند و سپس مجموع LineTotal را برای هر TerritoryID محاسبه می‌کند.

USE [AdventureWorks2014]
GO

SELECT TerritoryID,
           ProductID,
           OrderQty,
           UnitPrice,
           LineTotal
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN (799, 798, 771)
    ORDER BY TerritoryID
    COMPUTE SUM(LineTotal) BY TerritoryID;

همانطور که مشاهده می‌کنید، نتیجه کوئری شامل یک ردیف اضافی برای هر TerritoryID با مجموع LineTotal است. حال بیایید سعی کنیم نتایج مشابهی را با استفاده از بند ROLLUP به دست آوریم. ROLLUP یک توسعه برای بند GROUP BY است که به ما امکان می‌دهد ردیف‌های اضافی را شامل شویم که عملیات خلاصه‌سازی را برای هر گروه نمایش می‌دهند. این بند برای تولید گزارش‌هایی که شامل زیرمجموعه‌ها (subtotals) و مجموع کل (grand totals) هستند، بسیار مفید است.

کوئری ROLLUP زیر نتایج مشابهی را برمی‌گرداند. با افزودن WITH ROLLUP به بند GROUP BY، می‌توانیم مجموع کل برای هر TerritoryID و همچنین یک مجموع کل نهایی برای تمام ردیف‌ها را در یک کوئری واحد تولید کنیم. این روش برای تجزیه و تحلیل داده‌ها و گزارش‌گیری بسیار کارآمد است.

USE [AdventureWorks2014]
GO

SELECT TerritoryID,
           SUM(LineTotal) AS Total
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN (799, 798, 771)
    GROUP BY TerritoryID WITH ROLLUP
    ORDER BY TerritoryID;

اگر از ROLLUP با چندین ستون در بند GROUP BY استفاده کنیم، ردیف‌های خلاصه‌سازی را برای هر ترکیب از ستون‌ها تولید خواهد کرد. بیایید مثالی را بررسی کنیم.

در این مثال، ROLLUP نه تنها مجموع کل OrderQty را برای هر TerritoryID نمایش می‌دهد، بلکه زیرمجموعه‌هایی را نیز برای هر ProductID در داخل هر TerritoryID ایجاد می‌کند. این قابلیت برای گزارش‌هایی که نیاز به جزئیات چندسطحی دارند، بسیار مفید است.

USE [AdventureWorks2014]
GO

SELECT TerritoryID,
           ProductID,
           SUM(OrderQty) AS TotalOrderQty
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN (799, 798, 771)
    GROUP BY TerritoryID, ProductID WITH ROLLUP
    ORDER BY TerritoryID, ProductID;

هنگام استفاده از ROLLUP، اگر مقادیر NULL را در خروجی مشاهده کردید که انتظار یک مقدار گروه‌بندی شده را دارید، به این معنی است که آن ردیف یک ردیف خلاصه‌سازی است. می‌توانید مقادیر NULL را با استفاده از توابع ISNULL یا COALESCE جایگزین کنید.

برای بهبود خوانایی و وضوح گزارش، می‌توانیم مقادیر NULL تولید شده توسط ROLLUP را با عبارات توصیفی جایگزین کنیم. در این مثال، ISNULL برای جایگزینی NULL در TerritoryID با “Grand Total” و در ProductID با “Sub Total” استفاده شده است. این کار کمک می‌کند تا ردیف‌های مجموع کل و زیرمجموعه به راحتی قابل تشخیص باشند.

USE [AdventureWorks2014]
GO

SELECT ISNULL(CAST(TerritoryID AS VARCHAR(10)), 'Grand Total') AS TerritoryID,
           ISNULL(CAST(ProductID AS VARCHAR(10)), 'Sub Total') AS ProductID,
           SUM(OrderQty) AS TotalOrderQty
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN (799, 798, 771)
    GROUP BY TerritoryID, ProductID WITH ROLLUP
    ORDER BY TerritoryID, ProductID;

فرض کنید می‌خواهیم تعداد کل سفارشات را بر اساس TerritoryID به دست آوریم و همچنین یک زیرمجموعه برای هر ProductID در داخل هر TerritoryID داشته باشیم. علاوه بر این، می‌خواهیم مجموع کل نهایی را برای تمام سفارشات نیز به دست آوریم.

این کوئری نهایی، تمام قابلیت‌های پیشین را ترکیب می‌کند. با استفاده از ROLLUP بر روی TerritoryID و ProductID، نه تنها تعداد کل سفارشات (TotalOrderQty) و مجموع خط (TotalLineTotal) را برای هر ترکیب فراهم می‌کند، بلکه زیرمجموعه‌های دقیق و یک مجموع کل نهایی جامع را نیز ارائه می‌دهد. این مثال قدرت ROLLUP را در گزارش‌گیری پیچیده و تحلیل داده به وضوح نشان می‌دهد.

USE [AdventureWorks2014]
GO

SELECT ISNULL(CAST(TerritoryID AS VARCHAR(10)), 'Grand Total') AS TerritoryID,
           ISNULL(CAST(ProductID AS VARCHAR(10)), 'Sub Total') AS ProductID,
           SUM(OrderQty) AS TotalOrderQty,
           SUM(LineTotal) AS TotalLineTotal
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN (799, 798, 771)
    GROUP BY TerritoryID, ProductID WITH ROLLUP
    ORDER BY TerritoryID, ProductID;