دسترسی سریع

این مجموعه پنج جزء اصلی تعریف پنجره را از مرز کلی تا قاب فیزیکی و منطقی پوشش می‌دهد. برای رفتن مستقیم به مقاله تخصصی از پیوندهای زیر استفاده کنید.

• آموزش OVER در SQL Server
• آموزش PARTITION BY در SQL Server
• آموزش ORDER BY در SQL Server
• آموزش ROWS در SQL Server
• آموزش RANGE در SQL Server

Window Clauses چیست؟

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

پنجره با OVER آغاز می‌شود. PARTITION BY مرز گروه‌های مستقل، ORDER BY توالی منطقی و ROWS یا RANGE قاب مؤثر را مشخص می‌کنند. هر جزء اختیاری یا اجباری بودن خود را بر اساس تابع انتخاب‌شده دارد؛ برای نمونه ROW_NUMBER به ORDER BY نیاز دارد ولی SUM روی کل پنجره می‌تواند OVER خالی داشته باشد.

تفاوت اصلی با GROUP BY در Grain خروجی است. GROUP BY چند ردیف را به یک خلاصه تبدیل می‌کند؛ تابع پنجره‌ای معمولاً همان ردیف‌ها را نگه می‌دارد و یک ستون تحلیلی به هرکدام می‌افزاید. به همین دلیل این قابلیت برای Grid، API و گزارش‌هایی که جزئیات و KPI را هم‌زمان می‌خواهند بسیار مؤثر است.

مدل دقیق پنجره

برای هر تابع پنجره‌ای چهار تصمیم بگیرید: مجموعه ورودی پس از فیلتر چیست، پارتیشن کدام است، ترتیب چگونه قطعی می‌شود و قاب چه ردیف‌هایی را شامل می‌کند. اگر یکی از این تصمیم‌ها ضمنی بماند، نتیجه در حضور داده تکراری، NULL یا تغییر Plan ممکن است با انتظار کسب‌وکار فاصله بگیرد.

ترتیب منطقی اجرای Query توضیح می‌دهد چرا نمی‌توان خروجی ROW_NUMBER یا SUM پنجره‌ای را مستقیماً در WHERE همان SELECT فیلتر کرد. WHERE زودتر اجرا می‌شود؛ بنابراین ابتدا محاسبه را در CTE یا Derived Table انجام دهید و سپس در سطح بیرونی فیلتر کنید. این الگو خوانا، قابل تست و سازگار با Optimizer است.

ORDER BY داخل OVER مسئول توالی محاسبه است و ORDER BY نهایی مسئول ترتیب نمایش. یکی جای دیگری را نمی‌گیرد. حتی اگر Plan فعلی خروجی را مرتب نشان دهد، بدون ORDER BY نهایی هیچ تضمین قراردادی برای ترتیب نتیجه وجود ندارد.

مقایسه اجزای Window Clauses

عبارت OVER در SQL Server

OVER مرز بین محاسبات معمولی و محاسبات پنجره‌ای است. این عبارت مشخص می‌کند تابع تحلیلی یا تجمیعی روی کدام مجموعه از ردیف‌ها و با چه ترتیب منطقی اجرا شود، بی‌آنکه جزئیات داده مانند GROUP BY در یک ردیف خلاصه شوند.

آموزش کامل OVER با مثال‌های عملی و نکات کارایی

عبارت PARTITION BY در SQL Server

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

آموزش کامل PARTITION BY با مثال‌های عملی و نکات کارایی

عبارت ORDER BY در OVER در SQL Server

ORDER BY داخل OVER ترتیب منطقی ردیف‌ها را برای رتبه‌بندی، دسترسی به ردیف قبل و بعد و قاب‌های تجمعی تعیین می‌کند. این ترتیب تضمین نمی‌کند خروجی نهایی صفحه نیز مرتب باشد؛ برای آن باید ORDER BY جداگانه در انتهای SELECT نوشته شود.

آموزش کامل ORDER BY با مثال‌های عملی و نکات کارایی

قاب پنجره‌ای ROWS در SQL Server

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

آموزش کامل ROWS با مثال‌های عملی و نکات کارایی

قاب پنجره‌ای RANGE در SQL Server

