بهبود T-SQL Windowing در SQL Server 2022

بهبود T-SQL Windowing در SQL Server 2022

SQL Server 2022 با معرفی قابلیت‌های جدید و قدرتمند در حوزه توابع پنجره‌ای (Window Functions)، تحولی بزرگ در نوشتن کوئری‌های تحلیلی ایجاد کرده است.
این بهبودها که شامل افزودن عبارت WINDOW و قابلیت‌های IGNORE NULLS و RESPECT NULLS در توابع LAG و LEAD می‌شود، به توسعه‌دهندگان امکان می‌دهد کوئری‌های خواناتر، قابل نگهداری‌تر و کارآمدتری بنویسند.

پیش‌نیازها: سطح سازگاری دیتابیس

برای استفاده از قابلیت‌های جدید پنجره‌ای در SQL Server 2022، سطح سازگاری (Compatibility Level) دیتابیس باید روی 160 یا بالاتر تنظیم شده باشد.
برای بررسی و تغییر سطح سازگاری از دستورات زیر استفاده می‌شود:

-- بررسی سطح سازگاری فعلی
SELECT name, compatibility_level 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

-- تغییر سطح سازگاری به 160
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 160;

بهبود اول: عبارت WINDOW (تعریف پنجره نام‌دار)

مفهوم و مزایا عبارت WINDOW در SQL Server 2022 

عبارت WINDOW یکی از مهم‌ترین افزوده‌های T-SQL در SQL Server 2022 است که مطابق با استاندارد ISO/IEC SQL طراحی شده است.
این قابلیت به شما اجازه می‌دهد یک مشخصات پنجره (Window Specification) را یک بار تعریف کنید و سپس بارها در توابع پنجره‌ای مختلف از آن استفاده نمایید.

مزایای اصلی عبارت WINDOW عبارتند از:

• کاهش تکرار کد

  جلوگیری از تکرار عبارات طولانی PARTITION BY و ORDER BY

• افزایش خوانایی

  کوئری‌ها خواناتر و قابل فهم‌تر می‌شوند

• سهولت نگهداری

  تغییر در ساختار پنجره فقط در یک مکان انجام می‌شود

ساختار عبارت WINDOW

WINDOW window_name AS (
    [ reference_window_name ]
    [ PARTITION BY value_expression , ... ]
    [ ORDER BY order_by_expression [ ASC | DESC ] , ... ]
    [ ROWS | RANGE <window_frame_extent> ]
)

عبارت WINDOW در کوئری، بین بخش‌های HAVING و ORDER BY قرار می‌گیرد:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
WINDOW ...
ORDER BY ...

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

مثال 1: استفاده از یک پنجره در چند تابع

فرض کنید می‌خواهیم برای هر محصول، مجموع، میانگین، تعداد، کمترین و بیشترین مقدار سفارش را محاسبه کنیم.
بدون عبارت WINDOW، باید عبارت PARTITION BY را پنج بار تکرار کنیم:

-- بدون WINDOW (قبل از SQL Server 2022)
SELECT 
    SalesOrderID,
    ProductID,
    OrderQty,
    SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS Avg,
    COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS Count,
    MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS Min,
    MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS Max
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);

با استفاده از عبارت WINDOW در SQL Server 2022، کد بسیار تمیزتر می‌شود:

-- با WINDOW (SQL Server 2022)
SELECT 
    SalesOrderID,
    ProductID,
    OrderQty,
    SUM(OrderQty) OVER win AS Total,
    AVG(OrderQty) OVER win AS Avg,
    COUNT(OrderQty) OVER win AS Count,
    MIN(OrderQty) OVER win AS Min,
    MAX(OrderQty) OVER win AS Max
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664)
WINDOW win AS (PARTITION BY SalesOrderID);

نتیجه: هر دو کوئری خروجی یکسانی تولید می‌کنند، اما نسخه دوم خوانایی بسیار بهتری دارد.

مثال 2: ارجاع تو در تو (Nested Window References)

