مثالهای عملی مستقل و قابل اجرا
مثال 1: مقدار مرجع در کل توالی
هر ردیف با اولین مبلغ بر پایه ترتیب زمانی مقایسه میشود.
WITH Sales AS
(
SELECT *
FROM (VALUES
(1, N'فروش', CAST('2026-01-01' AS date), 100),
(2, N'فروش', CAST('2026-02-01' AS date), 140),
(3, N'فروش', CAST('2026-03-01' AS date), 120),
(4, N'پشتیبانی', CAST('2026-01-01' AS date), 80),
(5, N'پشتیبانی', CAST('2026-02-01' AS date), 110)
) AS V(Id, Department, SaleDate, Amount)
)
SELECT SaleDate,Amount,
FIRST_VALUE(Amount) OVER (ORDER BY SaleDate,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BoundaryAmount
FROM Sales WHERE Department=N'فروش' ORDER BY SaleDate,Id;
| تاریخ | مبلغ | مقدار مرزی |
|---|
| 2026-02-01 | 140 | 100 |
نکته کاربردی: Frame صریح، قصد نویسنده Query را برای نگهداری آینده روشن میکند.
مثال 2: محاسبه مستقل برای هر دپارتمان
PARTITION BY مرجع آغاز یا پایان هر واحد را مستقل نگه میدارد.
WITH Sales AS
(
SELECT *
FROM (VALUES
(1, N'فروش', CAST('2026-01-01' AS date), 100),
(2, N'فروش', CAST('2026-02-01' AS date), 140),
(3, N'فروش', CAST('2026-03-01' AS date), 120),
(4, N'پشتیبانی', CAST('2026-01-01' AS date), 80),
(5, N'پشتیبانی', CAST('2026-02-01' AS date), 110)
) AS V(Id, Department, SaleDate, Amount)
)
SELECT Department,SaleDate,Amount,
FIRST_VALUE(Amount) OVER (PARTITION BY Department ORDER BY SaleDate,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BoundaryAmount
FROM Sales ORDER BY Department,SaleDate,Id;
| واحد | مبلغ | مرجع |
|---|
| فروش | 140 | 100 |
| پشتیبانی | 110 | 80 |
نکته کاربردی: مرز پارتیشن از آمیخته شدن واحدهای کسبوکار جلوگیری میکند.
مثال 3: تعریف صریح Frame برای FIRST_VALUE
Frame پنجره بر معنای مقدار مرزی اثر مستقیم دارد و باید همراه ORDER BY خوانده شود.
WITH Sales AS
(
SELECT *
FROM (VALUES
(1, N'فروش', CAST('2026-01-01' AS date), 100),
(2, N'فروش', CAST('2026-02-01' AS date), 140),
(3, N'فروش', CAST('2026-03-01' AS date), 120),
(4, N'پشتیبانی', CAST('2026-01-01' AS date), 80),
(5, N'پشتیبانی', CAST('2026-02-01' AS date), 110)
) AS V(Id, Department, SaleDate, Amount)
)
SELECT SaleDate,Amount,
FIRST_VALUE(Amount) OVER (ORDER BY SaleDate,Id) AS DefaultFrame,
FIRST_VALUE(Amount) OVER (ORDER BY SaleDate,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ExplicitFrame
FROM Sales WHERE Department=N'فروش' ORDER BY SaleDate,Id;
نکته کاربردی: برای LAST_VALUE تفاوت حیاتی است؛ برای FIRST_VALUE نیز صراحت Frame از برداشت اشتباه جلوگیری میکند.
مثال 4: محاسبه فاصله از نقطه مرجع
تفاضل مبلغ جاری با مقدار مرزی، رشد تجمعی یا فاصله تا هدف نهایی را میسازد.
WITH Sales AS
(
SELECT *
FROM (VALUES
(1, N'فروش', CAST('2026-01-01' AS date), 100),
(2, N'فروش', CAST('2026-02-01' AS date), 140),
(3, N'فروش', CAST('2026-03-01' AS date), 120),
(4, N'پشتیبانی', CAST('2026-01-01' AS date), 80),
(5, N'پشتیبانی', CAST('2026-02-01' AS date), 110)
) AS V(Id, Department, SaleDate, Amount)
)
SELECT SaleDate,Amount,
Amount-FIRST_VALUE(Amount) OVER (ORDER BY SaleDate,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DifferenceFromBoundary
FROM Sales WHERE Department=N'فروش' ORDER BY SaleDate,Id;
| تاریخ | مبلغ | فاصله |
|---|
| 2026-02-01 | 140 | 40 |
نکته کاربردی: علامت تفاضل را مطابق سؤال کسبوکار انتخاب کنید؛ فاصله از آغاز با فاصله تا پایان متفاوت است.
مثال 5: انتخاب ردیف پس از محاسبه پنجره
برای فیلتر نتیجه تابع، محاسبه در CTE انجام و شرط در Query بیرونی اعمال میشود.
WITH Sales AS
(
SELECT *
FROM (VALUES
(1, N'فروش', CAST('2026-01-01' AS date), 100),
(2, N'فروش', CAST('2026-02-01' AS date), 140),
(3, N'فروش', CAST('2026-03-01' AS date), 120),
(4, N'پشتیبانی', CAST('2026-01-01' AS date), 80),
(5, N'پشتیبانی', CAST('2026-02-01' AS date), 110)
) AS V(Id, Department, SaleDate, Amount)
)
,W AS (SELECT *,FIRST_VALUE(Amount) OVER(PARTITION BY Department ORDER BY SaleDate,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS B FROM Sales)
SELECT Id,Department,Amount,B FROM W WHERE Amount<>B ORDER BY Department,Id;
نکته کاربردی: فیلتر زودهنگام میتواند پارتیشن را تغییر دهد؛ محل اعمال WHERE بخشی از تعریف مسئله است.
مثال 6: مدیریت NULL در نسخه 2022
IGNORE NULLS نخستین یا آخرین مقدار غیرNULL را در پنجره انتخاب میکند.
WITH D AS (SELECT * FROM (VALUES (1,CAST(NULL AS int)),(2,20),(3,NULL),(4,40))V(Id,Value))
SELECT Id,Value,
FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS NonNullBoundary
FROM D ORDER BY Id;
| Id | مقدار | مرز غیرNULL |
|---|
| 3 | NULL | 20 |
نکته کاربردی: در نسخههای قدیمیتر باید از الگوهای جایگزین مانند مرتبسازی شرطی یا زیرپرسوجو استفاده شود.
مثال 7: ترتیب تجاری متفاوت از کمینه و بیشینه
FIRST_VALUE بر اساس ORDER BY عمل میکند و الزاماً MIN یا MAX نیست.
WITH Prices AS (SELECT * FROM (VALUES (1,3,900),(2,1,1200),(3,2,1000))V(Id,Priority,Price))
SELECT Id,Priority,Price,
FIRST_VALUE(Price) OVER(ORDER BY Priority,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BusinessBoundary,
MIN(Price) OVER() AS MinimumPrice
FROM Prices ORDER BY Priority,Id;
| اولویت | قیمت | مرز تجاری |
|---|
| 3 | 900 | 1200 |
نکته کاربردی: انتخاب ORDER BY باید دقیقاً تعریف ترتیب کسبوکار باشد، نه صرفاً سادهترین ستون.
مثال 8: مقایسه وضعیت سفارش با مرز فرایند
در فرایند سفارش، همه رخدادها را میتوان با وضعیت ابتدایی یا نهایی همان سفارش مقایسه کرد.
WITH H AS (SELECT * FROM (VALUES (10,1,N'ثبت'),(10,2,N'پرداخت'),(10,3,N'ارسال'),(20,1,N'ثبت'))V(OrderId,StepNo,State))
SELECT OrderId,StepNo,State,
FIRST_VALUE(State) OVER(PARTITION BY OrderId ORDER BY StepNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BoundaryState
FROM H ORDER BY OrderId,StepNo;
| سفارش | مرحله | وضعیت مرزی |
|---|
| 10 | 2 | ثبت |
نکته کاربردی: این الگو برای Audit Trail مفید است، اما تاریخچه باید ترتیب یکتا و قابل اعتماد داشته باشد.
مثال 9: اصلاح TOP بدون پارتیشن
TOP یا زیرپرسوجوی مستقل معمولاً یک مقدار سراسری میدهد؛ تابع پنجرهای مرز هر گروه را حفظ میکند.
WITH Sales AS
(
SELECT *
FROM (VALUES
(1, N'فروش', CAST('2026-01-01' AS date), 100),
(2, N'فروش', CAST('2026-02-01' AS date), 140),
(3, N'فروش', CAST('2026-03-01' AS date), 120),
(4, N'پشتیبانی', CAST('2026-01-01' AS date), 80),
(5, N'پشتیبانی', CAST('2026-02-01' AS date), 110)
) AS V(Id, Department, SaleDate, Amount)
)
SELECT Department,SaleDate,Amount,
FIRST_VALUE(Amount) OVER(PARTITION BY Department ORDER BY SaleDate,Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CorrectPerGroup
FROM Sales ORDER BY Department,SaleDate,Id;
| واحد | مرجع صحیح |
|---|
| فروش | 100 |
| پشتیبانی | 80 |
نکته کاربردی: وقتی تعداد گروهها زیاد است، بیان پنجرهای هم کوتاهتر و هم قابل بهینهسازیتر از زیرپرسوجوی همبسته است.
مثال 10: ایندکس و کنترل Sort
یک ایندکس مطابق پارتیشن و ترتیب میتواند نیاز به مرتبسازی جداگانه را کاهش دهد.
CREATE TABLE #H(GroupId int,EventDate date,EventId int,Value int);
INSERT #H VALUES(1,'2026-01-01',1,10),(1,'2026-02-01',2,20),(1,'2026-03-01',3,15);
CREATE INDEX IX_H_Window ON #H(GroupId,EventDate,EventId) INCLUDE(Value);
SELECT *,FIRST_VALUE(Value) OVER(PARTITION BY GroupId ORDER BY EventDate,EventId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BoundaryValue FROM #H;
DROP TABLE #H;
| عملگر | انتظار |
|---|
| Sort | کاهش احتمالی با ایندکس همراستا |
نکته کاربردی: به حذف Sort اکتفا نکنید؛ Logical Reads، CPU، Memory Grant و Spill نیز باید اندازهگیری شوند.