گزارش‌گیری پیشرفته در SQL Server با GROUP BY CUBE ROLLUP GROUPING SETS

افزایش کارایی گزارش‌گیری: راهنمای جامع GROUP BY، CUBE، ROLLUP و GROUPING SETS در SQL Server

به طور کلی GROUP BY معمولاً برای بازیابی نتایج تجمیع‌شده (aggregated) بر اساس مجموعه‌ای از مقادیر ستون استفاده می‌شود. هنگامی که یک کوئری شامل تابع تجمیعی مانند SUM، COUNT، AVG، MIN یا MAX باشد، نتایج برای کل جدول نمایش داده می‌شوند. اما اگر می‌خواهید نتایج تجمیع‌شده را بر اساس گروه‌های خاصی بازیابی کنید، باید از کلاز GROUP BY استفاده نمایید. به عنوان مثال، اگر می‌خواهید مجموع فروش هر شهر را بازیابی کنید، می‌توانید از کلاز GROUP BY برای این منظور بهره ببرید.

بیایید یک جدول نمونه به نام ‘Sales’ ایجاد کرده و مقداری داده در آن وارد کنیم تا قابلیت‌های اپراتورهای GROUP BY، CUBE، ROLLUP و GROUPING SETS را نشان دهیم. اسکریپت زیر جدول را ایجاد کرده و با داده‌های نمونه پر می‌کند:

CREATE TABLE Sales (
    [SaleDate] DATETIME,
    [Region] VARCHAR(50),
    [City] VARCHAR(50),
    [Product] VARCHAR(50),
    [Quantity] INT,
    [Price] MONEY
);
GO

INSERT INTO Sales ([SaleDate], [Region], [City], [Product], [Quantity], [Price]) VALUES
('2023-01-01', 'North', 'Tehran', 'Laptop', 10, 1200.00),
('2023-01-01', 'North', 'Tehran', 'Mouse', 20, 25.00),
('2023-01-02', 'South', 'Shiraz', 'Keyboard', 15, 75.00),
('2023-01-02', 'South', 'Shiraz', 'Monitor', 8, 300.00),
('2023-01-03', 'East', 'Mashhad', 'Laptop', 5, 1200.00),
('2023-01-03', 'East', 'Mashhad', 'Mouse', 10, 25.00),
('2023-01-04', 'West', 'Tabriz', 'Keyboard', 12, 75.00),
('2023-01-04', 'West', 'Tabriz', 'Monitor', 7, 300.00),
('2023-01-05', 'North', 'Tehran', 'Monitor', 10, 300.00),
('2023-01-05', 'South', 'Shiraz', 'Laptop', 7, 1200.00),
('2023-01-06', 'East', 'Mashhad', 'Keyboard', 20, 75.00),
('2023-01-06', 'West', 'Tabriz', 'Mouse', 15, 25.00);
GO

اکنون، بیایید از یک کوئری ساده GROUP BY برای دریافت مجموع تعداد کالاهای فروخته شده برای هر محصول استفاده کنیم:

SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Product;

این کوئری مجموع تجمیع‌شده (quantity) برای هر محصول منحصر به فرد را نمایش می‌دهد و عملکرد اصلی GROUP BY را نشان می‌دهد.

تابع GROUPING عمدتاً با اپراتورهای CUBE و ROLLUP برای تمایز بین گروه‌های واقعی و ردیف‌های ابر-تجمیع (ردیف‌های کلی تولید شده توسط CUBE یا ROLLUP) استفاده می‌شود. اگر یک ستون بخشی از یک ردیف تجمیعی باشد، 1 و اگر بخشی از یک گروه معمولی باشد، 0 برمی‌گرداند.

GROUPING(column_name)

به عنوان مثال، در نتیجه CUBE یا ROLLUP، اگر `GROUPING(Region)` مقدار 1 را برگرداند، به این معنی است که آن ردیف نشان‌دهنده مجموع برای تمام مناطق (یک ابر-تجمیع) است، نه یک منطقه خاص. اگر 0 برگرداند، به این معنی است که داده مربوط به یک منطقه خاص است.

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

بیایید مثالی را با استفاده از CUBE بر روی Region و City ببینیم:

SELECT
    Region,
    City,
    SUM(Quantity) AS TotalQuantity,
    GROUPING(Region) AS GroupingRegion,
    GROUPING(City) AS GroupingCity
FROM Sales
GROUP BY CUBE (Region, City);

در این خروجی، ردیف‌هایی را مشاهده خواهید کرد که نشان‌دهنده تجمیع‌ها بر اساس Region و City، فقط بر اساس Region، فقط بر اساس City و یک مجموع کلی هستند. تابع GROUPING به شناسایی سطح تجمیع هر ردیف کمک می‌کند.

اپراتور ROLLUP یک مجموعه نتیجه تولید می‌کند که تجمیع‌ها را برای یک سلسله مراتب از ستون‌ها نشان می‌دهد. برخلاف CUBE که تمام ترکیبات را در نظر می‌گیرد، ROLLUP زیرمجموعه‌ها را از پایین‌ترین سطح تا یک مجموع کلی ایجاد می‌کند. اگر N ستون در کلاز GROUP BY ROLLUP خود داشته باشید، N+1 سطح سلسله مراتبی را تولید خواهد کرد.

مثالی با ROLLUP بر روی Region و City را در نظر بگیرید:

SELECT
    Region,
    City,
    SUM(Quantity) AS TotalQuantity,
    GROUPING(Region) AS GroupingRegion,
    GROUPING(City) AS GroupingCity
FROM Sales
GROUP BY ROLLUP (Region, City);

این کوئری خلاصه‌هایی را بر اساس (Region, City)، سپس بر اساس (Region)، و در نهایت یک مجموع کلی ارائه می‌دهد. این برای گزارش‌دهی سلسله مراتبی، مانند فروش بر اساس شهر در هر منطقه، سپس مجموع فروش برای هر منطقه، و مجموع فروش کلی مفید است.

اپراتور GROUPING SETS به شما امکان می‌دهد چندین کلاز GROUP BY را در یک دستور SELECT واحد تعریف کنید. این اپراتور بیشترین انعطاف‌پذیری را ارائه می‌دهد، زیرا می‌توانید دقیقاً مشخص کنید که کدام گروه‌ها را می‌خواهید تجمیع کنید. این در واقع راهی برای ترکیب نتایج چندین کلاز GROUP BY در یک مجموعه نتیجه واحد است، بدون نیاز به UNION ALL.

بیایید GROUPING SETS را برای دریافت مجموع‌ها بر اساس Region، بر اساس Product و یک مجموع کلی نشان دهیم:

SELECT
    Region,
    Product,
    SUM(Quantity) AS TotalQuantity,
    GROUPING(Region) AS GroupingRegion,
    GROUPING(Product) AS GroupingProduct
FROM Sales
GROUP BY GROUPING SETS (
    (Region),
    (Product),
    () -- Grand Total
);

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

GROUPING SETS همچنین می‌تواند برای دستیابی به آنچه CUBE و ROLLUP انجام می‌دهند استفاده شود، اما با کنترل صریح‌تر. به عنوان مثال، `GROUP BY CUBE (A, B)` معادل `GROUP BY GROUPING SETS ((A, B), (A), (B), ())` است. به طور مشابه، `GROUP BY ROLLUP (A, B)` معادل `GROUP BY GROUPING SETS ((A, B), (A), ())` است.