آموزش جامع توابع پنجره‌ای یا Window Functions در SQL Server برای تحلیل داده

آموزش جامع توابع پنجره‌ای یا Window Functions  در SQL Server: قدرت تحلیل داده‌ها را کشف کنید

توابع پنجره‌ای یا Window Functions در SQL Server قابلیتی قدرتمند هستند که به شما امکان می‌دهند محاسبات را روی مجموعه‌ای از سطرها که با سطر فعلی مرتبط‌اند، انجام دهید. برخلاف توابع تجمیعی (مانند SUM، AVG، COUNT) که سطرها را به یک خروجی واحد فشرده می‌کنند، توابع پنجره‌ای برای هر سطر یک مقدار بازمی‌گردانند و روی “پنجره‌ای” از سطرها که توسط عبارت OVER تعریف می‌شود، عمل می‌کنند. این ویژگی آن‌ها را برای وظایفی مانند محاسبه مجموع تجمعی، رتبه‌بندی، میانگین متحرک یا مقایسه مقادیر بین سطرها، فوق‌العاده مفید می‌سازد.

در حالی که هر دو عبارت GROUP BY و توابع پنجره‌ای با گروه‌هایی از سطرها سروکار دارند، تفاوت اساسی آن‌ها در خروجی‌شان است. GROUP BY سطرها را در یک سطر خلاصه‌شده برای هر گروه تجمیع می‌کند و به طور موثری تعداد سطرهای بازگردانده شده را کاهش می‌دهد. اما توابع پنجره‌ای تمام سطرهای اصلی را حفظ کرده و یک مقدار محاسبه شده را بر اساس “پنجره” خود به هر سطر اضافه می‌کنند. این تمایز برای تحلیل داده‌ها، جایی که به اطلاعات تجمیعی و همچنین سطرهای اصلی و جزئی نیاز دارید، بسیار حیاتی است.

قلب هر تابع پنجره‌ای، عبارت OVER است. این عبارت “پنجره” یا مجموعه سطرهایی را که تابع روی آن‌ها عمل می‌کند، تعریف می‌کند. عبارت OVER می‌تواند به چندین روش استفاده شود:

ساده‌ترین سینتکس، یک پنجره شامل تمام سطرهای مجموعه نتایج را ایجاد می‌کند:

Window_Function (expression) OVER ()

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

OVER (PARTITION BY <column(s)>)

برای مرتب‌سازی سطرها در هر بخش یا کل مجموعه نتایج، از ORDER BY استفاده می‌کنیم:

OVER (ORDER BY <column(s)>)

و برای تعریف یک فریم مشخص درون بخش فعلی یا مجموعه نتایج که تابع روی آن عمل کند، می‌توانیم ترکیب کاملی از PARTITION BY، ORDER BY و ROWS BETWEEN را به کار ببریم:

OVER (PARTITION BY <column(s)> ORDER BY <column(s)> ROWS BETWEEN <start_bound> AND <end_bound>)

برای مثال‌هایمان، از یک جدول نمونه با نام Sales استفاده می‌کنیم. این جدول شامل ستون‌های SaleID، SaleDate، Region، Product و Amount است.

برای ایجاد و پر کردن این جدول، از اسکریپت SQL زیر استفاده کنید:

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleDate DATE,
    Region VARCHAR(50),
    Product VARCHAR(50),
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, SaleDate, Region, Product, Amount) VALUES
(1, '2023-01-05', 'East', 'Laptop', 1200.00),
(2, '2023-01-10', 'West', 'Mouse', 25.00),
(3, '2023-01-12', 'East', 'Keyboard', 75.00),
(4, '2023-02-01', 'North', 'Monitor', 300.00),
(5, '2023-02-05', 'East', 'Laptop', 1500.00),
(6, '2023-02-10', 'West', 'Webcam', 50.00),
(7, '2023-03-01', 'North', 'Keyboard', 60.00),
(8, '2023-03-05', 'East', 'Mouse', 30.00),
(9, '2023-03-10', 'West', 'Laptop', 1000.00),
(10, '2023-03-15', 'East', 'Monitor', 250.00);

توابع رتبه‌بندی برای هر سطر درون بخش خود، یک رتبه اختصاص می‌دهند. توابع متداول شامل ROW_NUMBER، RANK، DENSE_RANK و NTILE هستند.

تابع ROW_NUMBER() یک شماره منحصر به فرد و متوالی به هر سطر درون بخش خود اختصاص می‌دهد که از عدد 1 شروع می‌شود:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    ROW_NUMBER() OVER (ORDER BY SaleDate) AS RowNum_Overall,
    ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SaleDate) AS RowNum_ByRegion
