SQL GROUP BY CUBE

فرض کنید جدولی به نام “Sales” با ستون‌های “ProductID”، “SalesPersonID”، “OrderDate” و “SalesAmount” دارید. هدف شما تجزیه و تحلیل عمیق داده‌های فروش با جزئیات ماهانه برای هر محصول، هر فروشنده و در کل است.

در اینجا یک پرس و جوی SQL که به طور کامل با توضیحات و کامنت‌ها نوشته شده است، با استفاده از GROUP BY CUBE برای دستیابی به این هدف ارائه می‌شود:

-- این پرس و جو مجموع فروش را برای هر ترکیبی از محصول، فروشنده، سال، ماه (و همچنین
--  مجموع کل و فروش برای هر سطح از جزئیات) از جدول "Sales" بازیابی می‌کند.
SELECT
  -- ابعاد مورد نظر را با استفاده از GROUP BY CUBE  گروه بندی می‌کنیم.
  CUBE(
    -- نام محصول را از جدول "Products" استخراج می‌کنیم.
    Products.ProductName,
    -- نام کارمند را از جدول "Employees" استخراج می‌کنیم.
    Employees.Name,
    -- سال را از ستون "OrderDate" استخراج و به صورت مجزا نمایش می‌دهیم.
    YEAR(Sales.OrderDate),
    -- ماه را از ستون "OrderDate" استخراج و به صورت مجزا نمایش می‌دهیم.
    MONTH(Sales.OrderDate)
  ) AS Dimension,
  --  میزان کل فروش را برای هر گروه با استفاده از تابع SUM محاسبه می‌کنیم.
  SUM(Sales.SalesAmount) AS TotalSales
FROM Sales
-- جدول "Sales" را با جدول "Products" بر اساس ستون مشترک "ProductID" پیوند می‌زنیم.
INNER JOIN Products ON Sales.ProductID = Products.ProductID
-- جدول "Sales" را با جدول "Employees" بر اساس ستون مشترک "SalesPersonID" پیوند می‌زنیم.
INNER JOIN Employees ON Sales.SalesPersonID = Employees.EmployeeID
--  داده ها را بر اساس ابعاد گروه بندی شده با استفاده از GROUP BY CUBE گروه بندی می‌کنیم.
GROUP BY CUBE(
  Products.ProductName,
  Employees.Name,
  YEAR(Sales.OrderDate),
  MONTH(Sales.OrderDate)
);

توضیح گام به گام:

1. انتخاب ستون‌ها:

   • Products.ProductName: نام محصول را از جدول “Products” انتخاب می‌کند.
   • Employees.Name: نام کارمند را از جدول “Employees” انتخاب می‌کند.
   • YEAR(Sales.OrderDate): سال را از ستون “OrderDate” استخراج و به صورت مجزا نمایش می‌دهد.
   • MONTH(Sales.OrderDate): ماه را از ستون “OrderDate” استخراج و به صورت مجزا نمایش می‌دهد.
   • SUM(Sales.SalesAmount): میزان کل فروش را برای هر گروه با استفاده از تابع SUM محاسبه می‌کند.

2. پیوند جداول:

   • از INNER JOIN برای پیوستن به جداول “Sales”، “Products” و “Employees” بر اساس ستون‌های مشترک “ProductID” و “SalesPersonID” استفاده می‌شود. این اطمینان حاصل می‌کند که فقط رکوردهایی که در هر سه جدول مطابقت دارند، در محاسبات نهایی لحاظ می‌شوند.

3. گروه بندی با GROUP BY CUBE:

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

4. محاسبه مقادیر:

   • از تابع SUM برای محاسبه مجموع فروش (SalesAmount) برای هر گروه از رکوردهای گروه بندی شده استفاده می‌شود.

نتیجه:

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

مثال:

استفاده از GROUP BY CUBE در SQL برای تجزیه و تحلیل داده‌های فروش

فرض کنید جدولی به نام “Sales” با ستون‌های “ProductID”، “SalesPersonID”، “OrderDate”، “SalesAmount” و “Region” دارید. می‌خواهید گزارشی تهیه کنید که شامل موارد زیر باشد:

• مجموع فروش ماهانه برای هر محصول: این شامل جمع SalesAmount برای هر محصول در هر ماه است.
• مجموع فروش ماهانه برای هر منطقه: این شامل جمع SalesAmount برای هر منطقه در هر ماه است.
• مجموع فروش ماهانه: این شامل جمع SalesAmount برای همه محصولات و همه مناطق در هر ماه است.

می‌توانید با استفاده از GROUP BY CUBE در SQL Server به این هدف برسید.

در اینجا یک پرس و جوی SQL که به طور کامل با توضیحات نوشته شده است آورده شده است:

SELECT
  -- ابعاد مورد نظر را با استفاده از GROUP BY CUBE گروه بندی می‌کنیم.
  CUBE(
    -- نام محصول را از جدول "Products" استخراج می‌کنیم.
    Products.ProductName,
    -- نام منطقه را از جدول "Sales" استخراج می‌کنیم.
    Region,
    -- ماه را از ستون "OrderDate" استخراج و به صورت مجزا نمایش می‌دهیم.
    MONTH(Sales.OrderDate)
  ) AS Dimension,
  --  میزان کل فروش را برای هر گروه با استفاده از تابع SUM محاسبه می‌کنیم.
  SUM(Sales.SalesAmount) AS TotalSales
FROM Sales
-- جدول "Sales" را با جدول "Products" بر اساس ستون مشترک "ProductID" پیوند می‌زنیم.
INNER JOIN Products ON Sales.ProductID = Products.ProductID
GROUP BY CUBE(
  Products.ProductName,
  Region,
  MONTH(Sales.OrderDate)
);

توضیح:

1. انتخاب ستون‌ها:

   • Products.ProductName: نام محصول را از جدول “Products” انتخاب می‌کند.
   • Region: نام منطقه را از جدول “Sales” انتخاب می‌کند.
   • MONTH(Sales.OrderDate): ماه را از ستون “OrderDate” استخراج و به صورت مجزا نمایش می‌دهد.
   • SUM(Sales.SalesAmount): میزان کل فروش را برای هر گروه با استفاده از تابع SUM محاسبه می‌کند.

2. گروه بندی با GROUP BY CUBE:

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

3. محاسبه مقادیر:

   • از تابع SUM برای محاسبه مجموع فروش (SalesAmount) برای هر گروه از رکوردهای گروه بندی شده استفاده می‌شود.

نتیجه:

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

نکات:

• می‌توانید از نام‌های مستعار برای ستون‌های گروه بندی شده در clause SELECT استفاده کنید.
• می‌توانید از توابع تجمیع دیگر مانند AVG، MIN و MAX در clause SELECT استفاده کنید.
• می‌توانید از where clause برای فیلتر کردن داده‌ها قبل از گروه بندی استفاده کنید.
• می‌توانید از clause ORDER BY برای مرتب کردن نتایج استفاده کنید.
• می‌توانید از clause HAVING برای فیلتر کردن گروه‌ها بعد از گروه بندی استفاده کنید.

منابع:

• https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/create-union-queries-visual-database-tools?view=sql-server-ver16
• [https://www.w3schools.com/sql/sql_ref_group_by.asp](https://www.