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

مقدمه

مایکروسافت اخیرا اولین پیش نمایش عمومی SQL Server 2022  را منتشر کرده است. این نسخه بهبودهایی در T-SQL داشته است. در این مقاله بر روی بهبودهای Windowing و NULL treatment تمرکز می‌کنیم. این عبارت‌های جدید شامل عبارت Window و عبارت windowing NULL treatment است.

من از نمونه پایگاه داده TSQLV6  در مثال‌های این مقاله استفاده خواهم کرد. این نمونه پایگاه داده را می‌توانید از لینک زیر دانلود کنید.

http://tsql.lucient.com/SampleDatabases/TSQLV6.zip

عبارت WINDOW

عبارت WINDOW بخشی از استاندارد ISO/IEC SQL است. این عبارت به شما این امکان را می‌دهد تا قسمت‌هایی از یک WINDOW را نام‌گذاری کنید (یا حتی کل آن را) و سپس از این نام در عبارت OVER در توابع WINDOW در کوئری خود استفاده کنید. این عبارت به شما اجازه می‌دهد تا با اجتناب از تکرار قسمت‌های یکسان window specifications  خود، کدتان را کوتاه‌تر کنید. این عبارت اکنون در Azure SQL Database و SQL Server 2022 موجود است، در صورتی که از سطح compatibility ، ۱۶۰ یا بالاتر در پایگاه داده استفاده کنید.

عبارت WINDOW بین عبارت‌های HAVING و ORDER BY در کوئری قرار دارد:

SELECT
FROM
WHERE
GROUP BY
HAVING
WINDOW
ORDER BY

عبارت WINDOW دارای سینتکس زیر است:

WINDOW window_name AS ( [ reference_window_name ]   
                        [ <window partition clause> ]  
                        [ <window order clause> ]   
                        [ <window frame clause> ] )

به عنوان یک مثال که عبارت WINDOW می‌تواند در کوتاه کردن کد شما مفید باشد، کوئری زیر را در نظر بگیرید:

USE TSQLV6;
 
SELECT orderid, custid, orderdate, qty, val,
  SUM(qty) OVER (PARTITION BY custid 
                 ORDER BY orderdate, orderid
                 ROWS UNBOUNDED PRECEDING) AS runsumqty,
  SUM(val) OVER (PARTITION BY custid 
                 ORDER BY orderdate, orderid
                 ROWS UNBOUNDED PRECEDING) AS runsumval
FROM Sales.OrderValues
WHERE custid IN (1, 2)
ORDER BY custid, orderdate, orderid;

این کوئری خروجی زیر را تولید می‌کند:

orderid      custid      orderdate         qty            val        runsumqty   runsumval
--------    ----------- ----------     -----------   -------    -----------    ----------
۱۰۶۴۳       ۱                ۲۰۲۱-۰۸-۲۵     ۳۸             ۸۱۴.۵۰     ۳۸                ۸۱۴.۵۰
۱۰۶۹۲       ۱                ۲۰۲۱-۱۰-۰۳     ۲۰             ۸۷۸.۰۰     ۵۸                ۱۶۹۲.۵۰
۱۰۷۰۲       ۱               ۲۰۲۱-۱۰-۱۳       ۲۱             ۳۳۰.۰۰     ۷۹                ۲۰۲۲.۵۰
۱۰۸۳۵       ۱               ۲۰۲۲-۰۱-۱۵      ۱۷             ۸۴۵.۸۰     ۹۶                ۲۸۶۸.۳۰
۱۰۹۵۲       ۱               ۲۰۲۲-۰۳-۱۶     ۱۸             ۴۷۱.۲۰      ۱۱۴               ۳۳۳۹.۵۰
۱۱۰۱۱         ۱              ۲۰۲۲-۰۴-۰۹     ۶۰             ۹۳۳.۵۰      ۱۷۴              ۴۲۷۳.۰۰
۱۰۳۰۸       ۲              ۲۰۲۰-۰۹-۱۸      ۶              ۸۸.۸۰        ۶                  ۸۸.۸۰
۱۰۶۲۵       ۲              ۲۰۲۱-۰۸-۰۸     ۱۸             ۴۷۹.۷۵      ۲۴                ۵۶۸.۵۵
۱۰۷۵۹       ۲              ۲۰۲۱-۱۱-۲۸      ۱۰             ۳۲۰.۰۰      ۳۴                ۸۸۸.۵۵
۱۰۹۲۶       ۲             ۲۰۲۲-۰۳-۰۴     ۲۹            ۵۱۴.۴۰       ۶۳                ۱۴۰۲.۹۵

