DENSE_RANK در SQL Server یافتن Nامین رکورد برتر

DENSE_RANK در SQL Server: راهنمای یافتن Nاُمین رکورد برتر

تابع پنجره‌ای (Window Function) DENSE_RANK در SQL Server یک تابع پرکاربرد است که برای اختصاص دادن یک رتبه منحصر به فرد به هر سطر (Row) در یک پارتیشن از مجموعه نتایج (Result Set)، بر اساس یک یا چند ستون مشخص، به کار می‌رود. این تابع برخلاف تابع RANK، در صورت وجود رکوردهای مساوی (Ties)، شکافی (Gap) در رتبه‌بندی ایجاد نمی‌کند. به عنوان مثال، اگر سه سطر با یک مقدار یکسان داشته باشیم که همگی رتبه ۱ را دریافت کنند، سطر بعدی رتبه ۲ را دریافت خواهد کرد، نه رتبه ۴. این ویژگی DENSE_RANK را برای سناریوهایی که نیاز به رتبه‌بندی پیوسته بدون پرش دارید، ایده‌آل می‌کند.

شاید بتوان آن را با فرمول زیر نمایش داد:

DENSE_RANK() OVER (PARTITION BY Expression1, Expression2,... ORDER BY Expression1 [ASC|DESC], Expression2 [ASC|DESC],...)

در این فرمول، بخش PARTITION BY برای تقسیم مجموعه نتایج به گروه‌ها یا پارتیشن‌ها استفاده می‌شود، که رتبه‌بندی به صورت جداگانه در هر پارتیشن انجام می‌گیرد. اگر از PARTITION BY استفاده نکنید، کل مجموعه نتایج به عنوان یک پارتیشن واحد در نظر گرفته می‌شود. بخش ORDER BY نیز برای تعیین ترتیب رتبه‌بندی سطرها در هر پارتیشن استفاده می‌شود.

برای درک بهتر، بیایید یک مجموعه داده نمونه (Sample Data Set) ایجاد کنیم که شامل اطلاعات فروش برای محصولات مختلف در دسته‌های گوناگون باشد. این مثال به ما کمک می‌کند تا نحوه عملکرد DENSE_RANK را در عمل مشاهده کنیم.

CREATE TABLE ProductSales (
    ProductID INT,
    ProductName VARCHAR(50),
    Category VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO ProductSales (ProductID, ProductName, Category, SalesAmount) VALUES
(1, 'لپ تاپ X', 'الکترونیک', 1200.00),
(2, 'موس بی سیم', 'الکترونیک', 50.00),
(3, 'کیبورد مکانیکی', 'الکترونیک', 150.00),
(4, 'گوشی هوشمند A', 'الکترونیک', 800.00),
(5, 'گوشی هوشمند B', 'الکترونیک', 800.00),
(6, 'کتاب آشپزی', 'کتاب', 30.00),
(7, 'رمان علمی تخیلی', 'کتاب', 25.00),
(8, 'رمان فانتزی', 'کتاب', 25.00),
(9, 'قهوه ساز', 'لوازم خانگی', 200.00),
(10, 'توستر', 'لوازم خانگی', 80.00),
(11, 'پلی استیشن 5', 'الکترونیک', 800.00),
(12, 'یخچال ساید بای ساید', 'لوازم خانگی', 1500.00);

حالا، بیایید از تابع DENSE_RANK برای یافتن برترین محصولات بر اساس میزان فروش در هر دسته (Category) استفاده کنیم. ما می‌خواهیم رتبه‌بندی محصولات را به ازای هر Category و بر اساس SalesAmount (میزان فروش) به صورت نزولی (DESC) انجام دهیم.

SELECT
    ProductID,
    ProductName,
    Category,
    SalesAmount,
    DENSE_RANK() OVER (PARTITION BY Category ORDER BY SalesAmount DESC) AS RankDenseRank
FROM
    ProductSales;

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

با استفاده از نتایج بالا، می‌توانیم به راحتی Nاُمین رکورد بالاترین را برای هر دسته پیدا کنیم. به عنوان مثال، اگر می‌خواهیم دومین محصول برتر (با توجه به میزان فروش) در هر دسته را پیدا کنیم، می‌توانیم از یک CTE (Common Table Expression) یا یک Subquery به همراه DENSE_RANK استفاده کنیم. این یک سناریوی بسیار رایج در تحلیل داده‌ها است.

WITH RankedSales AS (
    SELECT
        ProductID,
        ProductName,
        Category,
        SalesAmount,
        DENSE_RANK() OVER (PARTITION BY Category ORDER BY SalesAmount DESC) AS RankDenseRank
    FROM
        ProductSales
)
SELECT
    ProductID,
    ProductName,
    Category,
    SalesAmount,
    RankDenseRank
FROM
    RankedSales
WHERE
    RankDenseRank = 2; -- برای یافتن دومین رکورد برتر در هر دسته

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