خواندن ۶ دقیقه·۳ سال پیش

مفاهیم اساسی Group By

در Group By، دو مفهوم اساسی Group By Columns و Aggregate Columns وجود دارد.
در اینجا با مفاهیم
Having
Group By All
Cube
Rollup
Grouping Sets
نیز آشنا خواهید شد.

در رابطه با Group By Columns، ستون یا ستون هایی می باشند که بر اساس آنها گروه بندی انجام می شود. و در مورد Aggregate Columns، عملیات هایی می باشند که روی گروه ها اعمال می شوند.

اما در این رابطه نکاتی نیز وجود دارد که در مثال ها خواهیم دید.

نکته: هر زمانی که از Group By استفاده می کنیم، تمام Group By Columnهایی که در Select استفاده می شود باید به بخش Group By نیز انتقال داده شوند در غیر این صورت به ما خطا خواهد داد. این می تواند بزرگترین چالشی باشد که ما در استفاده از کوئری ها از جنس Group By خواهیم داشت. ولی برعکس این ماجرا امکان پذیر می باشد. یعنی یک فیلد در Group By استفاده شده باشد و در Select ذکر نشده باشد.

همچنین فیلدی که در Group By شرکت نکرده است را نمی توانیم در بخش Order By استفاده کنیم.

فرض کنید که من جدولی به نام Orders در دیتابیس دارم و قصد دارم بر اساس ستون های EmployeeID و CustomerID، عملیات Group By انجام دهم.

SELECT
	EmployeeID, CustomerID
FROM dbo.Orders
GROUP BY EmployeeID, CustomerID;
GO

در این کوئری، Group By ما شامل EmployeeID و CustomerID می باشد.

در مثال بعد، تعداد رکوردهای مشتری هایی را در جدول Customers بدست خواهیم آورد که در شهرهای اصفهان و تهران می باشند.

SELECT COUNT(City) FROM dbo.Customers
	WHERE City IN(N'تهران' , N'اصفهان');
GO

همانطور که می بینید در این کوئری Group By Columns نداریم. در نتیجه نیازی هم به Group By نداریم.

در ادامه ترکیب Group BY Columns و Aggregate Columns را خواهید دید.

در این کوئری، تعداد سفارش های هر مشتری را نمایش می دهیم.

SELECT
	CustomerID,
	COUNT(OrderID) AS Num
FROM dbo.Orders
GROUP BY CustomerID;
GO

فیلد CustomerID جزئی از Group By Columns می باشد و تعداد سفارش نیز Aggregate Column می باشد که برای بدست آوردن آن از Aggregate Function به نام Count استفاده کردیم. هر فیلدی که Aggregate Function روی آن زده نشده باشد باید در Group By شرکت داده شود.

در ادامه به غیر از تعداد سفارش های هر مشتری، جدیدترین سفارش آن مشتری را نیز نمایش خواهیم داد.

SELECT
	CustomerID,
	COUNT(OrderID) AS Num,
	MAX(OrderDate) AS NewOrder
FROM dbo.Orders
GROUP BY CustomerID;
GO

در نتیجه می توانیم بیش از یک Aggregate Function در Select خود داشته باشیم.

تا اینجا گروه بندی ما تک سطحی بود یعنی فقط روی یک فیلد گروه بندی خود را انجام دادیم. در ادامه گروه بندی به صورت چند سطحی را خواهیم دید.

در این کوئری قصد داریم تعداد مشتری را از هر استان و شهر نمایش دهیم.

SELECT
	State, City,
	COUNT(CustomerID) AS Num
FROM dbo.Customers
GROUP BY State, City;
GO

در ادامه قصد داریم سفارشات هر کارمند به تفکیک هر سال که شامل تعداد کل سفارش و مجموع کرایه‌های ثبت شده می باشد را نمایش دهیم.

SELECT
	EmployeeID,
	YEAR(OrderDate) AS OrderYear,
	COUNT(OrderID) AS Num,
	SUM(Freight) AS Rate
FROM dbo.Orders
GROUP BY EmployeeID, YEAR(OrderDate);
GO

عبارت هایی که من در جمله خود بعد از عبارت "هر" آوردم جزء Group By Columnهای من هستند.

گروه بندی و Where

برای اعمال محدودیت در کوئری ها از Where استفاده می کنیم.

