Window Functions و Queryهای پیشرفته

T-SQL | OVER، ROW_NUMBER، LAG و Running Total

Window Function محاسبه تحلیلی را بدون Collapse کردن Rowهای جزئی انجام می‌دهد. Partition، Order و Frame سه مؤلفه اصلی Window هستند.

متن این فصل با رویکرد مرجع فنی و قابل استفاده در پروژه واقعی تنظیم شده است؛ مثال‌ها در امتداد مدل StoreDb نوشته شده‌اند و اصطلاحات اصلی SQL Server و T-SQL به‌صورت یکدست به‌کار می‌روند.

1. مفاهیم و قراردادهای اصلی

Window Function محاسبه تحلیلی را بدون Collapse کردن Rowهای جزئی انجام می‌دهد. Partition، Order و Frame سه مؤلفه اصلی Window هستند.

در طراحی پایگاه داده، Syntax تنها بخشی از مسئله است. نوع داده، Constraint، الگوی دسترسی، Transaction و هزینه اجرای Query باید هم‌زمان با نیاز Domain در نظر گرفته شوند.

2. ساختار و Syntax پایه

برای Ranking پایدار ORDER BY داخل OVER باید Tie-breaker داشته باشد. Running Total و LAST_VALUE به Frame صریح نیاز دارند تا رفتار در Tieها روشن باشد.

SELECT
    Id,
    CategoryId,
    Name,
    Price,
    ROW_NUMBER() OVER
    (
        PARTITION BY CategoryId
        ORDER BY Price DESC, Id
    ) AS PriceRank
FROM dbo.Products;

3. سناریوی StoreDb

برای محاسبه Running Total فروش هر Customer، Partition بر CustomerId و Frame از اولین Row تا Row جاری تعریف می‌شود.

نمونه‌های این فصل بر مدل فروشگاهی StoreDb بنا شده‌اند تا ارتباط مفاهیم میان فصل‌ها حفظ شود و Queryها در یک Context یکپارچه قابل بررسی باشند.

SELECT
    CustomerId,
    OrderDate,
    TotalAmount,
    SUM(TotalAmount) OVER
    (
        PARTITION BY CustomerId
        ORDER BY OrderDate, Id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM dbo.Orders;

4. اصول طراحی و Performance

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

• Window Function زمانی استفاده شود که جزئیات Row باید حفظ شود.
• Frame در Running Aggregate صریح باشد.
• Top-N per group با CTE + ROW_NUMBER قابل پیاده‌سازی است.
• Sort Cost و Memory Grant در Plan بررسی شود.

5. خطاهای رایج و نکات ایمنی

دستورهای SQL می‌توانند مستقیماً داده و ساختار را تغییر دهند؛ بنابراین بازبینی Context، تعداد Rowهای هدف و اثر Transaction قبل از اجرای تغییرات اهمیت عملی دارد.

• ORDER BY ناقص و Ranking ناپایدار.
• LAST_VALUE با Frame پیشفرض نامناسب.
• Filter مستقیم Window Function در همان WHERE.
• استفاده Window به‌جای GROUP BY وقتی فقط یک Row در هر Group لازم است.

6. چک‌لیست نهایی فصل

• Partition و Order بر اساس Business Boundary انتخاب شده‌اند.
• Tie-breaker وجود دارد.
• Frame صریح است.
• Top-N و LAG/LEAD در Query مناسب استفاده می‌شوند.

7. جمع‌بندی

خروجی این فصل باید به تصمیمی قابل اتکا در طراحی یا Query منجر شود؛ صحت داده، قابلیت نگهداری و رفتار Performance سه معیار ثابت برای ارزیابی نمونه‌ها هستند.