در دنیای پایگاه‌داده و تحلیل داده‌ها، یکی از پرکاربردترین ابزارها برای انجام محاسبات تحلیلی بدون نیاز به JOINهای پیچیده، Window Functionها یا «توابع پنجره‌ای» در SQL هستند. این توابع امکان انجام عملیات‌هایی مانند محاسبه رتبه، میانگین، جمع و شمارش را در محدوده‌ای مشخص از داده‌ها فراهم می‌کنند، بدون اینکه نیاز باشد داده‌ها را در گروه‌های جداگانه تقسیم کنیم. Window Function در SQL در واقع پلی میان توابع تجمعی (Aggregate) و سطر به سطر هستند و قدرت بسیار زیادی در نوشتن Queryهای تحلیلی ایجاد می‌کنند.

Window Function در SQL چیست؟

Window Function در SQL یا تابع پنجره‌ای، تابعی است که روی مجموعه‌ای از ردیف‌ها (که به آن‌ها Window می‌گویند) اعمال می‌شود و برای هر ردیف خروجی جداگانه‌ای تولید می‌کند. تفاوت اصلی آن با توابع تجمعی معمولی این است که Window Functionها مقدار کل را در کنار جزئیات هر ردیف نمایش می‌دهند.
به عنوان مثال، می‌توان برای هر کارمند در یک شرکت، میانگین حقوق کل کارکنان همان دپارتمان را محاسبه کرد و همچنان اطلاعات مربوط به هر کارمند را حفظ نمود.

ساختار کلی Window Function در SQL

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

عبارت OVER() بخش اصلی این تابع است که محدوده داده‌ها (Window) را تعیین می‌کند.

• PARTITION BY: داده‌ها را به گروه‌هایی تقسیم می‌کند.

• ORDER BY: ترتیب ردیف‌ها را در هر گروه مشخص می‌کند.
  بدون استفاده از OVER، این توابع مانند توابع تجمعی رفتار می‌کنند، ولی با آن، هر ردیف تحلیل جداگانه‌ای دارد.

ROW_NUMBER(): شماره‌گذاری ردیف‌ها

تابع ROW_NUMBER() یکی از پرکاربردترین Window Function در SQL است. این تابع، به هر ردیف در مجموعه داده‌ها یک شماره یکتا اختصاص می‌دهد. اگر از ORDER BY درون OVER استفاده شود، شماره‌ها بر اساس آن ترتیب تعیین می‌شوند.

مثال:

در این مثال، کارمندی با بالاترین حقوق شماره 1 می‌گیرد.
کاربرد مهم ROW_NUMBER() در حذف داده‌های تکراری یا استخراج n ردیف برتر از داده‌هاست.

RANK(): رتبه‌بندی با پرش عددی

تابع RANK() برای رتبه‌بندی استفاده می‌شود، اما تفاوتش با ROW_NUMBER در نحوه‌ی تخصیص شماره‌هاست.
وقتی دو ردیف مقدار مساوی در ORDER BY دارند، RANK() به هر دو رتبه‌ی یکسان می‌دهد و رتبه‌ی بعدی را با پرش عددی ادامه می‌دهد.

مثال:

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

DENSE_RANK(): رتبه‌بندی بدون پرش عددی

تابع DENSE_RANK() دقیقاً مانند RANK عمل می‌کند با این تفاوت که در آن پرشی وجود ندارد.
اگر دو مقدار مشابه وجود داشته باشد، رتبه‌ی بعدی به صورت متوالی محاسبه می‌شود.

مثال:

در اینجا رتبه بعد از تساوی، به صورت متوالی (بدون پرش) تعیین شده است.
کاربرد DENSE_RANK در زمانی است که می‌خواهیم رتبه‌ها متوالی باشند و پرش بین اعداد اتفاق نیفتد.

NTILE(): تقسیم داده‌ها به گروه‌ها

تابع NTILE(n) داده‌ها را به تعداد n بخش تقریباً مساوی تقسیم می‌کند و شماره گروه را برای هر ردیف برمی‌گرداند.
این تابع در تحلیل‌های آماری بسیار مفید است، مثلاً برای تقسیم مشتریان به 4 بخش (ربعک‌ها یا Quartiles) بر اساس درآمد.

مثال:

LAG() و LEAD(): مقایسه ردیف‌ها

دو تابع LAG() و LEAD() برای دسترسی به مقدار ردیف‌های قبلی و بعدی در همان Window استفاده می‌شوند.

• LAG() مقدار ردیف قبلی را برمی‌گرداند.

• LEAD() مقدار ردیف بعدی را برمی‌گرداند.

مثلاً برای محاسبه تفاوت حقوق فعلی با حقوق قبلی:

این توابع در تحلیل روند تغییرات (Trend Analysis) بسیار پرکاربرد هستند.

SUM(), AVG(), COUNT() به عنوان Window Function

توابع تجمعی مانند SUM()، AVG() و COUNT() هم می‌توانند به صورت Window Function در SQL استفاده شوند.
به عنوان مثال، برای محاسبه مجموع حقوق به تفکیک دپارتمان، بدون گروه‌بندی کل داده‌ها:

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

کاربردهای عملی Window Function در SQL

1. محاسبه رتبه و امتیاز بازیکنان در بازی‌ها

2. محاسبه میانگین متحرک در داده‌های مالی

3. تحلیل روند فروش در بازه‌های زمانی

4. محاسبه فاصله زمانی بین رویدادها

5. حذف داده‌های تکراری با ROW_NUMBER()

نکات مهم در استفاده از Window Function در SQL

• همیشه از ORDER BY درون OVER استفاده کنید تا نتیجه قابل پیش‌بینی باشد.

• Window Functionها بعد از WHERE و GROUP BY ولی قبل از ORDER BY اصلی Query اعمال می‌شوند.

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

نتیجه‌گیری

Window Function در SQL یکی از قدرتمندترین ابزارهای SQL هستند که به شما امکان می‌دهند داده‌ها را به‌صورت تحلیلی، دقیق و انعطاف‌پذیر بررسی کنید. توابعی مثل ROW_NUMBER()، RANK()، DENSE_RANK()، LAG()، LEAD() و NTILE() باعث می‌شوند بتوانید بدون نیاز به Queryهای پیچیده، گزارش‌ها و تحلیل‌های سطح بالا ایجاد کنید. با درک درست از آن‌ها، Queryهای شما نه‌تنها کارآمدتر بلکه بسیار حرفه‌ای‌تر خواهند شد.