نکته: Where برای Aggregate Columnها نمی باشد و صرفا برای Group By Columnها می باشد. یعنی فیلدهایی که جزئی از Group By Column باشند.

در این کوئری تعداد سفارش های کارمندان به‌ جز سفارش های ثبت شده توسط کارمند شماره 9 را نمایش می دهیم.

SELECT
	EmployeeID,
	COUNT(OrderID) AS Num
FROM dbo.Orders
	WHERE EmployeeID <> 9
GROUP BY EmployeeID;
GO

گروه بندی رکوردها و Having

در گروه بندی ها، فیلترگزاری بر روی Aggregate Columnها را با استفاده از Having انجام می دهیم.

در ادامه مشتری هایی که بیش از 20 سفارش داشته‌اند را نمایش خواهیم داد.

SELECT
	CustomerID,
	COUNT(OrderID) AS Num
FROM dbo.Orders
GROUP BY CustomerID
	HAVING COUNT(OrderID)>20;
GO

یک کوئری می تواند همزمان دارای Where و Having باشد.

تعداد سفارش های بالای 70 و سفارش هایی که توسط کارمند شماره 9 ثبت نشده است را نمایش خواهیم داد.

SELECT
	EmployeeID,
	COUNT(OrderID) AS Num
FROM dbo.Orders
	WHERE EmployeeID <> 9
GROUP BY EmployeeID
HAVING COUNT(OrderID) > 70;
GO

نکته: زمانی که از Having استفاده می کنیم، ملزم به نوشتن Group By می باشیم حتی اگر در Select خود از Aggregate Function استفاده نکرده باشیم.

دستور Group By All

زمانی که قصد داشته باشیم رکوردهایی که در Where و در Group By Query فیلتر شده اند را با مقادیر صفر نمایش دهیم، از Group By All استفاده می کنیم.

در این کوئری تعداد سفارش های کارمندان شماره یک، دو و سه را نمایش خواهیم داد و باقی کارمندان را صرفا نمایش می دهیم و تعداد سفارش های آن ها را در نظر نمی گیریم.

SELECT 
	EmployeeID, 
	COUNT(OrderID) AS Num
FROM dbo.Orders
	WHERE EmployeeID BETWEEN 1 AND 3
GROUP BY ALL EmployeeID
ORDER BY EmployeeID;
GO

نکته: استفاده از Having در Group By All تاثیر خواهد گذاشت و باید در استفاده از آن دقت شود.

مفاهیم Cube، Rollup و Grouping Sets

با استفاده از این سه مفهوم، به جای نوشتن چند کوئری، می توانیم در یک کوئری به هدف خود برسیم.

در Rollup، بر اساس فیلدهایی که در گروه بندی شرکت می کنند، می توانیم سرجمع اصلی و سرجمع فرعی بدست آوریم.

عبارت زیر را در نظر بگیرید:

ROLLUP (A,B,C):
(A,B,C)
(A,B)
(A)
()

وقتی می گوییم ROLLUP (A,B,C)، عمل گروه بندی را یکبار بر اساس تمام فیلدهای B،A و C انجام می دهیم، یکبار بر اساس A و B، یکبار بر اساس A و در نهایت یکبار هم سرجمع کل را حساب می کنیم.

در این کوئری، مشتری ها، تعداد سفارش های آن ها و سرجمع نهایی به ازای گروه ها را نمایش خواهیم داد.

SELECT
	CustomerID,
	COUNT(OrderID) AS Num
FROM dbo.Orders
GROUP BY ROLLUP (CustomerID);
GO

در این کوئری Rollup یک عضو دارد. یک گروه بندی بر اساس CustomerID انجام می شود و یکبار هم سرجمع کل را حساب می کند.

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

SELECT
	EmployeeID,
	YEAR(OrderDate) AS OrderYear,
	MONTH(OrderDate) AS OrderMonth,
	COUNT(OrderID) AS Num
FROM dbo.Orders
	WHERE EmployeeID  IN (1,2)
GROUP BY ROLLUP(EmployeeID, YEAR(OrderDate), MONTH(OrderDate));
GO

در واقع ما چهار Group By را در یک کوئری بدست آوردیم.

در Cube هم همین کار را برای ما انجام می دهیم با این تفاوت که در Cube دامنه سطوح گروه بندی به ازای تمامی ترکیبات ممکن می باشد.

