WINDOW FUNCTION و توابع رتبه بندی - مقدمه

در آموزش WINDOW FUNCTION و پایان محدودیت های GROUP BY با Aggregate window function آشنا شدیم. در این آموزش توابع رتبه بندی(Ranking Window Function) را معرفی می کنیم و با ذکر چند مثال، کاربرد این توابع را خواهیم دید.  Ranking Window Function از نسخه sql server 2005 معرفی شده است و به کمک این توابع می توانیم بسیاری از کوئری ها را که قبلاً با نوشتن تعداد دستورات زیاد و پیچیده تولید می کردیم، با استفاده از این توابع با کارایی بالا و تعداد دستورات کمتر ایجاد کنیم. 

توابع رتبه بندی(Ranking Window Function)  عبارتند از ROW_NUMBER, RANK, DENSE_RANK و NTILE که در ادامه به معرفی نحوه عملکرد هر یک می پردازیم و سعی می کنیم برای هر تابع یک مثال عملی ذکر کنیم. پیشنهاد می شود در ابتدا آموزش WINDOW FUNCTION چیست را مطالعه کنید.

---

WINDOW FUNCTION و توابع رتبه بندی - معرفی

 1. WINDOW FUNCTION و توابع رتبه بندی - ROW_NUMBER

از این تابع برای تولید دنباله ای از اعداد بر اساس ترتیب یک ستون خاص(نزولی یا صعودی) استفاده می شود. به عبارتی دیگر، این تابع به هر کدام از رکوردهای خروجی یک شماره ردیف اختصاص می دهد. به منظور آشنایی با عملکرد تابع Row_Number و ارائه یک مثال از کاربرد آن، جدول Employees را با رکوردهای تستی به صورت زیر ایجاد می کنیم:

--Employees ایجاد جدول   
CREATE TABLE [dbo].[Employees](
	[NatinalCode] [char](10),
	[Name] [nvarchar](50) ,
	[Family] [nvarchar](50) ,
	[JobGroup] [char](1),
	[Salary] [numeric](3,1) 
) 
GO
--درج مقادیر تستی در جدول
insert into [Employees] Values('1199800001',N'محسن',N'بندامیر','A',8),('1199800002',N'غلامرضا',N'محمدی','A',7.5)
,('1199800003',N'علیرضا',N'خورسندی','A' ,7.8 ),('1199800004',N'حسین',N'نوربخش', 'B',6 ),('1199800005',N'آرش',N'کمالی', 'B', 6.5)
,('1199800006',N'نرگس',N'سلیمانی', 'B', 6.8),('1199800007',N'شیوا',N'نادری','C' ,4.8 ),('1199800008',N'نجمه',N'قاسمی', 'C',4.6 ),
('1199800009',N'محمد',N'میرزاده', 'C', 4.3),('1199800010',N'ناصر',N'ندیمی','C',4 )

شکل زیر نحوه استفاده از تابع Row_Number را نمایش می دهد:

نکته: در استفاده از تابع Row_Number حتماً بایستی داخل عبارت ()OVER از order by استفاده کنیم.

دلیل نکته بالا این است که در مدل رابطه ای، بدون استفاده از order by مرتب بودن رکورها تضمین شده نیست.(حتی با وجود کلید اصلی و ایندکس). در آموزش ترفندهای sql یک روش برای دور زدن این محدودیت(الزام به order by در این تابع) معرفی می کنیم. اما به عنوان یک کاربرد از تابع Row_number که در تهیه گزارشات می توانیم از آن استفاده کنیم، استخراج nرکورد اول از هر گروه می باشد. به عنوان مثال برای استخراج دو کارمند اول با بیشترین حقوق دریافتی از هر گروه کاری از کوئری زیر استفاده می کنیم:

همانطور که در شکل بالا مشخص شده است، در کادر سبز رنگ(subquery) با توجه به وجود عبارت partition by در ()OVER از تابع Row_Number، ابتدا رکوردهای خروجی بر اساس فیلد JobGroup گروهبندی می شوند و سپس رکوردهای هر گروه به ترتیب نزولی بر اساس حقوق دریافتی(salary) مرتب سازی شده و نهایتاً تابع Row_Numbet به صورت مستقل برای هر گروه محاسبه و رکوردهای موجود را شماره گذاری می کند. با توجه به شرط موجود در select بیرونی(RowNumer<=2) تنها دو کارمند اول(دارای مقادیر 1و2 در فیلد RowNumber) از هر گروه کاری انتخاب می شوند.

