Window Function در SQL Server؛ تحلیل قدرتمند داده‌ها بدون پیچیده کردن Queryها

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

در گذشته برای انجام این نوع تحلیل‌ها معمولاً از روش‌هایی مانند Subquery، Self Join، Cursor و Temporary Table استفاده می‌شد. اگرچه این روش‌ها امکان پیاده‌سازی چنین گزارش‌هایی را فراهم می‌کردند، اما در بسیاری از مواقع باعث افزایش پیچیدگی Query و کاهش Performance می‌شدند.

مایکروسافت با معرفی Window Functions در SQL Server، راهکاری قدرتمند برای انجام محاسبات تحلیلی روی داده‌ها ارائه کرد. این قابلیت به توسعه‌دهندگان اجازه می‌دهد بدون حذف جزئیات رکوردها، محاسبات پیچیده را روی مجموعه‌ای از داده‌ها انجام دهند.

Window Function چیست؟

Window Function مجموعه‌ای از توابع در SQL Server است که امکان انجام محاسبات روی گروهی از رکوردهای مرتبط با رکورد جاری را فراهم می‌کند.

تفاوت اصلی این توابع با Aggregate Functionهایی مانند SUM، AVG و COUNT این است که نتیجه را خلاصه نمی‌کنند و رکوردهای اصلی را حفظ می‌کنند.

به عنوان مثال، با استفاده از GROUP BY فروش هر دپارتمان را به صورت خلاصه مشاهده می‌کنیم، اما با Window Function می‌توانیم فروش هر کارمند را همراه با مجموع فروش دپارتمان نمایش دهیم.

ساختار کلی استفاده از Window Function به شکل زیر است:

Function_Name ()
OVER
(
    PARTITION BY column
    ORDER BY column
)

عبارت PARTITION BY داده‌ها را به گروه‌های مختلف تقسیم می‌کند و ORDER BY ترتیب پردازش رکوردها را مشخص می‌کند.

مهم‌ترین Window Functionها در SQL Server

Window Functionها به چند گروه اصلی تقسیم می‌شوند که مهم‌ترین آن‌ها عبارت‌اند از:

• Ranking Functions
• Aggregate Window Functions
• Analytic Functions

توابع رتبه‌بندی (Ranking Functions)

یکی از رایج‌ترین کاربردهای Window Function، رتبه‌بندی داده‌ها است.

ROW_NUMBER ()

این تابع به هر رکورد یک شماره یکتا اختصاص می‌دهد.

مثال:

SELECT
EmployeeName,
Salary,
ROW_NUMBER () OVER (ORDER BY Salary DESC) AS RowNumber
FROM Employees

از این تابع معمولاً برای Pagination، شماره‌گذاری رکوردها و حذف داده‌های تکراری استفاده می‌شود.

تفاوت RANK و DENSE_RANK

هر دو تابع برای رتبه‌بندی استفاده می‌شوند، اما در برخورد با مقادیر تکراری تفاوت دارند.

جدول SalesPerson:

SalesPerson	Score
Ali	1000
Sara	900
Reza	900
Mina	800
Nima	700

استفاده از RANK():

تابع RANK() به هر رکورد بر اساس مقدار مرتب‌شده یک رتبه اختصاص می‌دهد.

SELECT
    SalesPerson,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS RankNumber
FROM SalesPerson

خروجی:

SalesPerson	Score	RankNumber
Ali	1000	1
Sara	900	2
Reza	900	2
Mina	800	4
Nima	700	5

همان‌طور که می‌بینید، چون دو نفر امتیاز 900 دارند، هر دو رتبه 2 گرفته‌اند و رتبه بعدی پرش می‌کند و از 4 شروع می‌شود.

استفاده از DENSE_RANK():

حالا همان مثال را با DENSE_RANK() اجرا می‌کنیم:

SELECT
    SalesPerson,
    Score,
       DENSE_RANK () OVER (ORDER BY Score DESC) AS DenseRankNumber
FROM SalesPerson

خروجی:

SalesPerson	Score	DenseRankNumber
Ali	1000	1
Sara	900	2
Reza	900	2
Mina	800	3
Nima	700	4

در اینجا بعد از رتبه 2، رتبه 3 استفاده می‌شود و هیچ پرشی وجود ندارد.

تفاوت اصلی RANK و DENSE_RANK

به صورت خلاصه:

تابع	رفتار با مقادیر تکراری
RANK()	رتبه‌ها را پرش می‌دهد
DENSE_RANK()	رتبه‌ها را پشت سر هم نگه می‌دارد

تابع NTILE

تابع NTILE داده‌ها را بر اساس تعداد مشخص‌شده به بخش‌های (Group/Bucket) تقریباً برابر تقسیم کرده و به هر رکورد شماره گروه اختصاص می‌دهد.

کاربرد اصلی این تابع در تحلیل‌های آماری، دسته‌بندی مشتریان (مانند چارک‌بندی یا دهک‌بندی) است.

SELECT
    SalesPerson,
    Score,
    NTILE(4) OVER (ORDER BY Score DESC) AS Quartile
FROM SalesPerson

توابع تجمیعی پنجره‌ای (Aggregate Window Functions)

توابع تجمیعی مانند SUM، AVG، COUNT، MIN و MAX می‌توانند همراه با عبارت OVER استفاده شوند تا بدون خلاصه‌سازی رکوردهای خروجی، محاسبات تحلیلی انجام دهند.

محاسبه مجموع تراکمی (Running Total) و سهم از کل

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

SELECT
    EmployeeName,
    DepartmentID,
    SalesAmount,
    SUM(SalesAmount) OVER (PARTITION BY DepartmentID ORDER BY OrderDate) AS RunningTotal,
    SUM(SalesAmount) OVER (PARTITION BY DepartmentID) AS TotalDeptSales,
    (SalesAmount * 100.0) / SUM(SalesAmount) OVER (PARTITION BY DepartmentID) AS SalesContributionPercentage