یکی از قدرتمندترین قابلیت‌های عبارت WINDOW، امکان ارجاع یک پنجره به پنجره دیگر است.
این ویژگی به شما اجازه می‌دهد پنجره‌های پیچیده را به صورت لایه‌لایه بسازید:

SELECT 
    orderid, 
    custid, 
    orderdate, 
    qty, 
    val,
    ROW_NUMBER() OVER PO AS ordernum,
    MAX(orderdate) OVER P AS maxorderdate,
    SUM(qty) OVER POF AS runsumqty,
    SUM(val) OVER POF AS runsumval
FROM Sales.OrderValues
WHERE custid IN (1, 2)
WINDOW 
    P AS (PARTITION BY custid),                                    -- لایه اول
    PO AS (P ORDER BY orderdate, orderid),                         -- لایه دوم (ارث‌بری از P)
    POF AS (PO ROWS UNBOUNDED PRECEDING)                           -- لایه سوم (ارث‌بری از PO)
ORDER BY custid, orderdate, orderid;

در این مثال:

• پنجره P فقط شامل PARTITION BY است
• پنجره PO از P ارث‌بری می‌کند و ORDER BY را اضافه می‌نماید
• پنجره POF از PO ارث‌بری می‌کند و ROWS UNBOUNDED PRECEDING را اضافه می‌کند

نکته مهم: ترتیب تعریف پنجره‌ها در عبارت WINDOW اهمیتی ندارد و می‌توانید به پنجره‌هایی که بعداً تعریف می‌شوند نیز ارجاع دهید (Forward Reference).

مثال 3: ترکیب نام پنجره با المان‌های اضافی

می‌توانید یک پنجره نام‌دار را به عنوان پایه استفاده کنید و در OVER المان‌های بیشتری اضافه کنید:

SELECT 
    orderid, 
    custid, 
    orderdate, 
    qty, 
    val,
    ROW_NUMBER() OVER (P ORDER BY orderdate, orderid) AS ordernum,
    MAX(orderdate) OVER P AS maxorderdate
FROM Sales.OrderValues
WHERE custid IN (1, 2)
WINDOW P AS (PARTITION BY custid)
ORDER BY custid, orderdate, orderid;

در اینجا پنجره P فقط شامل PARTITION BY است، اما در تابع ROW_NUMBER ما ORDER BY را مستقیماً در OVER اضافه کرده‌ایم.

محدودیت‌های عبارت WINDOW

• نمی‌توانید در یک عبارت پنجره به بیش از یک پنجره دیگر ارجاع دهید
• ارجاعات حلقوی (Circular References) مجاز نیستند
• ویژگی‌هایی که در WINDOW تعریف شده‌اند را نمی‌توان در OVER بازتعریف کرد (فقط می‌توان المان‌های جدید اضافه کرد)

بهبود دوم: مدیریت NULL در توابع LAG و LEAD

• مشکل قبلی: یافتن آخرین مقدار غیر NULL

  قبل از SQL Server 2022، یافتن آخرین مقدار غیر NULL در یک ستون کاری پیچیده و نیازمند کوئری‌های چندمرحله‌ای بود.
  به عنوان مثال، فرض کنید می‌خواهیم آخرین نظر غیر خالی هر کاربر را پیدا کنیم:

-- روش پیچیده قبل از SQL Server 2022
WITH LastComments AS (
    SELECT 
        UserId,
        CommentDate,
        Comment,
        LAG(Comment) OVER (PARTITION BY UserId ORDER BY CommentDate) AS PrevComment
    FROM Comments
)
SELECT * FROM LastComments;

• راهکار جدید: IGNORE NULLS و RESPECT NULLS

  SQL Server 2022 قابلیت‌های IGNORE NULLS و RESPECT NULLS را به توابع LAG و LEAD اضافه کرده است.
  این قابلیت‌ها به طور قابل توجهی یافتن آخرین مقدار غیر NULL را ساده می‌کنند.

