دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی

دستور RANK در SQL Server مثل ترتیب صف نانوایی است. همانطور که در نانوایی براساس نوبتتان به شما نان می‌دهند، در SQL Server نیز به همه ردیف‌ها براساس دسته‌بندی‌شان رتبه‌ای تعلق می‌گیرد. دستور RANK ابزاری کارآمد برای رتبه‌بندی ردیف‌ها براساس معیارهای مختلف ارائه می‌دهد. در این مقاله، با مفهوم Rank و کاربردهای آن در SQL Server آشنا می‌شوید و نحوه استفاده از توابع رتبه‌بندی در سناریوهای مختلف را می‌آموزید.

دستور Rank در SQL Server؛ ابزار قدرتمند رتبه‌ بندی

دستور Rank در SQL Server که به نام Window Functions معروف است، برای هر ردیف از داده براساس یک دسته‌بندی و ترتیب مشخص، یک رتبه اختصاص می‌دهد. ترتیبی که شما برای مرتب‌سازی کل داده‌ها درنظر می‌گیرید، نحوه نمایش نتایج را مشخص می‌کند. به عبارت دیگر، ستون‌هایی که بر اساس آن‌ها داده‌ها را مرتب می‌کنید، روی ترتیب نهایی نتایج تاثیر می‌گذارد. تابع Rank، تابعی غیرقطعی است. یعنی خروجی آن ممکن است در دفعات بعدی اجرای دستور کمی تغییر کند.

نحوه استفاده از دستور Rank در SQL Server

سینتکس دستور Rank در SQL Server به‌صورت زیر است:

SELECT column_name,
RANK() OVER (PARTITION BY... ORDER BY...) as rank
FROM table_name;

• برای استفاده از این توابع، از دستور ()OVER استفاده می‌کنند که مجموعه‌ای از ردیف‌ها را به ازای کوئری مشخص می‌کند.
• column_name ستونی است که می‌خواهید براساس آن رتبه‌بندی کنید.
• PARTITION BY جدول را به گروه‌های مجزا براساس یک یا چند ستون تقسیم می‌کند.
• ORDER BY ترتیب مرتب‌سازی اطلاعات در هر گروه را مشخص می‌کند.
• در نهایت خروجی با دستور ORDER BY، ستون اصلی شما همراه با یک ستون جدید به اسم rank خواهد بود که رتبه‌ هر ردیف را نشان می‌دهد.

کاربرد دستور Rank در SQL Server

فرض کنید در یک فروشگاه آنلاین انبوهی از اطلاعات محصول دارید. تا امروز، از توابع جمع مثل مینیم (MIN)، ماکزیمم (MAX) و میانگین (AVG) برای جمع‌آوری داده‌ها استفاده می‌کردید که خروجی‌شان یک ردیف ساده بود. اکنون برای اینکه بدانید کدام محصول در کدام زمینه بهتر است، به رتبه‌بندی تک‌تک محصولات نیاز دارید. اینجاست که دستور Rank در SQL Server به کمک‌تان می‌آید. 

دستور Rank در SQL Server مثل یک سیستم رتبه‌دهی عمل می‌کند و به هر محصول یک رتبه می‌دهد . مثلا می‌توانید تعیین کنید کدام محصول در هر دسته جزو پرفروش‌ترین‌ها بوده یا کدام تبلت در لیست تبلت‌های بالای ۱۰ اینچ، بهترین امتیاز را از کاربرها گرفته است. برای استفاده از دستور Rank در SQL Server باید به سه سوال مهم جواب دهید:

• چه چیزی را می‌خواهید رتبه‌بندی کنید؟ (مثلا: قیمت، تعداد فروش، امتیاز کاربران)
• داخل کدام گروه می‌خواهید رتبه‌بندی کنید؟ (مثلا: تمام محصولات، دسته‌های کالایی خاص، محصولات یک برند خاص)
• براساس چه چیزی رتبه‌بندی کنید؟ (مثلا: براساس قیمت از کم به زیاد یا امتیاز کاربران از زیاد به کم)

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