CUBE (A,B,C):
(A,B,C)
(A,B)
(A,C)
(B,C)
(A)
(B)
(C)
()

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

SELECT
	CustomerID,
	COUNT(OrderID) AS Num
FROM dbo.Orders
GROUP BY CUBE (CustomerID);
GO

در یک سطح، خروجی Cube و Rollup یکسان خواهد شد. اما وقتی بر اساس چند سطحی انجام شود، داستان متفاوت خواهد شد.

SELECT
	EmployeeID,
	YEAR(OrderDate) AS OrderYear,
	MONTH(OrderDate) AS OrderMonth,
	COUNT(OrderID) AS Num
FROM dbo.Orders
GROUP BY CUBE (EmployeeID, YEAR(OrderDate), MONTH(OrderDate)) 
ORDER BY EmployeeID;
GO

تمام ترکیب های سه فیلدی که در Cube آوردیم را نمایش می دهد.

با استفاده از GROUPING در Rollup یا Cube، می توانیم متوجه شویم که چه فیلدی در گروه بندی من وجود نداشته است. می تواند بصورت تک سطحی و یا چند سطحی تعریف شود.

SELECT
	CustomerID,
	COUNT(OrderID) AS Num,
	GROUPING (CustomerID) AS GROUPING_CustomerID
FROM dbo.Orders
GROUP BY ROLLUP(CustomerID);
GO

در Rollup و Cube بر اساس یکسری قواعد ثابت گروه بندی را انجام دادیم، اما در Grouping Sets، قواعد گروه بندی را خود ما تعیین می کنیم.

در این کوئری، تمام سفارش های ثبت شده مشتری شماره یک یا دو را بر اساس کارمند و مشتری، بر اساس کارمند و سال و بر اساس مشتری و سال گروه بندی می کنیم.

SELECT 	
	EmployeeID,
	CustomerID,
	YEAR(OrderDate) AS OrderYear,
	COUNT(OrderID) AS Num
FROM dbo.Orders
	WHERE CustomerID=1 
		OR CustomerID=2
GROUP BY GROUPING SETS 
	(
		(EmployeeID,CustomerID),
		(EmployeeID,YEAR(OrderDate)),
		(CustomerID,YEAR(OrderDate))
	);
GO

نکته: Group By Colmnsها حداقل یکبار باید در ترکیب های گروه بندی ظاهر شوند.

در Grouping Sets می توانیم به جای Grouping از Grouping_ID استفاده کنیم. با استفاده از این تابع می توان فهمید که گروه‌بندی به‌ ازای کدامیک از ستون ها انجام شده و چه فیلدی در نتیجه‌ نهایی غایب است.

SELECT 	
	EmployeeID,
	CustomerID,
	YEAR(OrderDate) AS OrderYear,
	GROUPING_ID(EmployeeID, CustomerID, YEAR(OrderDate)) AS GROUPING_ID_Field
FROM dbo.Orders
	WHERE CustomerID = 1 
	OR CustomerID = 2
GROUP BY GROUPING SETS 
	(
		(EmployeeID,CustomerID),
		(EmployeeID,YEAR(OrderDate)),
		(CustomerID,YEAR(OrderDate))
	);
GO

اگر بخواهیم مقدار GROUPING_ID_Field توسط خود ما مشخص شود می توانیم از Case استفاده کنیم.

SELECT 	
	EmployeeID,
	CustomerID,
	YEAR(OrderDate) AS OrderYear,
	COUNT(OrderID) AS Num,
	CASE GROUPING_ID(EmployeeID, CustomerID, YEAR(OrderDate))
		WHEN 4 THEN N'مشتری و سال'
		WHEN 2 THEN N'کارمند و سال'
		WHEN 1 THEN N'کارمند و مشتری'
	END AS N'گروه بندی بر اساس'
FROM Orders
WHERE CustomerID=1 
	OR CustomerID=2
GROUP BY GROUPING SETS 
	(
		(EmployeeID,CustomerID),
		(EmployeeID,YEAR(OrderDate)),
		(CustomerID,YEAR(OrderDate))
	);
GO

پایان

Group By cube rollup grouping sets محسن فرخی

Mohsen Farokhi - محسن فرخی

Senior .NET Developer

شاید از این پست‌ها خوشتان بیاید