مثالهای عملی
مثال 1: رتبه قطعی
Salary ترتیب اصلی و EmployeeID کلید رفع تساوی است.
SELECT EmployeeID,Salary,ROW_NUMBER() OVER(ORDER BY Salary DESC,EmployeeID) AS rn
FROM (VALUES(1,90),(2,90),(3,70)) E(EmployeeID,Salary);
| EmployeeID | Salary | rn |
|---|
| 1 | 90 | 1 |
| 2 | 90 | 2 |
| 3 | 70 | 3 |
بدون EmployeeID، دو ردیف حقوق 90 ترتیب تضمینشدهای ندارند و شماره آنها میتواند با تغییر Plan عوض شود.
مثال 2: جمع تجمعی بر اساس تاریخ
ترتیب تاریخ و شناسه تراکنش توالی قابل اعتماد برای Running Total میسازد.
SELECT TxnID,Amount,SUM(Amount) OVER(ORDER BY TxnDate,TxnID ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM (VALUES(1,CONVERT(date,'2026-01-01'),10),(2,'2026-01-02',20)) T(TxnID,TxnDate,Amount);
| TxnID | Amount | RunningTotal |
|---|
| 1 | 10 | 10 |
| 2 | 20 | 30 |
تاریخ بهتنهایی در سامانه پرتراکنش معمولاً یکتا نیست. شناسه یا زمان دقیقتر را برای قطعیت اضافه کنید.
مثال 3: مقایسه با مقدار قبلی
LAG با ORDER BY مقدار رویداد قبلی را بدون Self Join میخواند.
SELECT DayNo,Amount,LAG(Amount) OVER(ORDER BY DayNo) AS PrevAmount
FROM (VALUES(1,10),(2,15),(3,12)) D(DayNo,Amount);
| DayNo | Amount | PrevAmount |
|---|
| 1 | 10 | NULL |
| 2 | 15 | 10 |
| 3 | 12 | 15 |
ردیف اول سابقه ندارد و NULL طبیعی است. با پارامتر سوم LAG میتوان Default صریح تعیین کرد.
مثال 4: انتخاب دو رکورد تازه
CTE رتبه را بر اساس تاریخ نزولی میسازد و سپس دو ردیف اول را انتخاب میکند.
WITH R AS(SELECT TicketID,CreatedAt,ROW_NUMBER() OVER(ORDER BY CreatedAt DESC,TicketID DESC) rn FROM (VALUES(1,CONVERT(datetime2,'2026-01-01')),(2,'2026-01-03'),(3,'2026-01-02')) T(TicketID,CreatedAt))
SELECT TicketID,CreatedAt FROM R WHERE rn<=2;
| TicketID | CreatedAt |
|---|
| 2 | 2026-01-03 |
| 3 | 2026-01-02 |
فیلتر رتبه در لایه بیرونی انجام میشود. برای Top ساده TOP مناسبتر است، اما الگو برای Top-N هر پارتیشن توسعهپذیر است.
مثال 5: مدیریت تساوی امتیاز
RANK برای امتیاز مساوی رتبه یکسان و فاصله بعدی ایجاد میکند.
SELECT StudentID,Score,RANK() OVER(ORDER BY Score DESC) AS RankNo
FROM (VALUES(1,95),(2,95),(3,80)) S(StudentID,Score);
| StudentID | Score | RankNo |
|---|
| 1 | 95 | 1 |
| 2 | 95 | 1 |
| 3 | 80 | 3 |
اگر فاصله رتبه مطلوب نیست، DENSE_RANK نتیجه 1،1،2 میدهد. انتخاب تابع بخشی از قانون کسبوکار است.
مثال 6: کنترل جایگاه NULL
CASE مقادیر NULL را پس از مقادیر واقعی قرار میدهد.
SELECT ItemID,DueDate,ROW_NUMBER() OVER(ORDER BY CASE WHEN DueDate IS NULL THEN 1 ELSE 0 END,DueDate,ItemID) AS rn
FROM (VALUES(1,CONVERT(date,'2026-01-02')),(2,NULL),(3,'2026-01-01')) D(ItemID,DueDate);
| ItemID | DueDate | rn |
|---|
| 3 | 2026-01-01 | 1 |
| 1 | 2026-01-02 | 2 |
| 2 | NULL | 3 |
SQL Server گزینه NULLS LAST ندارد؛ CASE راه صریح است، ولی ممکن است Sort ایجاد کند و باید اثر آن در Plan سنجیده شود.
مثال 7: ترتیب نزولی تغییرات
LEAD مقدار روز بعد را بر اساس ترتیب تعیینشده میخواند.
SELECT DayNo,Amount,LEAD(Amount) OVER(ORDER BY DayNo DESC) AS NextInDescendingOrder
FROM (VALUES(1,10),(2,20),(3,30)) D(DayNo,Amount);
| DayNo | Amount | NextInDescendingOrder |
|---|
| 3 | 30 | 20 |
| 2 | 20 | 10 |
| 1 | 10 | NULL |
معنای قبل و بعد تابعی از جهت ORDER BY است. نام Alias را طوری انتخاب کنید که جهت تحلیل برای خواننده روشن باشد.
مثال 8: تحلیل روند فروش
تفاضل مبلغ با LAG رشد یا افت هر دوره را نشان میدهد.
SELECT MonthNo,Amount,Amount-LAG(Amount) OVER(ORDER BY MonthNo) AS ChangeAmount
FROM (VALUES(1,100),(2,130),(3,120)) S(MonthNo,Amount);
| MonthNo | Amount | ChangeAmount |
|---|
| 1 | 100 | NULL |
| 2 | 130 | 30 |
| 3 | 120 | -10 |
برای درصد رشد، مخرج را با NULLIF محافظت کنید و نوع اعشاری مناسب به کار ببرید.
مثال 9: تفاوت ترتیب پنجره و خروجی
شماره بر اساس Amount ساخته میشود ولی نمایش نهایی بر اساس ItemID است.
SELECT ItemID,Amount,ROW_NUMBER() OVER(ORDER BY Amount DESC,ItemID) AS ValueRank
FROM (VALUES(2,50),(1,70),(3,40)) D(ItemID,Amount)
ORDER BY ItemID;
| ItemID | Amount | ValueRank |
|---|
| 1 | 70 | 1 |
| 2 | 50 | 2 |
| 3 | 40 | 3 |
دو ORDER BY مسئولیت جدا دارند. حذف مرتبسازی نهایی اجازه میدهد SQL Server خروجی را با هر ترتیب فیزیکی برگرداند.
مثال 10: کاهش Sort با ایندکس
ایندکس ترتیب موردنیاز پارتیشن و پنجره را فراهم میکند.
CREATE TABLE #O(CustomerID int,OrderDate date,OrderID int,Amount int);
INSERT #O VALUES(1,'2026-01-01',1,10),(1,'2026-01-02',2,20);
CREATE INDEX IX_O_POC ON #O(CustomerID,OrderDate,OrderID) INCLUDE(Amount);
SELECT OrderID,SUM(Amount) OVER(PARTITION BY CustomerID ORDER BY OrderDate,OrderID ROWS UNBOUNDED PRECEDING) AS rt FROM #O;
DROP TABLE #O;
وجود ایندکس تضمین حذف Sort نیست؛ فیلترها، Joinها و تخمینها میتوانند شکل Plan را تغییر دهند. Actual Plan مرجع تصمیم است.
سؤالات متداول
برای شروع یادگیری عبارت ORDER BY در OVER چه پیشنیازی لازم است؟
آشنایی با SELECT، مرتبسازی، توابع تجمیعی و ترتیب منطقی اجرای Query کافی است. سپس عبارت ORDER BY در OVER را روی چند ردیف کوچک با مقدار تکراری و NULL آزمایش کنید تا مرز پنجره فقط حفظ نشود، بلکه مشاهده شود.
چگونه صحت نتیجه عبارت ORDER BY در OVER را با یک تست کوچک بررسی کنیم؟
یک مجموعه داده VALUES با سه تا پنج ردیف بسازید، نتیجه مورد انتظار را دستی حساب کنید و ستون حاصل از عبارت ORDER BY در OVER را مقایسه نمایید. تست باید حالت عادی، تساوی، NULL و مرز پارتیشن را جداگانه پوشش دهد.
کاربرد تجاری عبارت ORDER BY در OVER در گزارش مدیریتی چیست؟
عبارت ORDER BY در OVER امکان ساخت KPI، رتبهبندی، روند، سهم از کل و مانده تجمعی را بدون حذف جزئیات فراهم میکند. این ویژگی Dataset گزارش را سادهتر میکند و تعداد رفتوبرگشتهای لایه برنامه به دیتابیس را کاهش میدهد.
آیا عبارت ORDER BY در OVER برای سامانههای مالی و فروش مناسب است؟
بله، به شرط آنکه ترتیب قطعی، نوع داده دقیق و مرز محاسبه مستند باشد. در پروژه مالی لازم است خروجی با داده مرجع تطبیق داده شود و برای حجم واقعی، Query Store و Actual Execution Plan نیز بررسی شوند.
تفاوت عبارت ORDER BY در OVER با GROUP BY یا روش تجمیع سنتی چیست؟
GROUP BY ردیفها را در سطح گروه خلاصه میکند، ولی عبارت ORDER BY در OVER در چارچوب تابع پنجرهای معمولاً جزئیات را حفظ میکند. انتخاب درست به شکل خروجی نیازمند وابسته است و هیچیک جایگزین مطلق دیگری نیست.
برای پیادهسازی حرفهای عبارت ORDER BY در OVER چه خدماتی مفید است؟
بازبینی مدل داده، طراحی شاخصها، تهیه تست رگرسیون، تحلیل Execution Plan و مشاوره SQL Server بیشترین ارزش را دارند. در پروژه حساس بهتر است منطق عبارت ORDER BY در OVER همراه تعریف KPI و نمونه مورد انتظار تحویل شود.
رایجترین خطای توسعهدهندگان هنگام استفاده از عبارت ORDER BY در OVER چیست؟
خطای پرتکرار، فرضکردن ترتیب یا قاب پیشفرض و بیتوجهی به مقادیر مساوی است. نسخه صحیح باید کلید رفع تساوی، رفتار NULL و تفاوت ترتیب پنجره با ترتیب نهایی را صریح کند.
چگونه کارایی Query دارای عبارت ORDER BY در OVER را بهبود دهیم؟
ابتدا تعداد ردیف ورودی را با فیلتر SARGable کاهش دهید، سپس ایندکس هماهنگ با پارتیشن و ترتیب را ارزیابی کنید. Sort، Window Spool، Memory Grant و Spill به tempdb در طرح واقعی باید اندازهگیری شوند.
بهترین روش نگهداری Queryهای مبتنی بر عبارت ORDER BY در OVER چیست؟
نامگذاری روشن Aliasها، قاب صریح، کامنت درباره سیاست تساوی، تست داده مرزی و ثبت Baseline کارایی بهترین روش است. Query باید در Code Review همراه Execution Plan نمونه و انتظار کسبوکار بررسی شود.
عبارت ORDER BY در OVER در کدام نسخههای SQL Server قابل استفاده است؟
قابلیتهای اصلی Window Functions از SQL Server 2012 به بعد گسترده و پایدارند، هرچند برخی توابع یا بهبودهای Optimizer میان نسخهها تفاوت دارند. Compatibility Level و مستندات نسخه مقصد را پیش از استقرار کنترل کنید.