دستور Group by در SQL Server

نوشته شده توسط: تیم فنی نیک آموز

۰۹ اسفند ۱۴۰۰

زمان مطالعه: 10 دقیقه

مسئله

دستور GROUP BY در SQL Server امکان گروه‌بندی ردیف‌های یک کوئری را می‌دهد. به طور کلی، GROUP BY با یک تابع Aggregate در SQL Server، مانند SUM، AVG، و غیره استفاده می‌شود. در این مقاله، من راه‌های مختلف به کارگیری یک GROUP BY را به همراه خروجی توضیح می‌دهم.

راه‌حل

هنگام ایجاد کوئری برای گزارش‌ها، ما اغلب از دستور GROUP BY استفاده می‌کنیم. همچنین مواردی وجود دارد که داشتن subtotal و total به عنوان بخشی از خروجی مفید است و در اینجاست که از عملگرهای اختیاری مانند: CUBE، ROLLUP و GROUPING SET استفاده می‌کنیم. این گزینه‌ها مشابه هستند، اما نتایج متفاوتی را ایجاد می‌کنند.

ایجاد پایگاه داده نمونه در SQL Server

ابتدا یک پایگاه داده نمونه و تعدادی جدول ایجاد می‌کنیم و داده‌های آزمایشی خود را درج می‌کنیم.

USE MASTER
GO

CREATE DATABASE EmpTest
GO

USE EmpTest
GO

CREATE TABLE EmpSalary
(
id INT PRIMARY KEY IDENTITY(1,1),
EmpName VARCHAR (200),
Department VARCHAR(100),
Category CHAR(1),
Salary money
)

INSERT EmpSalary
SELECT 'Bhavesh Patel','IT','A',$8000
UNION ALL
SELECT 'Alpesh Patel','Sales','A',$7000
UNION ALL
SELECT 'Kalpesh Thakor','IT','B',$5000
UNION ALL
SELECT 'Jay Shah','Sales','B',$4000
UNION ALL
SELECT 'Ram Nayak','IT','C',$3000
UNION ALL
SELECT 'Jay Shaw','Sales','C',$2000

در اینجا داده‌هایی است که ما ایجاد کردیم را نشان می‌دهد

SELECT * FROM EmpSalary

مثالی از GROUP BY در SQL Server

در زیر یک کوئری GROUP BY ساده وجود دارد که داده‌های salary را جمع می کند. در کوئری اول، بر اساس Department و در کوئری دوم بر اساس Department و Category گروه‌بندی انجام می‌شود.

SELECT
Department,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY Department

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY Department, Category

در زیر نتایج به دست آمده نشان داده شده است. اولین کوئری دو ردیف را با مجموع Salary برمی‌گرداند، زیرا دو دپارتمان وجود دارد. کوئری دوم، شش ردیف را بر اساس Department و Category با مجموع Salary برمی‌گرداند، زیرا دو دپارتمان وجود دارد و در هر دپارتمان سه دسته وجود دارد.

مثالی از GROUP BY در SQL Server با Having

در مثال زیر، ما از همان GROUP BY قبلی استفاده می‌کنیم، اما داده‌ها را با استفاده از HAVING که داده‌ها را فیلتر می کند، محدود می‌کنیم. در مثال‌های زیر، برای اولین کوئری فقط می‌خواهیم دپارتمان‌هایی را ببینیم که مجموع salary آن‌ها برابر با ۱۶۰۰۰ است و برای کوئری دوم که در آن مجموع salary به ازای گروه‌بندی دپارتمان و دسته‌بندی برابر با ۸۰۰۰ است

SELECT
Department,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY Department
HAVING SUM(salary) = 16000

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY Department, Category
HAVING SUM(salary) = 8000

در زیر تنها ۲ ردیف هستند که معیارها را برآورده می‌کنند. ما می‌توانیم این را با نگاه کردن به نتایج کوئری از اولین مجموعه کوئری‌های بالا بررسی کنیم.

مثالی از GROUP BY با CUBE در SQL Server