RANGE قاب را بر اساس مقدار منطقی کلید ORDER BY و مفهوم ردیف‌های همتا تعریف می‌کند. ردیف‌هایی که مقدار مرتب‌سازی یکسان دارند در مرز CURRENT ROW با هم دیده می‌شوند؛ بنابراین نتیجه تجمعی می‌تواند برای چند ردیف تکراری یکسان جهش کند.

آموزش کامل RANGE با مثال‌های عملی و نکات کارایی

ROWS در برابر RANGE

ROWS موقعیت فیزیکی ردیف‌ها را نسبت به جاری می‌شمارد؛ برای نمونه 2 PRECEDING یعنی حداکثر دو ردیف قبل. RANGE مرز منطقی را از مقدار ORDER BY می‌سازد و CURRENT ROW تمام همتاهای دارای مقدار مساوی را نیز شامل می‌کند. این تفاوت در کلیدهای یکتا پنهان و در داده تکراری آشکار می‌شود.

در SQL Server قابلیت Offset عددی RANGE مانند برخی سامانه‌های دیگر کامل نیست. برای قاب‌های تعدادی از ROWS استفاده کنید و برای منطق همتاها RANGE را صریح بنویسید. اگر هدف بازه زمانی هفت‌روزه است، تعداد ردیف لزوماً نماینده هفت روز نیست و طراحی جداگانه‌ای لازم دارد.

قاب پیش‌فرض برخی توابع تجمیعی با ORDER BY می‌تواند رفتار RANGE تا CURRENT ROW داشته باشد. نوشتن قاب صریح هم قصد طراح را ثبت می‌کند و هم Code Review و تست رگرسیون را قابل اعتمادتر می‌سازد.

مثال‌های ترکیبی

مثال 1: شماره‌گذاری سفارش‌ها

برای هر مشتری، سفارش‌ها بر اساس تاریخ و شناسه شماره‌گذاری می‌شوند.

SELECT CustomerID,OrderID,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate,OrderID) AS rn
FROM (VALUES(1,10,CONVERT(date,'2026-01-01')),(1,11,'2026-01-02'),(2,12,'2026-01-01')) O(CustomerID,OrderID,OrderDate);

این مثال هم‌زمان نقش OVER، PARTITION BY و ORDER BY را نشان می‌دهد و یک کلید رفع تساوی دارد.

مثال 2: میانگین هر واحد

AVG پنجره‌ای میانگین واحد را بدون حذف ردیف کارکنان محاسبه می‌کند.

SELECT EmployeeID,DepartmentID,Salary,AVG(1.0*Salary) OVER(PARTITION BY DepartmentID) AS DeptAvg
FROM (VALUES(1,10,80),(2,10,100),(3,20,120)) E(EmployeeID,DepartmentID,Salary);

برخلاف GROUP BY، جزئیات هر EmployeeID در خروجی حفظ شده است.

مثال 3: مانده تجمعی ROWS

قاب ROWS اثر هر تراکنش را در توالی قطعی به مانده اضافه می‌کند.

SELECT TxnID,Amount,SUM(Amount) OVER(ORDER BY TxnDate,TxnID ROWS UNBOUNDED PRECEDING) AS Balance
FROM (VALUES(1,CONVERT(date,'2026-01-01'),10),(2,'2026-01-01',20),(3,'2026-01-02',5)) T(TxnID,TxnDate,Amount);

TxnID ترتیب دو تراکنش هم‌تاریخ را قطعی می‌کند و ROWS قاب فیزیکی را صریح می‌سازد.

مثال 4: مقایسه دوره با LAG

LAG مقدار قبلی را برای محاسبه تغییر دوره‌ای در دسترس می‌گذارد.

SELECT MonthNo,Sales,Sales-LAG(Sales) OVER(ORDER BY MonthNo) AS ChangeAmount
FROM (VALUES(1,100),(2,125),(3,110)) S(MonthNo,Sales);

ردیف نخست مقدار قبلی ندارد. این NULL باید در UI یا منطق KPI آگاهانه تفسیر شود.

مثال 5: میانگین متحرک

قاب دو ردیف قبل و جاری روند کوتاه‌مدت را هموار می‌کند.