استفاده از Rank در کوئری‌های پیچیده

برای رتبه‌بندی داده در SQL Server چهار تابع اصلی وجود دارد. قبل از بررسی تفاوت این توابع رتبه‌بندی یک مثال ساده را تعریف کرده و براساس آن سایر توابع را مقایسه خواهیم کرد.

• ()ROW_NUMBER
• ()RANK
• ()DENSE_RANK
• ()NTILE

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

CREATE TABLE ExamResult
(StudentName VARCHAR(70), 
 Subject     VARCHAR(20), 
 Marks       INT
);
INSERT INTO ExamResult
VALUES
('Lily','Maths', ۶۵);
INSERT INTO ExamResult
VALUES
('Lily', 'Science', ۸۰);
INSERT INTO ExamResult
VALUES
('Lily', 'english', ۷۰);
INSERT INTO ExamResult
VALUES
('Isabella', 'Maths', ۵۰);
INSERT INTO ExamResult
VALUES
('Isabella', 'Science', ۷۰);
INSERT INTO ExamResult
VALUES
('Isabella', 'english', ۹۰);
INSERT INTO ExamResult
VALUES
('Olivia','Maths', ۵۵);
INSERT INTO ExamResult
VALUES
('Olivia', 'Science', ۶۰);
INSERT INTO ExamResult
VALUES
('Olivia', 'english', ۸۹);

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

حالا بررسی می‌کنیم با هرکدام از توابع دستور Rank در SQL Server چه تغییری روی این جدول می‌توانیم ایجاد کنیم.

تابع ROW_Number() SQL RANK

این تابع به‌صورت شمارنده عمل می‌کند و به هر ردیف یک شماره ترتیبی منحصربه‌فرد اختصاص می‌دهد. به‌طور پیش‌فرض، ROW_NUMBER رتبه‌بندی را به‌صورت صعودی در نظر می‌گیرد، یعنی به بالاترین نمره رتبه ۱ می‌دهد و به‌ترتیب نمره پایین‌تر، رتبه بالاتر می‌رود. در مثال بالا، دستور جدید ما، براساس نمره و از کمترین به بیشترین، به‌شکل زیر خواهد شد:

SELECT Studentname, 
       Subject, 
       Marks, 
       ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
FROM ExamResult;

تابع RANK() SQL RANK

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

برای مثال، فرض کنید دستور اصلی‌مان را می‌خواهیم براساس نمرات به‌طور صعودی به نزولی رتبه‌بندی کنیم. برای این کار کافی‌است در دستور ROW_NUMBER از عبارت ORDER BY Marks DESC استفاده کنیم:

SELECT Studentname, 
       Subject, 
       Marks, 
       RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;

خروجی ما به‌صورت زیر است. همانطور که می‌بینید اگر دو دانش‌آموز نمره یکسان بگیرند، رنک یا همان رتبه آنها را یکسان تحویل می‌دهد.

حالا اگر بخواهیم براساس گروه‌های جداگانه دانش‌آموزان (PARTITION) رتبه‌بندی را انجام دهیم، از PARTITION BY به جای RANK استفاده می‌کنیم. به دستور زیر نگاه کنید:

SELECT StudentName, 
       Subject, 
       Marks, 
       RANK() OVER(PARTITION BY StudentName ORDER BY Marks DESC) AS Rank
FROM ExamResult
ORDER BY StudentName, Rank;

با این دستور RANK در SQL Server، رتبه‌بندی برای هر دانش‌آموز به‌صورت جداگانه انجام می‌شود. در تصویر زیر، می‌بینید ایزابلا در درس زبان انگلیسی بالاترین نمره و در درس ریاضی پایین‌ترین نمره را کسب کرده است. بنابراین، او در انگلیسی رتبه ۱ و در ریاضی رتبه ۳ را دریافت می کند.

تابع DENSE_RANK() SQL RANK