مثال 4: استفاده از IGNORE NULLS در LAG

-- با IGNORE NULLS (SQL Server 2022)
SELECT 
    UserId,
    CommentDate,
    Comment,
    LAG(Comment) IGNORE NULLS OVER (PARTITION BY UserId ORDER BY CommentDate) AS LastNonNullComment
FROM Comments
ORDER BY UserId, CommentDate;

تابع LAG با IGNORE NULLS از روی مقادیر NULL عبور کرده و آخرین مقدار غیر NULL را برمی‌گرداند.

مثال 5: مقدار پیش‌فرض با پارامتر سوم

یکی از قابلیت‌های کمتر شناخته شده اما بسیار کاربردی، امکان تعیین مقدار پیش‌فرض برای توابع LAG و LEAD است.
این پارامتر سوم زمانی استفاده می‌شود که مقدار خالی (NULL) برگردانده شود:

SELECT 
    UserId,
    CommentDate,
    Score,
    LAG(Score, 1, 2147483647) IGNORE NULLS OVER (PARTITION BY UserId ORDER BY CommentDate) AS LastScoreWithDefault
FROM UserScores
ORDER BY UserId, CommentDate;

در این مثال، اگر مقدار قبلی وجود نداشته باشد یا NULL باشد، مقدار 2147483647 (بیشترین مقدار مجاز برای int) به عنوان جایگزین返回 می‌شود.

بهبود سوم: توابع پنجره‌ای جدید

• DATETRUNC و DATE_BUCKET

  SQL Server 2022 دو تابع جدید برای کار با تاریخ و زمان معرفی کرده است که در تحلیل‌های زمانی بسیار کاربرد دارند:

-- DATETRUNC: برش زمان به سطح مشخص شده
SELECT 
    OrderDate,
    DATETRUNC(month, OrderDate) AS MonthStart,
    DATETRUNC(day, OrderDate) AS DayStart,
    SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY DATETRUNC(month, OrderDate), DATETRUNC(day, OrderDate);

-- DATE_BUCKET: گروه‌بندی در بازه‌های زمانی مشخص
SELECT 
    DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
    SUM(TotalDue) AS WeeklySales
FROM Sales.SalesOrderHeader
GROUP BY DATE_BUCKET(week, 1, OrderDate);

مثال کاربردی جامع: گزارش فروش پیشرفته

در این مثال، تمام قابلیت‌های جدید را در یک سناریوی واقعی ترکیب می‌کنیم:

-- تنظیم سطح سازگاری
ALTER DATABASE AdventureWorks2022 SET COMPATIBILITY_LEVEL = 160;
GO

USE AdventureWorks2022;
GO

-- گزارش فروش با استفاده از قابلیت‌های جدید پنجره‌ای
SELECT 
    SOH.SalesOrderID,
    P.Name AS ProductName,
    SOD.OrderQty,
    SOD.UnitPrice,
    SOD.LineTotal,
    
    -- جمع فروش هر محصول (با استفاده از پنجره نام‌دار)
    SUM(SOD.LineTotal) OVER product_sales AS TotalSalesPerProduct,
    
    -- میانگین متحرک 3 فروش آخر (با IGNORE NULLS)
    AVG(SOD.LineTotal) OVER last_three_sales AS MovingAvg3Orders,
    
    -- جمع 3 فروش آخر
    SUM(SOD.LineTotal) OVER last_three_sales AS LastThreeSum,
    
    -- جمع تجمعی تا کنون
    SUM(SOD.LineTotal) OVER running_total AS CumulativeSales,
    
    -- آخرین قیمت غیر NULL
    LAST_VALUE(SOD.UnitPrice) IGNORE NULLS OVER product_sales AS LastNonNullPrice

FROM Sales.SalesOrderDetail SOD
    JOIN Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
    JOIN Production.Product P ON P.ProductID = SOD.ProductID
    
