آشنایی با مفاهیم Window Function

در مطالب گذشته، با کوئری ها از جنس گروه بندی آشنا شدیم. یکی از مشکلاتی که در این گونه کوئری ها داریم، فیلدهایی که در مقابل Select اضافه می کنیم و قرار نیست Aggregate Function روی آن ها اعمال شود، باید در مقابل Group By نیز قرار داده شوند. با افزایش تعداد فیلد ها، گروه های ما نیز افزایش پیدا می کند. مشکل بعدی این است که به جزئیات نیز دسترسی نداریم. زمانی که نتایجی را به ازای هر گروه بدست می آوریم، بر روی آن نتایج نمی توانیم محاسباتی را انجام دهیم. مفاهیم Window Function، از SQL Server 2005 برای بهبود کوئری نویسی بر پایه Group By مطرح شد.

در Window Function، بر روی رکوردهای جدول گروه بندی انجام می شود و عملیات هایی بر اساس آن گروه بندی می توانیم انجام دهیم. تفاوت با آن Group By این است که در Group By عملیات ما بر اساس یک گروه می باشد ولی در اینجا عملیات ما بر روی یک Window می باشد.

مفهوم Window مجموعه ای از رکوردها جهت انجام محاسبات می باشد.

اجزاء Window Function به سه دسته تقسیم می شود.

• Partitioning
• Ordering
• Framing

در Partitioning، بر روی رکوردهای کوئری می توانیم عملیات پارتیشن بندی انجام دهیم. در Ordering، نتایج ما بر اساس فیلدهایی که مشخص می کنیم مرتب می شود. و در Framing بر اساس اطلاعات مرتب سازی شده در یک پارتیشن، محاسبات انجام می شود.

برای استفاده از Window Function، از اپراتوری به نام OVER استفاده می کنیم. اپراتور OVER بستر لازم برای فعالیت Function را آماده می کند.

نحوه استفاده به این شکل می باشد.

Window_Function () OVER ([<partition_by_clause>] <order_by_clause>)

و فرآیند پردازش منطقی یک کوئری بر اساس Window Function به این شکل می باشد.

انواع Window Functionها به Distribution Function ،Ranking Functions ،Aggregate Function و Offset Function تقسیم می شود.

وظیفه Ranking Functions رتبه بندی کردن رکوردها می باشد و دارای چهار تابع می باشد که آن ها را بررسی خواهیم کرد.

• تابع ROW_NUMBER

از طریق آن می توانیم به رکوردهای خود یک شماره ردیف ترتیبی اضافه کنیم.

نحوه استفاده به این شکل می باشد.

ROW_NUMBER() OVER(ORDER BY Clause)

برای مثال از جدول Customers سه فیلد را به همراه شماره ردیف به صورت مرتب سازی شده بر اساس CustomerID نمایش می دهیم.

SELECT
	ROW_NUMBER() OVER(ORDER BY CustomerID) AS Ranking,
	CustomerID,
	CompanyName,
	City
FROM dbo.Customers;
GO

در مثال بعد ابتدا مرتب سازی را بر اساس استان و سپس شهر انجام می دهیم.

SELECT
	ROW_NUMBER() OVER(ORDER BY State, City DESC) AS Ranking,
	EmployeeID,
	State,
	City
FROM dbo.Employees;
GO

از alias ستون Window Function در قسمت Where نمی توانیم استفاده کنیم. بنابراین اگر قصد نمایش یک بازه خاص از ستون Ranking را داشته باشیم، باید از Derived Table یا CTE استفاده کنیم.

از طریق Derived Table به این صورت انجام می شود:

SELECT * FROM
    (SELECT
	   ROW_NUMBER() OVER(ORDER BY CustomerID) AS Ranking,
	   CustomerID,
	   CompanyName,
	   City
	 FROM dbo.Customers) AS Tmp
    WHERE Tmp.Ranking BETWEEN 10 AND 20;
GO

و از طریق CTE به این صورت انجام می شود:

WITH CTE
AS
(
	SELECT
	   ROW_NUMBER() OVER(ORDER BY CustomerID) AS Ranking,
	   CustomerID,
	   CompanyName,
	   City
	 FROM dbo.Customers
)
SELECT * FROM CTE
    WHERE CTE.Ranking BETWEEN 10 AND 20;
GO

• تابع DENSE_RANK

از طریق DENSE_RANK، می توانیم رتبه بندی انجام دهیم و می توانیم به مقادیر تکراری رتبه یکسان تخصیص دهیم و همچنین بین رتبه ها Gap ایجاد نشود.

نحوه استفاده به این شکل می باشد.

DENSE_RANK() OVER(ORDER BY Clause)

SELECT
	DENSE_RANK() OVER(ORDER BY City) AS Ranking,
	CustomerID, City
FROM dbo.Customers;
GO

• تابع RANK

از طریق RANK ما می توانیم رتبه بندی انجام دهیم و می توانیم به مقادیر تکراری رتبه یکسان تخصیص دهیم ولی در رتبه ها Gap هم ایجاد می شود.

نحوه استفاده به این شکل می باشد.

RANK() OVER(ORDER BY Clause)

SELECT
	RANK() OVER(ORDER BY City) AS Ranking,
	CustomerID, City
FROM dbo.Customers;
GO

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

SELECT
	RANK() OVER(ORDER BY COUNT(OrderID) DESC) AS Ranking,
	CustomerID,
	COUNT(OrderID) AS Num
FROM Sales.Orders
GROUP BY CustomerID;
GO

• تابع NTILE

از طریق تابع NTILE، بر اساس رکوردها دسته بندی انجام می دهیم.

NTILE(integer_expression) OVER(ORDER BY Clause)

برای مثال رکوردهای جدول Products را در دو قسمت دسته بندی می کنیم. به این شکل که کمترین قیمت بالا و بیشترین قیمت پایین قرار بگیرید.

SELECT
	NTILE(2) OVER (ORDER BY UnitPrice) AS Ranking,
	ProductName
FROM dbo.Products;
GO

یکی دیگر از اجزای Window Function، مفهوم Partitioning می باشد. Partitioning قابلیت ترکیب شدن با Ranking Functions و Aggregate Function را دارد.

در این مثال ابتدا گروه بندی انجام می شود و سپس بر اساس فیلد ORDER BY، مرتب‌ سازی به‌ ازای هر گروه و متناسب با تابع آن انجام می‌شود.

SELECT
	ROW_NUMBER() OVER(PARTITION BY City ORDER BY CustomerID) AS Ranking,
	CustomerID,
	City
FROM dbo.Customers;
GO

به ازای هر شهر در جدول Customers، یک Window ایجاد می شود.

در مثالی دیگر، بیشترین و کمترین قیمت محصولات از هر Category را بدست می آوریم.

SELECT
	CategoryID,
	ProductName,
	MIN(UnitPrice) OVER(PARTITION BY CategoryID) AS MIN_Price,
	MAX(UnitPrice) OVER(PARTITION BY CategoryID) AS MAX_Price
FROM dbo.Products;
GO

در هر پارتیشن، مقدار MIN و MAX را محاسبه می کند.

پایان