CREATE TABLE Test (ID INT, Product VARCHAR(100), Price INT, Color VARCHAR(100))
GO
INSERT INTO Test
SELECT 1, 'Toy', 100, 'Black'
UNION ALL
SELECT 2, 'Pen', 100, 'Black'
UNION ALL
SELECT 3, 'Pencil', 100, 'Blue'
UNION ALL
SELECT 4, 'Pencil', 100, 'Red'
UNION ALL
SELECT 5, 'Pencil', 200, 'Yellow'
UNION ALL
SELECT 6, 'Cup', 300, 'Orange'
UNION ALL
SELECT 7, 'Cup', 400, 'Brown'
GO

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

Select *, ROW_NUMBER() OVER ( ORDER BY Price DESC) AS RN from Test

• لازم به یادآوری است که استفاده از  Order by در Syntax تابع Row_Number  الزامی میباشد.

Select *,ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Price DESC) AS RN from Test

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

Select *,RANK() over (ORDER BY Price ) AS RANK from Test

یادآوری: زمانی که دورن Order by ترتیب صعودی یا نزولی بودن را تعیین نکنیم، Order by بصورت پیش فرض صعودی میباشد.

همانطور که در شکل مشاهده می‌نمایید،رتبه بندی انجام شده به ترتیب نمی‌باشد، و برای مقادیر تکراری فیلد Price از Rank یکسانی استفاده شده است. نکته دیگر این که بین اعداد مشاهده شده در فیلد Rank نیز gap ایجاد می‌شود. به عبارت دیگر عمده تفاوت تابع Rank با تابع Row_Number همین مواردی است که بیان شده است.

در Syntax تابع Rank نیز کلمه Partition هم وجود دارد، که در جهت Group by فیلد یا فیلدهای خاصی استفاده می‌شود، و رتبه بندی نیز در این حالت روی Group by انجام می‌گردد.

برای درک بهتر Query زیر را اجرا نمایی:

Select *,RANK() over (Partition by Product ORDER BY Price Desc) AS RANK from Test

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

همانطور که در شکل مشاهده می‌نمایید، رتبه بندی روی هر Group by بصورت جداگانه اعمال شده است.

تابع Dense_Rank

این تابع نیز همانند تابع Rank عمل می‌کند، با این تفاوت که هیچ gap ی بین اعداد آن رخ نمی‌دهد.

با جرای Query زیر خواهیم داشت:

Select *,dense_RANK() over (ORDER BY Price ) AS dense_RANK from Test

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

همانطور که ملاحظه می‌نماییدهیچ gap ی بین اعداد Rank ایجاد نشده است.

و برای استفاده از Partition، درتابع Dense_Rank همانند تابع‌های دیگر میباشد.

تابع NTILE:

این تابع نیز مانند توابع بالا در جهت رتبه بندی استفاده می‌شود، و بوسیله تابع فوق شما می‌توانید رکوردهای جدول خود را به تعداد گروه‌های دلخواه تقسیم نمایید.و Syntaxآن بصورت زیر میباشد:

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

برای درک مطلب فوق مثالی می‌زنیم:

Select * ,NTILE(4) over ( ORDER BY Price desc) from Test

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

در Syntax تابع فوق اشاره به Integer_Expressionشده است.که یک مقدار عددی دریافت می‌کند و بیانگر تعداد گروه بندی دلخواه میباشد.

حال سئوال اینجاست که رتبه بندی جدول به چه صورت انجام شده است:

همانطور که مشاهده می‌نمایید، جدول فوق شامل 7 رکورد می‌باشد،و ما در مثال خود،تمایل داشتیم که رکوردهای جدول به چهار گروه تقسیم و سپس رتبه بندی شوند، بنابراین 7 تقسیم بر 4 شده است و باقی مانده آن می‌شود 3

پس خواهیم داشت7=3+1*4

در ابتدا چهار گروه ایجاد می‌شودو در هر خانه یک رکورد قرار می‌گیرد

سپس 3 رکورد باقی می‌ماند که از اولین گروه رو به پایین ، برای هر گروه فقط یک رکورد درج می‌شود، یعنی یک رکورد به گروه یک،یک رکورد به گروه 2 و هم چنین یک رکورد به گروه 3 بنابراین خواهیم داشت:

نکته مهم: اگر تعداد رکورد باقی مانده بعد از تقسیم بیش از یک عدد باشد، در زمان اختصاص دادن به گروه ها، به هر گروه از بالا به پایین فقط یک رکورد اختصاص داده می‌شود. 

مثالی دیگر:

Select *,NTILE(3) over ( ORDER BY Price desc) AS NTILE from Test                              

خروجی:

در این حالت 7=1+2*3

امیدوارم مطلب فوق مفید واقع شده باشد.