این مثال به ما اجازه می‌دهد تا تمام ترکیب‌های داده‌ها را نشان دهیم. این شامل مجموع ترکیب‌های گروه‌بندی نیز می‌شود.

SELECT
Department,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY CUBE(Department)

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY CUBE(Department, Category)

نتایج کوئری اول، دو Department و grand total برای این دو دپارتمان را نشان می‌دهد. نتایج کوئری دوم همه ترکیب‌های Department و category را به ما نشان می‌دهد. در نمودار، گروه‌بندی‌های مختلفی را که بخشی از کوئری دوم هستند، تفکیک کرده‌ام.

مثالی از GROUP BY با ROLLUP در SQL Server

این مثال شبیه به Group By با Cube است، اما خواهیم دید که خروجی کمی متفاوت است، جایی که تعداد سطرهای برگشتی برای کوئری دوم، کمتر از مثال قبل است.

SELECT
Department,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY ROLLUP(Department)

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY ROLLUP(Department, Category)

می‌توانیم ببینیم که نتایج کوئری اول مانند مثال قبل است، اما کوئری دوم تنها ۹ ردیف را به جای ۱۲ ردیفی که در مثال قبل دریافت کردیم، برمی‌گرداند. کوئری دوم roll up را ابتدا توسط Department و سپس بر اساس Category انجام می‌دهد، که با Group By Cube که rollup را در هر دو جهت انجام می‌دهد متفاوت است. این به ما این امکان را می‌دهد که subtotal ها را برای هر Department و total را برای همه دپارتمان‌ها دریافت کنیم.

می‌توانیم کوئری دوم را، همان طور که در زیر نشان داده شده است، به صورت roll up بر اساس Category و سپس Department تغییر دهیم.

SELECT
Department,
SUM(Salary) AS salary
FROM EmpSalary
GROUP BY ROLLUP(Department)

SELECT
Department,
Category,
SUM(Salary) AS salary
FROM EmpSalary
GROUP BY ROLLUP (Category, Department)

اکنون می‌توانیم خروجی کوئری دوم را به صورت زیر مشاهده کنیم.

مثالی از GROUP BY با ROLLUP به همراه GROUPING_ID در SQL Server

گزینه دیگر استفاده از GROUPING_ID به عنوان بخشی از مجموعه نتایج برای نمایش هر گروه است.

SELECT
Department,
Category,
SUM(Salary) AS Salary,
GROUPING_ID(Category, Department) AS GroupingID
FROM EmpSalary
GROUP BY ROLLUP(Category, Department)

می‌بینیم که نتایج مشابهی نسبت مثال بالا داریم، اما اکنون برای هر یک از این گروه‌ها یک مقدار گروه‌بندی داریم.

مثالی از GROUP BY با GROUPING SETS در SQL Server

با grouping sets، می‌توانیم تعیین کنیم که چگونه می‌خواهیم داده‌ها کنار هم قرار گیرند.

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY GROUPING SETS(Category, Department,(Category, Department),())

در زیر می‌بینیم که گروهی را برای Category، گروهی دیگر برای Department، گروهی دیگر برای Category و Department و آخرین گروه را برای NULL انجام دادیم.

در اینجا مثال دیگری با Department و Category و یک گروه کلی برای NULL نشان داده‌ایم.

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY GROUPING SETS((Department, Category),())

همچنین می‌توانیم این کار را یک قدم دیگر توسعه دهیم و از CUBE و ROLLUP برای grouping sets مختلف استفاده کنیم.

SELECT
Department,
Category,
SUM(Salary) AS Salary
FROM EmpSalary
GROUP BY GROUPING SETS(CUBE(Department, Category), ROLLUP(Department, Category))

خروجی به صورت زیر است:

جمع‌بندی

به طور خلاصه می‌توان گفت، از عملگرهای اختیاری مانند CUBE، ROLLUP و GROUPING SET در کوئری می‌توانیم استفاده کنیم. GROUPING SET یک گزینه قابل کنترل و مقیاس‌پذیر است، بنابراین ترجیح می‌دهم از این گزینه به جای ROLLUP و CUBE استفاده کنم.