2. WINDOW FUNCTION و توابع رتبه بندی - DENSE_RANK

 از این تابع برای رتبه بندی استفاده می شود. به عنوان مثال رتبه بندی دانش آموزان بر اساس معدل، رتبه بندی تیم های شرکت کننده در یک مسابقه بر اساس امتیاز دریافتی و ... . در ادامه با مثال رتبه بندی دانش آموزان بر اساس معدل، نمونه ای از استفاده این تابع را می بینیم:

--ایجاد جدول
CREATE TABLE [dbo].[students](
	[std_ID] [int] IDENTITY(1,1) NOT NULL,
	[std_Code] [int] NULL,
	[std_Name] [nvarchar](50) NULL,
	[std_Family] [nvarchar](50) NULL,
	[std_Average] [decimal](4, 2) NULL
) 

--درج داده های تستی در جدول
insert into [students] Values(1199800001,N'محسن',N'بندامیر',18.08),(1199800002,N'غلامرضا',N'محمدی',18.08)
,(1199800003,N'علیرضا',N'خورسندی',17.85),(1199800004,N'حسین',N'نوربخش',16.65),(1199800005,N'آرش',N'کمالی', 15.25)
,(1199800006,N'نرگس',N'سلیمانی', 17.85),(1199800007,N'شیوا',N'نادری',15.25 ),(1199800008,N'نجمه',N'قاسمی', 17.85 ),
(1199800009,N'محمد',N'میرزاده', 14.12),(1199800010,N'ناصر',N'ندیمی',16.07 )

استفاده از تابع ()DENSE_RANK جهت رتبه بندی دانش آموزان بر اساس معدل:

با توجه به اینکه در تابع DENSE_RANK مرتب سازی بر اساس فیلد std_average انجام شده است از آنجایی که دو رکورد اول دارای معدل(std_average) یکسانی هستند بنابراین هر دو مشترکاً، رتبه اول(مقدار یک در فیلد  DENSE_RANK) می شوند. همینطور برای سه رکورد بعدی(با معدل 17.85) رتبه دوم در نظر گرفته می شود. دقیقاً مانند آنچه در تعیین رتبه دانش آموزان در مقاطع تحصیلی اتفاق می افتد.

نکته: نوشتن order by در تابع DENSE_RANK الزامی است. رتبه دهی بر اساس مقادیر موجود در فیلد order by انجام می شود.

در صورت تغییر دادن نحوه مرتب سازی معدل از desc به asc در تابع بالا، رتبه بندی به صورت برعکس انجام می شود(معدل 14.12 رتبه اول می شود!). فیلد DENSE_RANK در خروجی را به عنوان رتبه دانش آموزان در نظر بگیرید.

نکته: در صورت اضافه کردن Distinct به تابع Dense_Rank، خروجی آن مانند تابع Row_Number خواهد شد.

3. WINDOW FUNCTION و توابع رتبه بندی - RANK:

از تابع ()RANK نیز مانند DENSE_RANK برای رتبه بندی استفاده می کنیم، اما این تابع دارای عملکردی متفاوت با تابع DENSE_RANK است. برای مشخص شدن این تفاوت، مثال رتبه بندی دانش آموزان بر اساس معدل را این بار با استفاده از تابع ()RANK انجام می دهیم:

همانطور که در شکل بالا مشخص شده است یک شمارش داخلی بین رکوردهای خروجی وجود دارد و هر رکورد دارای یک شماره ردیف است(مانند آنچه در خروجی تابع row_number وجود داشت) مجموعه رکوردها با مقدار یکسان در فیلد order by (در اینجا std_average) همگی دارای رتبه مساوی با شمارش داخلی اولین رکورد با همان مقدار در فیلد order by هستند. به محض اینکه رکورد بعدی دارای مقدار متفاوتی در فیلد order by باشد، شمارش داخلی معادل با آن رکورد به عنوان رتبه لحاظ می شود.

بنابراین با توجه به اینکه دو نفر ابتدایی به صورت مشترک معدل 18.0 گرفته اند، شمارش داخلی رکورد اول یعنی عدد یک برای هر دوی آن ها لحاظ می شود. اما دانش آموز سوم با توجه به اینکه معدلی متفاوت با دو دانش آموز اول دارد، مقدار معادل با شمارش داخلی آن یعنی عدد سه به عنوان رتبه برای آن لحاظ می شود و تمامی دانشجویانی که معدلی مشابه با معدل این دانش آموز را دارند در رتبه سوم قرار می گیرند و ... .(مفهوم شمارش داخلی، تنها جهت آشنایی بهتر با نحوه عملکرد این تابع ذکر شده است)

