بهینه‌سازی گزارش‌گیری با GROUP BY SETS در SQL Server راهنمای جامع ROLLUP CUBE و GROUPING SETS

بهینه‌سازی گزارش‌گیری با GROUP BY SETS در SQL Server: راهنمای جامع ROLLUP, CUBE و GROUPING SETS

در SQL Server 2008 و نسخه‌های بالاتر، هنگام استفاده از عبارت `GROUP BY`، گزینه‌های متعددی برای دستیابی به سطوح مختلفی از خلاصه‌سازی داده‌ها وجود دارد. در ادامه به بررسی این قابلیت‌ها می‌پردازیم که برای تحلیل داده‌ها و تهیه گزارش‌های انعطاف‌پذیر در SQL Server بسیار کاربردی هستند.

فرض کنید جدول و داده‌های زیر را داریم:


CREATE TABLE Sales
(
    SalesYear INT,
    SalesQuarter INT,
    SalesMonth INT,
    SalesPerson VARCHAR(50),
    SalesAmount MONEY
)
GO

INSERT INTO Sales VALUES(2008,1,1,'Mike',1000)
INSERT INTO Sales VALUES(2008,1,2,'Mike',2000)
INSERT INTO Sales VALUES(2008,1,3,'Mike',3000)
INSERT INTO Sales VALUES(2008,1,1,'John',500)
INSERT INTO Sales VALUES(2008,1,2,'John',1000)
INSERT INTO Sales VALUES(2008,1,3,'John',1500)
INSERT INTO Sales VALUES(2008,2,4,'Mike',2000)
INSERT INTO Sales VALUES(2008,2,5,'Mike',3000)
INSERT INTO Sales VALUES(2008,2,6,'Mike',4000)
INSERT INTO Sales VALUES(2008,2,4,'John',1000)
INSERT INTO Sales VALUES(2008,2,5,'John',2000)
INSERT INTO Sales VALUES(2008,2,6,'John',3000)
INSERT INTO Sales VALUES(2008,3,7,'Mike',3000)
INSERT INTO Sales VALUES(2008,3,8,'Mike',4000)
INSERT INTO Sales VALUES(2008,3,9,'Mike',5000)
INSERT INTO Sales VALUES(2008,3,7,'John',1500)
INSERT INTO Sales VALUES(2008,3,8,'John',2000)
INSERT INTO Sales VALUES(2008,3,9,'John',2500)
INSERT INTO Sales VALUES(2008,4,10,'Mike',4000)
INSERT INTO Sales VALUES(2008,4,11,'Mike',5000)
INSERT INTO Sales VALUES(2008,4,12,'Mike',6000)
INSERT INTO Sales VALUES(2008,4,10,'John',2000)
INSERT INTO Sales VALUES(2008,4,11,'John',2500)
INSERT INTO Sales VALUES(2008,4/12,'John',3000)
GO

SELECT * FROM Sales

GROUP BY ROLLUP

عبارت `GROUP BY ROLLUP` برای ستون‌های مشخص شده، از راست به چپ، زیرمجموعه‌ها (subtotals) و همچنین یک مجموع کل (overall total) را برای تمام ستون‌ها ایجاد می‌کند. برای مثال، فرض کنید می‌خواهیم مجموع فروش را برای `SalesYear`، `SalesQuarter` و `SalesMonth` بدست آوریم. علاوه بر این، می‌خواهیم زیرمجموعه‌ها را برای `SalesQuarter` و `SalesYear` و همچنین مجموع کل را داشته باشیم. عبارت `ROLLUP` می‌تواند یک آرگومان شامل لیستی از ستون‌ها را برای خلاصه‌سازی بپذیرد. تعداد مجموعه‌های گروه‌بندی (grouping sets) که ایجاد می‌شوند، برابر است با:

2^N

که N تعداد آیتم‌های گروه‌بندی در لیست است.

کوئری زیر برای دریافت این نتایج استفاده می‌شود:


SELECT
 SalesYear, SalesQuarter, SalesMonth,
 SUM(SalesAmount) AS TotalSales,
 GROUPING(SalesYear) AS GrpYear,
 GROUPING(SalesQuarter) AS GrpQuarter,
 GROUPING(SalesMonth) AS GrpMonth
FROM Sales
GROUP BY ROLLUP (SalesYear, SalesQuarter, SalesMonth)
ORDER BY SalesYear, SalesQuarter, SalesMonth