FROM EmployeeSales

مقایسه رکوردها با LAG و LEAD

یکی از ویژگی‌های قدرتمند Window Function، امکان مقایسه رکورد جاری با رکوردهای قبلی و بعدی است.

تابع LAG():

با استفاده از LAG می‌توان مقدار رکورد قبلی را مشاهده کرد.

برای مثال مقایسه فروش ماه جاری با ماه قبل:

SELECT
[Month],
Sales,
LAG (Sales) OVER (ORDER BY Month) AS PreviousSales
FROM MonthlySales

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

تابع LEAD():

تابع LEAD عملکردی برعکس LAG دارد و مقدار رکورد بعدی را نمایش می‌دهد. از آن برای تحلیل روند آینده، مقایسه دوره‌های زمانی و بررسی تغییرات داده‌ها استفاده می‌شود.

توابع FIRST_VALUE و LAST_VALUE

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

SELECT
    EmployeeName,
    DepartmentID,
    Salary,
    FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS HighestSalaryInDept,
    LAST_VALUE(Salary) OVER (
        PARTITION BY DepartmentID 
        ORDER BY Salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LowestSalaryInDept
FROM Employees

مفهوم محدوده پنجره (Frame Specification: ROWS & RANGE)

هنگام استفاده از توابع تجمیعی همراه با ORDER BY، SQL Server به صورت پیش‌فرض یک محدوده (Frame) در نظر می‌گیرد. با استفاده از عبارات ROWS و RANGE می‌توان به صورت دقیق مشخص کرد که محاسبه شامل چه رکوردهایی نسبت به رکورد جاری شود.

تفاوت ROWS و RANGE

• ROWS: محاسبات را بر اساس تعداد فیزیکی سطرها انجام می‌دهد.
• RANGE: محاسبات را بر اساس مقادیر منطقی ستون ORDER BY انجام می‌دهد (در صورت وجود مقادیر تکراری، همه آن‌ها را هم‌زمان در نظر می‌گیرد).

سناریوی میانگین متحرک (Moving Average)

برای محاسبه میانگین متحرک ۳ روزه (روز جاری و ۲ روز قبل)، از کلاوز ROWS استفاده می‌کنیم:

SELECT
    SalesDate,
    DailySales,
    AVG(DailySales) OVER (
        ORDER BY SalesDate 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Days
FROM DailySalesReport

سناریوهای واقعی و کاربردی در پروژه‌ها

حذف داده‌های تکراری (Deduplication) با استفاده از CTE و ROW_NUMBER

یکی از تمیزترین روش‌ها برای شناسایی و حذف رکوردهای تکراری (Deduplication) در SQL Server استفاده از ROW_NUMBER است:

WITH DuplicateCTE AS (
    SELECT 
        CustomerID, 
        Email, 
        CreatedDate,
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CreatedDate DESC) AS RowNum
    FROM Customers
)
DELETE FROM DuplicateCTE WHERE RowNum > 1;

محاسبه مانده تراکمی حساب (Bank Account Balance)

در سیستم‌های مالی و حسابداری، محاسبه مانده حساب بعد از هر تراکنش بدون نیاز به Cursor به شکل زیر پیاده‌سازی می‌شود:

SELECT
    TransactionID,
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY AccountID 
        ORDER BY TransactionDate, TransactionID 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningBalance
FROM AccountTransactions

تأثیر Window Function بر Performance

اگرچه Window Functionها باعث ساده‌تر شدن Queryها می‌شوند، اما در حجم‌های بزرگ داده باید به Performance توجه داشت.

SQL Server هنگام اجرای این توابع ممکن است عملیات‌هایی مانند:

• Sort
• Segment
• Window Aggregate

را در Execution Plan ایجاد کند.

برای بهبود Performance بهتر است:

• روی ستون‌های مورد استفاده در PARTITION BY و ORDER BY ایندکس مناسب ایجاد شود.
• Execution Plan بررسی شود.
• از محاسبات غیرضروری روی حجم زیاد داده جلوگیری شود.

کاربرد Window Function در پروژه‌های واقعی

Window Functionها امروزه در بسیاری از سیستم‌های حرفه‌ای استفاده می‌شوند:

سیستم‌های بانکی

• تحلیل تراکنش‌ها
• رتبه‌بندی مشتریان
• محاسبه مانده حساب

Data Warehouse

• گزارش‌های تحلیلی
• محاسبه KPI
• تحلیل روندهای زمانی

سیستم‌های فروش

• رتبه‌بندی محصولات
• بررسی رشد فروش
• تحلیل رفتار مشتریان

سخن پایانی

Window Functionها یکی از مهم‌ترین قابلیت‌های SQL Server برای انجام تحلیل‌های پیشرفته روی داده‌ها هستند. این قابلیت‌ها به توسعه‌دهندگان اجازه می‌دهند Queryهای پیچیده را ساده‌تر، خواناتر و قابل نگهداری‌تر بنویسند.

در حوزه آموزش دیتابیس، تسلط بر توابعی مانند:

ROW_NUMBER، RANK، DENSE_RANK، SUM OVER، LAG، LEAD، NTILE، FIRST_VALUE، LAST_VALUE و مفاهیمی مانند Window Framing (ROWS/RANGE)

برای هر SQL Developer، DBA و متخصص Performance Tuning یک مهارت ضروری محسوب می‌شود.

استفاده صحیح از Window Functionها می‌تواند جایگزین مناسبی برای بسیاری از روش‌های قدیمی مانند Cursor و Self Join باشد و نقش مهمی در طراحی Queryهای حرفه‌ای SQL Server ایفا کند.