FROM Sales;

تابع RANK() یک رتبه به هر سطر درون بخش خود اختصاص می‌دهد. اگر سطرها در عبارت ORDER BY مقادیر یکسانی داشته باشند، رتبه یکسانی دریافت می‌کنند و رتبه بعدی نادیده گرفته می‌شود (gap ایجاد می‌کند):

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank_ByRegionAmount
FROM Sales;

تابع DENSE_RANK() مشابه RANK() عمل می‌کند، اما رتبه‌های متوالی را بدون هیچ فاصله‌ای (gap) اختصاص می‌دهد، حتی در صورت وجود مقادیر برابر (ties):

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank_ByRegionAmount
FROM Sales;

تابع NTILE(n) سطرها را در یک بخش به تعداد مشخصی از گروه‌ها (n) تقسیم می‌کند و به هر گروه یک شماره رتبه اختصاص می‌دهد. سطرهایی با رتبه یکسان در یک گروه قرار می‌گیرند. این مثال سطرها را به 3 گروه تقسیم می‌کند:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    NTILE(3) OVER (PARTITION BY Region ORDER BY Amount DESC) AS Ntile_ByRegionAmount
FROM Sales;

این توابع به شما امکان می‌دهند به مقداری از یک سطر قبلی یا بعدی، یا اولین/آخرین مقدار در پنجره دسترسی پیدا کنید.

تابع LAG(column, offset, default_value) مقدار یک عبارت را از سطری که با یک آفست مشخص، قبل از سطر فعلی در بخش خود قرار دارد، بازمی‌گرداند. offset تعداد سطرهای قبل را مشخص می‌کند و default_value در صورتی برگردانده می‌شود که offset از ابتدای بخش فراتر رود:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    LAG(Amount, 1, 0) OVER (PARTITION BY Region ORDER BY SaleDate) AS PreviousSaleAmount
FROM Sales;

تابع LEAD(column, offset, default_value) مقدار یک عبارت را از سطری که با یک آفست مشخص، بعد از سطر فعلی در بخش خود قرار دارد، بازمی‌گرداند:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    LEAD(Amount, 1, 0) OVER (PARTITION BY Region ORDER BY SaleDate) AS NextSaleAmount
FROM Sales;

تابع FIRST_VALUE(column) مقدار عبارت را از اولین سطر در فریم پنجره بازمی‌گرداند:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    FIRST_VALUE(Amount) OVER (PARTITION BY Region ORDER BY SaleDate) AS FirstSaleInRegion
FROM Sales;

تابع LAST_VALUE(column) مقدار عبارت را از آخرین سطر در فریم پنجره بازمی‌گرداند. برای اطمینان از دریافت آخرین مقدار در کل بخش، فریم پنجره باید به صراحت تعریف شود:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    LAST_VALUE(Amount) OVER (PARTITION BY Region ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleInRegion
FROM Sales;

توابع تجمیعی استاندارد مانند SUM، AVG، COUNT، MIN و MAX نیز می‌توانند در ترکیب با عبارت OVER به عنوان توابع پنجره‌ای استفاده شوند. این امکان به آن‌ها می‌دهد تا تجمیع‌ها را روی یک پنجره تعریف‌شده انجام دهند، بدون اینکه سطرها را فشرده کنند.

برای محاسبه مجموع تجمعی فروش برای هر منطقه، مرتب شده بر اساس تاریخ، از تابع SUM() به عنوان تابع پنجره‌ای استفاده می‌کنیم:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    SUM(Amount) OVER (PARTITION BY Region ORDER BY SaleDate) AS RunningTotalAmount
FROM Sales;

برای محاسبه میانگین متحرک 3 روزه از مبلغ فروش برای هر منطقه، می‌توان از AVG() با تعریف فریم پنجره استفاده کرد:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    AVG(Amount) OVER (PARTITION BY Region ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage3Day
FROM Sales;

به همین ترتیب، توابع COUNT()، MIN() و MAX() نیز می‌توانند به عنوان توابع پنجره‌ای به کار روند. به عنوان مثال، برای دریافت تعداد کل فروش در یک منطقه یا حداقل/حداکثر فروش در تاریخچه یک منطقه تا تاریخ فعلی:

SELECT
    SaleID,
    SaleDate,
    Region,
    Product,
    Amount,
    COUNT(SaleID) OVER (PARTITION BY Region) AS TotalSalesInRegion,
    MIN(Amount) OVER (PARTITION BY Region ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MinSaleUpToDate,
    MAX(Amount) OVER (PARTITION BY Region ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MaxSaleUpToDate
FROM Sales;