این تابع هم مانند دستور Rank در SQL Server براساس ترتیب مرتب‌سازی، رتبه اختصاص می‌دهد. البته برخلاف RANK، درصورت وجود مقادیر تکراری، هیچ جهشی در رتبه‌بندی ایجاد نمی‌شود و رتبه به‌صورت متوالی افزایش پیدا می‌کند. اجازه دهید با یک مثال، تفاوتش را با RANK مشخص کنیم.

SELECT Studentname, 
       Subject, 
       Marks, 
       DENSE_RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;

حالا در خروجی خواهید دید که پس از رتبه‌بندی نمرات تکراری، به‌صورت متوالی سراغ رنک بعدی می‌رود.

تابع NTILE() SQL RANK

این تابع از دستور Rank در SQL Server داده‌ها را به گروه‌های مساوی تقسیم می‌کند و به هر ردیف براساس گروهی که به آن تعلق دارد، یک رتبه اختصاص می‌دهد. فرض کنید دو گروه را «گروه ۱» و «گروه ۲» نامگذاری می‌کنیم. گروه دانش‌آموزان ما ۹ نفره است. قصد داریم گروه ۱ شامل ۵ رکورد و گروه ۲ شامل ۴ رکورد باشد. برای این کار دستور زیر را به‌کار می‌بریم.

SELECT *, 
       NTILE(2) OVER(
       ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;

خروجی به‌شکل زیر است:

مقایسه دستور Rank با سایر توابع رتبه‌بندی در یک نگاه

بهینه‌سازی و نکات عملکردی دستور RANK در SQL Server

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

۱. فراموش کردن دستور ORDER BY در گروه OVER()

دستور ORDER BY در پارتیشن ()OVER برای تعیین ترتیب رتبه‌بندی بسیار مهم است. بدون آن، ترتیب رتبه‌بندی غیرقابل پیش‌بینی می‌شود. همیشه برای اطمینان از رتبه‌بندی منسجم و موردانتظار، یک دستور ORDER BY را درون پارتیشن ()OVER مشخص کنید.

۲. سوءتفاهم در تفاوت بین RANK() و DENSE_RANK()

این دو تابع به نظر شبیه هستند، اما دستور Rank در SQL Server درصورت تکراری بودن، رتبه بعدی را رد می‌کند، درحالی‌که ()DENSE_RANK این کار را انجام نمی‌دهد. تابعی را انتخاب کنید که با معیار رتبه‌بندی شما مطابقت داشته باشد. اگر مطمئن نیستید از کدام تابع استفاده کنید و عملکرد برای شما مهم است، هر دو را روی نمونه‌ای از داده‌های خود امتحان کنید و ببینید کدام یک عملکرد بهتری دارد.

۳. استفاده از چندین تابع پنجره‌ای در یک کوئری

استفاده از چندین تابع پنجره‌ای یا دستورات PARTITION BY مختلف باعث پیچیدگی و افزایش زمان اجرای کوئری می‌شود. با تحلیل کردن برنامه اجرا (execution plan) شروع کنید. یعنی ببینید آیا می‌توانید بهینه‌سازی مانند استفاده مجدد از نتیجه یک تابع برای سایر توابع انجام دهید یا خیر.  

کلام پایانی؛ تسلط بر دستور Rank در SQL Server

دستور Rank در SQL Server گامی فراتر از مرتب سازی ساده داده ها است. این ابزار قدرتمند در جعبه ابزار SQL Server به شما در تجزیه‌وتحلیل بهتر داده‌ها کمک می‌کند. نکات کلیدی که در این مقاله بررسی کردیم:

• ()RANK به شما امکان می‌دهد به هر ردیف در مجموعه نتایج براساس معیارهای دلخواه‌تان رتبه اختصاص دهید.
• با استفاده از PARTITION BY می‌توانید رتبه‌بندی را در گروه‌های فرعی انجام دهید.
• ()DENSE_RANK برای اختصاص رتبه های متوالی بدون شکاف استفاده می شود.
• برای عملکرد بهتر، از ()RANK و ()DENSE_RANK بااحتیاط و درصورت نیاز استفاده کنید.