در این کوئری می‌توانید دو تابع window  را با استفاده از window specifications یکسان، شامل window partitioning، ordering  و framing  مشاهده کنید. برای کوتاه کردن کوئری، می‌توانید از عبارت WINDOW برای نام‌گذاری window specifications با هر سه آیتم استفاده کنید، مثلاً با W نام گزاری کنید، و سپس OVER W را در هر دو تابع window  مشخص کنید. به صورت زیر خواهد بود:

SELECT orderid, custid, orderdate, qty, val,
  SUM(qty) OVER W AS runsumqty,
  SUM(val) OVER W AS runsumval
FROM Sales.OrderValues
WHERE custid IN (1, 2)
WINDOW W AS ( PARTITION BY custid 
              ORDER BY orderdate, orderid
              ROWS UNBOUNDED PRECEDING )
ORDER BY custid, orderdate, orderid;

همان طور که می‌بینید، زمانی که نام window  نمایانگر تمام window specification  هایی است که مورد نیاز شما است (نه فقط بخشی از آن)، نام window  را دقیقاً بعد از عبارت OVER بدون پرانتز اضافه کنید.

ممکن است متوجه شده باشید که در سینتکس عبارت WINDOW، window name specification می‌تواند ارجاع به نام WINDOW دیگری داشته باشد. این مورد به ویژه زمانی مفید است که کوئری شما دارای توابع مختلف WINDOW با window specification های مختلف باشد و یک window specification همانند بخشی از یک WINDOW دیگر باشد. کوئری زیر را به عنوان نمونه در نظر بگیرید:

SELECT orderid, custid, orderdate, qty, val,
  ROW_NUMBER() OVER( PARTITION BY custid
                     ORDER BY orderdate, orderid ) AS ordernum,
  MAX(orderdate) OVER( PARTITION BY custid ) AS maxorderdate,
  SUM(qty) OVER( PARTITION BY custid 
                 ORDER BY orderdate, orderid
                 ROWS UNBOUNDED PRECEDING ) AS runsumqty,
  SUM(val) OVER( PARTITION BY custid           
                 ORDER BY orderdate, orderid   
                 ROWS UNBOUNDED PRECEDING ) AS runsumval
FROM Sales.OrderValues
WHERE custid IN (1, 2)
ORDER BY custid, orderdate, orderid;

این کوئری خروجی زیر را تولید می‌کند:

