تابع ROW_NUMBER در SQL Server

تابع ROW_NUMBER در SQL Server: راهنمای جامع Window Functions برای ردیف‌شماری پیشرفته

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

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

ساختار کلی استفاده از تابع `ROW_NUMBER()` به این صورت است:

ROW_NUMBER() OVER ([PARTITION BY column_name, ... ] ORDER BY column_name [ASC|DESC], ...)

در این ساختار:

• `PARTITION BY`: این بند اختیاری است و مجموعه نتایج را به پارتیشن‌های منطقی تقسیم می‌کند. ردیف‌شماری برای هر پارتیشن به صورت جداگانه و از 1 آغاز می‌شود.
• `ORDER BY`: این بند اجباری است و ترتیب سطرها را در هر پارتیشن (یا کل مجموعه نتایج در صورت عدم وجود `PARTITION BY`) برای اختصاص اعداد ردیف مشخص می‌کند.

برای درک بهتر، مثالی ساده از نحوه استفاده از `ROW_NUMBER()` بدون `PARTITION BY` را بررسی می‌کنیم. در این حالت، تابع `ROW_NUMBER()` به تمام سطرهای بازگردانده شده توسط کوئری، بر اساس ترتیب مشخص شده در `ORDER BY`، یک عدد ردیف متوالی اختصاص می‌دهد.

SELECT
ProductName,
Price,
ROW_NUMBER() OVER (ORDER BY Price ASC) AS RowNum
FROM
Products;

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

اکنون، فرض کنید می‌خواهیم در یک جدول فروش، برای هر منطقه (Region) و بر اساس میزان فروش (SalesAmount)، رتبه‌بندی کنیم. در اینجا، `PARTITION BY` بر اساس `Region` استفاده می‌شود تا ردیف‌شماری برای هر منطقه به صورت جداگانه انجام شود و `ORDER BY` نیز بر اساس `SalesAmount` برای تعیین ترتیب در هر منطقه خواهد بود.

SELECT
Region,
SalesPerson,
SalesAmount,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionRank
FROM
SalesData;

این کوئری، برای هر `Region`، به فروشندگان بر اساس `SalesAmount` (از بیشترین به کمترین) ردیف اختصاص می‌دهد. `RegionRank` برای هر منطقه از 1 شروع می‌شود که به ما امکان می‌دهد پرفروش‌ترین فروشنده در هر منطقه را به راحتی شناسایی کنیم.

یکی از کاربردهای رایج `ROW_NUMBER()`، حذف ردیف‌های تکراری از یک جدول است. اگر چندین ردیف کاملاً مشابه یا با یک مجموعه خاص از ستون‌ها تکراری باشند، می‌توانیم با استفاده از `ROW_NUMBER()` یکی از آن‌ها را نگه داشته و بقیه را حذف کنیم. برای این کار، یک ردیف‌شماری بر اساس ستون‌های مورد نظر انجام می‌دهیم و سپس سطرهایی را که `RowNum` آن‌ها بزرگتر از 1 است (یعنی تکراری هستند) حذف می‌کنیم.

WITH CTE_DuplicateRemover AS (
SELECT
Column1,
Column2,
Column3,
ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY Column3) AS RowNum
FROM
YourTable
)
DELETE FROM CTE_DuplicateRemover
WHERE RowNum > 1;

در این مثال، `CTE_DuplicateRemover` برای شناسایی ردیف‌های تکراری بر اساس `Column1` و `Column2` استفاده می‌شود. `ORDER BY Column3` تضمین می‌کند که یک ترتیب ثابت برای انتخاب ردیف اصلی وجود داشته باشد. سپس، هر ردیفی که `RowNum` آن بزرگتر از 1 باشد (یعنی یک کپی است) حذف می‌شود و تنها یک نمونه از هر ترکیب `Column1, Column2` باقی می‌ماند.

همچنین، تابع `ROW_NUMBER()` برای یافتن N سطر برتر یا پایین‌تر در هر گروه نیز بسیار مفید است. به عنوان مثال، اگر بخواهید دو محصول گران‌قیمت را از هر دسته محصول (Category) پیدا کنید، می‌توانید از این تابع به همراه یک CTE (Common Table Expression) یا Subquery استفاده کنید.

WITH TopProducts AS (
SELECT
Category,
ProductName,
Price,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Price DESC) AS CategoryRowNum
FROM
Products
)
SELECT
Category,
ProductName,
Price
FROM
TopProducts
WHERE
CategoryRowNum <= 2;

این کوئری، ابتدا برای هر `Category`، محصولات را بر اساس `Price` نزولی مرتب می‌کند و یک `CategoryRowNum` به آن‌ها اختصاص می‌دهد. سپس، تنها آن دسته از محصولاتی را انتخاب می‌کند که `CategoryRowNum` آن‌ها 1 یا 2 باشد، یعنی دو محصول گران‌قیمت هر دسته را نمایش می‌دهد.

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