رتبه بندی داده ها با توابع پنجره SQL Server RANK DENSE RANK NTILE

توابع پنجره SQL Server: راهنمای کامل RANK, DENSE_RANK و NTILE برای رتبه‌بندی داده‌ها

در مقاله قبل خواندیم که توابع پنجره (Window Functions) در SQL Server ابزارهای قدرتمندی هستند که به شما امکان می‌دهند محاسبات تحلیلی را روی مجموعه‌ای از ردیف‌های مرتبط با ردیف فعلی انجام دهید. برخلاف توابع تجمیعی که کل مجموعه نتایج را در یک ردیف خلاصه می‌کنند، توابع پنجره برای هر ردیف ورودی، یک ردیف خروجی تولید می‌کنند. این توابع در سناریوهایی مانند رتبه‌بندی داده‌ها، محاسبه مجموع متحرک، یا درصدبندی اهمیت پیدا می‌کنند.

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

تابع RANK() در SQL Server

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

ساختار کلی استفاده از تابع RANK به این صورت است:

RANK() OVER ( [partition_by_clause] order_by_clause )

عبارت `partition_by_clause` اختیاری است و برای تقسیم مجموعه نتایج به گروه‌های کوچکتر استفاده می‌شود. `order_by_clause` نیز برای تعیین ترتیب رتبه‌بندی ضروری است.

برای درک بهتر، ابتدا یک جدول نمونه ایجاد کرده و تعدادی داده به آن اضافه می‌کنیم:

CREATE TABLE SalesData (
    Id INT IDENTITY(1,1),
    ProductName VARCHAR(50),
    Sales INT
);

INSERT INTO SalesData (ProductName, Sales) VALUES
('ProductA', 100),
('ProductB', 150),
('ProductC', 100),
('ProductD', 200),
('ProductE', 150),
('ProductF', 200),
('ProductG', 50);

حال، بیایید از تابع RANK() برای رتبه‌بندی محصولات بر اساس فروش (Sales) استفاده کنیم:

SELECT
    Id,
    ProductName,
    Sales,
    RANK() OVER (ORDER BY Sales DESC) AS RankNo
FROM SalesData;

خروجی این کوئری رتبه‌بندی را به شکل زیر نشان می‌دهد: ردیف‌هایی با فروش ۲۰۰ (ProductD, ProductF) رتبه ۱ را می‌گیرند. ردیف‌های با فروش ۱۵۰ (ProductB, ProductE) رتبه ۳ را دریافت می‌کنند (زیرا رتبه‌های ۲ پرش شده‌اند). ردیف‌های با فروش ۱۰۰ (ProductA, ProductC) رتبه ۵ را خواهند داشت و در نهایت ProductG با فروش ۵۰ رتبه ۷ را کسب می‌کند.

تابع DENSE_RANK() در SQL Server

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

ساختار کلی استفاده از تابع DENSE_RANK به این صورت است:

DENSE_RANK() OVER ( [partition_by_clause] order_by_clause )

بیایید با همان داده‌های SalesData، از تابع DENSE_RANK() استفاده کنیم:

SELECT
    Id,
    ProductName,
    Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRankNo
FROM SalesData;

در خروجی این کوئری، ProductD و ProductF با فروش ۲۰۰، رتبه ۱ را می‌گیرند. ProductB و ProductE با فروش ۱۵۰، رتبه ۲ را دریافت می‌کنند (برخلاف RANK که رتبه ۳ می‌داد). ProductA و ProductC با فروش ۱۰۰، رتبه ۳ را خواهند داشت و ProductG با فروش ۵۰، رتبه ۴ را کسب می‌کند. تفاوت اصلی در عدم وجود پرش در رتبه‌ها است.

تابع NTILE() در SQL Server

تابع NTILE() برای تقسیم مجموعه ردیف‌ها به گروه‌های مساوی (تقسیم به N کاشی) استفاده می‌شود. این تابع هر ردیف را به یک گروه اختصاص می‌دهد و تعداد گروه‌ها توسط یک عبارت عددی صحیح (integer_expression) مشخص می‌شود. اگر تعداد ردیف‌ها بر تعداد گروه‌ها بخش‌پذیر نباشد، گروه‌ها به صورت نامساوی تقسیم می‌شوند به طوری که گروه‌های اول تعداد اعضای بیشتری خواهند داشت.

ساختار کلی استفاده از تابع NTILE به این صورت است:

NTILE (integer_expression) OVER ( [partition_by_clause] order_by_clause )

`integer_expression` تعداد گروه‌هایی است که می‌خواهید مجموعه داده را به آن‌ها تقسیم کنید.

با استفاده از همان جدول SalesData، بیایید محصولات را به ۴ گروه مساوی تقسیم کنیم:

SELECT
    Id,
    ProductName,
    Sales,
    NTILE(4) OVER (ORDER BY Sales DESC) AS NtileNo
FROM SalesData;

در این مثال، چون ۷ ردیف داریم و می‌خواهیم به ۴ گروه تقسیم کنیم (۷/۴ = ۱ باقیمانده ۳)، سه گروه اول ۲ عضو و گروه آخر ۱ عضو خواهند داشت. ProductD و ProductF (فروش ۲۰۰) در Ntile 1 قرار می‌گیرند. ProductB و ProductE (فروش ۱۵۰) در Ntile 2. ProductA و ProductC (فروش ۱۰۰) در Ntile 3. و ProductG (فروش ۵۰) در Ntile 4 قرار می‌گیرد.

مقایسه توابع RANK, DENSE_RANK و NTILE

برای درک بهتر تفاوت‌های این سه تابع، می‌توانیم هر سه را در یک کوئری با هم به کار ببریم و نتایج را مقایسه کنیم. این مقایسه به شما نشان می‌دهد که هر تابع چگونه به ردیف‌های با مقادیر یکسان و همچنین نحوه تقسیم‌بندی کلی رتبه‌ها و گروه‌ها واکنش نشان می‌دهد.

SELECT
    Id,
    ProductName,
    Sales,
    RANK() OVER (ORDER BY Sales DESC) AS RankNo,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRankNo,
    NTILE(4) OVER (ORDER BY Sales DESC) AS NtileNo
FROM SalesData;

با اجرای این کوئری، مشاهده خواهید کرد که برای ProductD و ProductF (فروش ۲۰۰):
* `RankNo` هر دو ۱ خواهد بود و رتبه بعدی ۳ می‌شود.
* `DenseRankNo` هر دو ۱ خواهد بود و رتبه بعدی ۲ می‌شود.
* `NtileNo` هر دو ۱ خواهد بود (چون در اولین گروه ۴ تایی قرار می‌گیرند).

این مقایسه تصویری واضح از کاربرد و رفتار هر یک از این توابع پنجره حیاتی در SQL Server را ارائه می‌دهد و به شما کمک می‌کند تا تابع مناسب را برای نیازهای تحلیلی و رتبه‌بندی داده‌های خود انتخاب کنید.