orderid  custid  orderdate      qty      val          ordernum  maxorderdate     runsumqty   runsumval
-------- ------- ----------       ----     -------    ---------     ------------         -----------    -----------
۱۰۶۴۳    ۱       ۲۰۲۱-۰۸-۲۵       ۳۸      ۸۱۴.۵۰      ۱              ۲۰۲۲-۰۴-۰۹              ۳۸          ۸۱۴.۵۰
۱۰۶۹۲    ۱       ۲۰۲۱-۱۰-۰۳       ۲۰      ۸۷۸.۰۰      ۲             ۲۰۲۲-۰۴-۰۹               ۵۸          ۱۶۹۲.۵۰
۱۰۷۰۲    ۱       ۲۰۲۱-۱۰-۱۳        ۲۱      ۳۳۰.۰۰      ۳             ۲۰۲۲-۰۴-۰۹               ۷۹          ۲۰۲۲.۵۰
۱۰۸۳۵    ۱       ۲۰۲۲-۰۱-۱۵       ۱۷      ۸۴۵.۸۰      ۴             ۲۰۲۲-۰۴-۰۹               ۹۶          ۲۸۶۸.۳۰
۱۰۹۵۲    ۱       ۲۰۲۲-۰۳-۱۶      ۱۸      ۴۷۱.۲۰       ۵            ۲۰۲۲-۰۴-۰۹               ۱۱۴         ۳۳۳۹.۵۰
۱۱۰۱۱     ۱        ۲۰۲۲-۰۴-۰۹     ۶۰     ۹۳۳.۵۰       ۶            ۲۰۲۲-۰۴-۰۹               ۱۷۴         ۴۲۷۳.۰۰
۱۰۳۰۸    ۲       ۲۰۲۰-۰۹-۱۸      ۶      ۸۸.۸۰         ۱             ۲۰۲۲-۰۳-۰۴                ۶           ۸۸.۸۰
۱۰۶۲۵    ۲       ۲۰۲۱-۰۸-۰۸     ۱۸      ۴۷۹.۷۵      ۲             ۲۰۲۲-۰۳-۰۴               ۲۴          ۵۶۸.۵۵
۱۰۷۵۹    ۲       ۲۰۲۱-۱۱-۲۸      ۱۰      ۳۲۰.۰۰      ۳             ۲۰۲۲-۰۳-۰۴               ۳۴          ۸۸۸.۵۵
۱۰۹۲۶    ۲       ۲۰۲۲-۰۳-۰۴    ۲۹      ۵۱۴.۴۰      ۴             ۲۰۲۲-۰۳-۰۴               ۶۳          ۱۴۰۲.۹۵

به موارد زیر توجه کنید:

• window specification برای تابع MAX فقط یک عبارت window partition دارد.
• window specification برای تابع ROW_NUMBER دارای یک عبارت window partition است که همانند تابع MAX است، به علاوه یک عبارت window order.
• تابع SUM دارای window partition و عبارت‌های order مشابه تابع ROW_NUMBER است، به علاوه یک عبارت window frame.

قابلیت بازگشتی سینتکس عبارت 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 ),
       POF AS ( PO ROWS UNBOUNDED PRECEDING )
ORDER BY custid, orderdate, orderid;

window name definitionها در عبارت 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 POF AS ( PO ROWS UNBOUNDED PRECEDING ),
       PO AS ( P ORDER BY orderdate, orderid ),
       P AS ( PARTITION BY custid )
ORDER BY custid, orderdate, orderid;

توجه داشته باشید، هر چند، شما نمی‌توانید از چندین window name reference در یک window name specification استفاده کنید. شما فقط به یک window name reference محدود شده‌اید. به عنوان مثال، کد زیر به این دلیل معتبر نیست:

SELECT orderid, custid, orderdate, qty, val,
  SUM(qty) OVER ( P O F ) AS runsumqty,
  SUM(val) OVER ( P O F ) AS runsumval
FROM Sales.OrderValues
WHERE custid IN (1, 2)
WINDOW P AS ( PARTITION BY custid ),
       O AS ( ORDER BY orderdate, orderid ),
       F AS ( ROWS UNBOUNDED PRECEDING )
ORDER BY custid, orderdate, orderid;

این کد خطای زیر را ایجاد می‌کند:

Msg 102, Level 15, State 1, Line 106
Incorrect syntax near 'O'.

شما مجاز به ترکیب یک window name و عناصر بیشتر، در window specification هستید، مانند زیر:

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;

این کوئری خروجی زیر را تولید می‌کند:

orderid     custid   orderdate        qty             val      ordernum    maxorderdate
--------   --------  ----------       --------     -------   ------------- ------------
۱۰۶۴۳       ۱           ۲۰۲۱-۰۸-۲۵      ۳۸          ۸۱۴.۵۰    ۱                    ۲۰۲۲-۰۴-۰۹
۱۰۶۹۲       ۱           ۲۰۲۱-۱۰-۰۳      ۲۰          ۸۷۸.۰۰    ۲                    ۲۰۲۲-۰۴-۰۹
۱۰۷۰۲       ۱           ۲۰۲۱-۱۰-۱۳       ۲۱          ۳۳۰.۰۰    ۳                    ۲۰۲۲-۰۴-۰۹
۱۰۸۳۵       ۱           ۲۰۲۲-۰۱-۱۵      ۱۷          ۸۴۵.۸۰    ۴                    ۲۰۲۲-۰۴-۰۹
۱۰۹۵۲       ۱           ۲۰۲۲-۰۳-۱۶     ۱۸          ۴۷۱.۲۰     ۵                    ۲۰۲۲-۰۴-۰۹
۱۱۰۱۱         ۱           ۲۰۲۲-۰۴-۰۹    ۶۰          ۹۳۳.۵۰    ۶                    ۲۰۲۲-۰۴-۰۹
۱۰۳۰۸       ۲           ۲۰۲۰-۰۹-۱۸     ۶           ۸۸.۸۰       ۱                    ۲۰۲۲-۰۳-۰۴
۱۰۶۲۵       ۲           ۲۰۲۱-۰۸-۰۸    ۱۸          ۴۷۹.۷۵     ۲                    ۲۰۲۲-۰۳-۰۴
۱۰۷۵۹       ۲           ۲۰۲۱-۱۱-۲۸     ۱۰          ۳۲۰.۰۰     ۳                    ۲۰۲۲-۰۳-۰۴
۱۰۹۲۶       ۲           ۲۰۲۲-۰۳-۰۴    ۲۹          ۵۱۴.۴۰    ۴                    ۲۰۲۲-۰۳-۰۴

همان طور که قبلاً اشاره کردم، وقتی window name تمام window specification را نشان می‌دهد، مانند تابع MAX در این کوئری، نام window  را دقیقا بعد از عبارت OVER بدون پرانتز مشخص می‌کنید. اما هنگامی که نام window  تنها بخشی از window name است، مانند تابع ROW_NUMBER در این کوئری، نام window  را مشخص می‌کنید و بقیه عناصر window  را در داخل پرانتز قرار می‌دهید.

در حال حاضر، شما می‌دانید که مجاز به تعریف بازگشتی یک نام window  بر اساس دیگری هستید. با این حال، در صورتی که این روال بازگشتی واضح نباشد، ارجاعات سیکلی مجاز نیستند. به عنوان مثال، کوئری زیر معتبر است زیرا تعاریف نام پنجره واضح هستند و سیکلی نیستند:

SELECT 'This is valid'
WINDOW W1 AS (), W2 AS (W1), W3 AS (W2);

با این حال، کوئری زیر نامعتبر است زیرا تعاریف نام window به صورت سیکلی است:

SELECT 'This is invalid'
WINDOW W1 AS (W2), W2 AS (W3), W3 AS (W1);

در نهایت، به دامنه window name های تعریف ‌شده دقت کنید. به عنوان مثال، اگر یک window name را در کوئری داخلی یک CTE، جدول مشتق شده، view  یا به صورت inline table valued function تعریف کنید، کوئری بیرونی، window name داخلی را تشخیص نخواهد داد. به عنوان مثال، کوئری زیر به این دلیل نامعتبر است:

WITH C AS
(
  SELECT orderid, custid, orderdate, qty, val,
    SUM(qty) OVER W AS runsumqtyall
  FROM Sales.OrderValues
  WHERE custid IN (1, 2)
  WINDOW W AS ( PARTITION BY custid 
                ORDER BY orderdate, orderid
                ROWS UNBOUNDED PRECEDING )
)
SELECT *,
  SUM(qty) OVER W AS runsumqty22
