توابع پنجره ROW_NUMBER

توابع پنجره SQL: ROW_NUMBER – شرح جامع و کاربردها

مقدمه:

در دنیای تحلیل داده، توابع پنجره ابزاری قدرتمند برای محاسبات بر اساس گروه های متوالی از ردیف ها در یک مجموعه نتایج ارائه می دهند. در میان این توابع، ROW_NUMBER جایگاه ویژه ای دارد و به طور گسترده برای اختصاص اعداد ترتیبی منحصر به فرد به هر ردیف در یک پارتیشن بندی خاص استفاده می شود. این مقاله به بررسی جامع تابع ROW_NUMBER، نحو آن، کاربردها و مثال های عملی می پردازد.

نحو:

ROW_NUMBER() OVER (PARTITION BY expression_list ORDER BY expression)

• PARTITION BY expression_list: این بخش پارتیشن هایی را که ROW_NUMBER در آنها شمارش می کند، تعریف می کند. ردیف ها در هر پارتیشن به طور جداگانه شمارش می شوند.
• ORDER BY expression: این بخش ترتیبی را که ROW_NUMBER برای ردیف ها در هر پارتیشن اختصاص می دهد، تعیین می کند.

پارامترها:

• پارتیشن بندی: پارتیشن بندی به شما امکان می دهد تا ROW_NUMBER را برای گروه های جداگانه از ردیف ها اعمال کنید. به عنوان مثال، می توانید از یک ستون مانند CustomerID برای پارتیشن بندی داده ها بر اساس مشتری استفاده کنید.
• مرتب سازی: مرتب سازی ترتیبی را که اعداد ترتیبی به ردیف ها اختصاص داده می شود، تعیین می کند. به عنوان مثال، می توانید از یک ستون مانند OrderDate برای مرتب سازی ردیف ها بر اساس تاریخ سفارش استفاده کنید.

کاربردها:

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

مزایا:

• سادگی: استفاده از ROW_NUMBER بسیار آسان است و نیاز به دانش عمیق SQL ندارد.
• کارایی: ROW_NUMBER به طور کارآمد در پایگاه های داده مدرن اجرا می شود.
• انعطاف پذیری: ROW_NUMBER را می توان با توابع دیگر پنجره مانند LAG و LEAD برای انجام محاسبات پیچیده تر ترکیب کرد.

ملاحظات:

• عدم قطعی بودن: ROW_NUMBER یک تابع غیر قطعی است، به این معنی که ممکن است ردیف های با مقادیر یکسان، اعداد ترتیبی متفاوتی دریافت کنند.
• ترتیب دهی پیش فرض: اگر از ORDER BY در کوئری خود استفاده نکنید، ROW_NUMBER ردیف ها را به ترتیب دلخواه مرتب می کند.

مثال ها:

• یافتن اولین سفارش برای هر مشتری:

SELECT CustomerID, OrderID, MIN(OrderDate) AS FirstOrderDate,
   ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNumber
FROM Orders;

در این مثال، برای هر مشتری، ROW_NUMBER اعداد ترتیبی را به سفارشات بر اساس تاریخ سفارش اختصاص می دهد. اولین سفارش برای هر مشتری با RowNumber برابر با ۱ مشخص می شود.

• محاسبه میانگین فروش برای هر ماه:

SELECT OrderMonth, AVG(SalesAmount) AS AvgSales,
   ROW_NUMBER() OVER (PARTITION BY OrderMonth ORDER BY SalesAmount DESC) AS Rank
FROM Orders;

این کوئری میانگین مبلغ فروش را برای هر ماه محاسبه می کند و سپس ردیف ها را بر اساس SalesAmount به ترتیب نزولی مرتب می کند. ROW_NUMBER به هر ردیف در هر ماه، رتبه ای اختصاص می دهد.

• نمایش ۱۰ ردیف در هر صفحه:

SELECT CustomerID, Name, City,
   ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber
FROM Customers
WHERE RowNumber BETWEEN ۱۱ AND ۲۰;

این مثال ۱۰ ردیف را از ردیف ۱۱ تا ۲۰ از جدول Customers با RowNumber مربوطه نمایش می دهد.