WHERE P.Name IN ('Touring-2000 Blue, 50', 'Touring-3000 Blue, 62')
    AND SOH.OrderDate = '2014-03-01'
    
WINDOW 
    -- پنجره فروش هر محصول
    product_sales AS (PARTITION BY P.Name),
    
    -- پنجره 3 فروش آخر
    last_three_sales AS (
        PARTITION BY P.Name 
        ORDER BY SOH.OrderDate, SOH.SalesOrderID 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ),
    
    -- پنجره جمع تجمعی
    running_total AS (
        ORDER BY SOH.OrderDate, SOH.SalesOrderID 
        ROWS UNBOUNDED PRECEDING
    )
    
ORDER BY P.Name, SOH.OrderDate, SOH.SalesOrderID;

این کوئری یک گزارش فروش کامل تولید می‌کند که شامل:

• جمع فروش هر محصول
• میانگین متحرک 3 سفارش آخر
• جمع 3 سفارش آخر
• جمع تجمعی فروش
• آخرین قیمت غیر NULL برای هر محصول

نکات عملکردی و بهینه‌سازی عبارت WINDOW

1. سطح سازگاری دیتابیس

همیشه مطمئن شوید سطح سازگاری دیتابیس روی 160 یا بالاتر تنظیم شده است، در غیر این صورت عبارت WINDOW اجرا نخواهد شد.

2. استفاده از نمایه‌های مناسب

توابع پنجره‌ای با PARTITION BY و ORDER BY از نمایه‌های پوششی (Covering Indexes) بهره می‌برند:

-- نمایه پیشنهادی برای کوئری‌های پنجره‌ای
CREATE INDEX IX_SalesOrderDetail_Product_Date 
ON Sales.SalesOrderDetail (ProductID, OrderDate) 
INCLUDE (LineTotal, OrderQty, UnitPrice);

3. اجتناب از پنجره‌های خیلی بزرگ

وقتی از ROWS UNBOUNDED PRECEDING استفاده می‌کنید، SQL Server باید تمام ردیف‌های قبلی را پردازش کند.
برای جداول بسیار بزرگ، این می‌تواند سنگین باشد.

4. محدودیت در ارجاعات

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

-- این کوئری خطا می‌دهد
WINDOW 
    P1 AS (PARTITION BY custid),
    P2 AS (ORDER BY orderdate),
    Invalid AS (P1 P2)  -- نامعتبر: دو ارجاع همزمان

جمع‌بندی

SQL Server 2022 بهبودهای چشمگیری در حوزه توابع پنجره‌ای ایجاد کرده است که می‌توان آنها را در سه دسته اصلی خلاصه کرد:

قابلیت	کاربرد	مزیت اصلی
عبارت WINDOW	تعریف و reuse کردن مشخصات پنجره	کاهش تکرار کد، افزایش خوانایی
IGNORE NULLS	عبور از مقادیر NULL در LAG/LEAD	ساده‌سازی یافتن آخرین مقدار غیر NULL
RESPECT NULLS	رفتار پیش‌فرض   (نگهداری NULL)	حفظ سازگاری با نسخه‌های قبل

این بهبودها اگرچه ممکن است در نگاه اول کوچک به نظر برسند، اما در عمل تأثیر قابل توجهی بر کیفیت و قابلیت نگهداری کدهای T-SQL دارند.
با استفاده از عبارت WINDOW می‌توانید کوئری‌های تحلیلی پیچیده را با ساختاری تمیز و ماژولار بنویسید، و با IGNORE NULLS منطق پردازش مقادیر خالی را به طور قابل توجهی ساده‌سازی کنید.

توصیه می‌شود برای بهره‌مندی از این قابلیت‌ها، سطح سازگاری دیتابیس‌های خود را به 160 ارتقا دهید و در کوئری‌های جدید از عبارت WINDOW استفاده کنید.
این کار نه تنها کد شما را خواناتر می‌کند، بلکه نگهداری و توسعه آن را در آینده بسیار آسان‌تر خواهد ساخت.