FROM C
WHERE orderdate >= '20220101';

شما باید window nameای را که می‌خواهید، در هر یک از دامنه‌هایی که می‌خواهید از آن استفاده کنید، تعریف کنید، مانند زیر:

WITH C AS
(
  SELECT orderid, custid, orderdate, qty, val,
    SUM(qty) OVER W AS runsumqtyall
  FROM Sales.OrderValues
  WHERE custid IN (1, 2)
  WINDOW W AS ( PARTITION BY custid 
                ORDER BY orderdate, orderid
                ROWS UNBOUNDED PRECEDING )
)
SELECT *,
  SUM(qty) OVER W AS runsumqty22
FROM C
WHERE orderdate >= '20220101'
WINDOW W AS ( PARTITION BY custid 
              ORDER BY orderdate, orderid
              ROWS UNBOUNDED PRECEDING );

هر یک از دامنه‌ها نام پنجره خود را W تعریف می‌کنند و لازم نیست آنها بر اساس مشخصات یکسانی باشند (اگرچه در این مثال هستند).

Windowing NULL Treatment

NULL Treatment بخشی از استاندارد ISO/IEC SQL است و برای افست توابع window ، شامل FIRST_VALUE، LAST_VALUE، LAG و LEAD در دسترس است. این عبارت دارای سینتکس زیر است:

<function>(<scalar_expression>[, <other args>]) [IGNORE NULLS | RESPECT NULLS] OVER( <specification> )

گزینه RESPECT NULLS پیش فرض است و به این معنی است که شما می‌خواهید تابع، مقدار <scalar_expression> را برگرداند، خواه NULL یا non-NULL باشد.

گزینه IGNORE NULLS قابلیت جدیدی را معرفی می‌کند که برنامه نویسان مدت‌ها بود مشتاقانه منتظر اضافه شدن آن در T-SQL بودند. این گزینه بدان معنی است که شما می‌خواهید تابع، مقدار <scalar_expression>  را در صورت non-NULL برگرداند. با این حال، اگر NULL باشد، می‌خواهید که تابع به روال خود ادامه داده تا زمانی که یک مقدار non-NULL پیدا شود. اگر مقدار non-NULL پیدا نشد، یک NULL برمی‌گرداند.

برای نشان دادن کاربرد این عبارت، از جدولی به نام T1 در ادامه مقاله استفاده می‌کنم. برای ایجاد و پر کردن T1 از کد زیر استفاده کنید:

DROP TABLE IF EXISTS dbo.T1;
 
CREATE TABLE dbo.T1
(
  id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1 INT NULL,
  col2 INT NULL
);
GO
 
INSERT INTO dbo.T1(id, col1, col2) VALUES
  ( ۲, NULL,  200),
  ( ۳,   ۱۰, NULL),
  ( ۵,   -۱, NULL),
  ( ۷, NULL,  202),
  (۱۱, NULL,  150),
  (۱۳,  -۱۲,   ۵۰),
  (۱۷, NULL,  180),
  (۱۹, NULL,  170),
  (۲۳, ۱۷۵۹, NULL);

فرض کنید ستون id نشان دهنده ترتیب وقوع رویدادهای ثبت شده در T1 است. هر ردیف نشان دهنده رویدادی است که در آن یک یا چند مقدار attribute  تغییر کرده است. NULL به این معنی است که attribute آخرین مقدار non-NULL را که تا آن لحظه داشته است را همچنان حفظ کرده است.

فرض کنید باید آخرین مقدار col1 یعنی lastknowncol1 را به صورت non-NULL در هر رویداد برگردانید. بدون استفاده از NULL Treatment، باید از یک تکنیک نسبتاً پیچیده مانند موارد زیر استفاده کنید:

WITH C AS
(
  SELECT id, col1,
    MAX(CASE WHEN col1 IS NOT NULL THEN id END)
      OVER(ORDER BY id
           ROWS UNBOUNDED PRECEDING) AS grp
  FROM dbo.T1
)
SELECT id, col1,
  MAX(col1) OVER(PARTITION BY grp
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING) AS lastknowncol1
FROM C;

اگر قبلاً با این تکنیک آشنا نیستید، درک منطق آن ممکن است کمی برایتان پیچیده باشد.

با استفاده از به NULL Treatment، می‌توانید به راحتی با استفاده از تابع LAST_VALUE با گزینه IGNORE NULLS به همان خروجی بالا، دست پیدا کنید. کد به صورت زیر خواهد بود:

SELECT id, col1,
  LAST_VALUE(col1) IGNORE NULLS OVER( ORDER BY id ROWS UNBOUNDED PRECEDING ) AS lastknowncol
FROM dbo.T1;

البته اگر بخواهید این منطق را برای چندین attribute  اعمال کنید، تفاوت چشمگیرتر خواهد بود.

بدون استفاده از NULL Treatment، از کد زیر برای برگرداندن آخرین مقادیر ستون‌های col1 و col2 یعنی lastknowncol1 و lastknowncol2 استفاده می‌کنیم:

WITH C AS
(
  SELECT id, col1, col2,
    MAX(CASE WHEN col1 IS NOT NULL THEN id END)
      OVER(ORDER BY id
           ROWS UNBOUNDED PRECEDING) AS grp1,
    MAX(CASE WHEN col2 IS NOT NULL THEN id END)
      OVER(ORDER BY id
           ROWS UNBOUNDED PRECEDING) AS grp2
  FROM dbo.T1
)
SELECT id,
  col1,
  MAX(col1) OVER(PARTITION BY grp1
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING) AS lastknowncol1,
  col2,
  MAX(col2) OVER(PARTITION BY grp2
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING) AS lastknowncol2
FROM C;

همچنین باید توجه داشته باشید که حتی اگر جدول T1 دارای یک ایندکس با id به عنوان کلید باشد، هر یک از lastknown  های attribute  در کوئری بالا منجر به یک عملگر explicit sorting در plan می‌شود.

این واقعیت، باعث می‌شود که این راه حل با سربار بالایی همراه باشد. در ادامه از راه حل جایگزین، یعنی استفاده از NULL Treatment را خواهیم داشت:

SELECT id, 
  col1, LAST_VALUE(col1) IGNORE NULLS OVER W AS lastknowncol1,
  col2, LAST_VALUE(col2) IGNORE NULLS OVER W AS lastknowncol2
FROM dbo.T1
WINDOW W AS ( ORDER BY id ROWS UNBOUNDED PRECEDING );

این راه‌حل بسیار کوتاه‌تر است و بهینه‌سازی توابع با این گزینه می‌تواند به اسکن مرتب‌سازی یک ایندکس تکیه کند و بنابراین از explicit sorting، اجتناب شود.

همان طور که گفته شد، NULL treatment برای افست همه توابع window شامل (FIRST_VALUE، LAST_VALUE، LAG و LEAD ) در دسترس است. در اینجا یک مثال با استفاده از LAG برای برگرداندن مقدار prevknowncol آورده شده است:

SELECT id, col1, 
  LAG(col1) IGNORE NULLS OVER ( ORDER BY id ) AS prevknowncol1
FROM dbo.T1;

با توضیحات ارائه شده در این مقاله مطمئنم قصد ندارید این مثال را بدون استفاده از NULL treatment انجام دهید.

جمع‌بندی

در این مقاله به بهبودهای T-SQL در SQL Server 2022 در مورد توابع window و مدیریت NULL پرداخته شد. در این مقاله روی دو مفهوم زیر صحبت شد:

• استفاده مجدد بخشی از window definition و یا کل آن
• کنترل NULL treatment در آفست توابع window