SELECT DayNo,Value,AVG(1.0*Value) OVER(ORDER BY DayNo ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM (VALUES(1,10),(2,20),(3,30),(4,50)) D(DayNo,Value);

در مرز ابتدایی تعداد ردیف قاب کمتر از سه است؛ AVG فقط مقادیر موجود را حساب می‌کند.

مثال 6: تفاوت RANGE و ROWS

کلید تکراری نشان می‌دهد RANGE همتاها را یک‌جا و ROWS ردیف‌ها را جداگانه وارد می‌کند.

SELECT ID,K,Amount,SUM(Amount) OVER(ORDER BY K RANGE UNBOUNDED PRECEDING) AS ByRange,SUM(Amount) OVER(ORDER BY K,ID ROWS UNBOUNDED PRECEDING) AS ByRows
FROM (VALUES(1,1,10),(2,1,20),(3,2,5)) D(ID,K,Amount);

انتخاب بین RANGE و ROWS یک تصمیم معنایی است و باید با تعریف شاخص سازگار باشد.

کارایی و ابزارهای تحلیل

Window Query معمولاً به داده مرتب نیاز دارد. اگر ایندکس مناسب وجود نداشته باشد، Sort می‌تواند حافظه و CPU زیادی مصرف کند و در کمبود Memory Grant به tempdb Spill کند. Actual Execution Plan محل Sort، Window Aggregate، Sequence Project، Segment و Spool را نشان می‌دهد.

الگوی POC Index از کلیدهای Partitioning، سپس Ordering و در صورت نیاز ستون‌های Covering استفاده می‌کند. این الگو قانون ثابت نیست؛ فیلترهای بسیار انتخابی ممکن است پیش از آن قرار گیرند و بار نوشتن نیز باید سنجیده شود. هر تغییر را با SET STATISTICS IO, TIME ON، Query Store و حجم واقعی مقایسه کنید.

Extended Events برای رخدادهای مهم، Query Store برای تاریخچه Plan و زمان اجرا، Live Query Statistics برای مشاهده اجرای جاری و DMVs برای مصرف کلی مفیدند. ابزار فقط داده می‌دهد؛ نتیجه‌گیری باید صحت خروجی، توزیع داده، هم‌زمانی و اثر روی سایر Queryها را با هم در نظر بگیرد.

خطاهای رایج

• استفاده از رتبه یا تابع پنجره‌ای مستقیم در WHERE همان SELECT.
• فرض اینکه ORDER BY داخل OVER نتیجه نهایی را مرتب می‌کند.
• نبود کلید رفع تساوی برای ROW_NUMBER یا محاسبه ROWS.
• اتکا به قاب پیش‌فرض بدون بررسی تفاوت RANGE و ROWS.
• نادیده‌گرفتن NULL و پارتیشن‌های بسیار بزرگ.
• ساخت ایندکس عریض بدون سنجش هزینه درج و نگهداری.

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

بهترین روش پیاده‌سازی

• Grain دقیق خروجی و KPI را پیش از کدنویسی تعریف کنید.
• PARTITION BY را از مرز واقعی کسب‌وکار و نه صرفاً ستون‌های در دسترس انتخاب کنید.
• ORDER BY را با کلید یکتای رفع تساوی قطعی کنید.
• ROWS یا RANGE را آگاهانه و صریح بنویسید.
• منطق را با CTEهای کوتاه و Aliasهای توصیفی خوانا نگه دارید.
• تست صحت و Baseline کارایی را کنار کد نسخه‌بندی کنید.
• پیش از افزودن ایندکس، Plan و بار نوشتن کل سامانه را بررسی کنید.

سؤالات متداول

Window Clauses در SQL Server چه مسئله‌ای را حل می‌کنند؟

این عبارت‌ها رتبه، روند، سهم از کل، مقدار قبلی و محاسبه تجمعی را کنار جزئیات هر ردیف تولید می‌کنند. بنابراین بسیاری از Self Joinها و Queryهای چندمرحله‌ای ساده‌تر می‌شوند و Dataset نهایی برای گزارش غنی باقی می‌ماند.

برای شروع یادگیری عبارت‌های پنجره‌ای از کجا آغاز کنیم؟

از OVER و تفاوت آن با GROUP BY شروع کنید، سپس PARTITION BY و ORDER BY را روی داده کوچک بیاموزید و در پایان ROWS و RANGE را با کلید تکراری مقایسه کنید. هر مرحله باید خروجی دستی قابل پیش‌بینی داشته باشد.

مزیت تجاری Window Functions برای داشبورد چیست؟

داشبورد می‌تواند KPI گروه، رتبه و تغییر دوره‌ای را در یک Dataset دریافت کند. این کار پیچیدگی لایه برنامه را کم می‌کند، اما تعریف شاخص و کنترل Plan باید همچنان حرفه‌ای انجام شود.

آیا این قابلیت برای گزارش مالی مناسب است؟

بله؛ مانده، جمع روزانه، رتبه و مقایسه دوره‌ای کاربرد مستقیم دارند. در سیستم مالی ترتیب قطعی، decimal مناسب، مرز تراکنش و تست رگرسیون الزامی است و یک مشاور SQL Server می‌تواند طراحی و Plan را بازبینی کند.

تفاوت Window Functions و GROUP BY چیست؟

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

چه زمانی مشاوره بهینه‌سازی Query لازم می‌شود؟

وقتی Sort بزرگ، Spill به tempdb، مصرف CPU بالا، نوسان Plan یا زمان پاسخ نامناسب دیده می‌شود، تحلیل تخصصی ارزشمند است. همچنین پیش از استقرار گزارش مالی حساس، بازبینی صحت منطق و تست حجم واقعی ریسک را کاهش می‌دهد.

رایج‌ترین خطا در Window Clauses چیست؟

اتکا به ترتیب غیرقطعی یا قاب پیش‌فرض روی داده تکراری رایج‌ترین خطاست. Query در داده آزمایشی یکتا درست به نظر می‌رسد، اما در تولید رتبه یا جمع تجمعی متفاوتی می‌دهد. کلید تساوی و قاب را صریح کنید.

چگونه کارایی عبارت‌های پنجره‌ای را بسنجیم؟

Actual Execution Plan، SET STATISTICS IO و TIME، Query Store، Memory Grant و هشدار Spill را بررسی کنید. اندازه‌گیری باید روی حجم و توزیع نماینده انجام شود و Baseline پیش از تغییر ایندکس نگهداری گردد.

بهترین روش طراحی یک Window Query چیست؟

ابتدا Grain خروجی، مرز پارتیشن، ترتیب قطعی و قاب را روی کاغذ مشخص کنید. سپس نمونه کوچک، تست NULL و تساوی، Alias روشن و در پایان ارزیابی POC Index و Plan واقعی را انجام دهید.

سازگاری Window Clauses با نسخه‌های SQL Server چگونه است؟

پایه توابع تحلیلی مدرن از SQL Server 2012 گسترده شده است، ولی جزئیات برخی توابع و بهبودهای Optimizer به نسخه و Compatibility Level وابسته‌اند. قبل از انتشار، Query را دقیقاً روی نسخه مقصد تست کنید.

سؤالات مصاحبه

1. تفاوت نقش Window Clauses در تعریف پنجره با مرتب‌سازی نهایی نتیجه چیست؟
2. در چه شرایطی استفاده نادرست از Window Clauses پاسخ تحلیلی را غیرقطعی می‌کند؟
3. برای بررسی کارایی Query دارای Window Clauses کدام بخش‌های Execution Plan را کنترل می‌کنید؟
4. چگونه یک تست کوچک برای اثبات رفتار Window Clauses در حضور مقادیر تکراری می‌نویسید؟
5. چه ایندکسی می‌تواند Sort و خواندن داده در سناریوی Window Clauses را کاهش دهد؟

جمع‌بندی و مسیر مطالعه

Window Clauses ابزار اصلی تحلیل سطری در SQL Server هستند. OVER ظرف محاسبه را می‌سازد، PARTITION BY مرز گروه را تعیین می‌کند، ORDER BY توالی را مشخص می‌سازد و ROWS یا RANGE قاب را کنترل می‌کنند. کیفیت نتیجه به صراحت همین تصمیم‌ها وابسته است.

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

• آموزش OVER در SQL Server
• آموزش PARTITION BY در SQL Server
• آموزش ORDER BY در SQL Server
• آموزش ROWS در SQL Server
• آموزش RANGE در SQL Server