مثالهای عملی
مثال 1: شمارهگذاری کارکنان
در سادهترین کاربرد، ROW_NUMBER با OVER برای هر ردیف شمارهای بر اساس حقوق و شناسه میسازد. افزودن شناسه به ترتیب، نتیجه را در حقوقهای مساوی قطعی میکند.
SELECT EmployeeID, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC, EmployeeID) AS RowNo
FROM (VALUES (1, 70000), (2, 85000), (3, 85000)) E(EmployeeID, Salary);
| EmployeeID | Salary | RowNo |
|---|
| 2 | 85000 | 1 |
| 3 | 85000 | 2 |
| 1 | 70000 | 3 |
این الگو برای صفحهبندی و تولید شماره ردیف مناسب است؛ اما برای ترتیب ظاهری نتیجه باید در انتهای Query نیز ORDER BY نوشته شود.
مثال 2: جمع کل بدون حذف جزئیات
SUM با یک پنجره خالی کل فروش را کنار هر تراکنش نشان میدهد و برخلاف GROUP BY ردیفها را ادغام نمیکند.
SELECT OrderID, Amount,
SUM(Amount) OVER () AS GrandTotal
FROM (VALUES (101, 120), (102, 80), (103, 50)) S(OrderID, Amount);
| OrderID | Amount | GrandTotal |
|---|
| 101 | 120 | 250 |
| 102 | 80 | 250 |
| 103 | 50 | 250 |
پنجره خالی یعنی تمام ردیفهای خروجی جاری. فیلتر WHERE پیش از محاسبه پنجره اعمال میشود و دامنه GrandTotal را تغییر میدهد.
مثال 3: جمع هر مشتری
افزودن PARTITION BY باعث میشود مجموع برای هر مشتری جداگانه محاسبه شود و مبلغ هر سفارش نیز باقی بماند.
SELECT CustomerID, OrderID, Amount,
SUM(Amount) OVER (PARTITION BY CustomerID) AS CustomerTotal
FROM (VALUES (1, 10, 40), (1, 11, 60), (2, 12, 30)) O(CustomerID, OrderID, Amount);
| CustomerID | OrderID | Amount | CustomerTotal |
|---|
| 1 | 10 | 40 | 100 |
| 1 | 11 | 60 | 100 |
| 2 | 12 | 30 | 30 |
این روش برای محاسبه سهم هر ردیف از کل مشتری یا مقایسه سفارش با میانگین همان مشتری کاربرد مستقیم دارد.
مثال 4: فیلتر رتبه با CTE
توابع پنجرهای در مرحله WHERE همان SELECT قابل استفاده نیستند. ابتدا رتبه در CTE ساخته و سپس ردیفهای برتر فیلتر میشوند.
WITH Ranked AS (
SELECT ProductID, Price,
ROW_NUMBER() OVER (ORDER BY Price DESC, ProductID) AS rn
FROM (VALUES (1, 20), (2, 35), (3, 25)) P(ProductID, Price)
)
SELECT ProductID, Price
FROM Ranked
WHERE rn <= 2;
CTE ترتیب منطقی پردازش را روشن میکند. استفاده مستقیم از ROW_NUMBER در WHERE خطاست زیرا WHERE پیش از محاسبه تابع پنجرهای اجرا میشود.
مثال 5: جمع تجمعی صریح
ترکیب ORDER BY و ROWS جمع را از ابتدای پارتیشن تا ردیف جاری محاسبه میکند و رفتار در مقادیر تکراری را شفاف نگه میدارد.
SELECT TxnID, Amount,
SUM(Amount) OVER (
ORDER BY TxnID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM (VALUES (1, 10), (2, 15), (3, 7)) T(TxnID, Amount);
| TxnID | Amount | RunningTotal |
|---|
| 1 | 10 | 10 |
| 2 | 15 | 25 |
| 3 | 7 | 32 |
برای سیستم مالی کلید ترتیب باید قطعی و نماینده توالی واقعی باشد. تاریخ تکراری بدون شناسه رفع تساوی معمولاً کافی نیست.
مثال 6: رفتار NULL در شمارش
COUNT(Value) فقط مقادیر غیر NULL را میشمارد، در حالی که COUNT(*) تعداد تمام ردیفهای پنجره را گزارش میکند.
SELECT ItemID, Value,
COUNT(Value) OVER () AS NonNullCount,
COUNT(*) OVER () AS RowCount
FROM (VALUES (1, 5), (2, NULL), (3, 9)) D(ItemID, Value);
| ItemID | Value | NonNullCount | RowCount |
|---|
| 1 | 5 | 2 | 3 |
| 2 | NULL | 2 | 3 |
| 3 | 9 | 2 | 3 |
انتخاب عبارت داخل تابع اهمیت دارد؛ OVER فقط دامنه را تعیین میکند و قواعد NULL متعلق به تابع COUNT است.
مثال 7: رفع تساوی در رتبهبندی
وقتی Score تکراری است، افزودن StudentID ترتیب را قطعی میکند تا اجرای دوباره شمارههای متفاوت نسازد.
SELECT StudentID, Score,
ROW_NUMBER() OVER (ORDER BY Score DESC, StudentID ASC) AS StableRank
FROM (VALUES (7, 90), (3, 90), (5, 80)) S(StudentID, Score);
| StudentID | Score | StableRank |
|---|
| 3 | 90 | 1 |
| 7 | 90 | 2 |
| 5 | 80 | 3 |
اگر منطق به رتبه مشترک نیاز دارد از RANK یا DENSE_RANK استفاده کنید؛ افزودن کلید یکتا برای ROW_NUMBER صرفاً قطعیت را تضمین میکند.
مثال 8: گزارش سهم شعبه
مبلغ هر شعبه و کل سازمان در همان نتیجه محاسبه میشود تا درصد مشارکت بدون Query جداگانه به دست آید.
SELECT BranchID, Sales,
SUM(Sales) OVER (PARTITION BY BranchID) AS BranchTotal,
SUM(Sales) OVER () AS CompanyTotal
FROM (VALUES (1, 100), (1, 50), (2, 150)) B(BranchID, Sales);
| BranchID | Sales | BranchTotal | CompanyTotal |
|---|
| 1 | 100 | 150 | 300 |
| 1 | 50 | 150 | 300 |
| 2 | 150 | 150 | 300 |
چند پنجره در یک SELECT مجاز است. اگر مشخصات ترتیب و پارتیشن نزدیک باشند، Optimizer گاهی عملیات مشترک ایجاد میکند.
مثال 9: اصلاح روش اشتباه
استفاده مستقیم از تابع پنجرهای در WHERE مجاز نیست. نسخه صحیح ابتدا خروجی را در CTE نامگذاری میکند.
-- روش نادرست: WHERE ROW_NUMBER() OVER (...) = 1
WITH X AS (
SELECT ProductID, Price,
ROW_NUMBER() OVER (ORDER BY Price DESC, ProductID) AS rn
FROM (VALUES (1, 10), (2, 30)) P(ProductID, Price)
)
SELECT ProductID, Price FROM X WHERE rn = 1;
این اصلاح هم قابل اجراست و هم به Optimizer امکان میدهد طرح مناسبی بسازد. Alias تابع پنجرهای نیز در WHERE همان سطح قابل ارجاع نیست.
مثال 10: ایندکس مناسب پنجره
جدول موقت یک ایندکس مطابق CustomerID و OrderDate میگیرد تا محاسبه تجمعی ورودی مرتب و پوششدادهشده داشته باشد.
CREATE TABLE #Sales(CustomerID int, OrderDate date, OrderID int, Amount int);
INSERT #Sales VALUES (1,'2026-01-01',1,10),(1,'2026-01-02',2,20),(2,'2026-01-01',3,15);
CREATE INDEX IX_Sales_Window ON #Sales(CustomerID, OrderDate, OrderID) INCLUDE(Amount);
SELECT OrderID, SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM #Sales;
DROP TABLE #Sales;
| OrderID | RunningTotal |
|---|
| 1 | 10 |
| 2 | 30 |
| 3 | 15 |
ایندکس را صرفاً به دلیل یک Query اضافه نکنید؛ هزینه نوشتن و فضای ذخیرهسازی را با حذف Sort و کاهش خواندن مقایسه کنید.