نکته: نوشتن order by در تابع RANK الزامی است.

 نکته قابل توجه در این تابع، ایجاد گپ در تخصیص رتبه می باشد. به عنوان مثال در خروجی کوئری بالا، به دو رکورد ابتدایی عدد یک، به عنوان رتبه اختصاص داده شده است و در ادامه رکورد سوم، رتبه سه را دریافت کرده، به عبارتی در اینجا اثری از عدد دو نیست و به نوعی یک گپ در توالی اعداد ایجاد شده است در حالیکه تابع DENSE_RANK در رتبه دهی، توالی اعداد را حفظ می کند. در شکل زیر، تفاوت عملکرد دو تابع ()RANK و ()DENSE_RANK در تولید نتیجه نهایی را مشاهده می کنید:

همانطور که از نتایج اجرای دو تابع ()RANK و ()DENSE_RANK مشخص است، خروجی تولید شده در تابع ()DENSE_RANK شباهت بیشتری با آنچه ما در دنیای واقعی تحت عنوان رتبه بندی می شناسیم دارد. اگرچه تابع ()RANK  نیز می تواند کاربرد خاص خود را داشته باشد(بنده تا به حال مثالی واقعی برای این تابع نداشته ام)

نکته: در صورتیکه مجموعه رکوردها، دارای مقدار تکراری در فیلد order by در عبارت ()OVER نباشند. خروجی دو تابع Rank و Dense_Rank یکسان است.

4. WINDOW FUNCTION و توابع رتبه بندی - NTILE:

از تابع NTile برای دسته بندی رکوردها استفاده می شود به عبارتی دیگر توزیع رکوردهای مرتب شده در تعداد مشخصی گروه. مثلاً گروهبندی دانشجویان دانشگاه جهت تخصیص خوابگاه دانشجویی و یا گروهبندی دانشجو ها در گروهای درسی و ... . کوئری زیر نحوه استفاده از تابع NTILE جهت توزیع دانش آموزان در سه گروه را نمایش می دهد: 

پارامتر ورودی تابع NTile، مشخص کننده تعداد گروه هایی است که قصد داریم، رکورها را بین آن ها توزیع کنیم. 

 نکته: در تابع NTile، اینکه چه رکوردهایی در هر گروه باشند وابسته به ستون مرتب سازی است.

در رابطه با این تابع، به دو نکته زیر توجه داشته باشید:

    1. نحوه محاسبه تعداد رکورد در هر گروه:

اگر تعداد رکوردها بر تعداد گروه های مورد نظر ما بخشپذیر نباشد(باقیمانده بیشتر از صفر داشته باشد) از تعداد رکوردهای باقیمانده، یک واحد به تعداد رکورد هر گروه اضافه می کنیم(به ترتیب از اولین گروه). به عنوان نمونه در کوئری بالا قصد داریم تا 10دانش آموز را در سه گروه قرار دهیم از آنجایی که 10 تقسیم بر 3 دارای خارج قسمت 3 و باقیمانده 1 است. در نتیجه در هر گروه 3 دانش آموز قرار می گیرد و یک واحد به تعدا دانش آموزان گروه اول اضافه می شود. بنابراین گروه اول دارای 4 دانش آموز و دو گروه دیگر هر کدام سه دانش آموز دارند.

    2. نحوه شماره گذاری رکورهای هر گروه:

گروه ها با شماره گذاری از یک شروع می شوند. مجموعه رکوردهای متعلق به یک گروه دارای شماره گروه یکسان هستند.

در شکل زیر، نتیجه اجرای چهار تابع Ranking Window Function بر روی جدول Students جهت مقایسه با یکدیگر نمایش داده شده است:

---

WINDOW FUNCTION و توابع رتبه بندی - جمع بندی

در این آموزش علاوه بر معرفی هر یک ار توابع رتبه بندی، یک مثال عملی از کاربرد این توابع ارائه شد. در هریک از توابع معرفی شده، امکان استفاده از partition by به منظور گروهبندی رکوردها، قبل از اعمال توابع نیز وجود دارد که با توجه به تمرکز بر نحوه عملکرد این توابع در آموزش WINDOW FUNCTION و توابع رتبه بندی تنها ساده ترین حالت از کاربرد این توابع را بررسی کردیم. شما به عنوان تمرین می توانید مثال های استفاده شده در این آموزش را همراه با اضافه کردن partition by انجام دهید.

در جدول زیر هر چهار تابع رتبه بندی به همراه کاربرد آن ها آورده شده است:

• قبلی
• بعدی