دستور Group by در SQL Server

دستور 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

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

مثالی از 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

مثالی از 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 در SQL Server با Having

مثالی از 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 با CUBE در SQL Server

مثالی از 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 را برای همه دپارتمان‌ها دریافت کنیم.مثالی از GROUP BY با ROLLUP در SQL Server

می‌توانیم کوئری دوم را، همان طور که در زیر نشان داده شده است، به صورت 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 در SQL Server

مثالی از 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 با ROLLUP به همراه GROUPING_ID در SQL Server

مثالی از 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 انجام دادیم.مثالی از GROUP BY با GROUPING SETS در SQL Server

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

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

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

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

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

خروجی به صورت زیر است:مثالی از GROUP BY با GROUPING SETS در SQL Server

جمع‌بندی

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

منابع

 

 

https://www.mssqltips.com/sqlservertip/6315/group-by-in-sql-server-with-cube-rollup-and-grouping-sets-examples/

 

 

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.
 
دستورهای SQL Server

چه رتبه ای می‌دهید؟

میانگین ۴.۵ / ۵. از مجموع ۲

اولین نفر باش

title sign
دانلود مقاله
دستور Group by در SQL Server
فرمت PDF
9 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
تیم فنی نیک آموز
مقالات
236 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تیم فنی نیک آموز
پروفایل نویسنده
title sign
دیدگاه کاربران

ثبت نام رایگان در همایش Tehran .NET Conf 2023 ، همین الان کلیک کنید
ثبت نام رایگان..
close-image