تابع `GROUPING` زمانی که یک ردیف زیرمجموعه (subtotal) برای آن ستون باشد، مقدار `1` و در غیر این صورت مقدار `0` را برمی‌گرداند. این تابع در تحلیل داده‌ها برای تشخیص ردیف‌های تجمیعی از ردیف‌های جزئی بسیار مفید است.

GROUP BY CUBE

`GROUP BY CUBE` زیرمجموعه‌ها را برای تمام ترکیبات ممکن از ستون‌های مشخص شده و همچنین یک مجموع کل را برای تمام ستون‌ها ایجاد می‌کند. برای مثال، فرض کنید می‌خواهیم مجموع فروش را برای `SalesYear`، `SalesQuarter` و `SalesMonth` بدست آوریم. علاوه بر این، می‌خواهیم تمام ترکیبات ممکن از زیرمجموعه‌ها را برای `SalesYear`، `SalesQuarter` و `SalesMonth` و همچنین مجموع کل را داشته باشیم. عبارت `CUBE` می‌تواند یک آرگومان شامل لیستی از ستون‌ها را برای خلاصه‌سازی بپذیرد. تعداد مجموعه‌های گروه‌بندی (grouping sets) که ایجاد می‌شوند، برابر است با:

2^N

که N تعداد آیتم‌های گروه‌بندی در لیست است.

کوئری زیر برای دریافت این نتایج استفاده می‌شود:


SELECT
 SalesYear, SalesQuarter, SalesMonth,
 SUM(SalesAmount) AS TotalSales,
 GROUPING(SalesYear) AS GrpYear,
 GROUPING(SalesQuarter) AS GrpQuarter,
 GROUPING(SalesMonth) AS GrpMonth
FROM Sales
GROUP BY CUBE (SalesYear, SalesQuarter, SalesMonth)
ORDER BY SalesYear, SalesQuarter, SalesMonth

GROUPING SETS

عبارت `GROUPING SETS` یک مجموعه نتیجه (result set) تولید می‌کند که معادل `UNION ALL` مجموعه‌های گروه‌بندی منفرد مشخص شده در عبارت `GROUPING SETS` است. این روش بسیار ساده‌تر از نوشتن تمامی دستورات `SELECT` به صورت جداگانه و سپس استفاده از `UNION ALL` است. برای کوئری زیر، می‌خواهیم مجموع فروش را برای `SalesYear`، `SalesQuarter` و `SalesMonth` بدست آوریم. همچنین می‌خواهیم زیرمجموعه‌ها را فقط برای `SalesYear` و `SalesQuarter` و همچنین مجموع کل را داشته باشیم.

کوئری زیر برای دریافت این نتایج استفاده می‌شود. برای شبیه‌سازی کوئری `ROLLUP` از `GROUPING SETS`، آن را به شکل زیر می‌نویسیم: عبارت `GROUPING SETS` می‌تواند یک آرگومان شامل لیست ستون‌ها، یا چندین لیست، یا اصلاً هیچ ستونی برای مجموع کل دریافت کند. تعداد مجموعه‌های گروه‌بندی ایجاد شده برابر با تعداد آیتم‌های گروه‌بندی در لیست است.


SELECT
 SalesYear, SalesQuarter, SalesMonth,
 SUM(SalesAmount) AS TotalSales,
 GROUPING(SalesYear) AS GrpYear,
 GROUPING(SalesQuarter) AS GrpQuarter,
 GROUPING(SalesMonth) AS GrpMonth
FROM Sales
GROUP BY GROUPING SETS
 (
  (SalesYear, SalesQuarter, SalesMonth),
  (SalesYear, SalesQuarter),
  (SalesYear),
  ()
 )
ORDER BY SalesYear, SalesQuarter, SalesMonth

این کوئری برای دریافت نتایج `GROUPING SETS` قبلی است:


SELECT
 SalesYear, SalesQuarter, SalesMonth,
 SUM(SalesAmount) AS TotalSales,
 GROUPING(SalesYear) AS GrpYear,
 GROUPING(SalesQuarter) AS GrpQuarter,
 GROUPING(SalesMonth) AS GrpMonth
FROM Sales
GROUP BY GROUPING SETS
 (
  (SalesYear, SalesQuarter, SalesMonth),
  (SalesYear, SalesQuarter),
  ()
 )
ORDER BY SalesYear, SalesQuarter, SalesMonth

بهینه‌سازی گزارش‌گیری با GROUP BY SETS در SQL Server راهنمای جامع ROLLUP CUBE و GROUPING SETS