مثالهای عملی
مثال 1: جمع از ابتدا تا جاری
قاب صریح تمام ردیفهای قبلی و جاری را در ترتیب TxnID شامل میشود.
SELECT TxnID,Amount,SUM(Amount) OVER(ORDER BY TxnID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt
FROM (VALUES(1,10),(2,20),(3,5)) T(TxnID,Amount);
| TxnID | Amount | rt |
|---|
| 1 | 10 | 10 |
| 2 | 20 | 30 |
| 3 | 5 | 35 |
نوشتن BETWEEN خواناتر است؛ شکل کوتاه ROWS UNBOUNDED PRECEDING نیز همین انتهای CURRENT ROW را دارد.
مثال 2: میانگین متحرک سه ردیفی
دو ردیف قبل بههمراه جاری یک پنجره حداکثر سهردیفی میسازد.
SELECT DayNo,Amount,AVG(1.0*Amount) OVER(ORDER BY DayNo ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM (VALUES(1,10),(2,20),(3,30),(4,40)) D(DayNo,Amount);
| DayNo | Amount | MovingAvg |
|---|
| 1 | 10 | 10.0 |
| 2 | 20 | 15.0 |
| 3 | 30 | 20.0 |
| 4 | 40 | 30.0 |
در ابتدای پارتیشن که دو ردیف قبل وجود ندارد، SQL Server فقط ردیفهای موجود را در میانگین لحاظ میکند.
مثال 3: جمع جاری و ردیف بعد
قاب از CURRENT ROW تا یک FOLLOWING برای نگاه کوتاه رو به جلو استفاده میشود.
SELECT StepNo,Cost,SUM(Cost) OVER(ORDER BY StepNo ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS CurrentAndNext
FROM (VALUES(1,10),(2,20),(3,30)) S(StepNo,Cost);
| StepNo | Cost | CurrentAndNext |
|---|
| 1 | 10 | 30 |
| 2 | 20 | 50 |
| 3 | 30 | 30 |
ردیف آخر همسایه بعدی ندارد و فقط خودش محاسبه میشود. این رفتار خطا نیست و باید در تفسیر گزارش لحاظ شود.
مثال 4: فیلتر میانگین متحرک
ابتدا مقدار قاب در CTE محاسبه و سپس ردیفهای با میانگین حداقل 20 انتخاب میشوند.
WITH M AS(SELECT DayNo,AVG(1.0*Amount) OVER(ORDER BY DayNo ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ma FROM (VALUES(1,10),(2,30),(3,40)) D(DayNo,Amount))
SELECT DayNo,ma FROM M WHERE ma>=20;
توابع پنجرهای در WHERE همان سطح مجاز نیستند؛ CTE مرز محاسبه و فیلتر را درست میکند.
مثال 5: پنجره متقارن
یک ردیف قبل، جاری و یک ردیف بعد برای هموارسازی محلی استفاده میشود.
SELECT SeqNo,Value,AVG(1.0*Value) OVER(ORDER BY SeqNo ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LocalAvg
FROM (VALUES(1,10),(2,20),(3,50)) D(SeqNo,Value);
| SeqNo | Value | LocalAvg |
|---|
| 1 | 10 | 15.0 |
| 2 | 20 | 26.67 |
| 3 | 50 | 35.0 |
اندازه قاب در مرز پارتیشن کوچک میشود؛ بنابراین تعداد نمونه مخرج برای ابتدا و انتها کمتر است.
مثال 6: رفتار NULL در قاب
SUM مقدار NULL را نادیده میگیرد ولی موقعیت آن همچنان یک ردیف از قاب ROWS را اشغال میکند.
SELECT SeqNo,Value,SUM(Value) OVER(ORDER BY SeqNo ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS PairSum
FROM (VALUES(1,10),(2,NULL),(3,30)) D(SeqNo,Value);
| SeqNo | Value | PairSum |
|---|
| 1 | 10 | 10 |
| 2 | NULL | 10 |
| 3 | 30 | 30 |
تفاوت «عضویت ردیف» و «مشارکت مقدار» مهم است؛ ROWS ردیف را انتخاب میکند و SUM قواعد NULL را اجرا میکند.
مثال 7: کل آینده از ردیف جاری
قاب جاری تا انتهای پارتیشن مانده آینده را محاسبه میکند.
SELECT InstallmentNo,Amount,SUM(Amount) OVER(ORDER BY InstallmentNo ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS Remaining
FROM (VALUES(1,100),(2,80),(3,60)) I(InstallmentNo,Amount);
| InstallmentNo | Amount | Remaining |
|---|
| 1 | 100 | 240 |
| 2 | 80 | 140 |
| 3 | 60 | 60 |
این الگو برای مانده اقساط یا ظرفیت آینده مناسب است؛ جهت ترتیب باید با تعریف زمانی کسبوکار هماهنگ باشد.
مثال 8: موجودی جاری انبار
ورودی و خروجی علامتدار با قاب تجمعی به مانده موجودی تبدیل میشوند.
SELECT MoveID,QtyChange,SUM(QtyChange) OVER(ORDER BY MoveDate,MoveID ROWS UNBOUNDED PRECEDING) AS StockBalance
FROM (VALUES(1,CONVERT(date,'2026-01-01'),100),(2,'2026-01-02',-25),(3,'2026-01-02',10)) M(MoveID,MoveDate,QtyChange);
| MoveID | QtyChange | StockBalance |
|---|
| 1 | 100 | 100 |
| 2 | -25 | 75 |
| 3 | 10 | 85 |
MoveID تساوی تاریخ را رفع میکند. بدون ترتیب قطعی، مانده میانی در رویدادهای همزمان قابل اتکا نیست.
مثال 9: اصلاح قاب پیشفرض
در کلیدهای تکراری قاب پیشفرض RANGE میتواند همتاها را با هم جمع کند؛ ROWS صریح اثر هر ردیف را جدا میکند.
SELECT SeqNo,SortKey,Amount,
SUM(Amount) OVER(ORDER BY SortKey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RowsTotal
FROM (VALUES(1,1,10),(2,1,20),(3,2,5)) D(SeqNo,SortKey,Amount)
ORDER BY SortKey,SeqNo;
| SeqNo | SortKey | RowsTotal |
|---|
| 1 | 1 | 10 |
| 2 | 1 | 30 |
| 3 | 2 | 35 |
برای قطعیت کامل SeqNo را نیز در ORDER BY پنجره قرار دهید. مثال، تفاوت معنایی قاب فیزیکی را برجسته میکند.
مثال 10: ایندکس برای قاب متحرک
ایندکس روی SensorID، SampleTime و SampleID ترتیب لازم را فراهم میکند.
CREATE TABLE #M(SensorID int,SampleTime datetime2,SampleID int,Value decimal(10,2));
INSERT #M VALUES(1,'2026-01-01T00:00:00',1,10),(1,'2026-01-01T00:01:00',2,20);
CREATE INDEX IX_M_Window ON #M(SensorID,SampleTime,SampleID) INCLUDE(Value);
SELECT SampleID,AVG(Value) OVER(PARTITION BY SensorID ORDER BY SampleTime,SampleID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ma FROM #M;
DROP TABLE #M;
ایندکس پوششی میتواند Lookup و Sort را کم کند، اما نرخ بالای درج حسگرها هزینه نگهداری ایندکس را افزایش میدهد.
سؤالات متداول
برای شروع یادگیری قاب پنجرهای ROWS چه پیشنیازی لازم است؟
آشنایی با SELECT، مرتبسازی، توابع تجمیعی و ترتیب منطقی اجرای Query کافی است. سپس قاب پنجرهای ROWS را روی چند ردیف کوچک با مقدار تکراری و NULL آزمایش کنید تا مرز پنجره فقط حفظ نشود، بلکه مشاهده شود.
چگونه صحت نتیجه قاب پنجرهای ROWS را با یک تست کوچک بررسی کنیم؟
یک مجموعه داده VALUES با سه تا پنج ردیف بسازید، نتیجه مورد انتظار را دستی حساب کنید و ستون حاصل از قاب پنجرهای ROWS را مقایسه نمایید. تست باید حالت عادی، تساوی، NULL و مرز پارتیشن را جداگانه پوشش دهد.
کاربرد تجاری قاب پنجرهای ROWS در گزارش مدیریتی چیست؟
قاب پنجرهای ROWS امکان ساخت KPI، رتبهبندی، روند، سهم از کل و مانده تجمعی را بدون حذف جزئیات فراهم میکند. این ویژگی Dataset گزارش را سادهتر میکند و تعداد رفتوبرگشتهای لایه برنامه به دیتابیس را کاهش میدهد.
آیا قاب پنجرهای ROWS برای سامانههای مالی و فروش مناسب است؟
بله، به شرط آنکه ترتیب قطعی، نوع داده دقیق و مرز محاسبه مستند باشد. در پروژه مالی لازم است خروجی با داده مرجع تطبیق داده شود و برای حجم واقعی، Query Store و Actual Execution Plan نیز بررسی شوند.
تفاوت قاب پنجرهای ROWS با GROUP BY یا روش تجمیع سنتی چیست؟
GROUP BY ردیفها را در سطح گروه خلاصه میکند، ولی قاب پنجرهای ROWS در چارچوب تابع پنجرهای معمولاً جزئیات را حفظ میکند. انتخاب درست به شکل خروجی نیازمند وابسته است و هیچیک جایگزین مطلق دیگری نیست.
برای پیادهسازی حرفهای قاب پنجرهای ROWS چه خدماتی مفید است؟
بازبینی مدل داده، طراحی شاخصها، تهیه تست رگرسیون، تحلیل Execution Plan و مشاوره SQL Server بیشترین ارزش را دارند. در پروژه حساس بهتر است منطق قاب پنجرهای ROWS همراه تعریف KPI و نمونه مورد انتظار تحویل شود.
رایجترین خطای توسعهدهندگان هنگام استفاده از قاب پنجرهای ROWS چیست؟
خطای پرتکرار، فرضکردن ترتیب یا قاب پیشفرض و بیتوجهی به مقادیر مساوی است. نسخه صحیح باید کلید رفع تساوی، رفتار NULL و تفاوت ترتیب پنجره با ترتیب نهایی را صریح کند.
چگونه کارایی Query دارای قاب پنجرهای ROWS را بهبود دهیم؟
ابتدا تعداد ردیف ورودی را با فیلتر SARGable کاهش دهید، سپس ایندکس هماهنگ با پارتیشن و ترتیب را ارزیابی کنید. Sort، Window Spool، Memory Grant و Spill به tempdb در طرح واقعی باید اندازهگیری شوند.
بهترین روش نگهداری Queryهای مبتنی بر قاب پنجرهای ROWS چیست؟
نامگذاری روشن Aliasها، قاب صریح، کامنت درباره سیاست تساوی، تست داده مرزی و ثبت Baseline کارایی بهترین روش است. Query باید در Code Review همراه Execution Plan نمونه و انتظار کسبوکار بررسی شود.
قاب پنجرهای ROWS در کدام نسخههای SQL Server قابل استفاده است؟
قابلیتهای اصلی Window Functions از SQL Server 2012 به بعد گسترده و پایدارند، هرچند برخی توابع یا بهبودهای Optimizer میان نسخهها تفاوت دارند. Compatibility Level و مستندات نسخه مقصد را پیش از استقرار کنترل کنید.